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 role with the Create Destinations or Manage Destinations permissions.
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 thecreate <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
-
Log in to your Snowflake data warehouse.
-
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;
-
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.IMPORTANT: Do not use this username for any other purpose.
-
Run the script.
Configure Snowflake network policylink
IMPORTANT: Skip this step if you are connecting to Fivetran using AWS PrivateLink or Azure Private Link.
Alter network policy
If you have defined a Snowflake Network Policy, update your Snowflake Network Policy to add Fivetran’s IP addresses.
Use the ALTER NETWORK POLICY
command to update your policy.
For example, to update the network policy to safelist the IPs if you are in the US region, execute the following command:
ALTER NETWORK POLICY <your_network_policy_name> SET {[ALLOWED_IP_LIST] = ('35.227.135.0/29', '35.234.176.144/29', '52.0.2.4/32')]};
Create network policy
If you don’t have a Snowflake Network Policy, you must create a network policy to safelist Fivetran’s IP addresses.
Use the CREATE NETWORK POLICY
command to specify the IP addresses that are allowed access to your Snowflake account.
NOTE: Make sure to add the IP addresses you want to use to access Snowflake because Snowflake automatically blocks all IP addresses that are not in the allowed list.
For example, to create the network policy to safelist the IPs if you are in the US region, execute the following command:
CREATE NETWORK POLICY <fivetran_ip_whitelist_us> ALLOWED_IP_LIST = ('35.227.135.0/29', '35.234.176.144/29', '52.0.2.4/32');
(Optional) 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, 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
-
-
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>';
.
(Optional) Connect using AWS PrivateLink or Azure Private Linklink
IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink or Azure Private Link.
You can connect Fivetran to your Snowflake destination using either AWS PrivateLink or Azure Private Link. Fivetran uses your chosen service to move your data securely between our system and your Snowflake destination.
Follow the setup instructions below for your chosen service.
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. PrivateLink is the most secure connection method. Learn more in AWS’ PrivateLink documentation.
Prerequisites
To set up AWS PrivateLink, you need:
- A Fivetran instance configured to run in AWS
- A Business Critical Snowflake destination in one of our supported regions
Configure AWS PrivateLink for Snowflake destination
-
Contact Snowflake Support and tell them that you want to enable AWS PrivateLink for Fivetran. Provide the following information:
- Fivetran’s AWS VPC Account ID:
arn:aws:iam::834469178297:root
- The Fivetran VPC network for your Snowflake destination’s region:
AWS Region VPC Network us-east-1
North Virginia, USA10.128.0.0/18
us-east-2
Ohio, USA10.129.64.0/18
us-west-2
Oregon, USA10.129.0.0/18
ap-southeast-2
Sydney, Australia10.130.0.0/18
eu-west-2
London10.131.0.0/18
eu-central-1
Frankfurt10.132.0.0/18
Once Snowflake receives this information, they will allow the Fivetran account and VPC network to connect to your endpoint service.
- Fivetran’s AWS VPC Account ID:
-
Snowflake will provide you with a VPCe in the format
com.amazonaws.vpce.<region_id>.vpce-svc-xxxxxxxxxxxxxxxxx
. Make a note of this VPCe. You will need it later. -
Go to your Snowflake instance and execute the following query:
SELECT SYSTEM$GET_PRIVATELINK_CONFIG();
-
Send the VPCe you noted in Step 2 and the output of the query to your Fivetran account manager. The output will be in the following format:
{ "privatelink-account-name": "<account_name>", "privatelink-account-url": "<privatelink_account_url>", "privatelink-ocsp-url": "<privatelink_ocsp_url>", "privatelink-vpce-id": "<aws_vpce_id>" }
-
In all your data sources that do not use PrivateLink, safelist Fivetran’s IP addresses (in the table above) for their respective regions. You do not need to do this for data sources that do not have a firewall.
-
Notify your Fivetran account manager that you have completed these steps. We then finish setting up PrivateLink for your Snowflake destination on our side. Once the setup is complete, we send you the host address for your PrivateLink connection.
-
Make a note of the host address that you received from Fivetran. You need it to configure Fivetran.
Azure Private Link BETA
Azure Private Link allows VNet and Azure-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Microsoft’s Azure Private Link documentation.
Prerequisites
To set up Azure Private Link, you need:
- A Fivetran instance configured to run in Azure
- An Azure-hosted, Azure Virtual Machine-hosted, or on-premises* source in one of our supported regions
* Your on-premises source must be one of our supported databases. See a complete list in our Databases documentation.
Configure Azure Private Link for Snowflake destination
-
Contact Snowflake Support and tell them that you want to enable Azure Private Link for Fivetran. Provide the following information:
- Fivetran’s Azure subscription ID:
6d755170-32cd-4a50-8bf2-621c984f3528
- Your Snowflake account URL
Once Snowflake receives this information, they will allow auto-approval for Fivetan’s Azure Subscription ID.
- Fivetran’s Azure subscription ID:
-
Once Snowflake has approved your request, go to your Snowflake instance and execute the following query as a user with the Snowflake ACCOUNTADMIN role to obtain the URL that we need to access Snowflake through Azure Private Link:
SELECT SYSTEM$GET_PRIVATELINK_CONFIG();
-
Send the output of the query to your Fivetran account manager. The output will be in the following format:
{ "privatelink-account-name": "<account_identifier>", "privatelink-internal-stage": "<privatelink_stage_endpoint>", "privatelink-account-url":"<privatelink_account_url>", "privatelink-ocsp-url": "<privatelink_ocsp_url>", "privatelink-pls-id": "<azure_privatelink_service_id>" }
Fivetran then finishes setting up Private Link for your Snowflake destination on our side. Once the setup is complete, we send you the host address for your Private Link connection.
-
Make a note of the host address that you received from Fivetran. You need it to configure Fivetran.
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 Add Destination.
-
Select Snowflake as the destination type.
-
In the destination setup form, enter your Host name.
IMPORTANT: If you connected using AWS PrivateLink or Azure Private Link, enter the host address that you received from Fivetran in the Host field.
-
Enter the Port number.
-
Enter your User name. 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.
-
(Optional) If you want Fivetran to use a specific role instead of your default role, enter the Role name.
-
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.
-
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.
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
settings API Destination Configuration