Snowflake Setup Guide Betalink
Follow our setup guide to replicate your Snowflake database to your destination using Fivetran.
Prerequisiteslink
To connect Snowflake to Fivetran, you need the following:
- A Snowflake account with the appropriate permissions to create a user and destination for Fivetran.
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 destination typelink
You can choose to create an exclusive destination for Fivetran or use an existing destination:
You can create and use an exclusive destination for Fivetran. Fivetran operations will never contend with your queries for resources. You will have to pay the cost of running the destination.
You can use a shared destination to reduce your destination running cost. 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 edit as needed (to add schemas).
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 = 'FIVETRAN_PASSWORD_123_!@#'; set warehouse_name = 'FIVETRAN_WAREHOUSE'; set database_name = 'YOUR_DATABASE'; -- change role to securityadmin for user / role steps use role securityadmin; -- create role for fivetran create role if not exists identifier($role_name); -- 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 the role to the fivetran user grant role identifier($role_name) to user identifier($user_name); -- change role to sysadmin for warehouse / database steps use role sysadmin; -- Only perform this step if you want to create a -- dedicated warehouse for fivetran (recommended) create warehouse if not exists identifier($warehouse_name) warehouse_size = xsmall warehouse_type = standard auto_suspend = 60 auto_resume = true initially_suspended = true; -- change role to accountadmin to grant permissions use role ACCOUNTADMIN; -- grant fivetran role access to warehouse grant usage on warehouse identifier($warehouse_name) to role identifier($role_name); -- grant fivetran access to database grant usage on database identifier($database_name) to role identifier($role_name); use database identifier($database_name); -- add a statement like this one for each schema you want to have synced by fivetran grant usage on schema PUBLIC to role identifier($role_name); -- add statements granting select permissions and create table permissions if necessary (see step 5 below) commit;
Depending on whether you want to create a new destination or use a shared destination, do either of the following:
- If you want to create a new exclusive destination, don't make any changes to the
FIVETRAN_WAREHOUSE
value in the script - If you want Fivetran to use a shared destination to process source queries, change the
FIVETRAN_WAREHOUSE
value in the script to the name of the shared destination
- If you want to create a new exclusive destination, don't make any changes to the
Replace the default
FIVETRAN_ROLE
,FIVETRAN_DATABASE
,FIVETRAN_USER
, andFIVETRAN_PASSWORD_123_!@#
values with values that conform to your specific naming conventions for those resources.IMPORTANT: Do not use the actual value of
FIVETRAN_USER
for any other purpose.Make a note of the values that replace the default
FIVETRAN_DATABASE
,FIVETRAN_USER
, andFIVETRAN_PASSWORD_123_!@#
values. You will need them to configure Fivetran.Ensure that SELECT access is granted for the tables and views you want to sync using statements similar to the following:
grant select on [TABLE|VIEW|MATERIALIZED VIEW] <schema_name>.<table_name> to role identifier($role_name) grant select on all [TABLES|VIEWS|MATERIALIZED VIEWS] in schema <schema_name> to role identifier($role_name); grant select on future [TABLES|VIEWS|MATERIALIZED VIEWS] in schema <schema_name> to role identifier($role_name);
If any of the schemas you intend to sync contain tables without a primary key, then you must also grant create table access for each of those schemas:
grant create table on schema <schema_name> to role identifier($role_name);
This access is required for performing Teleport syncs on tables without a primary key, because our connector creates a temporary table as part of the sync operation.
Select the All Queries checkbox.
Run the script.
Log in as the newly created user and verify that the tables and views you want to sync are visible in the Snowflake UI.
(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>';
.IMPORTANT: You must replace the
<PUBLIC_KEY>
value with the string between the-----BEGIN PUBLIC KEY-----
and-----END PUBLIC KEY-----
statements.
(Optional) Provide a workspace for temporary tableslink
IMPORTANT: Perform this step only if you need to sync shared read-only database tables that do not have a primary key.
While performing syncs of source tables without a primary key, Fivetran creates temporary tables in the source database. By default, we create these tables in the same database and schema as the source table that we're syncing.
To create these temporary tables in a different location, do the following:
- Select the Use working dataset for queries toggle in your connector setup form.
- Enter the name of the database and schema to be used as the workspace.
(Optional) Connect using AWS PrivateLink, Azure Private Link, or Google Cloud Private Service Connectlink
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, 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. Follow the setup instructions below for your chosen service.
AWS PrivateLinklink
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.
Prerequisiteslink
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 destinationlink
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
- Your Snowflake account URL
Once Snowflake receives this information, they will allow Fivetran to establish a private link connection to your Snowflake destination.
- 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 found 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>" }
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 and resource ID for your PrivateLink connection.
Make a note of the host address that you received from Fivetran. You need it to configure Fivetran.
Contact Snowflake Support and provide the resource ID that you received from Fivetran.
Azure Private Linklink
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.
Prerequisiteslink
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 destinationlink
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 Fivetran to establish a private link connection to your Snowflake destination.
- 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>" }
We then finish setting up Private Link for your Snowflake destination on our side. Once the setup is complete, we send you the host address and the resource ID for your Private Link connection.
Make a note of the host address that you received from Fivetran. You need it to configure Fivetran.
Contact Snowflake Support and provide the resource ID that you received from Fivetran.
For more information, see How to set up Privatelink to Snowflake from 3rd party Cloud Service vendors.
Google Cloud Private Service Connect Betalink
Google Cloud Private Service Connect allows VPCs and Google-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Google Cloud's Private Service Connect documentation.
Prerequisiteslink
To set up Google Cloud Private Service Connect, you need:
- A Fivetran instance configured to run in Google Cloud
- A Business Critical Snowflake destination in one of our supported regions
Configure Google Cloud Private Service Connect for Snowflake destinationlink
Follow the instructions in Snowflake's Google Cloud Private Service Connect & Snowflake documentation. When contacting Snowflake Support, tell them that you want to enable Google Cloud Private Service Connect for Fivetran. Provide the following information:
- Fivetran’s project id:
fivetran-donkeys
- Your Snowflake account URL
Once Snowflake receives this information, they will allow auto-approval for Fivetran’s project.
- Fivetran’s project 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 Google Cloud Private Service Connect:
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-account-url":"<privatelink_account_url>", "privatelink-ocsp-url": "<privatelink_ocsp_account_url>", "privatelink-gcp-service-attachment": "<privatelink_service_attachment_id>" }
We then finish setting up Private Service Connect for your Snowflake destination on our side. Once the setup is complete, we send you the host address and the Private Service Connection ID.
Make a note of the host address that you received from Fivetran. You need it to configure Fivetran.
NOTE: If you need to troubleshoot your connection with Snowflake Support, be sure to provide your Private Service Connection ID.
(Optional) Configure Snowflake network policylink
If you have defined a Snowflake Network Policy, update your Snowflake Network Policy to add Fivetran IP address CIDRs from one of the following sections
Without private linkslink
If you haven't configured AWS PrivateLink or Azure Private Link connectivity, add Fivetran's IP addresses to your network policy's allowed list to allow connections from Fivetran.
AWS PrivateLinklink
If you have configured AWS PrivateLink, add Fivetran's internal VPC CIDR range to the Snowflake network policy's allowed list:
Fivetran VPC Network |
---|
10.0.0.0/8 |
Azure Private Linklink
If you have configured Azure Private Link, add Fivetran's internal VNet CIDR range to the Snowflake network policy's allowed list:
Fivetran VNet |
---|
10.0.0.0/8 |
Google Cloud Private Service Connectlink
If you have configured Google Cloud Private Service Connect, add Fivetran's internal VPC CIDR range to the Snowflake network policy's allowed list:
Fivetran VPC Network |
---|
10.0.0.0/8 |
(Optional) Configure failoverlink
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:
- If you haven't already, replicate your database. Follow the setup instructions in Snowflake's Replicating Databases Across Multiple Accounts documentation.
- 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.
- 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 configurationlink
In the connector setup form, enter your Host name.
IMPORTANT: 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
.Enter the Port number.
Enter the User and Database names you found in Step 2.
IMPORTANT: Do not use this username for any other purpose.
Choose your authentication mode: PASSWORD or KEY-PAIR.
If you selected PASSWORD, enter the Password you found in Step 2.
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.
Click Save & Test.
Fivetran tests and validates the Snowflake connection. On successful completion of the setup tests, you can sync your data using your new Snowflake connector.
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 Permission test checks that we can connect to the database and access the destination.
NOTE: The tests may take a couple of minutes to finish running.
Related articleslink
description Connector Overview