Snowflake Destination

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

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

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

Set user permissions in Snowflake

Log in with the ACCOUNTADMIN role, or ask your Snowflake admin to grant your account the MANAGE GRANTS privilege. Then, follow the below sections to create a warehouse, database, role, and user in Snowflake.

Optional: Create a warehouse

Skip this step to use an existing warehouse.

In your Snowflake console, create a warehouse depending on your data volume requirements.

Alternatively, run the following SQL commands to create a new warehouse:

CREATE WAREHOUSE "<WAREHOUSE_NAME>"
  WITH WAREHOUSE_SIZE = 'XSMALL'
    WAREHOUSE_TYPE = 'STANDARD'
    AUTO_SUSPEND = 600
    AUTO_RESUME = TRUE;

Tip: Set AUTO_SUSPEND to ~10 minutes and enable AUTO_RESUME to avoid extra costs.

Create a database

Create a new database to avoid conflicts with your existing data. New tables are created automatically while storing your events.

Alternatively, run the following command:

CREATE DATABASE "<DATABASE_NAME>";

Create a role for RudderStack

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

1. Create a new role called <ROLE>:

CREATE ROLE "<ROLE>";

2. Grant access to the warehouse <WAREHOUSE_NAME>:

GRANT USAGE ON WAREHOUSE "<WAREHOUSE_NAME>" TO ROLE "<ROLE>";

3. Grant access to the database <DATABASE_NAME>:

GRANT USAGE ON DATABASE "<DATABASE_NAME>" TO ROLE "<ROLE>";
GRANT CREATE SCHEMA ON DATABASE "<DATABASE_NAME>" TO ROLE "<ROLE>";
GRANT ALL ON ALL SCHEMAS IN DATABASE "<DATABASE_NAME>" TO ROLE "<ROLE>";

You can also create a custom role with the necessary permissions and specify it in the dashboard settings while setting up the Snowflake destination.

Create a user

Create a user to connect RudderStack to your warehouse:

CREATE USER "<USER_NAME>"
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = "<ROLE>"
  PASSWORD = "<STRONG_PASSWORD>";
GRANT ROLE "<ROLE>" TO USER "<USER_NAME>";

Set up the Snowflake destination in RudderStack

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

Connection settings

Snowflake account ID examples

For AWS, .aws is present in the account locator for some regions and must be included in the Account setting. See the Snowflake documentation for account locator formats by region and cloud provider.

Key pair authentication

For enhanced security, use key pair authentication over basic authentication (username and password).

• See the Migration Guide to migrate from username/password to key pair authentication.

• 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

Authentication fails if your private key is encrypted and you don’t specify the passphrase.

Configure object storage

You can configure the following object storage settings:

• 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, specify your own object storage configuration.

• Choose your Cloud: Select the cloud provider for your Snowflake instance:

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

• Clean up object storage files after successful sync: Turn on this toggle to delete the object storage files after the sync completes successfully.

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 7 days. 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 Users Table: This setting is toggled on by default and sends events exclusively to the identifies table while skipping the users table. This eliminates the need for a merge operation on the users table. If toggled off, RudderStack sends the events to both the identifies and users tables.
• 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.

Configure cloud storage integration with Snowflake

Use the storage integration to run the COPY command. Configure the Storage Integration setting specified in the Configure 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": [{
      "Effect": "Allow",
      "Principal": {
        "AWS": [
          "arn:aws:iam::422074288268:root"
        ]
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<WORKSPACE_ID>"
        }
      }
    },
    {
      "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 "<ROLE>";

GRANT CREATE STAGE ON SCHEMA public to role <ROLE>;

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 "<ROLE>";

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 "<ROLE>";

IPs to allowlist

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

All the outbound traffic is routed through these RudderStack IPs.

Troubleshooting