Snowflake Setup Guide

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

Prerequisites

To connect Snowflake to Fivetran, you need the following:

• A Snowflake account with a user role that has the permissions to create a user, role, and warehouse for Fivetran (such as the sysadmin or securityadmin roles).
• A Fivetran role with the Create Destinations or Manage Destinations permissions.
• Your Snowflake data warehouse's collation should be capable of handling both case-sensitive and non-ASCII characters to ensure the integrity of your data.

  NOTE: We do not recommend applying custom collation to your account, database, schema, table, or column. Instead, utilize collation directly in your queries. Custom collation reduces column storage (String) from 16 MB to 8 MB. If we attempt to load more than 8 MB of your data into a column with collation enabled, you will see a String is too long to be compared using collation error.

• The PREVENT_LOAD_FROM_INLINE_URL and REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION parameters of your Snowflake data warehouse must be set to FALSE.

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 instructions

Choose Snowflake warehouse type

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 warehouse

1. Log in to your Snowflake data warehouse.

2. 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);
   
      -- set binary_input_format to BASE64
      ALTER USER identifier($user_name) SET BINARY_INPUT_FORMAT = 'BASE64';
   
      -- 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: Do not use this username for any other purpose.

5. Make a note of the values that replace the default FIVETRAN_DATABASE, FIVETRAN_USER, and password123 values. You will need them to configure Fivetran.

6. Depending on the Snowflake interface you are using, do one of the following:

   • Classic web interface: Select the All Queries checkbox.
   • Snowsight: Select the text of all queries in the query editor.

7. Run the script.

(Optional) Key-pair authentication

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, you can execute one of the following commands:

     • openssl genrsa -out rsa_key.pem 2048
     • openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

   • 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>';.

(Optional) Configure external storage for Hybrid Deployment

IMPORTANT: This is a mandatory step only for the Hybrid Deployment model. Skip to the next step if you want to use the SaaS Deployment model.

In the Hybrid Deployment model, we temporarily stage your data in an external storage location before writing it to your Snowflake destination.

We support the following storage services for staging your data:

• Snowflake internal stage: Stores your data in Snowflake's user stage. By default, we use this storage for staging your data. To use Snowflake's internal stage, you do not have to configure anything. We will use the default user stage associated with your Snowflake account.
• Amazon S3: Stores your data in an S3 bucket.
• Azure Blob Storage: Stores your data in an Azure Blob Storage container.
• Google Cloud Storage: Stores your data in a Google Cloud Storage bucket.

(Optional) Connect using AWS PrivateLink, Azure Private Link, or Google Cloud Private Service Connect

IMPORTANT: Do not perform this step if you want to use Hybrid Deployment for your data pipeline. You must have a Business Critical plan to use AWS PrivateLink, Azure Private Link, or Google Cloud Private Service Connect.

You can connect Fivetran to your Snowflake destination using either AWS PrivateLink, Azure Private Link, or Google Cloud Private Service Connect. Fivetran uses your chosen service to move your data securely between our system and your Snowflake destination.

(Optional) Configure Snowflake network policy

If you have defined a Snowflake Network Policy, update your Snowflake Network Policy to add Fivetran IP address CIDRs or VPC IDs from one of the following sections.

(Optional) Configure failover

IMPORTANT: You must have a Snowflake Business Critical account or higher to use failover. Learn more in Snowflake's failover documentation.

Since Fivetran supports using Snowflake Client Redirect, you can set up database failover for Snowflake if you'd like. To configure failover in Snowflake, you must have two Snowflake accounts in two different regions. You must also have an account with the ORGADMIN role to add the accounts to a Snowflake organization and navigate between them.

Alternatively, you could have one Snowflake account and one organization account if you have the ACCOUNTADMIN role. To enable failover for the organization account, run the following command as the ORGADMIN:

USE ROLE ORGADMIN;
SELECT system$global_account_set_parameter('<org-name>.<org-account-itself>', 'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

In either case, do the following to set up failover:

1. If you haven't already, replicate your database. Follow the setup instructions in Snowflake's Replicating Databases Across Multiple Accounts documentation.
2. Enable failover for your primary database to your secondary database. Follow the setup instructions in Snowflake's Enable Replication for Accounts in the Organization documentation.
3. Set up Client Redirect to generate a single global URL that automatically points to the current primary account. Follow the setup in Snowflake's Configuring Client Redirect documentation.

Provide your Snowflake Client Redirect URL in the Host section of your setup form to ensure that Fivetran always points to the active account in case of failover.

Complete Fivetran configuration

1. Log in to your Fivetran account.

2. Go to the Destinations page and click Add destination.

3. Enter a Destination name of your choice and then click Add.

4. Select Snowflake as the destination type.

5. (Enterprise and Business Critical accounts only) Choose your deployment model:

   • SaaS Deployment
   • Hybrid Deployment

6. If you choose Hybrid Deployment, do the following:

   i. In the Select an existing agent drop-down menu, select an existing Hybrid Deployment Agent or configure a new agent.

   NOTE: For more information about configuring a new agent, see our Hybrid Deployment setup guides.

   ii. Enter the following details of the external storage you configured:

   • For Amazon S3 bucket, enter the S3 bucket name, S3 bucket region, AWS access key ID, and AWS secret access key.
   • For Azure Blob storage container, enter your storage account name and storage account key.
   • For GCS bucket, upload the JSON file containing the service account key and enter the GCS bucket name.

7. (Not applicable to Hybrid Deployment) In the Connection Method drop-down menu, select how you want Fivetran to connect to your destination.

   • Connect directly
   • Connect via PrivateLink

   NOTE: The Connect via PrivateLink option is available only for Business Critical accounts.

8. (Not applicable to Hybrid Deployment) If you selected Connect via PrivateLink as the connection method, select an existing PrivateLink connection, or create a new AWS PrivateLink or Azure Private Link connection.

9. If you selected Connect directly as the connection method or if you choose Hybrid Deployment as the deployment model, enter the host name or IP address of the database server in the Host field.

   NOTE: If you're using database failover, enter the Snowflake Client Redirect URL in the Host field to ensure that Fivetran always points to the active account in case of failover. If you use AWS PrivateLink or Azure Private Link, the URL format is <org-name>-<conn-name>.privatelink.snowflakecomputing.com. If you don't, the URL format is <org-name>-<conn-name>.snowflakecomputing.com.

10. Enter the User and Database names you found in Step 2.

11. Choose your authentication mode: PASSWORD or KEY-PAIR. See the additional steps for key-pair authentication.

    • If you selected PASSWORD, enter the Password you found in Step 2.
    • If you selected KEY-PAIR, enter the Private key. The private key cannot have spaces and must be prefixed by -----BEGIN PRIVATE KEY----- and postfixed by -----END PRIVATE KEY-----.

    IMPORTANT: If you use an encrypted private key, set the Is Private Key encrypted toggle to ON, then enter your Passphrase. Encrypted private keys must be prefixed by -----BEGIN RSA PRIVATE KEY----- and postfixed with -----END RSA PRIVATE KEY-----.

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

13. (Not applicable to Hybrid Deployment) 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 cloud region as described in our Destinations documentation.

    IMPORTANT: If you are using AWS PrivateLink, Azure Private Link, or Google Cloud Private Connect Service, select the corresponding Cloud service provider.

14. Choose your Timezone.

15. (Optional for Business Critical accounts and not applicable to Hybrid Deployment) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.

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

In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connector logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connectors, track your usage, and audit changes. The connector sends all these details at the destination level.

IMPORTANT: If you are an Account Administrator, you can manually add the Fivetran Platform Connector on an account level so that it syncs all the metadata and logs for all the destinations in your account to a single destination. If an account-level Fivetran Platform Connector is already configured in a destination in your Fivetran account, then we don't add destination-level Fivetran Platform Connectors to the new destinations you create.

Setup tests

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.

• The Validate Privileges On Integration test checks if the default role assigned to the Fivetran user account has the required permissions on the storage integration. Fivetran performs this test only if your Snowflake data warehouse is hosted on Google Cloud Platform (GCP).

  TIP: If you are setting up a new destination in the Hybrid Deployment model, the Validate Privileges On Integration test will fail for the first time. To resolve this, retrieve the cloud storage service account for your Snowflake account and grant the service account permissions to access bucket objects, and then click Save & Test again.

Related articles

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home