Snowflake is an elastically scalable cloud data warehouse.

NOTE: You need to allowlist Savant’s IP addresses to let our systems contact your warehouse. Reference our IP Addresses docs to determine which IPs you need to whitelist.

Connecting Savant to Snowflake requires some setup in both platforms. It's recommended to set up a service account with the correct permissions in Snowflake before configuring the Savant connector.

Prerequisites

A Snowflake account with the appropriate permissions to create a user and warehouse for Fivetran
Savant role with the required permissions to warehouses, databases, schemas, and tables
Whitelist Savant IPs

Set Up Snowflake in Savant

In Snowflake

There are two methods to connect Snowflake to Savant. The most common method is to create a user / role in Snowflake for Savant, but the platform also supports an OAuth connection as well. In this section, we'll walk you through both options.

Method #1: Database User

To allow Savant access to Snowflake, it's best to create a user specifically provisioned with access to the required tables and schemas. You can also use a personal Snowflake login for your credentials, as long as it has the correct permissions.

The provided commands are examples of creating a service account with the necessary roles and permissions with some considerations; you may need to alter it depending on your Snowflake implementation details:
- The role will need access to the warehouse, database, schema, and tables
- For reading data from Snowflake in Savant, the user needs read access to the desired data objects
- To write data from Savant to Snowflake, the user needs write access to the desired data objects

Create Snowflake User

Copy the following commands to a new worksheet and run them:

CREATE ROLE IF NOT EXISTS 
savant_role COMMENT = "Access to selected tables for Savant"; 

CREATE USER 
savant_user password = '<secure_password>' 

first_name = 'Savant' 
last_name = 'User' 
default_warehouse = '<warehouse>' 
default_namespace = '<database.schema>' 
default_role = '<savant_role>' 
comment = 'Used for Savant integrations'; 

GRANT ROLE savant_role TO ROLE SYSADMIN; 
GRANT ROLE savant_role TO USER savant_user;

Grant Permissions

Copy the following commands to your worksheet and run them:

GRANT USAGE ON WAREHOUSE "<warehouse>" TO ROLE savant_role; 

GRANT USAGE ON DATABASE "<database>" TO ROLE savant_role; 

GRANT USAGE ON SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON ALL TABLES IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON FUTURE TABLES IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON ALL VIEWS IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role;

Method #2: OAuth

If you prefer to use OAuth instead of creating a database user, you need to create an OAuth client in your Snowflake.

Login to Snowflake as an ACCOUNTADMIN.

Run the following statement to create a Savant role in Snowflake:

CREATE ROLE IF NOT EXISTS savant_role COMMENT = "Access to selected tables for Savant";

GRANT ROLE savant_role TO ROLE SYSADMIN; 

GRANT USAGE ON WAREHOUSE "<warehouse>" TO ROLE savant_role; 

GRANT USAGE ON DATABASE "<database>" TO ROLE savant_role; 

GRANT USAGE ON SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON ALL TABLES IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON FUTURE TABLES IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON ALL VIEWS IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role; 

GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<database>"."<schema>" 
TO ROLE savant_role;

Update the statement below:

For EU region, change OAUTH_REDIRECT_URI to https://app-eu.savantlabs.io/api/callback

If you have a Snowflake network policy that blocks public IPs, make sure to whitelist Savant's IPs and add update the <network policy> value. Otherwise, remove the line.

CREATE SECURITY INTEGRATION SAVANT_CLIENT
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://app.savantlabs.io/api/callback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
NETWORK_POLICY = '<network policy>'
;

Run the updated statement from above to create an integration.
Run the following statement to retrieve its client id and client secret:
```
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'SAVANT_CLIENT' );
```
The result is a JSON document with one client id and two client secrets.
Write down the Client Id and one of the Secrets, which will be needed to setup the connection in Savant.

In Savant

Once you have configured the connection in Snowflake, adding the connection to Savant is simple.

Method #1: Database User

If you used the Database User to setup Snowflake, follow these steps in Savant:

Choose the Snowflake connection option.
Enter the Server you are connecting to (ex: https://123.us-region1/gcp.snowflakecomputing.com)
Enter the Warehouse (ex: Example_Warehouse)
Specify the Database (ex: Example_Database)
Enter the [savant_role] Role you created in Snowflake (ex: savant_role)
Specify the account username with the required permissions (ex: savant_user)
Enter your password
Click Authenticate

Method #2: OAuth

If you used the OAuth to setup Snowflake, follow these steps in Savant:

Choose the OAuth connection option
Enter the Server you are connecting to (ex: https://123.us-region1/gcp.snowflakecomputing.com)
Enter Client Id from Snowflake (ex: oLOlP+yDErz8/H/vOpv4g5Gy=)
Enter your Client Secret from Snowflake
Enter savant_role in Role
Enter your Role, Warehouse and Database from Snowflake, or leave the fields blank opt to use the defaults.
Click Authenticate

SSH Tunnel

Additional properties can be seen when the SSH Tunnel checkbox is selected. Click here to learn more about configuring SSH Tunneling in Savant.

Read Data from Snowflake

Learn how to create datasets from Savant’s Snowflake connector here.

Write Data to Snowflake

Learn how to write data to Snowflake using Savant’s Snowflake connector here.

Understanding Snowflake Limitations

There are no known limitations to Savant’s Snowflake connector.

AWS Redshift Connection Setup

MariaDB Connector Setup

MySQL Connector Setup

PostgreSQL Connector Setup

Azure Synapse Connector