# Snowflake Destination


[Snowflake](https://www.snowflake.com/) is a popular cloud-based data warehouse known for its speed, scalability, and reliability.

See the [Warehouse Schema]({{< ref "destinations/warehouse-destinations/warehouse-schema.md" >}}) guide for how events are mapped to Snowflake tables.

Find the open source code for this destination in the [GitHub repository](https://github.com/rudderlabs/rudder-transformer/tree/main/src/v0/destinations/snowflake).

## 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

{{< info >}}
Skip this step to use an existing warehouse.
{{< /info >}}

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

{{< image src="images/dw-integrations/snowflake-create-warehouse.webp" alt="Create a virtual warehouse" >}}

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

```sql
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.
{{< /tip >}}

### Create a database

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

{{< image src="images/dw-integrations/snowflake-create-database.webp" alt="Create a database" >}}

Alternatively, run the following command:

```sql
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>`:

```sql
CREATE ROLE "<ROLE>";
```

2. Grant access to the warehouse `<WAREHOUSE_NAME>`:

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

3. Grant access to the database `<DATABASE_NAME>`:

```sql
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>";
```

{{< info >}}
You can also create a custom role with the necessary permissions and specify it in the [dashboard settings](#connection-settings) while setting up the Snowflake destination.
{{< /info >}}

### Create a user

Create a user to connect RudderStack to your warehouse:

```sql
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](https://app.rudderstack.com/), add a source. Then select **Snowflake** from the list of destinations.
2. Assign a name to your destination and click **Continue**.

### Connection settings

| Setting | Description |
| :--- | :--- |
| Account | Enter the account ID of your Snowflake warehouse. This ID is part of the Snowflake URL. See the [Snowflake account ID examples](#snowflake-account-id-examples) below for more information. |
| Database | Enter the name of the database created in the [Create a database](#create-a-database) section. |
| Warehouse | Enter the name of the warehouse created in the [Create a warehouse](#create-a-warehouse) section. |
| User | Enter the name of the user created in the [Create a user](#create-a-user) section. |
| Role | Specify the role to assign to the above user. If not specified, the [default role](#create-a-role-for-rudderstack) is used. Make sure your role has the necessary permissions to load data into the warehouse. |
| Use Key Pair Authentication | Turn on this toggle to authenticate the user via a [key pair](https://docs.snowflake.com/en/user-guide/key-pair-auth). See the [key pair authentication](#key-pair-authentication) section below for details. |
| Password | Visible only if **Use Key Pair Authentication** is toggled off. Enter the password for the user specified above. |
| Namespace | Enter the schema name where all tables are created. If not specified, the namespace defaults to the source name. You **cannot** change the namespace later. |
| Sync Frequency | Specify how often data syncs to your Snowflake warehouse. |
| Sync Starting At | (Optional) Specify the time of day (in UTC) to start the sync. |
| Exclude Window | (Optional) Set a time window during which data **will not** sync to the warehouse. |

#### Snowflake account ID examples

{{< customreadfile "/includes/snowflake-account-id-examples.md" >}}

#### Key pair authentication

{{< info >}}
For enhanced security, use key pair authentication over basic authentication (username and password).

- See the [Migration Guide]({{< ref "user-guides/migration-guides/snowflake-key-pair-migration.md" >}}) to migrate from username/password to key pair authentication.
- See the following sections in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/key-pair-auth#configuring-key-pair-authentication) to generate and use the key pair:

  - [Generate private key](https://docs.snowflake.com/en/user-guide/key-pair-auth#generate-the-private-key)
  - [Generate public key](https://docs.snowflake.com/en/user-guide/key-pair-auth#generate-a-public-key)
  - [Assign public key to Snowflake user](https://docs.snowflake.com/en/user-guide/key-pair-auth#assign-the-public-key-to-a-snowflake-user)
{{< /info >}}

| Setting | Description |
| :--- | :--- |
| Private Key | Enter your private key, including the delimiters. |
| Private Key Passphrase | Enter the passphrase you set while encrypting the private key. Leave blank if your private key is not encrypted. |

{{< danger >}}
Authentication fails if your private key is encrypted and you don't specify the passphrase.
{{< /danger >}}

### 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.

{{< warning >}}
This option is applicable only for RudderStack-hosted data planes. For self-hosted data planes, specify your own object storage configuration.
{{< /warning >}}

- **Choose your Cloud**: Select the cloud provider for your Snowflake instance:

{{< tabs tabTotal="3">}}
{{% tab tabName="AWS" %}}

- **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](#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]({{< ref "destinations/aws-iam-role-for-rudderstack/_index.md#creating-a-rudderstack-iam-role" >}}).
  - **IAM Role ARN**: Enter the ARN of the IAM role.

{{< warning >}}
Enable this setting as the access keys-based authentication method is now deprecated.
{{< /warning >}}

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]({{< ref "destinations/streaming-destinations/amazon-s3.md#permissions" >}}).

- **Enable Server-side Encryption for S3**: Toggle on this setting to enable server-side encryption for your S3 bucket.

{{% /tab %}}
{{% tab tabName="Azure" %}}

- **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](#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]({{< ref "destinations/streaming-destinations/microsoft-azure-blob-storage.md#setting-up-azure-blob-storage" >}}) for more information.

{{% /tab %}}
{{% tab tabName="GCP" %}}

- **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](#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.

{{% /tab %}}
{{< /tabs >}}

See [How RudderStack stores data in an object storage platform]({{< ref "destinations/warehouse-destinations/faq.md#how-does-rudderstack-store-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

{{< customreadfile "/includes/warehouse-advanced-settings.md" >}}

## 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](#configure-object-storage) section above.

{{< tabs tabTotal="3">}}
{{% tab tabName="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 detailed instructions in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/data-load-s3-config.html#option-1-configuring-a-snowflake-storage-integration).

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](#configure-object-storage) section above, and create the policy with a name of your choice.

```json
{
  "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:

```sql
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:

```sql
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:

{{< warning >}}
Both statements in the JSON below are **mandatory** — one is required for RudderStack ingestion and the other for Snowflake access.
{{< /warning >}}

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

{{< info >}}
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]({{< ref "dashboard-guides/_index.md#workspace" >}})
{{< /info >}}

6. Grant integration access to the Snowflake role you created in the [Create a role for RudderStack](#create-a-role-for-rudderstack) section by running the following command:

```sql
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**](#configure-object-storage) dashboard setting to `<INTEGRATION_NAME>`.

Data is loaded via an external location, so the following command listed in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration.html#step-6-create-an-external-stage) is **not required**:

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

{{% /tab %}}
{{% tab tabName="Azure" %}}

To use Azure Blob Storage as your object storage, follow the instructions below. You can find detailed instructions in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/data-load-azure-config.html#option-1-configuring-a-snowflake-storage-integration).

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:

```sql
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**.

```sql
DESC INTEGRATION <INTEGRATION_NAME>;
```

{{< info >}}
Record the values for `AZURE_CONSENT_URL` and `AZURE_MULTI_TENANT_APP_NAME`.
{{< /info >}}

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](#create-a-role-for-rudderstack) section by running the following command:

```sql
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**](#configure-object-storage) dashboard setting to `<INTEGRATION_NAME>`.

{{% /tab %}}
{{% tab tabName="GCP" %}}

To use Google Cloud Storage as your object storage, follow the instructions below. You can find detailed instructions in the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/data-load-gcs-config.html#configuring-an-integration-for-google-cloud-storage).

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

```sql
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](#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**:

```sql
DESC STORAGE INTEGRATION <INTEGRATION_NAME>;
```

{{< info >}}
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`.
{{< /info >}}

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](#create-a-role-for-rudderstack) section by running the following command:

```sql
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**](#configure-object-storage) dashboard setting to `<INTEGRATION_NAME>`.
{{% /tab %}}
{{< /tabs >}}

## IPs to allowlist

{{< customreadfile "/includes/iplist.md" >}}

## Troubleshooting

| Issue | Solution |
| :--- | :--- |
| Connection verification step fails | Verify the following:<br /><br /><ul><li>The entire contents of the private key are specified in the **Private Key** field, including the `BEGIN RSA PRIVATE KEY` and `END RSA PRIVATE KEY` delimiters.</li><li>In case of an encrypted private key, you have specified the passphrase in the **Passphrase** field.</li></ul> |
| HTTP 404 error during connection verification | Verify your [Snowflake account URL](#snowflake-account-id-examples) is correct. You can use the following command to check if the account URL is correct:<br /><br />`CURL -I https://<account_id>.snowflakecomputing.com` |