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:
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:
From your RudderStack dashboard, configure the data source. Then, select Snowflake from the list of destinations.
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:
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.
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:
Staging S3 Storage Bucket Name: Specify the name of your S3 bucket where RudderStack will store the data before loading it into Snowflake.
Prefix: If specified, RudderStack will create a folder in the bucket with this prefix and push all data within that folder.
Role Based Authentication: Enable this setting to use the RudderStack IAM role for authentication. For more information on creating an AWS IAM role for RudderStack, refer to this guide.
IAM Role ARN: Enter the ARN of the IAM role.
It is highly recommended to enable this setting as the access keys-based authentication method is now deprecated.
If Role-based Authentication is disabled, you need to enter the AWS Access Key ID and AWS Secret Access Key to authorize RudderStack to write to your S3 bucket. Refer to these S3 permissions.
Enable Server-side Encryption for S3: Toggle on this setting to enable server-side encryption for your S3 bucket.
Staging Azure Blob Storage Container Name: Specify the name of your Azure container where RudderStack will store the data before loading it into Snowflake.
Prefix: If specified, RudderStack will create a folder in the bucket with this prefix and push all data within that folder.
Credentials: Paste the contents of your GCP service account credentials JSON. The service account should have a role with storage.objectCreator access.
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.
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.
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.
Create a role and attach the above policy in AWS: Follow the steps listed below:
Create a role of type Another AWS account.
Enter your AWS account ID and enable the Require External ID option.
For external ID, you can add a placeholder value like 0000. This can be modified later.
Attach the policy created in Step 1. Assign a name to this role and keep the role ARN handy for the next step.
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:
Retrieve the AWS IAM user for your Snowflake account as shown:
DESCINTEGRATION<integration_name>;
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:
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:
GRANTCREATESTAGEONSCHEMAPUBLICpublictorolemyrole;
To leverage Azure Blob Storage as your object storage, follow the instructions below. You can find the detailed instructions in this Snowflake documentation.
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.
Run the following commands to create a cloud storage integration in Snowflake:
You can get your <tenant_id> by navigating to Azure Active Directory > Properties > Directory ID.
Grant Snowflake access to the storage locations: Run the following command and replace <integration_name> with the integration name created in Step 2.
DESCINTEGRATION<integration_name>;
Record the values for AZURE_CONSENT_URL and AZURE_MULTI_TENANT_APP_NAME.
Go to the URL obtained in AZURE_CONSENT_URL and accept the consent requirements.
Grant Snowflake access to the container: Navigate to Azure Services > Storage Accounts and select the storage account created in Step 1.
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.
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.
Grant integration access to the Snowflake role you created in the Creating a role for RudderStack section by running the following command:
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.
Create a Cloud Storage integration in Snowflake: Run the following command:
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.
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.
DESCSTORAGEINTEGRATION<integration_name>;
The output of this command will have a property named as STORAGE_GCP_SERVICE_ACCOUNT. Retrieve this property value. It should be of the format service-account-id@UNIQUE_STRING.iam.gserviceaccount.com.
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:
Log into the GCP console as a Project Editor.
From the dashboard, go to IAM & Admin > Roles.
Click CREATE ROLE.
Enter the title and description for the custom role.
Click ADD PERMISSIONS.
Filter the following permissions in the Enter property name or value and add them to the list. Then, click ADD.
storage.buckets.get
storage.objects.get
storage.objects.list
storage.objects.create
Assign the custom role to the Cloud Storage service account:
In your GCP console dashboard, go to Cloud Storage > Browser.
Select the bucket to configure the access.
Select SHOW INFO PANEL in the upper right corner. The information panel for the bucket will pop out.
In the Add Members section, get the service account name from the DESC command run in Step 2.
From the Select a role dropdown, select Storage > Custom > <role>, where <role> is the custom Cloud Storage role.
Click the ADD button. The service account name will be added to the Storage Object Viewer role dropdown in the information panel.
Grant integration access to the Snowflake role you created in the Creating a role for RudderStack section by running the following command:
To enable network access to RudderStack, allowlist the following RudderStack IPs depending on your region and RudderStack Cloud plan:
Plan
Region
US
EU
Free, Starter, and Growth
3.216.35.97
18.214.35.254
23.20.96.9
34.198.90.241
34.211.241.254
52.38.160.231
54.147.40.62
3.123.104.182
3.125.132.33
18.198.90.215
18.196.167.201
Enterprise
3.216.35.97
34.198.90.241
44.236.60.231
54.147.40.62
100.20.239.77
3.66.99.198
3.64.201.167
3.123.104.182
3.125.132.33
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:
This site uses cookies to improve your experience while you navigate through the website. Out of
these
cookies, the cookies that are categorized as necessary are stored on your browser as they are as
essential
for the working of basic functionalities of the website. We also use third-party cookies that
help
us
analyze and understand how you use this website. These cookies will be stored in your browser
only
with
your
consent. You also have the option to opt-out of these cookies. But opting out of some of these
cookies
may
have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This
category only includes cookies that ensures basic functionalities and security
features of the website. These cookies do not store any personal information.
This site uses cookies to improve your experience. If you want to
learn more about cookies and why we use them, visit our cookie
policy. We'll assume you're ok with this, but you can opt-out if you wish Cookie Settings.