Snowflake

​

Prerequisites

• Locate your Public Key generated on your behalf. The Public Key will be a long string of text, loosely resembling the format: 'MIIBI...<SHORTENED>...Xrw2nwIDAQAB'
• In order 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.)
• If your Snowflake data warehouse is using Snowflake Access Policies, you will need to have the data syncing service’s static IP available to complete Step 2.

📘 Recommendation: Key-pair authentication with service user Snowflake is deprecating single-factor passwords and will disallow passwords for service users (TYPE=SERVICE) by October 2026. For that reason, we strongly recommend configuring the transfer user as a service user with key-pair authentication.

​

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

1. Review and make any changes to the following setup script.

begin;

-- create variables for user / role / warehouse / database
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 = 'TRANSFER_DATABASE'; -- all letters must be uppercase

-- 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; -- 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 data transfer service
create user if not exists identifier($user_name)
RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key as required in the prerequisite

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

-- create database for data transfer service
create database if not exists identifier($database_name);

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

-- grant service access to database
grant CREATE SCHEMA, MONITOR, USAGE
on database identifier($database_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:

Copy

Ask AI

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

Use the following block:

Copy

Ask AI

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

📘 Using an existing schema By default, a new schema (with a name you provide) will be created in the target Snowflake database upon the initial connection. If instead you create the schema ahead of time, you may remove the CREATE SCHEMA permission, and instead grant ALL PRIVILEGES on the target schema for the designated role. The script below can be used to complete this step:

Copy

Ask AI

set role_name = 'TRANSFER_ROLE';
set database_name = 'TRANSFER_DATABASE';
set schema_name = 'PRECREATED_SCHEMA';

use database identifier($database_name);
grant ALL PRIVILEGES on schema identifier($schema_name) to role identifier($role_name);

📘 Using an existing warehouse or database By default, this script creates a new warehouse and a new database. If you’d prefer to use an existing warehouse/database, change the warehouse_name variable from TRANSFER_WAREHOUSE to the name of the warehouse to be shared/database_name variable from TRANSFER_DATABASE to the name of the database to be shared.

2. 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 2: Configure the Snowflake access policy

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 syncing service’s static IP address. Use the CREATE NETWORK POLICY command to specify the IP addresses that can access your Snowflake warehouse.

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

❗️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 your destination

​

Permissions checklist

• Role grants:
  • USAGE on the target warehouse
  • If the destination schema will be created by the service:
    • USAGE and CREATE SCHEMA on the target database (the setup script also includes MONITOR)
  • If using a pre-created schema:
    • USAGE on the target database
    • ALL PRIVILEGES on the target schema
• User defaults set (optional but recommended): DEFAULT_ROLE, DEFAULT_WAREHOUSE
• If using key auth: user has the PKCS#8 RSA_PUBLIC_KEY set
• If network policies are enforced: our egress IP is allowlisted

​

FAQ

​

Q: How is the Snowflake connection secured?

​

Q: What permissions does the data transfer role need?

• USAGE on the warehouse
• If the destination schema will be created by the service: USAGE and CREATE SCHEMA on the database
• If using a pre-created schema: USAGE on the database and ALL PRIVILEGES on the schema

​

Q: Can I use an existing warehouse?

​

Q: Should I include the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- tags in the public key when adding it to Snowflake?

​

Q: Can I use the same transfer role for multiple destinations?