How to set up a Snowflake Data Pool

This guide explains how to connect Propel to your Snowflake data warehouse for creating customer-facing analytics dashboards.

It covers how to:

1. Ensure change tracking is enabled on your Snowflake tables.
2. Create a Snowflake user, role, and warehouse.
3. (Optional) Configure Snowflake Network Policy.
4. 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: 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 2: 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 3: (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:

3.17.239.162
3.15.73.135
18.219.73.236

Step 4: 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.

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

Click Add new credentials:

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

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:

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

Then select primary timestamp and unique ID.

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

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

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.