Snowflake setup guide

This guide covers how to:

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.

Step 1: Find your Snowflake Account Identifier

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

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.

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

European regions

South American regions

Asian regions

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.

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.

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:

18.219.73.236
3.15.73.135
3.17.239.162

Step 5: Create a Snowflake Data Pool

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

Create a Data Pool

Go to the “Data Pools” section in the Console, click “Create Data Pool” and click on the “Snowflake” tile.

If you create a Snowflake Data Pool for the first time, you must create your Snowflake credentials for Propel to connect to your Snowflake account.

Enter credential details

To create your Snowflake credentials, you will need the following details:

Credentials name: A name to identify the credentials in Propel.
Snowflake Account Identifier: The account identifier from Step 1.
Database: The database where your schema is located, for example ANALYTICS.
Schema: The database and schema name where your tables are located, for example PUBLIC.
Warehouse: The warehouse you created in Step 3.
Role: The role you created in Step 3.
User: The user you created in Step 3.
Password: The password for the user you created in Step 3.

Test your credentials

After entering your Snowflake credentials, click “Create and test credentials” to ensure Propel can successfully connect to your Snowflake account.

If the connection is successful, you will see a confirmation message. If not, check your entered credentials and try again.

Introspect your Snowflake tables

Here, you will see a list of tables available to ingest. If you don’t see the table you want to ingest, make sure your user has the right permissions to access the table.

Select table and columns

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

Configure data type and settings

Select whether your data is “Append-only” or “Mutable data”.

To learn more, read out guide on Selecting table engine and sorting key.

Answer the questions in the wizard to complete the setup.

Confirm your table settings and click “Continue”.

Set sync interval

Specify how often you want Propel to sync your data.

Name your Data Pool

You can enable access policies later to restrict access to the data pool.

Click “Create Data Pool” to complete the setup.

Validate setup

Propel will connect to your Snowflake account, create a stream on the table to capture change data and sync it to Propel.

Preview data

Then you can click “Preview Data” to make sure your data is synchronized correctly.

Create a Data Pool

Go to the “Data Pools” section in the Console, click “Create Data Pool” and click on the “Snowflake” tile.

If you create a Snowflake Data Pool for the first time, you must create your Snowflake credentials for Propel to connect to your Snowflake account.

Enter credential details

To create your Snowflake credentials, you will need the following details:

Credentials name: A name to identify the credentials in Propel.
Snowflake Account Identifier: The account identifier from Step 1.
Database: The database where your schema is located, for example ANALYTICS.
Schema: The database and schema name where your tables are located, for example PUBLIC.
Warehouse: The warehouse you created in Step 3.
Role: The role you created in Step 3.
User: The user you created in Step 3.
Password: The password for the user you created in Step 3.

Test your credentials

After entering your Snowflake credentials, click “Create and test credentials” to ensure Propel can successfully connect to your Snowflake account.

If the connection is successful, you will see a confirmation message. If not, check your entered credentials and try again.

Introspect your Snowflake tables

Here, you will see a list of tables available to ingest. If you don’t see the table you want to ingest, make sure your user has the right permissions to access the table.

Select table and columns

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

Configure data type and settings

Select whether your data is “Append-only” or “Mutable data”.

To learn more, read out guide on Selecting table engine and sorting key.

Answer the questions in the wizard to complete the setup.

Confirm your table settings and click “Continue”.

Set sync interval

Specify how often you want Propel to sync your data.

Name your Data Pool

You can enable access policies later to restrict access to the data pool.

Click “Create Data Pool” to complete the setup.

Validate setup

Propel will connect to your Snowflake account, create a stream on the table to capture change data and sync it to Propel.

Preview data

Then you can click “Preview Data” to make sure your data is synchronized correctly.

First, you need to create a Data Source with your Snowflake credentials.

mutation {
  createSnowflakeDataSource(input: {
    uniqueName: "SnowflakeCredentials"
    description: "My Snowflake Credentials"
    connectionSettings: {
      account: "xy12761.us-east-2.aws"
      database: "ANALYTICS"
      warehouse: "PROPELLING"
      schema: "PUBLIC"
      role: "PROPELLER"
      username: "PROPEL_USER"
      password: "<SUPER_SECURE_PASSWORD>"
    }
  }) {
    ...on DataSourceResponse {
      dataSource {
        id
        uniqueName
        status
      }
    }
  }
}

To create the Data Pool, you need to:

Take the id of the Data Source to create the Data Pool replacing the <DATA_SOURCE_ID> in the example below.
Provide the name of the table to ingest in the table field.
Specify the columns you want to ingest.

mutation {
  createDataPoolV2(
    input: {
      dataSource: "<DATA_SOURCE_ID>"
      table: "ORDERS"
      timestamp: {
        columnName: "created_at"
      }
      uniqueName: "SnowflakeDataPool"
      description: "A sample dataset consisting of orders for a taco ordering SaaS"
      accessControlEnabled: true
      tableSettings: {
        engine: {
          mergeTree: {
            type: MERGE_TREE
          }
        }
        orderBy: ["created_at"]
      }
      columns: [
        { columnName: "quantity", type: INT32, isNullable: false },
        { columnName: "taco_name", type: STRING, isNullable: false },
        { columnName: "sauce_name", type: STRING, isNullable: false },
        { columnName: "created_at", type: TIMESTAMP, isNullable: false },
        { columnName: "restaurant_id", type: STRING, isNullable: false },
        { columnName: "restaurant_name", type: STRING, isNullable: false },
        { columnName: "taco_total_price", type: FLOAT, isNullable: false },
        { columnName: "order_item_id", type: STRING, isNullable: false },
        { columnName: "tortilla_id", type: STRING, isNullable: false },
        { columnName: "toppings", type: JSON, isNullable: false },
        { columnName: "sauce_id", type: STRING, isNullable: false },
        { columnName: "taco_unit_price", type: FLOAT, isNullable: false }
      ]
    }
  ) {
    dataPool {
      id
      uniqueName
      description
      columns {
        nodes {
          columnName
          clickHouseType
          isNullable
        }
      }
    }
  }
}

resource "propel_data_source" "my_snowflake" {
  unique_name = "SnowflakeCredentials"
  description = "My Snowflake Credentials"
  type        = "SNOWFLAKE"

  snowflake_connection_settings {
    account   = "xy12761.us-east-2.aws"
    database  = "ANALYTICS"
    warehouse = "PROPELLING"
    schema    = "PUBLIC"
    role      = "PROPELLER"
    username  = "PROPEL_USER"
    password  = var.snowflake_password
  }
}

variable "snowflake_password" {
  type = string
  sensitive = true
}

resource "propel_data_pool" "snowflake_data_pool" {
  unique_name = "SnowflakeDataPool"
  description = "A sample dataset consisting of orders for a taco ordering SaaS"
  data_source = propel_data_source.my_snowflake.id
  table       = "ORDERS"
  timestamp   = "created_at"

  access_control_enabled = true

  table_settings {
    engine {
      type = "MERGE_TREE"
    }
    order_by = ["timestamp"]
  }

  column {
    name     = "quantity"
    type     = "INT32"
    nullable = false
  }
  column {
    name     = "taco_name"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "sauce_name"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "restaurant_id"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "restaurant_name"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "taco_total_price"
    type     = "FLOAT"
    nullable = false
  }
  column {
    name     = "order_item_id"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "tortilla_id"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "toppings"
    type     = "JSON"
    nullable = false
  }
  column {
    name     = "sauce_id"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "taco_unit_price"
    type     = "FLOAT"
    nullable = false
  }
  column {
    name     = "order_id"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "order_item_generated_at"
    type     = "TIMESTAMP"
    nullable = false
  }
  column {
    name     = "taco_id"
    type     = "STRING"
    nullable = false
  }
  column {
    name     = "timestamp"
    type     = "TIMESTAMP"
    nullable = false
  }
  column {
    name     = "tortilla_name"
    type     = "STRING"
    nullable = false
  }
}

Get Started

Streaming

Data warehouses

Databases

ETL Platforms

Snowflake setup guide

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 Snowflake Data Pool

Get Started

Streaming

Data warehouses

Databases

ETL Platforms

​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 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 Snowflake Data Pool