Step 1 - Set up your Snowflake instance

In order to set up your Snowflake instance, we provide a script that sets up your Snowflake account so that Permutive can connect and import data from your Snowflake account.

The user that runs the script must have the permissions to use the roles;

1. SECURITYADMIN
2. ACCOUNTADMIN

The script does the following;

1. Creates a new role PERMUTIVE_ROLE
2. Creates a new user PERMUTIVE_USER
3. Creates a new warehouse if the specified one does not exist
4. Grants access to PERMUTIVE_ROLE to read the specified database name.
5. Grants access to PERMUTIVE_ROLE to use the schema provided
   1. Grants read access to PERMUTIVE_ROLE for all tables within the schema

Script to setup Password Authentication

There are some fields to fill out at the top of the script;

1. <PASSWORD> - The password for the PERMUTIVE_USER
2. <WAREHOUSE_NAME> - The warehouse that Permutive will use to query the data
3. <DATABASE_NAME> - The database name you want to import
4. <SCHEMA_NAME> - The schema within the database that contains the tables you want to import

begin;

   -- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
   set role_name = 'PERMUTIVE_ROLE';
   set user_name = 'PERMUTIVE_USER';
   set user_password = '<PASSWORD>';
   set warehouse_name = '<WAREHOUSE_NAME>';
   set database_name = '<DATABASE_NAME>';
   set schema_name = '<SCHEMA_NAME>';

   -- change role to securityadmin for user / role steps
   use role securityadmin;

   -- create role for permutive
   create role if not exists identifier($role_name);

   -- create a user for permutive
   create user if not exists identifier($user_name)
   password = $user_password
   default_role = $role_name
   default_warehouse = $warehouse_name;

   -- grant the role to the permutive user
   grant role identifier($role_name) to user identifier($user_name);

   -- change role to accountadmin to grant permissions
   use role ACCOUNTADMIN;

   -- grant permutive role access to warehouse
   grant usage on warehouse identifier($warehouse_name)
   to role identifier($role_name);

   -- grant permutive access to database
   grant usage on database identifier($database_name)
   to role identifier($role_name);

   use database identifier($database_name);

   -- add a statement like this one for each schema you want to have synced by permutive
   grant usage on schema identifier($schema_name) to role identifier($role_name);

   -- add statements granting select permissions and create table permissions (Applicable for tables which has primary key)
   grant select on all TABLES in schema identifier($schema_name) to role identifier($role_name);

 commit;

Step 2 - Create the Connection within Permutive

Once the script has been run, you are ready to connect Permutive to your Snowflake account.

Database

This is the database name you used within the set up script to give Permutive access

Host

To find the host of your instance, look under Admin -> Accounts.

Find the account you want to use and use the copy link next to the Locator field to copy the locator. This should be within the format of https://xy12345.europe-west4.gcp.snowflakecomputing.com

Remove the https:// from the locator and this is now your host; xy12345.europe-west4.gcp.snowflakecomputing.com

Port

This can be left as 443; the default for Snowflake

User

The User created by the set up script; the default is PERMUTIVE_USER.

Role

The Role created by the set up script; the default is PERMUTIVE_ROLE.

Password

The password that was input into the script; this should have been generated by you before running the script and is now used for authentication for the PERMUTIVE_USER