# Snowflake Reverse ETL Source

[Snowflake](https://www.snowflake.com/) is a cloud-based data warehouse provided as Software-as-a-Service \(SaaS\). It offers all features of a modern data warehouse, including scalability, ease of use, secure data access, and much more.

RudderStack supports Snowflake as a data source from which you can ingest data and route it to your desired downstream destinations.

## Grant permissions {#granting-permissions}

Before you set up Snowflake as a source, you must grant certain permissions on your Snowflake warehouse for RudderStack to access data from it.

Run the queries listed in the following sections in the  **exact order** to grant the required permissions:

{{< warning >}}
You must have the **ACCOUNTADMIN** role to grant these permissions.
{{< /warning >}}

{{< figure src="images/warehouse-actions-sources/snowflake-accountadmin.webp" alt="Snowflake account admin role" >}}

### Step 1: Create a new role and user in Snowflake {#step-1-creating-a-new-role-and-user-in-snowflake}

1. In your Snowflake console, run the following command to create a role `RUDDER_ROLE` in Snowflake. After creating the role, you can [grant object privileges](https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html) to it.

```sql
CREATE ROLE RUDDER_ROLE;
```

2. Verify if the role `RUDDER_ROLE` is successfully created.

```sql
SHOW ROLES;
```

3. Create a new user `RUDDER` with a password `<strong_unique_password>`.

```sql
CREATE USER RUDDER PASSWORD = '<strong_unique_password>' DEFAULT_ROLE = 'RUDDER_ROLE';
```

{{< info >}}
You can assign some other role to this user in the RudderStack dashboard settings later. See [Configuring the connection credentials](#configuring-the-connection-credentials) section for more information.
{{< /info >}}

4. Verify if the user `RUDDER` is successfully created.

```sql
SHOW USERS;
```

### Step 2: Create RudderStack schema and grant permissions to the role {#creating-the-rudderstack-schema-and-granting-permissions}

1. Create a dedicated schema `_RUDDERSTACK` in your database `<YOUR_DATABASE>`.

```sql
CREATE SCHEMA "<YOUR_DATABASE>"."_RUDDERSTACK";
```

{{< warning >}}
The `_RUDDERSTACK` schema stores Reverse ETL sync state, snapshots, and related tables. **Do not change this name**.

See [`_rudderstack` Schema Reference]({{< ref "data-pipelines/reverse-etl/developer-guides/rudderstack-schema.md" >}}) for more details.
{{< /warning >}}

2. Grant full access to the schema `_RUDDERSTACK` for the previously created role `RUDDER_ROLE`.

```sql
GRANT ALL PRIVILEGES ON SCHEMA "<YOUR_DATABASE>"."_RUDDERSTACK" TO ROLE RUDDER_ROLE;
```

Replace `<YOUR_DATABASE>` with the exact name of your Snowflake database

### Step 3: Grant permissions on warehouse, database, schema, and table {#step-3-granting-permissions-on-the-warehouse-database-schema-and-the-table}

1. Enable the user `RUDDER` to perform all operations allowed for the role `RUDDER_ROLE`(via the privileges granted to it).

```sql
GRANT ROLE RUDDER_ROLE TO USER RUDDER;
```

2. Run the following commands to allow the role `RUDDER_ROLE` to look up the objects within your warehouse, database, schema, and the specific table or view:

```sql
GRANT USAGE ON WAREHOUSE "<YOUR_WAREHOUSE>" TO ROLE RUDDER_ROLE;
GRANT USAGE ON DATABASE "<YOUR_DATABASE>" TO ROLE RUDDER_ROLE;
GRANT USAGE ON SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE RUDDER_ROLE;
GRANT SELECT ON TABLE "<YOUR_DATABASE>"."<YOUR_SCHEMA>"."<YOUR_TABLE>" TO ROLE  RUDDER_ROLE;
GRANT SELECT ON VIEW "<YOUR_DATABASE>"."<YOUR_SCHEMA>"."<YOUR_VIEW>" TO ROLE  RUDDER_ROLE;
```

Replace `<YOUR_WAREHOUSE>`, `<YOUR_DATABASE>`, `<YOUR_SCHEMA>`, `<YOUR_TABLE>`, and `<YOUR_VIEW>` with the exact names of your Snowflake warehouse, database, schema, table, and view respectively.

#### **Optional commands**

{{< warning >}}
Run the following commands **only** if you're okay with RudderStack being able to access all current or future tables/views within your specified schema.
{{< /warning >}}

- To allow the role `RUDDER_ROLE` to read data from **all** the tables in the schema `<YOUR_SCHEMA>`.

```sql
GRANT SELECT ON ALL TABLES IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE RUDDER_ROLE;
```

- To allow the role `<RUDDER_ROLE>` to read data from all **future tables** in the schema `<YOUR_SCHEMA>`.

```sql
GRANT SELECT ON FUTURE TABLES IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE RUDDER_ROLE;
```

- To allow the role `RUDDER_ROLE` to read data from **all** the views in the schema `<YOUR_SCHEMA>`.

```sql
GRANT SELECT ON ALL VIEWS IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE RUDDER_ROLE;
```

- To allow the role `<RUDDER_ROLE>` to read data from all **future views** in the schema `<YOUR_SCHEMA>`.

```sql
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<YOUR_DATABASE>"."<YOUR_SCHEMA>" TO ROLE RUDDER_ROLE;
```

Replace `<YOUR_DATABASE>` and `<YOUR_SCHEMA>` with the exact Snowflake database and the schema names.

## Set up Snowflake source in RudderStack {#setting-up-the-snowflake-source-in-rudderstack}

1. Log in to your [RudderStack dashboard](https://app.rudderstack.com/).
2. On the **Connections** page, click **Add source**.
3. Under **Sources**, click **Reverse ETL** and select **Snowflake**.

### Configure warehouse credentials {#configuring-the-connection-credentials}

You can choose to proceed with your existing warehouse credentials if you have configured them in the RudderStack dashboard previously. Otherwise, click **Add new credentials** to add new credentials for your warehouse.

- **Authentication Type**: Select the user authentication mechanism from the dropdown. RudderStack supports the following methods:
    - **Username Password Authentication**
    - **Key Pair Authentication**

{{< info >}}
For enhanced security, RudderStack recommends using the key pair authentication over the basic authentication mechanism (username and password).

- See the [Migration Guide]({{< ref "user-guides/migration-guides/snowflake-key-pair-migration.md" >}}) to migrate from the username/password authentication to the key pair authentication.
- Refer to 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 >}}

- **Account**: Your warehouse account ID is part of your Snowflake URL. The following examples illustrate the slight differences in the account ID for various cloud providers:

    | Account ID sample | Snowflake URL | Snowflake cloud provider |
    | :-------------- | :----------------------------- | :------------------ |
    | **qya56091.us-east-1**      | `https://`**`qya56091.us-east-1`**`.snowflakecomputing.com`      | AWS  |
    | **rx18795.east-us-2.azure** | `https://`**`rx18795.east-us-2.azure`**`.snowflakecomputing.com` | Microsoft Azure |
    | **ah76025.us-central1.gcp** | `https://`**`ah76025.us-central1.gcp`**`.snowflakecomputing.com` | Google Cloud Platform  |

- **Database**: Enter the name of the database in which your data resides.
- **Warehouse**: Specify the name of your data warehouse.
- **User**: Enter the name of the user that has the required read/write access to the above database.
- **Role**: Enter a role you want to assign to the above user. For syncing the data, you can use this role apart from the default role (`RUDDER_ROLE`) assigned in the [Create a new role and user](#step-1-creating-a-new-role-and-user-in-snowflake) section.

If you have set **Authentication Type** to **Username Password Authentication**:

- **Password**: Enter the password for the user specified in the **User** field.

If you have set **Authentication Type** to **Key Pair Authentication**:

- **Private Key**: Specify the private key generated above. Make sure to include the delimiters.
- **Private Key Passphrase**: Specify the password you set while encrypting the private key. Note that:

    - RudderStack requires a non-empty passphrase for encrypted private keys.
    - You can leave this field blank if your private key is not encrypted.

{{< danger >}}
The user authentication will fail if your private key is encrypted and you do not specify the passphrase.
{{< /danger >}}

Click the **Verify** button on the top right. RudderStack will then [verify and validate your credentials](#faq). Once verified, click **Continue** to proceed.

### Specify name and source type

{{< customreadfile "/includes/retl/retl-name-sourcetype.md" >}}

#### **Use warehouse table as source**

{{< customreadfile "/includes/retl/table-as-source.md" >}}

#### **Use model as source**

{{< customreadfile "/includes/retl/model-as-source.md" >}}

#### **Use audience as source**

{{< customreadfile "/includes/retl/audience-as-source.md" >}}

### Review and complete setup

{{< customreadfile "/includes/retl/review-retl-setup.md" >}}

## Connect destination

{{< customreadfile "/includes/retl/connect-retl-destination.md" >}}

## Update source configuration and settings

{{< customreadfile "/includes/retl/update-retl-source-configuration.md" >}}

## FAQ

{{< customreadfile "/includes/retl/retl-faq.md" >}}