Snowflake's architecture separates storage from computing, allowing customers to run Fivetran in a separate logical data warehouse. You can have multiple logical data warehouses running queries on the same underlying data. You can then provision one logical data warehouse as a destination for Fivetran to load new data into and another for your analysts to query the data.
You may have several data sources, each of which has its own complex schema. We sync the entire schema of every data source to Snowflake, automatically and continuously. You can connect all your data sources, which we'll sync several times a day, without worrying about bogging down analysts' queries.
A Snowflake account can have multiple compute warehouses and multiple databases. If you'd like to sync data into two databases in a single Snowflake account, and/or use multiple compute warehouses to do so, you will have to create two Fivetran destinations; one for each warehouse or database connection.
- Snowflake Account
- Ability to create a user and warehouse for Fivetran
- If you have defined a Snowflake Network Policy, add Fivetran's IP to that policy
Connecting your warehouse
After selecting Snowflake on the welcome screen, fill in the required information in the UI about:
- Password / PrivateKey
- Database name
- Time zone
The username must contain 'fivetran'. You should not use this username for any other purpose.
Run script in Snowflake warehouse
Shared vs. exclusive warehouse
If you would like Fivetran to use a shared warehouse to ingest data, in the next step change
the name of the shared warehouse. Otherwise, enter
<WAREHOUSE> in the script and you will be prompted to create
Using an exclusive warehouse means that Fivetran operations will never contend with your queries for resources,
but you will have to pay the cost of running
fivetran_warehouse all the time.
Since Fivetran loads data incrementally, it usually requires very little compute resources, so it may make sense to use a shared warehouse to reduce cost.
Copy the script given below. Log in to your Snowflake data warehouse. In a new workbook, run the script you copied.
You can prepare the script yourself. To do that replace
with the values you picked in the script below. The defaults for
RUN THE ENTIRE SCRIPT:
-- change role to ACCOUNTADMIN for user / role steps use role ACCOUNTADMIN; -- create role for fivetran create role if not exists fivetran_role; grant role fivetran_role to role SYSADMIN; -- create a user for fivetran create user if not exists <USERNAME>; alter user <USERNAME> set default_role = fivetran_role default_warehouse = <WAREHOUSE> password = '<PASSWORD>'; grant role fivetran_role to user <USERNAME>; -- change role to SYSADMIN for warehouse / database steps use role SYSADMIN; -- create a warehouse for fivetran create warehouse if not exists <WAREHOUSE> warehouse_size = xsmall warehouse_type = standard auto_suspend = 60 auto_resume = true initially_suspended = true; -- change role to ACCOUNTADMIN for user / role steps use role ACCOUNTADMIN; -- grant fivetran access to warehouse grant all privileges on warehouse <WAREHOUSE> to role fivetran_role; -- create database for fivetran create database if not exists <DATABASE>; -- grant fivetran access to database grant all privileges on database <DATABASE> to role fivetran_role;
If you want to authenticate using
KEY_PAIR, perform the following steps:
Use the command line in a terminal window to 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, use the following command:
openssl genrsa -out rsa_key.pem 2048
To generate an encrypted version, use the following command:
openssl genrsa 2048 | openssl pkcs8 -topk8 -v1 <ALGORITHM> -inform PEM -out rsa_key.p8
Various algorithms can be used with the -v1 command line option. These algorithms use the PKCS#12 password based encryption algorithm and allow strong encryption algorithms like triple DES or 128-bit RC2 to be used.
From the command line, generate the public key by referencing the private key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Assign the public key to the Snowflake user:
alter user <USERNAME> set rsa_public_key='<PUBLIC_KEY>';
Type transformation and mapping
The data types in your Snowflake warehouse follow Fivetran's standard data type storage.
How to Flatten and Query JSON data
Next you can use Snowflake's SQL Extensions to query semi-structured (JSON) data.
Note : Table identifiers and aliases
Snowflake does not allow ANSI reserved keywords to be used as table names and aliases in a FROM clause.
When you query your Snowflake warehouse, be sure to encase the table names and aliases in double quotes (for example, use "JOIN" instead of JOIN) to avoid exception errors.
Note : Column Identifiers
Using Fivetran's native Transformations, you will be able to run the functions above automatically against your JSON data and save the results in a new schema.
Snowflake does not allow the following ANSI reserved keywords to be column names when they are capitalized:
To ensure a valid column name, Fivetran converts the keyword to lowercase and encases it in double quotes (for example,
"localtime"). When you query these columns, be sure to use the converted column name.
Data load costs
Fivetran uses SQL queries to merge data into the tables in your warehouse. Because Snowflake bills for compute usage on a per-second basis, they charge you when Fivetran loads data into your warehouse. To reduce costs, we recommend that you provision an X-Small data warehouse with one-minute auto-suspend for Fivetran to load new data into. Most Fivetran customers will be billed for 10 - 20% usage of an X-Small warehouse, though more frequent syncs will increase the usage.