Skip to main content

Snowflake

Overview

To set up connection with Snowflake, Statsig needs the following

  • Account Name
  • Database Name
  • Schema Name
  • Admin User Name
  • Admin User Password
Admin user name and password will be used by Statsig to create a user with restricted access to query from your data warehouse.

Account Name

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

aa00000.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:

Frame 6

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.

image

Database and Schema Name

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

Frame 7

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.

Screen Shot 2022-09-07 at 10 36 57 AM