Snowflake Setup Guide

Follow our setup guide to connect your Snowflake data warehouse to Fivetran.

Prerequisiteslink

To connect Snowflake 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.

IMPORTANT: In Snowflake, if you use double quotes around an identifier name, it makes the identifier name case-sensitive. We recommend using the create <identifier> <identifier_name> or the create <identifier> "IDENTIFIER_NAME" format. See Snowflake’s documentation on identifiers for more information.

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

1. Log in to your Snowflake data warehouse.

2. Copy the following script to a new worksheet and select the All Queries checkbox:

   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);
   
      -- change role to ACCOUNTADMIN for STORAGE INTEGRATION support (only needed for Snowflake on GCP)
      use role ACCOUNTADMIN;
      grant CREATE INTEGRATION on account to role identifier($role_name);
      use role sysadmin;
    
    commit; 
   
   

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

4. Replace the default FIVETRAN_ROLE, FIVETRAN_DATABASE, FIVETRAN_USER, and password123 values with values that conform to your specific naming conventions for those resources.

   IMPORTANT: The username must contain ‘fivetran’. Do not use this username for any other purpose.

5. Run the script.

Configure Snowflake network policylink

IMPORTANT: Skip this step if you are connecting to Fivetran using AWS PrivateLink.

Complete Fivetran configurationlink

1. Log in to your Fivetran account.

2. Go to the Manage Account page.

3. In the Destinations tab, click +Destination.

4. On the Add Destination To Fivetran page, enter a Destination Name of your choice.

5. Click Add Destination.

6. Select Snowflake as the destination type.

7. In the destination setup form, enter your Host name.

8. Enter the Port number.

9. Enter your User name. The username must contain ‘fivetran’. Do not use this username for any other purpose.

10. Enter the Database name. For example, FIVETRAN_DATABASE.

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

12. (Optional) If you want Fivetran to use a specific role instead of your default role, enter the Role name.

13. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and AWS region as described in our Destinations documentation.

14. Choose your Timezone.

15. 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:

1. Open the command line in a terminal window.

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

   • To use stronger encryption algorithms, execute the command openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 <ALGORITHM> -inform PEM -out rsa_key.p8. You can use different algorithms with the -v2 command line option. You can use the following encryption algorithms:

     • AES128
     • AES256
     • DES3

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

4. Assign the public key to the Snowflake user. In a Snowflake worksheet, execute the command alter user <USERNAME> set rsa_public_key='<PUBLIC_KEY>';.

(Additional) AWS PrivateLink BETAlink

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in AWS’ PrivateLink documentation.

Fivetran uses PrivateLink to move your data securely between our system and your Snowflake destination.

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 data 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 Contentlink

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home