Snowflake

Overview

In this article we will discuss how to connect a Snowflake database to Preset. So let's get started!

Allowlist Preset IPs

Before starting, validate your Snowflake Network Policies configuration, to ensure that Preset IP addresses are allowed to access Snowflake. Below IPs should be allowed (according to the workspace region):

If you are not sure where your Preset workspace is located, you can refer to the URL on your browser when accessing Preset. It should look like this: https://xxxxxxxx.us2a.app.preset.io/superset..., where us2a means it is in us-east-1.

Connect Database to Preset

Let's start by selecting + Database — have a look at Connecting your Data if you need help with this step.

Select Snowflake.

The Connect a database panel appears. There are three supported authentication methods to connect to Snowflake:

• Connect using username and password
• Connect using an encrypted key pair
• Connect using OAuth

Connect using username and password

Fill the form with information from Snowflake:

• Database Name with name of the database that you are connecting to
• Username and Password with the credentials for the user
• Display Name with the name to be used on Preset for this connection
• Account with the account identifier. Refer to the Snowflake documentation to identify the structure to your Cloud Provider and Region.
• Warehouse and Role with the relevant warehouse (e.g., COMPUTE_WH) and role (e.g., ACCOUNTADMIN) that should be used when running queries.

Click on Connect to create the connection.

At this stage, you can optionally configure Advanced options, if needed. To learn more, please see Advanced Connection Settings.

To finalize the connection, select Finish. Great job!

Connect using an encrypted key pair

It's also possible to connect to Snowflake authenticating with an encripted key pair instead of a password, using a SQLAlchemy String URI.

In the Display Name field, enter a meaningful name for the new connection to Snowflake.
In the SQLAlchemy URI field, customize your URI connection string using the following syntax:

snowflake://<Username>@<Account>/<Database>?role=<Role>&warehouse=<Warehouse>

Then, navigate to the ADVANCED tab, expand the Security section and use add below JSON structure to the SECURE EXTRA field:

{
    "auth_method": "keypair",
    "auth_params": {
        "privatekey_body": "Your Private Key",
        "privatekey_pass":"Your Private Key Password"
    }
}

Note that the privatekey_body should receive a single line value, so replace line breaks with \n. For example:

{
     "auth_method": "keypair",
     "auth_params": {
         "privatekey_body": "-----BEGIN ENCRYPTED PRIVATE KEY-----\n...\n...\n-----END ENCRYPTED PRIVATE KEY-----",
         "privatekey_pass":"my_password"
    }
 }

Navigate back to the Basic tab, and click on Test Connection to validate the information is correct. Lastly, click on Connect to save the connection.

Connect using OAuth

The OAuth integration allows Preset to run queries while impersonating users. When implemented, users are prompted to grant consent to the integration, which generates a token to be used for each user. Refer to the Snowflake documentation for additional context.

Setting up the OAuth integration in Snowflake

Below SQL query can be used to set up an OAuth integration in Snowflake named PRESET:

CREATE SECURITY INTEGRATION PRESET
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
    OAUTH_USE_SECONDARY_ROLES = 'IMPLICIT'
    OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE
    OAUTH_REDIRECT_URI = 'https://${WORKSPACE}/api/v1/database/oauth2/';

Replace ${WORKSPACE} with the Workspace domain URL you'll connect to Snowflake. The structure is https://${WorksapceSlug}.${WorkspaceRegion}.app.preset.io.

Once the integration is created, run below SQL query to get the required information:

SELECT 
JSON_EXTRACT_PATH_TEXT(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PRESET'), 'OAUTH_CLIENT_ID') as "Client ID",
JSON_EXTRACT_PATH_TEXT(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('PRESET'), 'OAUTH_CLIENT_SECRET') as "Client Secret";

Fill the Snowflake connection form in Preset:

• Database Name with name of the database that you are connecting to
• Display Name with the name to be used on Preset for this connection
• Account with the account identifier. Refer to the Snowflake documentation to identify the structure to your Cloud Provider and Region.
• Warehouse with the warehouse that should be used for the connection.

Then, expand the OAuth2 client information section and provide:

• The Client ID and Client Secret values retrieved with the SQL query
• Use https://${ACCOUNT-URL}/oauth/authorize as the Authorization Request URI
• Use https://${ACCOUNT-URL}/oauth/token-request as the Token Request URI
• Use refresh_token in the Scope field. Note that you can also optionally limit the role that's used by the integration.

Click on the Connect button. If the information is correct, the connection is created and you are redirected to the Advanced tab. Expand the Security section and enable the Impersonate logged in user option, and finally click on FINISH.

To test the integration:

1. Navigate to SQL > SQL Lab.
2. Select the Snowflake connection in the Database dropdown.
3. You should see an Authorization needed error. Click on See more.
4. Click on the provide authorization hyperlink to be reditected to Snowflake. Authenticate using your Snowflake credentials and grant consent for the integration.
5. After authentication, you're redirected back to Preset. Refresh the page.

Queries should now be properly authenticated with your OAuth token. Note that this Authorization needed prompt will be displayed to all users the first time they interact with this database (either in SQL Lab or via charts).