Snowflake Setup Guidelink

Follow our setup guide to connect Snowflake to Fivetran.

Prerequisiteslink

To connect a Snowflake data warehouse to Fivetran, you need the following:

A Snowflake account with the appropriate permissions to create a user and warehouse for Fivetran.
Fivetran account owner permission to add destinations.
If you have defined a Snowflake Network Policy, add Fivetran's IP addresses to that policy.

Setup instructionslink

Choose Snowflake warehouse typelink

You can choose to create an exclusive warehouse for Fivetran or use an existing warehouse:

You can create and use an exclusive warehouse for Fivetran. Fivetran operations will never contend with your queries for resources. You will have to pay the cost of running the warehouse.
You can use a shared warehouse to reduce your warehouse running cost. Fivetran loads data incrementally and consumes very little compute resources. Fivetran operations may have to contend with your queries for the shared resources.

Run script in Snowflake warehouselink

Copy the following script to a new worksheet:

begin;
 
   -- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
   set role_name = 'FIVETRAN_ROLE';
   set user_name = 'FIVETRAN_USER';
   set user_password = 'password123';
   set warehouse_name = 'FIVETRAN_WAREHOUSE';
   set database_name = 'FIVETRAN_DATABASE';

   -- change role to securityadmin for user / role steps
   use role securityadmin;
 
   -- create role for fivetran
   create role if not exists identifier($role_name);
   grant role identifier($role_name) to role SYSADMIN;
 
   -- create a user for fivetran
   create user if not exists identifier($user_name)
   password = $user_password
   default_role = $role_name
   default_warehouse = $warehouse_name;
 
   grant role identifier($role_name) to user identifier($user_name);
 
   -- change role to sysadmin for warehouse / database steps
   use role sysadmin;
 
   -- create a warehouse for fivetran
   create warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;
 
   -- create database for fivetran
   create database if not exists identifier($database_name);
 
   -- grant fivetran role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);
 
   -- grant fivetran access to database
   grant CREATE SCHEMA, MONITOR, USAGE
   on database identifier($database_name)
   to role identifier($role_name);
 
 commit;

Depending on whether you want to create a new warehouse or use a shared warehouse do either:
- If you want to create a new exclusive warehouse, don't make any changes to the FIVETRAN_WAREHOUSE value in the script.
- If you want Fivetran to use a shared warehouse to ingest data, change the FIVETRAN_WAREHOUSE value in the script to the name of the shared warehouse.
Replace the default FIVETRAN_ROLE, FIVETRAN_DATABASE, FIVETRAN_USER, and password123 values with values that conform to your specific naming conventions for those resources.
Run the script.

Complete Fivetran configurationlink

Log in to your Fivetran account.
Go to the Manage Account page.
In the Destinations tab, click +Destination.
On the Add Destination To Fivetran page, enter a destination name of your choice.
Click Continue.
Select Snowflake as the destination type.
In the destination setup form, enter your Host name.
Enter the Port number.
Enter your User name. For example, FIVETRAN_USER.

IMPORTANT: The username must contain 'fivetran'. Do not use this username for any other purpose.
Enter the Database name. For example, FIVETRAN_DATABASE.
Choose your authentication mode: PASSWORD or KEY-PAIR. See the additional steps for key-pair authentication.
- If you selected PASSWORD, enter your Password.
- If you selected KEY-PAIR, enter the Private key. If you use an encrypted private key, set the Is Private Key encrypted toggle to ON, and then enter your Passphrase.
Choose the data processing location.
Choose your Timezone.
Click Save & Test.

Fivetran tests and validates the Snowflake connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the Snowflake destination.

(Additional) Key-pair authenticationlink

Perform the following steps if you want to use key-pair authentication:

Open the command line in a terminal window.
Generate a private key. You can generate an encrypted version of the private key or an unencrypted version of the private key.
- To generate an unencrypted version, execute the command openssl genrsa -out rsa_key.pem 2048.
- To generate an encrypted version, execute the command openssl genrsa 2048 | openssl pkcs8 -topk8 -v1 <ALGORITHM> -inform PEM -out rsa_key.p8. You can use different algorithms with the -v1 command line option. These algorithms use the PKCS#12 password-based encryption algorithm and allow you to use strong encryption algorithms like triple DES or 128-bit RC2. You can use the following encryption algorithms:
  - PBE-SHA1-RC2-40
  - PBE-SHA1-RC4-40
  - PBE-SHA1-RC2-128
  - PBE-SHA1-RC4-128
  - PBE-SHA1-3DES
  - PBE-SHA1-2DES
From the command line, generate the public key by referencing the private key. Execute the command openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub.
Assign the public key to the Snowflake user. In a Snowflake worksheet, execute the command alter user <USERNAME> set rsa_public_key='<PUBLIC_KEY>';.

Setup testslink

Fivetran performs the following Snowflake connection tests:

The Host Connection test checks the accessibility of the host and validates the database credentials you provided in the setup form.
The Validate Passphrase test validates your private key against the passphrase if you are using key-pair authentication.
The Default Warehouse test checks if the Snowflake warehouse exists and if you have set it as the default warehouse.
The Database Connection test checks if we can connect to your Snowflake database.
The Permission test checks if we have the CREATE SCHEMA and CREATE TEMPORARY TABLES permission on your Snowflake database.

NOTE: The tests may take a couple of minutes to finish running.

Documentation