How to set up a Snowflake Data Pool

This guide explains how to connect Snowflake to Propel's Serverless ClickHouse.

It covers how to:

Find your Snowflake Account Identifier
Ensure change tracking is enabled on your Snowflake tables.
Create a Snowflake user, role, and warehouse.
(Optional) Configure Snowflake Network Policy.
Create a Data Pool in the Propel Console.

Requirements

You have a Propel account.
You have a Snowflake account.
You have a Snowflake user with the SECURITYADMIN, SYSADMIN, and ACCOUNTADMIN system-defined roles.
You have the data you want to use with Propel in a Snowflake schema in a database.

Step 1: Find your Snowflake Account Identifier

Your Snowflake Account Identifier is a string composed of your account locator, region, and cloud.

⚠️Important

The Snowflake Account Identifier is different from the Snowflake Account Name.

Here is how to find your Snowflake Account Identifier:

Option 1: Using SQL

To find your Snowflake Account Identifier, run the following queries in your Snowflake worksheet:

SELECT CURRENT_ACCOUNT();

and

SELECT LOWER(REPLACE(CURRENT_REGION(), '_', '-')) AS region_in_lower_case;

Option 2: Using the UI

Using the UI, you can find your Snowflake Account Identifier by clicking on your account menu on the bottom left.

A screenshot demonstrating how to find your Snowflake Account Identifier in the Snowflake Console.

The Snowflake Account Identifier is a string in the following format depending on the region and cloud:

<account_locator> or
<account_locator>.<cloud_region_id> or
<account_locator>.<cloud_region_id>.<cloud>

Where:

cloud_region_id is the identifier for the cloud region (dictated by the cloud platform).
cloud is the identifier for the cloud platform (aws, azure, or gcp).

The following are examples of the Snowflake Account Locator for different regions and clouds, for the full list see the Snowflake documentation.

North American regions:

AWS US West (Oregon): xy12345 (No region, no cloud)
AWS US East (N. Virginia): xy12345.us-east-1 (No cloud)
AWS US East (Ohio): xy12345.us-east-2.aws
GCP US Central1 (Iowa): xy12345.us-central1.gcp
GCP US East4 (N. Virginia): xy12345.us-east4.gcp
AWS Canada (Central): xy12345.ca-central-1.aws
Azure West US 2 (Washington): xy12345.west-us-2.azure
Azure Central US (Iowa): xy12345.central-us.azure
Azure South Central US (Texas): xy12345.south-central-us.azure
Azure East US 2 (Virginia): xy12345.east-us-2.azure
Azure Canada Central (Toronto): xy12345.canada-central.azure

European regions:

AWS EU (Ireland): xy12345.eu-west-1 (No cloud)
AWS EU (London): xy12345.eu-west-2.aws
AWS EU (Paris): xy12345.eu-west-3.aws
AWS EU (Frankfurt): xy12345.eu-central-1 (No cloud)
AWS EU (Zurich): xy12345.eu-central-2.aws
AWS EU (Stockholm): xy12345.eu-north-1.aws
GCP Europe West2 (London): xy12345.europe-west2.gcp
GCP Europe West4 (Netherlands): xy12345.europe-west4.gcp
Azure UK South (London): xy12345.uk-south.azure
Azure North Europe (Ireland): xy12345.north-europe.azure
Azure West Europe (Netherlands): xy12345.west-europe.azure
Azure Switzerland North (Zurich): xy12345.switzerland-north.azure

South American regions:

AWS South America (Sao Paulo): xy12345.sa-east-1.aws

Asian regions:

AWS Asia Pacific (Tokyo): xy12345.ap-northeast-1.aws
AWS Asia Pacific (Osaka): xy12345.ap-northeast-3.aws
AWS Asia Pacific (Seoul): xy12345.ap-northeast-2.aws
AWS Asia Pacific (Mumbai): xy12345.ap-south-1.aws
AWS Asia Pacific (Singapore): xy12345.ap-southeast-1 (No cloud)
AWS Asia Pacific (Sydney): xy12345.ap-southeast-2 (No cloud)
AWS Asia Pacific (Jakarta): xy12345.ap-southeast-3.aws
Azure Asia Pacific (Singapore): xy12345.ap-southeast-1
Azure Asia Pacific (Sydney): xy12345.ap-southeast-2
Azure Asia Pacific (Jakarta): xy12345.ap-southeast-3
Azure Central India (Pune): xy12345.central-india.azure
Azure Japan East (Tokyo): xy12345.japan-east.azure
Azure Southeast Asia (Singapore): xy12345.southeast-asia.azure

⚠️Important

The account identifier does not include the snowflakecomputing.com domain.

Save your Snowflake Account Identifier and region as you will need them in the next steps.

Step 2: Ensure change tracking is enabled

Before setting up a Snowflake Data Pool (Propel's high-speed data store and cache), it's important to make sure CHANGE_TRACKING is enabled on the tables that you want to use as the underlying data.

You can enable it with the following command:

ALTER TABLE YOUR_TABLE_NAME SET CHANGE_TRACKING = TRUE;

Step 3: Create the Snowflake user, role, and warehouse

Next, you need to create the Snowflake user, role, and warehouse for Propel.

We recommend creating a dedicated user, role, and warehouse for Propel. By creating a dedicated role and user, you can apply the principle of least privilege to give it only the necessary privileges. Additionally, by having a dedicated warehouse, you can monitor and control costs and ensure you have the necessary compute resources to operate the integration.

Copy the script below to a new Snowflake worksheet and select the "All Queries" checkbox.

Important

Make sure you replace the values for the user_password, database_name, and schema_name variables with the following values before running the script.

user_password it must be at least 8 characters long, contain at least 1 digit, 1 uppercase letter and 1 lowercase letter
database_name with the name of the Snowflake database where your schema is located, for example ANALYTICS.
schema_name with the database and schema name where your tables are located, for example ANALYTICS.PUBLIC.

Important

You will need to run this script with a user that has SECURITYADMIN and ACCOUNTADMIN system-defined roles.

begin;

    use role accountadmin;
    /* Create variables for user, password, role, warehouse, database, and schema (needs to be uppercase for objects) */
    set role_name = 'PROPELLER';
    set user_name = 'PROPEL_USER';
    set warehouse_name = 'PROPELLING';

    /* Must be at least 8 characters long, contain at least 1 digit, 1 uppercase letter and 1 lowercase letter */
    set user_password = '';               /* Replace with a strong password */
    set database_name = 'ANALYTICS';      /* Replace with your Snowflake database name */
    set schema_name = 'ANALYTICS.PUBLIC'; /* Replace with your Snowflake schema name */

    /* Grant sysadmin role access to the database */
    grant usage,modify
    on database identifier($database_name)
    to role sysadmin;

    /* Grant sysadmin role access to the schema */
    grant usage,modify
    on schema identifier($schema_name)
    to role sysadmin;

    /* Change role to securityadmin for user / role steps */
    use role securityadmin;

    /* Create a role for Propel */
    create role if not exists identifier($role_name);
    grant role identifier($role_name) to role SYSADMIN;

     /* Change role to sysadmin for warehouse and database steps */
    use role sysadmin;

    /* Create a warehouse for Propel */
    create warehouse if not exists identifier($warehouse_name)
    warehouse_size = xsmall
    warehouse_type = standard
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

    /* Change role to securityadmin for user / role steps */
    use role securityadmin;

    /* Create a user for Propel */
    create user if not exists identifier($user_name)
    password = $user_password
    default_role = $role_name
    default_warehouse = $warehouse_name;

    grant role identifier($role_name) to user identifier($user_name);

    /* Change role to accountadmin for warehouse and database steps */
    use role accountadmin;

    /* Grant Propel role access to the warehouse */
    grant usage, monitor
    on warehouse identifier($warehouse_name)
    to role identifier($role_name);

    /* Grant Propel role access to the database */
    grant usage, monitor
    on database identifier($database_name)
    to role identifier($role_name);

    /* Grant Propel role access to the schema */
    grant create procedure, create stage, create task, create stream, usage
    on schema identifier($schema_name)
    to role identifier($role_name);

    /* Grant Propel role select on all tables in the schema */
    grant select on all tables
    in schema identifier($schema_name)
    to role identifier($role_name);

    /* Grant Propel role select on all future tables in the schema */
    grant select on future tables
    in schema identifier($schema_name)
    to role identifier($role_name);

    grant execute task on account
    to role identifier($role_name);

commit;

Step 4: (Optional) Configure Snowflake Network Policy

If you have defined a Snowflake Network Policy, you need to update it to include the Propel IP address by following the instructions on modifying network policies.

Propel IP Addresses:

17.239.162
15.73.135
219.73.236

Step 5: Create a Data Pool in the Propel Console

Now that you have created the Snowflake user, role, and warehouse, you can create a Data Pool in the Propel Console.

First, on the left-hand side menu, click on Data Pools. Then click on the plus sign (+) to create a new Data Pool.

A screenshot demonstrating how to create a new Data Pool in the Propel Console.

Click Snowflake on the next screen to select it as a data source.

A screenshot demonstrating how to select Snowflake to create a new Data Pool in the Propel Console.

Click Add new credentials:

A screenshot demonstrating where to add new credentials for a new Data Pool in the Propel Console.

Then, enter a unique credentials name, as well as account, database, schema, warehouse, role, user, and password.

A screenshot demonstrating how to create new credentials for a new Snowflake Data Pool in the Propel Console.

note

Your Snowflake account should be in the following format "accountId.region.cloud", for example: "fn25463.us-east-2.aws". Do not include the snowflakecomputing.com domain.

When you've added all of those details, click Create and test Credentials.

Then you should see a “Connected” status message:

A screenshot demonstrating how the Snowflake credentials are tested in the Propel Console.

Continue with the setup flow, and then select the table as well as the columns you want to use.

A screenshot demonstrating how to select a table for a new Snowflake Data Pool in the Propel Console.

Then select primary timestamp and unique ID.

A screenshot demonstrating how to select the select primary timestamp and Unique ID for a new Snowflake Data Pool in the Propel Console.

Click Next and make sure it’s set up correctly.

A screenshot demonstrating how validate a new Snowflake Data Pool is set up correctly in the Propel Console.

Then you can click Preview Data to make sure your data is loaded correctly.

A screenshot demonstrating how to preview data in a Data Pool in the Propel Console.

Wrap up

That's it! To recap, we created a Snowflake Data Pool in Propel syncing from a table in your Snowflake account and verified that it arrived in the Data Pool successfully.

What's next?

You can learn more about using the GraphQL API you set up and check the examples.

Learn about Defining Metrics.
Learn about the APIs to Query Your Data.

How to set up a Snowflake Data Pool

Requirements​

Step 1: Find your Snowflake Account Identifier​

Option 1: Using SQL​

Option 2: Using the UI​

Step 2: Ensure change tracking is enabled​

Step 3: Create the Snowflake user, role, and warehouse​

Step 4: (Optional) Configure Snowflake Network Policy​

Step 5: Create a Data Pool in the Propel Console​

Wrap up​

What's next?​