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

Setting user permissions in Snowflake

To enable RudderStack access, make sure you have an 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:

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

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

Creating a database

The following image demonstrates the Create Database option in Snowflake.

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

Copy
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:

Copy
CREATE ROLE "RUDDER";

2. Grant access to the warehouse RUDDER_WAREHOUSE:

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

3. Grant access to the database RUDDER_EVENTS:

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

Creating a user

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

Copy
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 will first need to add it as a destination in RudderStack and connect it to a data source.

Follow these steps to configure your Azure data lake 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 Next.

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:

• 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.
• Password: Enter the password you set for the above user.
• Namespace: Enter the schema name for the warehouse where RudderStack will create all the tables.

• 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.
• JSON Columns: Use this optional setting to specify the required JSON column paths in dot notation, separated by commas. This option applies to all the incoming track events for this destination.

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.

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

Refer to the following settings depending on your cloud provider:

Configuring cloud storage integration with Snowflake

This section lists the steps to configure the Storage Integration setting specified in the Configuring the object storage section above.

AWS

If you have Amazon Web Services (AWS) as your cloud provider and want to use S3 as your object storage, follow the steps below. You can find the detailed instructions in this Snowflake documentation.

1. Create a policy in AWS: In the following JSON, replace <bucket_name> and <prefix> with the name of your S3 bucket and the prefix set in the Configuring the object storage section above, and create the policy with a name of your choice.

Copy
{
  "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>/*"]
        }
      }
    }
  ]
}

2. Create a role and attach the above policy in AWS: Follow the steps listed below:

   1. Create a role of type Another AWS account.
   2. Enter your AWS account ID and enable the Require External ID option.
   3. For external ID, you can add a dummy value like 0000. This can be modified later.
   4. Attach the policy created in Step 1. Assign a name to this role and keep the role ARN handy for the next step.

3. Create the cloud storage integration in Snowflake: Replace <integration_name> with the name of your choice and <iam_role> with the role ARN obtained in Step 2 and run the following command:

Copy
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>/') ]

4. Retrieve the AWS IAM user for your Snowflake account as shown:

Copy
DESC INTEGRATION <integration_name>;

5. Grant the IAM user permissions to access the bucket objects in AWS. Choose the role you created in Step 2 and edit the trust relationship as shown in the following JSON:

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

Note that <snowflake_user_arn> is the STORAGE_AWS_ROLE_ARN option seen in Step 4, whereas <snowflake_external_id> is the STORAGE_AWS_EXTERNAL_ID.

6. Grant integration access to the Snowflake role you created in the Creating a role for RudderStack section by running the following command:

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

Here, <integration_name> is the name of the integration created in the Step 3.

Note that while sending the data to Snowflake, RudderStack uses an external location instead of a stage in its queries. Hence, the following command listed in the Snowflake documentation is not required:

Copy
GRANT CREATE STAGE ON SCHEMA PUBLIC public to role myrole;

Azure

If you want to leverage Azure Blob Storage as your object storage, follow the instructions below. You can find the detailed instructions in this Snowflake documentation.

1. Create a storage account and container in Azure: First, create a storage account in Azure. Then, navigate to Storage Explorer > Blob Containers > Create a Blob Container.
2. Run the following commands to create a cloud storage integration in Snowflake:

Copy
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>/') ]

You can get your <tenant_id> by navigating to Azure Active Directory > Properties > Directory ID.

3. Grant Snowflake access to the storage locations: Run the following command and replace <integration_name> with the integration name created in Step 2.

Copy
DESC INTEGRATION <integration_name>;

4. Go to the URL obtained in AZURE_CONSENT_URL and accept the consent requirements.
5. Grant Snowflake access to the container: Navigate to Azure Services > Storage Accounts and select the storage account created in Step 1.
6. Add the role: Navigate to Access Control (IAM) > Add Role Assignment. Select either Storage Blob Data Reader with Read access, or Storage Blob Data Contributor with Read and Write access.
7. Add Assign Access: Add Service Principal as the security principal type for the role. Search for AZURE_MULTI_TENANT_APP_NAME that you obtained in Step 3.
8. Grant integration access to the Snowflake role you created in the Creating a role for RudderStack section by running the following command:

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

Here, <integration_name> is the integration you created in Step 2.

GCP

If you want to leverage Google Cloud Storage as your object storage, follow the instructions below. You can find the detailed instructions in this Snowflake documentation.

1. Create a Cloud Storage integration in Snowflake: Run the following command:

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

Replace <integration_name> with the name of your Cloud Storage integration, <bucket> with Staging GCS Object Storage Bucket Name, and <path> with the prefix set in the Configuring the object storage section above.

2. The following command retrieves the Cloud Storage service account ID created for your Snowflake account, where <integration_name> is the integration name you specified in Step 1.

Copy
DESC STORAGE INTEGRATION <integration_name>;

3. Grant service account permissions to access the bucket objects: Create a custom IAM role with the required permissions to access the bucket and fetch the objects by following these steps:

   1. Log into the GCP console as a Project Editor.
   2. From the dashboard, go to IAM & Admin > Roles.
   3. Click CREATE ROLE.
   4. Enter the title and description for the custom role.
   5. Click ADD PERMISSIONS.
   6. Filter the following permissions in the Enter property name or value and add them to the list. Then, click ADD.

4. Assign the custom role to the Cloud Storage service account:

   1. In your GCP console dashboard, go to Cloud Storage > Browser.
   2. Select the bucket to configure the access.
   3. Select SHOW INFO PANEL in the upper right corner. The information panel for the bucket will pop out.
   4. In the Add Members section, get the service account name from the DESC command run in Step 2.
   5. From the Select a role dropdown, select Storage > Custom > <role>, where <role> is the custom Cloud Storage role.
   6. Click the ADD button. The service account name will be added to the Storage Object Viewer role dropdown in the information panel.

5. Grant integration access to the Snowflake role you created in the Creating a role for RudderStack section by running the following command:

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

Here, <integration_name> is the integration name you set up in Step 1.

IPs to be allowlisted

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

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: