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, you need to allowlist the Preset IP addresses to ensure the connection with Snowflake.

In Snowflake, allowlist Preset IPs by running:

CREATE NETWORK POLICY PRESET_WHITELIST
ALLOWED_IP_LIST = ('x.x.x.x', 'x.x.x.x', 'x.x.x.x')

...with x.x.x.x being the relevant IP address based on the region where Preset Cloud is deployed:

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.

Check Snowflake User Used

When a new user is created on Snowflake, a password reset might be required on the initial login or use. Make sure the account that will be used to create the connection has a definitive password (is not pending a password update).

Connect Database to Preset

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

...then, in the Connect a database window, select the Snowflake card.

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

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

Connect using username and password

Please note that all fields below are required.

In the Database Name field, enter the name of the database that you are connecting to.

In the Username and Password fields, enter your user credentials.

In the Display Name field, enter a nickname for the database for display in Preset; by default, this is "Snowflake."

In the Account field, enter the account name of the database that you are connecting to. Refer to the Snowflake documentation to identify the structure to your Cloud Provider and Region. The account identifier can vary by region.

In the Warehouse and Role fields, enter the relevant warehouse (e.g., compute_wh) and role (e.g., accountadmin) for the connection.

When done, select Connect.

Step 3 of 3 will appear. 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"
    }
 }