Snowflake Setup Guide

Follow our setup guide to replicate your Snowflake database to your destination using Fivetran.

Prerequisites

To connect Snowflake to Fivetran, you need a Snowflake account with the following:

(Applicable only for Time Travel Sync) Change tracking enabled for all the tables in the database.
NOTE: You must enable change tracking for all the tables only if you want to use Time Travel Sync to capture your updates. By default, we use Fivetran Teleport Sync to capture them. However, we recommend that you use Time Travel Sync for your connector.
(Applicable only if you want to use the working database) You must have SELECT, CREATE, and OWNERSHIP privileges on your working schema and database.

Setup instructions

Enable Time Travel Sync

IMPORTANT: Perform this step only if you want to use Time Travel Sync for your connector. By default, we use Fivetran Teleport Sync to capture your updates. However, we recommend that you use Time Travel Sync as your incremental sync method.

Grant the OWNERSHIP privilege to the Fivetran role.
Execute the following command in your Snowflake worksheet:

ALTER TABLE <TABLE_NAME> SET CHANGE_TRACKING = TRUE;

NOTE:
Make sure you set the data_retention_in_days parameter for your Snowflake table to a value greater than 0.
To enable CDC on views, ensure CDC is enabled on the parent table.

Choose connection method

Decide whether to connect Fivetran to your Snowflake database directly or to use AWS PrivateLink, Azure Private Link, or Google Cloud Private Service Connect.

Connect directly

This is the simplest connection method. To use this method, add Fivetran's IP addresses to your network policy's allowed list to allow connections from Fivetran.

Connect using AWS PrivateLink

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. 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 database in one of our supported regions

Configure AWS PrivateLink

Inform Snowflake's support team that you want to enable AWS PrivateLink for Fivetran and provide them with 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 PrivateLink connection to your Snowflake database and provide you with a VPCe in the following format: com.amazonaws.vpce.<region_id>.vpce-svc-xxxxxxxxxxxxxxxxx.
Make a note of the VPCe that Snowflake provides you. You will need it to complete the AWS PrivateLink configuration.
Go to your Snowflake instance and run the following query:
SELECT SYSTEM$GET_PRIVATELINK_CONFIG();
NOTE: For more information about the query, see Snowflake's documentation.

Make a note of the query output. 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>"
}

Send the VPCe you found in Step 2 and the output of the query to your Fivetran account manager.
Notify your Fivetran account manager that you have completed these steps. We will complete the set up for your Snowflake database from our side. Once the setup is complete, we will 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 will need it to configure Fivetran.
Contact Snowflake's support team and provide the resource ID that you received from Fivetran.

(Optional) Configure Snowflake network policy

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`

Alternatively, add the Fivetran VPC Endpoint ID or AWS VPCE ID to your network policy's allowed list.

NOTE: Contact our support team for the Fivetran VPC Endpoint ID or AWS VPCE ID.

Connect using Azure Private Link

IMPORTANT: You must have a Business Critical plan to use Azure Private Link.

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 Snowflake database in one of our supported regions

Configure Azure Private Link

Inform Snowflake's support team that you want to enable Azure Private Link for Fivetran and provide them with 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 database.
Once Snowflake has approved your request, go to your Snowflake instance and run 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();
NOTE: For more information about the query, see Snowflake's documentation.

Make a note of the query output. 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>"
}

Send the output of the query to your Fivetran account manager. Notify your Fivetran account manager that you have completed these steps. We will complete the set up for your Snowflake database from our side. Once the setup is complete, we will 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 will need it to configure Fivetran.
Contact Snowflake's support team and provide the resource ID that you received from Fivetran.
NOTE: For more information about configuring Azure Private Link, see Snowflake's documentation.

(Optional) Configure Snowflake network policy

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`

Alternatively, add the Fivetran Resource ID or Azure LinkID to your network policy's allowed list. To add the Fivetran Resource ID or Azure LinkID, do the following:

Run the following command as an account admin:
SELECT SYSTEM$GET_PRIVATELINK_AUTHORIZED_ENDPOINTS();
Make a note of the linkIdentifier value and provide it to our support team to complete the setup for you.

Connect using Google Cloud Private Service Connect

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.

Prerequisites

To set up Google Cloud Private Service Connect, you need:

A Fivetran instance configured to run in Google Cloud
A Business Critical Snowflake database in one of our supported regions

Configure Google Cloud Private Service Connect

Inform Snowflake's support team that you want to enable Google Cloud Private Service Connect for Fivetran and provide them with 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.
Once Snowflake has approved your request, go to your Snowflake instance and run 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();
NOTE: For more information about the query, see Snowflake's documentation.

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>"
}

Notify your Fivetran account manager that you have completed these steps. We will complete the set up for your Snowflake database from our side. Once the setup is complete, we will send you the host address and Private Service Connection ID.
Make a note of the host address that you received from Fivetran. You need it to configure Fivetran.

(Optional) Configure Snowflake network policy

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`

Complete Fivetran configuration

In your connector setup form, enter a Destination schema prefix of your choice. This prefix applies to each replicated schema and cannot be changed once your connector is created.
Enter your Host name.
NOTE: 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 Username of the Snowflake account you want Fivetran to use to access your data.
In the Auth drop-down menu, select one of the following authentication methods for your connector:
- PASSWORD: This method uses the credentials of your Snowflake account.
- KEY-PAIR: This method uses the private key associated with your Snowflake account.
If you selected PASSWORD as the authentication method, enter the Password of your Snowflake account.
If you selected KEY-PAIR as the authentication method, do the following:
i. Enter the Private Key associated with your Snowflake account.
NOTE: The private key must not contain any spaces and must be prefixed with -----BEGIN PRIVATE KEY----- and postfixed with -----END PRIVATE KEY-----.
ii. If you use an encrypted private key, set the Is Private Key Encrypted toggle to ON, and then enter your Passphrase.
NOTE: Encrypted private keys must be prefixed with -----BEGIN RSA PRIVATE KEY----- and postfixed with -----END RSA PRIVATE KEY-----.
Enter your Snowflake Database name.
NOTE: This field is case-sensitive.
(Optional) If you want Fivetran to use a specific role instead of your default role, enter the Role name.
(Optional) If you want to use a separate workspace for query results, set the Use working dataset for queries toggle to ON, and then enter the Query results database name and Query results schema name.
NOTE: Perform this step only if you want to sync shared read-only database tables without a primary key. To use this feature, you must have SELECT, CREATE, and OWNERSHIP privileges on the database you want to create for the query results.
(Not applicable to Hybrid Deployment) Choose your Connection Method:

Connect directly
Connect via Private Networking

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 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 Database Connection test validates the database name you specified in the setup form and verifies Fivetran's access to your database.
The Permission test checks whether Fivetran can connect to the database and access the destination.
The Workspace test verifies Fivetran's ability to create a temporary table or replace an existing table with the same name in your database.
NOTE: The tests may take a couple of minutes to finish running.