Snowflake

Step 1: locate your public key

Locate the public key provided to you. The public key will be a long string of text, loosely resembling the format: MIIBI...<SHORTENED>...Xrw2nwIDAQAB. You will register this public key on the Snowflake user you create in Step 2.

Step 2: create role, user, and warehouse

To complete the following setup steps, you or a Snowflake admin on your team must have the SECURITYADMIN and SYSADMIN roles. To check your account for these roles, run SHOW GRANTS TO USER <your_username>; and review the role column.

Review the setup script

Review and make any changes to the following setup script. Update database_name and schema_name to point at the database and schema you want to read from, list the tables you want to sync in the grant statements, and paste the public key from Step 1 into RSA_PUBLIC_KEY.

Setup script

begin;

-- create variables for user / role / warehouse
set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
set warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
set database_name = 'SOURCE_DATABASE'; -- all letters must be uppercase
set schema_name = 'SOURCE_SCHEMA'; -- all letters must be uppercase

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

-- create role for the transfer service
create role if not exists identifier($role_name);
grant role identifier($role_name) to role SYSADMIN; -- establish SYSADMIN as the parent of the new role. Note: this does not grant the access privileges of SYSADMIN to the new role.

-- create a user for the transfer service
create user if not exists identifier($user_name)
RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key from Step 1

-- set default role and warehouse, and mark the user as a service account
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 the 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 warehouse and database access to the transfer role
grant USAGE on warehouse identifier($warehouse_name) to role identifier($role_name);
grant USAGE on database identifier($database_name) to role identifier($role_name);

-- grant schema and table read access to the transfer role
use database identifier($database_name);
grant USAGE on schema identifier($schema_name) to role identifier($role_name);
grant SELECT on table identifier($schema_name || '.<TABLE_NAME_A>') to role identifier($role_name); -- repeat for each table you want to sync
grant SELECT on table identifier($schema_name || '.<TABLE_NAME_B>') to role identifier($role_name);

commit;

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

To grant access to every table in the schema instead of listing tables individually, replace the per-table grants with a single grant on all tables in the schema.

Grant all tables

grant SELECT on all tables in schema identifier($schema_name) to role identifier($role_name);

Run the script

In the Snowflake interface, select the dropdown next to the Run button, and click Run All. This will run every query in the script at once. If successful, you will see Statement executed successfully in the query results.

Step 3: configure the Snowflake access policy

If your Snowflake data warehouse is using Snowflake Access Policies, a new policy must be added to allow the static IP to read from the warehouse. Reach out to your contact for the static IP address to use.

Review current network policies

Run the following to check for existing IP safelists.

Show network policies

SHOW NETWORK POLICIES;

If there is no existing Snowflake network policy (the SHOW query returns no results), you can skip to Step 4. If there is an existing Snowflake network policy, you must alter the existing policy or create a new one to safelist the static IP address.

Create a network policy

Run the following to create a new network policy that safelists the static IP address.

Create network policy

CREATE NETWORK POLICY <transfer_service_policy_name> ALLOWED_IP_LIST = ('<static_ip>');

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, and will return an error if you try. Be careful when setting your first network policy.

Step 4: submit your connection details

Provide the following details to complete the source setup:

The name is a descriptive name of the source
The host (e.g., account.us-central1.gcp.snowflakecomputing.com)
The port [e.g., 443]
The database from Step 2
The username from Step 2

Getting started

Core concepts

Features

Sources

Observability

Security & compliance

Developer SDKs

Step 1: locate your public key

Step 2: create role, user, and warehouse

Step 3: configure the Snowflake access policy

Step 4: submit your connection details

​Step 1: locate your public key

​Step 2: create role, user, and warehouse

​Step 3: configure the Snowflake access policy

​Step 4: submit your connection details

Step 1: locate your public key

Step 2: create role, user, and warehouse

Step 3: configure the Snowflake access policy

Step 4: submit your connection details