Snowflake

The first step in querying your Snowflake instance is to connect FactBranch to your Snowflake instance. You do this by creating a Snowflake authentication in FactBranch.

Once you've entered your credentials in the authentication, they are stored on FactBranch's servers, encrypted-at-rest and the password will never be shown again anywhere in your FactBranch account.

You can, however, use the authentication in your nodes. This allows you to save the credentials once and then re-use it in several nodes. You can even let another team member create nodes and use the authentication without them seeing the credentials.

In this article you'll learn:

Creating a read-only role in Snowflake

We recommend to create a role and a user for FactBranch only and only grant the role rights it needs to run your query. Typically this means giving read-only access on the tables you will query.

This is a basic example of how to create an appropriate user and role in your Snowflake instance. Use this as a starting point depending on your own requirements.

First, create a role for FactBranch:

CREATE ROLE IF NOT EXISTS fb_readonly_role

Creating a user in Snowflake

Create a database user for FactBranch. Replace all values surrounded by <...> with your own values.

CREATE USER fb_user
    password = '<PASSWORD>'
    first_name = 'FactBranch'
    last_name = 'User'
    default_warehouse = '<WAREHOUSE>'
    default_namespace = '<DATABASE.SCHEMA>'
    default_role = 'fb_readonly_role'

Granting read-access to the role

Grant the role we've created above to the user, allow the role to use your warehouse and grant read-access to the role for the database, schema, tables and views. Again, replace all values surrounded by <...> with your own values.

GRANT ROLE fb_readonly_role TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE <your_warehouse> TO ROLE fb_readonly_role;
GRANT ROLE fb_readonly_role TO USER fb_user;

GRANT USAGE ON DATABASE "<your_database>" TO ROLE fb_readonly_role;
GRANT USAGE ON SCHEMA "<your_database>"."<your_schema>" TO ROLE fb_readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA "<your_database>"."<your_schema>" TO ROLE fb_readonly_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA "<your_database>"."<your_schema>" TO ROLE fb_readonly_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA "<your_database>"."<your_schema>" TO ROLE fb_readonly_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<your_database>"."<your_schema>" TO ROLE fb_readonly_role;

Connecting FactBranch to your Snowflake instance

To create a Snowflake authentication in FactBranch, click on Manage authentications in your Flows dashboard, click on Create authentication, then select Snowflake. You'll be forwarded to the credentials form.

Account identifier

The URL for your Snowflake instance looks something like this: https://<account_locator>.<region>.<cloud>.snowflakecomputing.com - the account identifier is the part between https:// and .snowflakecomputing.com and in this example would be <account_locator>.<region>.<cloud>

Database name

The database you want to query and also where you've created the user and role above.

User, password, role

You've created user and role in the previous steps. If you are following this tutorial, it will be fb_user and fb_readonly_role

Use a static IP when making database requests

Keep this checkbox checked. FactBranch will use a single IP to query your Snowflake data warehouse and in the yellow box below the checkbox you'll see the IP address. In your Snowflake firewall allow access from this IP address.

Once you've filled out everything, click Connect. FactBranch will test the credentials you've entered. If it was successful, you'll be forwarded to the list of authentications.

You can now use this authentication in a Snowflake node to run an SQL query in your Snowflake data warehouse.

Renaming the authentication

To rename the authentication, either click on Rename next to the title, or double-click on the title itself. Then enter the new name and click on Save or hit Enter on your keyboard. To revert to the old name, hit the Escape key.

Using the authentication in a Snowflake node

First create a Snowflake node in one of your Flows. Edit the node by clicking Edit next to the node in the Flow Editor. Select the Authentication tab on the left side of the screen. Then click on Select an authentication... - or on change... if that node already has an authentication associated. Select the authentication you'd like to use and from now on this node will use for all its requests the credentials you've stored in the authentication.