Snowflake

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

FactBranch connects to Snowflake with key-pair authentication, because Snowflake no longer allows passwords for users that applications log in with. FactBranch creates the key pair for you. The private key stays on FactBranch's servers, encrypted at rest, and is never shown anywhere in your FactBranch account. You only register the public key with your Snowflake user, with one SQL statement that FactBranch shows you.

You can 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 data source 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 user for FactBranch. TYPE = SERVICE marks it as a user for an application: it cannot log in with a password or to the Snowsight web interface, and Snowflake does not ask it for a second factor. Replace all values surrounded by <...> with your own values.

CREATE USER fb_user
    type = SERVICE
    default_warehouse = '<WAREHOUSE>'
    default_namespace = '<DATABASE.SCHEMA>'
    default_role = 'fb_readonly_role'

The user has no public key yet. You add it after you have connected FactBranch, in the step Registering the public key in Snowflake.

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 Add data source in your Data Sources dashboard, then select Snowflake. You'll be forwarded to the credentials form.

Account identifier

In Snowsight, open the user menu by selecting your user name, then select Connect a tool to Snowflake. The Account Details dialog shows the account identifier in the form ORGNAME-ACCOUNTNAME, for example ABCDEFG-XY12345. Enter it as shown. You can also read it from your account URL https://ORGNAME-ACCOUNTNAME.snowflakecomputing.com: the account identifier is the part between https:// and .snowflakecomputing.com.

Database name

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

User, 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 Save and generate key pair. FactBranch creates the key pair and shows you the statement for the next step.

Registering the public key in Snowflake

Below the form, FactBranch shows an ALTER USER statement with the public key, for example:

ALTER USER fb_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Click Copy statement and run it in Snowflake as a user who may change fb_user, for example with the role SECURITYADMIN.

To check that Snowflake has the right public key, run DESC USER fb_user and compare the value of RSA_PUBLIC_KEY_FP with the fingerprint of the public key that FactBranch shows below the statement. The two must be equal.

Then click Test connection. FactBranch logs in to Snowflake with the private key and runs SELECT 1. If that works, FactBranch shows FactBranch connected to Snowflake and you can click Continue. If it does not work, FactBranch shows the error message from Snowflake. The most frequent cause is that the ALTER USER statement has not run yet, or ran for another user.

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

Allowing access only from FactBranch's IP address

If you keep Use a static IP when making database requests checked, you can make Snowflake accept logins of fb_user only from that address. Replace <IP address> with the address from the yellow box below the checkbox:

CREATE NETWORK POLICY fb_only_policy ALLOWED_IP_LIST = ('<IP address>');
ALTER USER fb_user SET NETWORK_POLICY = fb_only_policy;

Replacing the key pair

To replace the key pair, for example if your security policy requires regular key rotation, click Generate new key pair below the statement and confirm.

FactBranch deletes the current private key immediately. Until you run the new ALTER USER statement, which registers the new public key in Snowflake, every flow that uses this data source fails. Run the new statement right away, then click Test connection.

Renaming the data source

To rename the data source, 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.