# Snowflake Connection

> For AI agents: a documentation index is available at [/llms.txt](/llms.txt). Append `.md` to any page URL for markdown, or send `Accept: text/markdown`.

## How Snowflake connection works

Statsig Warehouse Native connects to your Snowflake account to read experiment data and materialize results in a Statsig-specific schema. To set up the connection, Statsig needs the following:

- Account Name
- Database Name
- Schema Name
- Service User Name
- If authenticating using login credentials:

  - Service User Password
- If authenticating using key-pair authentication:

  - Private Key
  - Private Key Passphrase (Optional)

The service user needs the following permissions:

- READ on any tables you're using for experimentation
- USAGE/WRITE on a Statsig-specific schema that Statsig uses to materialize temp tables and results

> **Info:**
>
> If your data warehouse is IP protected, you must include allowlisting of Statsig IP ranges in your setup steps.

### Account name

For the Account Name field, enter in the format `<id>.<region>.<provider>`. For example:

`xy12345.us-central1.gcp`

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

![Frame 6](https://docs.statsig.com/images/statsig-warehouse-native/connecting-your-warehouse/snowflake/187517221-4bb3dce3-8b8f-4f30-b4d4-fd12e5249722.png)

The copied URL looks something like this:

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

Extract the required Account Name value from this URL. For this example, the Account Name is `xy12345.us-central1.gcp`.

> **Info:**
>
> ### Using <orgname>-<account\_name> for account name
>
> For the Account Name field, you can also enter your Snowflake [account identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier.html#format-1-preferred-account-name-in-your-organization), 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.
>
> ![Snowflake account profile interface](https://docs.statsig.com/images/statsig-warehouse-native/connecting-your-warehouse/snowflake/195217037-ad630f37-a8fe-4b61-823f-ce0e8c984ed0.png)

### Database and schema name

Provide the Schema and corresponding Database where Statsig materializes results.

![Frame 7](https://docs.statsig.com/images/statsig-warehouse-native/connecting-your-warehouse/snowflake/187517225-017b4626-eaea-443b-a042-59fd474ae657.png)

### Key-pair authentication

To set up key-pair authentication, follow the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/key-pair-auth) to generate the private and public keys, then set the public key on the service user.

Provide the private key in the field below:

![Key-pair authentication configuration interface](https://docs.statsig.com/images/statsig-warehouse-native/connecting-your-warehouse/snowflake/1fa7bda1-5d9c-414b-8e2f-7e36b900acfb.png)

### Boilerplate setup SQL

To create a Statsig user with sufficient privileges and a Statsig staging schema, run the following code in a Snowflake worksheet with sysadmin and securityadmin roles.

Replace `<USER>` and `<PASSWORD>` with your values, which you copy into the Statsig console.

Adjust the warehouse size based on the scope of your data.

```sql
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 = '<USERNAME>'; -- 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 WITH warehouse_size='XLARGE'; -- adjust based on your data size
  CREATE DATABASE IF NOT EXISTS STATSIG_STAGING;
  CREATE SCHEMA IF NOT EXISTS STATSIG_STAGING.STATSIG_TABLES;

  -- 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_namespace = STATSIG_STAGING.STATSIG_TABLES
  default_warehouse = STATSIG;
  GRANT ROLE identifier($role_name) TO USER identifier($user_name);

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

  -- ONLY GIVE THIS LEVEL OF ACCESS in the staging schema.
  GRANT CREATE TABLE, CREATE FUNCTION ON SCHEMA STATSIG_STAGING.STATSIG_TABLES TO ROLE identifier($role_name);
  GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA STATSIG_STAGING.STATSIG_TABLES TO ROLE identifier($role_name);
  GRANT SELECT, UPDATE, INSERT, DELETE ON FUTURE TABLES IN SCHEMA STATSIG_STAGING.STATSIG_TABLES TO ROLE identifier($role_name);
  GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA STATSIG_STAGING.STATSIG_TABLES TO ROLE identifier($role_name);

  -- grant Statsig role read access to database and schema passed in
  -- do this at a table level, database level, and/or schema level
  -- for data Statsig needs to access
  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 <DATABASE> TO ROLE identifier($role_name);

COMMIT;
```

### What IP addresses Statsig accesses data warehouses from

[Go to FAQ](https://docs.statsig.com/data-warehouse-ingestion/faq#what-ip-addresses-will-statsig-access-data-warehouses-from)

### Additional setup for Warehouse Explorer

Warehouse Explorer lets you find and bring data from any table into Statsig for ad-hoc analysis.

To enable Warehouse Explorer, grant Statsig additional permission to query the INFORMATION\_SCHEMA metadata tables. These tables allow the Statsig user to read schema, column, and table definitions.

```sql
  GRANT USAGE ON SCHEMA <DATABASE>.INFORMATION_SCHEMA TO ROLE identifier($role_name);
```