To set up an automatic import of your usage data from your Snowflake into Sequence, you will need:

  • A dedicated user with the access credentials to the usage data table. You may also want to created a dedicated database and schema to host this table, and a dedicated warehouse to run the transfers
  • If you use network policies, you will need to allow the IP used by Sequence to access your instance of Snowflake

​
Step 1: Create role, user and warehouse in Snowflake

Your Snowflake user must be granted securityadmin andsysadmin roles to complete this step. To verify these roles, run SHOW GRANTS TO USER <your_username>; and review therole column

  1. Review and make changes to the set-up script below:
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 user_password = 'some_password';
   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'; -- table with usage data

   -- 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)
   password = $user_password
   default_role = $role_name
   default_warehouse = $warehouse_name;

   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;

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

  1. 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 Sequence static IP to access the warehouse

  1. Review current network policies to check for existing IP allow-lists
SHOW NETWORK POLICIES;
  1. If there is no existing Snowflake Network Policies (the SHOW query returns no results), skip to Step 3
  2. If there is an existing Snowflake Network Policy, you must alter the existing policy or create a new one to allow the following IP address: 104.199.49.149. You can do this via the CREATE NETWORK POLICY command:
CREATE NETWORK POLICY <policy_name> ALLOWED_IP_LIST = ('104.199.49.149/32');

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: Sharing your details with Sequence

The last step is to share the following details with the Sequence team:

  • The fully qualified Snowflake source table ID: <database>.<schema>.<table_name>
  • The host name of your Snowflake instance: <orgname>-<account_name>.snowflakecomputing.com
  • The full table schema of the Snowflake source table
  • The username and password created in Step 1. We recommend you do this via a password manager
That’s it! You are now ready to automatically import usage data into Sequence 🎉
BigQueryRedshift