Snowflake

Create role, user, warehouse, and database in the data warehouse

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

   1
   begin;
   2
   3
   -- create variables for user / role / warehouse / database
   4
   set user_name = 'TRANSFER_USER'; -- all letters must be uppercase
   5
   set role_name = 'TRANSFER_ROLE'; -- all letters must be uppercase
   6
   set warehouse_name = 'TRANSFER_WAREHOUSE'; -- all letters must be uppercase
   7
   set database_name = 'TRANSFER_DATABASE'; -- all letters must be uppercase
   8
   9
   -- change role to securityadmin for user / role steps
   10
   use role securityadmin;
   11
   12
   -- create role for data transfer service
   13
   create role if not exists identifier($role_name);
   14
   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.
   15
   16
   -- create a user for data transfer service
   17
   create user if not exists identifier($user_name)
   18
   RSA_PUBLIC_KEY='MIIBIjANBgkqh...'; -- replace with the complete public key as required in the prerequisite
   19
   20
   -- set default role and warehouse to new user
   21
   alter user identifier($user_name) SET default_role = $role_name;
   22
   alter user identifier($user_name) SET default_warehouse = $warehouse_name;
   23
   alter user identifier($user_name) SET type = service;
   24
   25
   grant role identifier($role_name) to user identifier($user_name);
   26
   27
   -- change role to sysadmin for warehouse / database steps
   28
   use role sysadmin;
   29
   30
   -- create a warehouse for data transfer service
   31
   create warehouse if not exists identifier($warehouse_name)
   32
   warehouse_size = xsmall
   33
   warehouse_type = standard
   34
   auto_suspend = 60
   35
   auto_resume = true
   36
   initially_suspended = true;
   37
   38
   -- create database for data transfer service
   39
   create database if not exists identifier($database_name);
   40
   41
   -- grant service role access to warehouse
   42
   grant USAGE
   43
   on warehouse identifier($warehouse_name)
   44
   to role identifier($role_name);
   45
   46
   -- grant service access to database
   47
   grant CREATE SCHEMA, MONITOR, USAGE
   48
   on database identifier($database_name)
   49
   to role identifier($role_name);
   50
   51
   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:

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

   Use the following block:

   1
   create user if not exists identifier($user_name)
   2
   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:

   1
   set role_name = 'TRANSFER_ROLE';
   2
   set database_name = 'TRANSFER_DATABASE';
   3
   set schema_name = 'PRECREATED_SCHEMA';
   4
   5
   use database identifier($database_name);
   6
   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.

Configure the Snowflake access policy

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

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

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

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

Add your destination

Use the following details to complete the connection setup: host name, database name, your chosen schema name, username, and password.

• Instructions for Analyze / Optimize for Webflow sites
• Instructions for Optimize for non-Webflow sites