How to set up a Snowflake Data Source

This guide explains how to connect Propel to your Snowflake data warehouse.

It covers how to create the Snowflake user, role, and warehouse for Propel, how to create the Data Source in the Propel Console, and how to know if it is working.

1. Create the Snowflake user, role, and warehouse
2. Create the Data Source 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: Create the Snowflake user, role, and warehouse

As a first step, you need to create the Snowflake user, role, and warehouse for Propel.

We recommend that you create 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 privileges it needs. Additionally, by having a dedicated warehouse, you can monitor and control costs, as well as ensure you have the necessary compute 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.
• schema_name with the schema name where your tables are located, for example ["ANALYTICS"."PUBLIC"]. :::

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 2: Create the Data Source in the Propel Console

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

• On the left hand side menu, click on Data Sources.
• Click on the plus sign (+) to create a new Data Source.
• Select Snowflake as the Data Source Type.
• Enter a unique name and description for your Data Source.
• Enter your Snowflake connection details (account, database, schema, warehouse, role, username, password).
• Click on the "Create Data Source" button.

Important

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

Once you click the "Create Data Source" button, Propel will automatically test it to verify that everything is working as expected.

Propel will test that the user can:

• Connect to the Snowflake database and schema.
• Use and monitor the warehouse.
• Create tasks and execute them.
• Create, use and delete streams, procedures, and stages in the schema.
• Select data from the current tables in the schema.

Understanding the Data Source status

The Data Source status property describes the current health of the Data Source. The status property can have one of the following values:

• Created: The Data Source has been created but is not yet connected.
• Connecting: The Data Source is in the process of establishing a connection.
• Broken: The Data Source is not working. It could be because it cannot connect to Snowflake or because it does not have the necessary permissions.
• Connected: The Data Source is connected to Snowflake and is ready to use.
• Deleting: The Data Source is in the process of being deleted.