Snowflake Destination

• Staging S3 Storage Bucket Name: Specify the name of your S3 bucket where data is staged before loading into Snowflake.
• Prefix: If specified, a folder is created in the bucket with this prefix and all data is pushed within that folder.
• Storage Integration: Use this setting to run the COPY command. See the Configure cloud storage integration with Snowflake section below for details.
• 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, see this guide.
  • IAM Role ARN: Enter the ARN of the IAM role.

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 data is staged before loading into Snowflake.
• Prefix: If specified, a folder is created in the bucket with this prefix and all data is pushed within that folder.
• Storage Integration: Use this setting to run the COPY command. See the Configure cloud storage integration with Snowflake section below for details.
• Azure Blob Storage Account Name: Enter the account name for the Azure container.
• Azure Blob Storage Account Key: Enter the account key for your Azure container. See the Blob Storage settings for more information.

• Staging GCS Object Storage Bucket Name: Specify the name of your GCS bucket where data is staged before loading into Snowflake.
• Prefix: If specified, a folder is created in the bucket with this prefix and all data is pushed within that folder.
• Storage Integration: Use this setting to run the COPY command. See the Configure cloud storage integration with Snowflake section below for details.
• Credentials: Paste the contents of your GCP service account credentials JSON. The service account should have a role with storage.objectCreator access.

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 detailed instructions in the 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 Configure object storage section above, and create the policy with a name of your choice.

{
  "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 placeholder 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 (note this name for the later steps) and <IAM_ROLE> with the role ARN obtained in Step 2 and run the following command:

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:

DESC INTEGRATION <INTEGRATION_NAME>;

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

Both statements in the JSON below are mandatory — one is required for RudderStack ingestion and the other for Snowflake access.

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

Note that:

• <SNOWFLAKE_USER_ARN> is the STORAGE_AWS_IAM_USER_ARN option seen in Step 4
• <SNOWFLAKE_EXTERNAL_ID> is the STORAGE_AWS_EXTERNAL_ID
• <WORKSPACE_ID> is your RudderStack workspace ID

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

GRANT USAGE ON INTEGRATION <INTEGRATION_NAME> TO ROLE "<ROLE>";

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

7. Set the Storage Integration dashboard setting to <INTEGRATION_NAME>.

Data is loaded via an external location, so the following command listed in the Snowflake documentation is not required:

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

To use Azure Blob Storage as your object storage, follow the instructions below. You can find detailed instructions in the 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:

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.

DESC INTEGRATION <INTEGRATION_NAME>;

Record the values for AZURE_CONSENT_URL and AZURE_MULTI_TENANT_APP_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 Create a role for RudderStack section by running the following command:

GRANT USAGE ON INTEGRATION <INTEGRATION_NAME> TO ROLE "<ROLE>";

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

9. Set the Storage Integration dashboard setting to <INTEGRATION_NAME>.

To use Google Cloud Storage as your object storage, follow the instructions below. You can find detailed instructions in the Snowflake documentation.

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

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 Configure object storage section above.

2. Retrieve the Cloud Storage service account ID created for your Snowflake account, where <INTEGRATION_NAME> is the integration name you specified in Step 1:

DESC STORAGE INTEGRATION <INTEGRATION_NAME>;

The output of this command has a property named STORAGE_GCP_SERVICE_ACCOUNT. Retrieve this property value. It should be of the format service-account-id@UNIQUE_STRING.iam.gserviceaccount.com.

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:

   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:

      • storage.buckets.get
      • storage.objects.get
      • storage.objects.list
      • storage.objects.create

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 appears.
   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 ADD. The service account name is added to the Storage Object Viewer role dropdown in the information panel.

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

GRANT USAGE ON INTEGRATION <INTEGRATION_NAME> TO ROLE "<ROLE>";

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

6. Set the Storage Integration dashboard setting to <INTEGRATION_NAME>.