Snowflake Destination

Snowflake is a popular cloud-based data warehouse known for its speed, scalability, and reliability.

Refer to the Warehouse Schema guide for how events are mapped to Snowflake tables.

Find the open source code for this destination in the GitHub repository.

Setting user permissions in Snowflake

To enable RudderStack access, make sure you have ACCOUNTADMIN or an account that has the MANAGE GRANTS privilege.

The following sections illustrate how to set up a virtual warehouse, a database, a role, and an user in Snowflake:

Creating a virtual warehouse

In your Snowflake console, create a X-Small warehouse:

Set your data warehouse size as per your data volume requirements.

Alternatively, create a new warehouse by running the following SQL commands:

CREATE WAREHOUSE "RUDDER_WAREHOUSE"
  WITH WAREHOUSE_SIZE = 'XSMALL'
    WAREHOUSE_TYPE = 'STANDARD'
    AUTO_SUSPEND = 600
    AUTO_RESUME = TRUE;

Set AUTO_SUSPEND to ~10 mins and enable AUTO_RESUME to avoid any extra costs.

Creating a database

Create a new database to avoid conflicts with your existing data; RudderStack creates its own tables while storing your events.

The following image demonstrates the Create Database option in Snowflake.

Alternatively, you can create a new database by running the following SQL command:

CREATE DATABASE "RUDDER_EVENTS";

Creating a role for RudderStack

Run the following SQL commands to create a new role with the required permissions to load your data into the newly created warehouse:

1. Create a new role called RUDDER:

CREATE ROLE "RUDDER";

2. Grant access to the warehouse RUDDER_WAREHOUSE:

GRANT USAGE ON WAREHOUSE "RUDDER_WAREHOUSE" TO ROLE "RUDDER";

3. Grant access to the database RUDDER_EVENTS:

GRANT USAGE ON DATABASE "RUDDER_EVENTS" TO ROLE "RUDDER";
GRANT CREATE SCHEMA ON DATABASE "RUDDER_EVENTS" TO ROLE "RUDDER";
GRANT ALL ON ALL SCHEMAS IN DATABASE "RUDDER_EVENTS" TO ROLE "RUDDER";

Alternatively, you can also create up a custom role and specify it in the dashboard settings while setting up the Snowflake destination in RudderStack. Note that this role must have the necessary permissions for RudderStack to load the data into your warehouse.

Creating a user

Finally, create a user to connect RudderStack to the newly created warehouse using the following SQL query:

CREATE USER "RUDDER_USER"
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = "RUDDER"
  PASSWORD = "<your_password>";
GRANT ROLE "RUDDER" TO USER "RUDDER_USER";

Configuring Snowflake destination in RudderStack

To start sending data to Snowflake, you first need to add it as a destination in RudderStack and connect it to a data source. Follow these steps to configure Snowflake as a destination in RudderStack:

1. From your RudderStack dashboard, configure the data source. Then, select Snowflake from the list of destinations.
2. Assign a name to your destination and click Continue.

Connection settings

• Account: Enter the account ID of your Snowflake warehouse. This account ID is part of the Snowflake URL.

The following examples illustrate the slight differences in the account ID for various cloud providers:

In case of AWS, .aws is present in the account locator of some region’s accounts and must be included in the Account field above.

For more information on account locator formats depending on your region or cloud provider, refer to the Snowflake documentation.

• Database: Enter the name of the database created in the Creating a database section above.
• Warehouse: Enter the name of the warehouse created in the Creating a virtual warehouse section above.
• User: Enter the name of the user created in the Creating a user section above.
• Role: Specify the role to be assigned to the above user. If not specified, RudderStack will use the default role.

Make sure your role has the necessary permissions for RudderStack to load the data into the warehouse.

• Use Key Pair Authentication: Turn on the toggle to authenticate the user via a key pair.

For enhanced security, RudderStack recommends using the key pair authentication over the basic authentication mechanism(username and password).

See the following sections in the Snowflake documentation to generate and use the key pair:

• Generate private key
• Generate public key
• Assign public key to Snowflake user

If Use Key Pair Authentication is turned on, enter the following settings:

• Private Key: Specify the private key. Make sure to include the delimiters.
• Private Key Passphrase: Specify the password you set while encrypting the private key. Leave this field blank if your private key is not encrypted.

The user authentication will fail if your private key is encrypted and you do not specify the passphrase.

• Password: If Use Key Pair Authentication is toggled off, then enter the password for the user specified in the User field.
• Namespace: Enter the schema name for the warehouse where RudderStack will create all tables. If not specified, RudderStack sets the namespace to the source name by default.

You cannot change the namespace later.

• Sync Frequency: Specify how often RudderStack should sync the data to your Snowflake warehouse.
• Sync Starting At: This optional setting lets you specify the particular time of the day (in UTC) when you want RudderStack to sync the data to the warehouse.
• Exclude Window: This optional setting lets you set a time window when RudderStack will not sync the data to the warehouse.

Advanced settings

RudderStack provides the following advanced settings:

• Warehouse Append: This setting is turned on by default - RudderStack appends your incoming Event Stream data to the existing data in your warehouse. Turning it off causes RudderStack to merge your incoming data into your warehouse to ensure 100% non-duplicate data.

The append operation helps to achieve faster data syncs while reducing warehouse costs. However, note that it may increase the number of duplicates in the warehouse, especially if the existing data is older than two weeks. A common scenario where duplication might occur is when the SDKs retry sending events in case of failures.

A merge strategy ensures deduplication but can lead to longer sync times and increased warehouse costs.

• Skip User Table: Toggle on this setting to send events exclusively to the identifies table and skip the users table, eliminating the need for a merge operation on the users table.
• Skip Tracks Table: Toggle on this setting to skip sending events to the tracks table.
• JSON Columns: Lets you ingest semi-structured event data not defined by a fixed schema. You can specify the required JSON column paths in this setting in dot notation, separated by commas. This option applies to all incoming track events for this destination. See JSON Column Support for more information.

Configuring the object storage

RudderStack lets you configure the following object storage settings during set up:

• Use RudderStack-managed Object Storage: Enable this setting to use RudderStack-managed buckets for object storage.

This option is applicable only for RudderStack-hosted data planes. For self-hosted data planes, you will have to specify your own object storage configuration settings.

• Choose your Cloud: Select the cloud provider for your Snowflake instance. Refer to the following settings depending on your cloud provider:

See How RudderStack stores data in an object storage platform for more information.

Configuring cloud storage integration with Snowflake

Storage integration setting can be used to run the COPY command. This section lists the steps to configure the Storage Integration setting specified in the Configuring the object storage section above.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:DeleteObject",
        "s3:DeleteObjectVersion"
      ],
      "Resource": "arn:aws:s3:::<bucket_name>/<prefix>/*"
    },
    {
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::<bucket_name>",
      "Condition": {
        "StringLike": {
          "s3:prefix": ["<prefix>/*"]
        }
      }
    }
  ]
}

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<iam_role>'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<bucket_name>/<path>/', 's3://<bucket_name>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('s3://<bucket_name>/<path>/', 's3://<bucket_name>/<path>/') ]

DESC INTEGRATION <integration_name>;

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "AWS": "<snowflake_user_arn>"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<snowflake_external_id>"
        }
      }
    }
  ]
}

GRANT usage ON integration <integration_name> TO ROLE "RUDDER";

GRANT CREATE STAGE ON SCHEMA PUBLIC public to role myrole;

CREATE STORAGE INTEGRATION <integration_name>
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = AZURE
ENABLED = TRUE
AZURE_TENANT_ID = '<tenant_id>'
STORAGE_ALLOWED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/<path>/', 'azure://<account>.blob.core.windows.net/<container>/<path>/')
[ STORAGE_BLOCKED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/<path>/', 'azure://<account>.blob.core.windows.net/<container>/<path>/') ]

DESC INTEGRATION <integration_name>;

GRANT USAGE ON integration <integration_name> to role "RUDDER";

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = GCS
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/')

DESC STORAGE INTEGRATION <integration_name>;

GRANT USAGE ON INTEGRATION <integration_name> TO ROLE "RUDDER";

IPs to be allowlisted

To enable network access to RudderStack, allowlist the following RudderStack IPs depending on your region and RudderStack Cloud plan:

All the outbound traffic is routed through these RudderStack IPs.

FAQ

While configuring the Snowflake destination, what should I enter in the Account field?

While configuring Snowflake as a destination in RudderStack, you need to enter your Snowflake connection credentials which include the Account field, as shown below:

The Account field corresponds to the account ID of your Snowflake warehouse and is a part of the Snowflake URL.

The following examples illustrate the slight differences in the Snowflake account ID for various cloud providers:

In case of AWS, .aws is present in the account locator of some region’s accounts and must be included in the Account field above.

For more information on account locator formats depending on your region or cloud provider, refer to the Snowflake documentation.

For a more comprehensive FAQ list, refer to the Warehouse FAQ guide.

Questions? Contact us by email or on Slack