Snowflake

Overview

To set up connection with Snowflake, Statsig needs the following

• Account Name
• Database Name
• Schema Name
• Admin User Name
• If authenticating via login credentials:
  • Admin User Password
• If authenticating via key-pair authentication:
  • Private Key
  • Private Key Passphrase (Optional)

Admin user name and password will be used by Statsig to create a user with restricted access to query from your data warehouse. If you don't want to use this, skip ahead here

Account Name

For the Account Name field, please enter in the format of <id>.<region>.<provider>. So, your information may look something like this:

xy12345.us-central1.gcp

To get this information navigate to bottom left in your Snowflake console, as shown in the picture below and copy the link URL:

The copied URL will look something like this:

https://xy12345.us-central1.gcp.snowflakecomputing.com

You can extract information from here to get the required fields for Account Name, which for this example would be xy12345.us-central1.gcp.

Using <orgname>-<account_name> for Account Name

For the Account Name field, you can also enter your Snowflake account identifier, which typically takes the form <orgname>-<account_name>. To find the <orgname> in the Snowflake console, click on your account profile (usually at the bottom left) to view account details as shown below.

Database and Schema Name

For each data type, provide the database/schema of the table(s) you will be ingesting from.

Key-Pair Authentication

To set up key-pair authentication, first follow the snowflake documentation to generate the private and public keys, and then set the public key on the service user.

The private key can then be provided here

Custom User Privileges

To create a custom user with specific privileges instead of using an admin user, run the following code in your Snowflake worksheet that has sysadmin and securityadmin roles. Replace <USER> and <PASSWORD> with your value, which you will copy over into our console.

BEGIN;

  -- set up variable values to be used in statements later
  -- make sure to configure user_name and user_password with your own values
  SET user_name = '<USER>'; -- REPLACE WITH YOUR OWN VALUE
  SET user_password = '<PASSWORD>'; -- REPLACE WITH YOUR OWN VALUE
  SET role_name = 'STATSIG_ROLE';

  -- change role to sysadmin for warehouse / database steps
  USE ROLE sysadmin;

  -- create a warehouse, database, schema and tables for Statsig
  CREATE OR REPLACE WAREHOUSE STATSIG_INGESTION WITH warehouse_size='XSMALL';
  CREATE DATABASE IF NOT EXISTS STATSIG_STAGING;

  -- change current role to securityadmin to create role and user for Statsig's access
  USE ROLE securityadmin;

  -- create role for Statsig
  CREATE ROLE IF NOT EXISTS identifier($role_name);
  GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

  -- create a user for Statsig
  CREATE USER IF NOT EXISTS identifier($user_name)
  password = $user_password
  default_role = $role_name
  default_warehouse = STATSIG_INGESTION;
  GRANT ROLE identifier($role_name) TO USER identifier($user_name);

  -- grant Statsig role access to create warehouse and schema
  GRANT USAGE ON WAREHOUSE STATSIG_INGESTION TO ROLE identifier($role_name);
  GRANT CREATE SCHEMA, MONITOR, USAGE ON DATABASE STATSIG_STAGING TO ROLE identifier($role_name);

  -- grant Statsig role read access to database and schema passed in
  GRANT USAGE ON DATABASE <DATABASE> TO ROLE identifier($role_name);
  GRANT USAGE ON SCHEMA <DATABASE>.<SCHEMA> TO ROLE identifier($role_name);
  GRANT SELECT ON ALL TABLES IN DATABASE <DATABASE> TO ROLE identifier($role_name);
  GRANT SELECT ON FUTURE TABLES IN DATABASE <DATABASE> TO ROLE identifier($role_name);
  GRANT SELECT ON ALL VIEWS IN DATABASE <DATABASE> TO ROLE identifier($role_name);
  GRANT SELECT ON FUTURE VIEWS IN DATABASE ACTUAL_DATA TO ROLE identifier($role_name);

COMMIT;

After running the script, input the <USER> and <PASSWORD> you created in our console, during Connection Set Up stage under the Advanced settings options.