Skip to content

Set up Snowflake

This page is a guide to configuring your Snowflake account to use Snowflake as a destination within Data Loader.

Prerequisites

  • An active Snowflake account.
  • The user must have ACCOUNTADMIN or SECURITYADMIN privileges in Snowflake to create a role for Data Loader. Read more about Roles in Snowflake.
  • Data Loader to be assigned USAGE permissions on data warehouses.

In Snowflake, when you use double quotes around an identifier name, it makes the identifier name case-sensitive. We recommend using the create <identifier> <identifier_name> or the create <identifier> "IDENTIFIER_NAME"format. See Snowflake's documentation on identifiers for more information.

Configuring Snowflake as a destination

Once you satisfy the prerequisites, perform the following steps to configure Snowflake as a destination.

Create a Snowflake warehouse

  1. Log in to your Snowflake account.
  2. In the Worksheets tab, click + Worksheet to create a new worksheet.
  3. Paste the following script in the worksheet. The script creates a new role for Data Loader in your Snowflake Destination. 
-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'MATILLION'; -- Replace "MATILLION" with your role name
set user_name = 'USERNAME'; -- Replace "USERNAME" with your username
set user_password = 'PASSWORD'; -- Replace "PASSWORD" with the user password
set warehouse_name = 'WAREHOUSE'; -- Replace "WAREHOUSE" with the name of your warehouse
set database_name = 'DATABASE'; -- Replace "DATABASE" with the name of your database
set schemaName = 'SCHEMA'; -- Replace "SCHEMA" with the database schema name

set db_schema = concat($database_name, '.', $schemaName);

begin;

-- change role to securityadmin for user / role steps
use role securityadmin;

-- create role for Data Loader
create
role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN;

-- create a user for Data Loader
create user if not exists identifier($user_name)
password = $user_password
default_role = $role_name
default_warehouse = $warehouse_name;

-- Grant access to the user
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 Data Loader, if it does not exist
create
warehouse if not exists identifier($warehouse_name)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 600
auto_resume = true
initially_suspended = true;

-- create database for Data Loader
create database if not exists identifier($database_name);

-- grant Data Loader role access to warehouse
grant USAGE
on warehouse identifier($warehouse_name)
to role identifier($role_name);

-- grant Data Loader access to database
grant CREATE SCHEMA, MONITOR, USAGE, MODIFY
on database identifier($database_name)
to role identifier($role_name);

use role accountadmin;
CREATE SCHEMA IF not exists identifier($db_schema);
GRANT USAGE, MONITOR, CREATE TABLE, CREATE EXTERNAL TABLE, MODIFY ON SCHEMA identifier($db_schema) TO ROLE identifier($role_name);

commit;

The values for role_name, user_name, warehouse_name, database_name, and schemaName must be in uppercase.

  1. Press CMD + RETURN (Mac) or CTRL + Enter (Windows) to run the script.
  2. Once the script runs successfully, you can use the credentials from lines 2-7 of the script to connect your Snowflake warehouse to Data Loader.