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. Grants access to PERMUTIVE_ROLE to read the specified database name.
4. Grants access to PERMUTIVE_ROLE to use the schema provided
5. Grants read access to PERMUTIVE_ROLE for all tables within the schema

There are two methods of authentication;

1. Password
2. Key Pair

We recommend using a Password to setup your Snowflake instance as it is the most straight-forward.

Option 1: Run Snowflake setup script for Password Authentication

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

1. SECURITYADMIN
2. SYSADMIN
3. ACCOUNTADMIN

The script does the following;

1. Creates a new role PERMUTIVE_ROLE
2. Creates a new user PERMUTIVE_USER
3. Grants access to PERMUTIVE_ROLE to read the specified database name.

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;

Option 2: Run Snowflake setup script for Private Key Authentication

Before setting up a Permutive User within your Snowflake instance for Private Key authentication, you will need to generate a Public/Private key pair.

Follow the Key-Pair Auth documentation from Snowflake to generate a key-pair. Once you have both a public and private key, follow the Snowflake setup script below with the public key. You will then use the private key to connect to your Snowflake instance from Permutive.

Note that we also support a Private key that is encrypted with a password.

Script to setup Private Key Authentication

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

1. <RSA_PUBLIC_KEY> - The public key 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 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)
rsa_public_key = '<RSA_PUBLIC_KEY>'
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

Snowflake uses UPPERCASE for all of it’s Datbases and Schemas; please ensure you are using uppercase for these values when setting up a connection.

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