Snowflake

Prerequisites

• Your Snowflake user must be granted securityadmin and sysadmin roles to complete the next step. To verify these roles, run SHOW GRANTS TO USER <your_username>; and review the role column.

Step 1: Create role, user, and warehouse in the data warehouse

1. Review and make any changes to the following setup script. As written, the script should be run once for each table that needs to be transferred.

begin;

   -- create variables for user / password / role / warehouse / database
   set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
   set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
   set warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
   set database_name = 'SOME_DATABASE';
   set schema_name = 'SOME_SCHEMA';
   set table_name = 'SOME_TABLE'; -- repeat for all tables

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

   -- create role for data transfer service
   create role if not exists identifier($role_name);
   grant role identifier($role_name) to role SYSADMIN;

   -- create a user for data transfer service
   create user if not exists identifier($user_name)
   RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- where the complete public key can be copied from the connection form in the onboarding UI

   -- set default role and warehouse to new user
   alter user identifier($user_name) SET default_role = $role_name;
   alter user identifier($user_name) SET default_warehouse = $warehouse_name;
   alter user identifier($user_name) SET type = service;


   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 transfer service
   create warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;

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

   -- grant service access to database
   grant MONITOR, USAGE
   on database identifier($database_name)
   to role identifier($role_name);

   -- use provided database
   use database identifier($database_name);

   -- grant service access to schema
   grant USAGE
   on schema identifier($schema_name)
   to role identifier($role_name);

   -- use provided schema
   use schema identifier($schema_name);

   -- grant service access to table
   grant SELECT
   on identifier($table_name)
   to role identifier($role_name);

 commit;

📘

Alternative authentication method: username & password

By default, this script creates a new user using key-pair authentication. If you'd prefer to use username & password authentication, instead of:

SQL

create user if not exists identifier($user_name)
 RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Use the following block:

SQL

create user if not exists identifier($user_name)
password = 'some_password';

Because service accounts cannot use username & password authentication, you will also need to remove the following line from the provided script:

SQL

alter user identifier($user_name) SET type = service;

📘

Using an existing warehouse

By default, this script creates a new warehouse. If you'd prefer to use an existing warehouse, change the warehouse_name variable from TRANSFER_WAREHOUSE to the name of the warehouse to be shared.

2. In the Snowflake interface, select the All Queries checkbox, and click "Run". This will run every query in the script at once. If successful, you will see Statement executed successfully in the query results.

Step 2: Configure the Snowflake access policy

If your Snowflake data warehouse is using Snowflake Access Policies, a new policy must be added to allow the transfer service static IP to write to the warehouse.

1. Review current network policies to check for existing IP safelists.

SHOW NETWORK POLICIES;

2. If there is no existing Snowflake Network Policies (the SHOW query returns no results), you can skip to Step 3.
3. If there is an existing Snowflake Network Policy, you must alter the existing policy or create a new one to safelist the data transfer service static IP address. Use the CREATE NETWORK POLICY command to specify the IP addresses that can access your Snowflake warehouse. The IP addresses can be found here.

CREATE NETWORK POLICY <transfer_service_policy_name> ALLOWED_IP_LIST = ('5.4.7.8/32');

❗️

Creating your first network policy

If you have no existing network policies and you create your first as part of this step, all other IPs outside of the ALLOWED_IP_LIST will be blocked. Snowflake does not allow setting a network policy that blocks your current IP address. (An error message results while trying to create a network policy that blocks the current IP address.) But be careful when setting your first network policy.

Step 3: Add source to service

Use the host name, database name, username, and password to complete the connection.