Skip to content

Connect Fivetran to Snowflake

In this tutorial we will show how to setup Snowflake as a destination for snowflake.

Video

Video is still in developemnt.

Requirement

  • You will need a Snowflake account which can be setup as trial if we don't have one today.
  • You will need to have a fivetran account or sign up for a free trial.

Snowflake

Lets start in Snowflake by opening a SQL worksheet (1).

  1. Worksheet

Setup

Let's setup up the necessary resources to have fivetran function correctly. In this example we are going to create:

  • Database: Raw
  • User: service_fivetran
  • Role: service_fivetran
  • Warehouse (XS): service_fivetran

And finish it off by providring the necessary permission for fivetran. All we have to do is provide a password to the script. To run all the code, you will want to select and hit run in the top right.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- create variables for user / password / role / warehouse / database 
  -- Everything (other than the password) needs to be uppercase for it to work.
set role_name      = 'SERVICE_FIVETRAN';
set user_name      = 'SERVICE_FIVETRAN';
set user_password  = '...'; -- UPDATE PASSWORD
set warehouse_name = 'SERVICE_FIVETRAN';
set database_name  = 'RAW';

-- 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;

-- 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 securityadmin for user / role steps
use role securityadmin;

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

1
Statement executed successfully.

Account Locator

Before we move to setting up Fivetran we will want to copy our "account identifier url". This is a unique url to your account that will be used in the setup process of fivetran. The url is unique (1) to a few different type of Snowflake accounts but the "copy account url" feature should work for this tutorial.

  1. URL type URL format
    Regional https://locator.region.snowflakecomputing.com
    Organization https://organization-name.snowflakecomputing.com
    Connection https://organization-connection.snowflakecomputing.com
    Regional Privatelink https://locator.region.privatelink.snowflakecomputing.com
    Organization Privatelink https://organization-name.privatelink.snowflakecomputing.com
    Connection Privatelink https://organization-connection.privatelink.snowflakecomputing.com

Account URL

Fivetran

Next we will setup Fivetran to have Snowflake as a destination.

Setup

Lets start at the homepage once we have our free trial up and running. The first thing we'll want to click is destination on the left side tab. Destination

In the top right click "Add Destination". Add Destination

Next we'll want to select Snowflake as the destination of choice. Select Snowflake

We will want to fill out all the sections marked with arrows using the information we got from the setup and our account locator we copied. We will not need the https:// part to be added into fivetran. Account Locator

Testing

Finally we will click "Save and Test" and fivetran will test our connection. Testing

We can then see our destination and start adding sources to it. Done

Troubleshooting

If you get an error with regard to your host, it is likely you incorrectly have your account locator. Here is some documentation regarding the account locator.