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
-
Log in to your Snowflake data warehouse.
-
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.
- If you want to create a new exclusive warehouse, don't make any changes to the
-
Replace the default
FIVETRAN_ROLE
,FIVETRAN_DATABASE
,FIVETRAN_USER
, andpassword123
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.
Related articleslink
description Destination Overview
settings API Destination Configuration