Snowflake

Though not required, we recommend you create a dedicated Snowflake user for Polytomic. Instructions on how to do so are below.

1. In Polytomic, go to Connections → Add Connection → Snowflake.

2. Enter your Snowflake connection parameters in the relevant fields. Any additional parameters can be added through the Additional parameters field as a query string.

Optional: If your Snowflake instance mandates key pair authentication, tick the Use key pair authentication box above and upload the user's private key (make sure to generate your key pair by following Snowflake's instructions):

3. Click Save.

📘

Troubleshooting connection issues

If saving the connection results in an error, you can try and determine which part of your configuration is wrong by logging into the Snowflake web console and verifying the names of your database, schema, and compute warehouse.

To log into your Snowflake web console, take your account ID and create a URL that looks like this: https://<accountid>.snowflakecomputing.com/console/login. You can log in using the username and password you supplied above.

Setting Snowflake permissions

The sections below contains queries to run in your Snowflake console to set permissions for Polytomic to operate. They should be followed in-order (though you can skip steps if you already have resources you want Polytomic to reuse).

The resource names used (POLYTOMIC_ROLE, POLYTOMIC_DB, POLYTOMIC_WH, etc) can be replaced with ones of your choosing.

1. Create a role

Giving Polytomic its own role allows you to better control the permissions assigned to the Polytomic user under this role. You can create a role as follows:

use role securityadmin;

create role if not exists POLYTOMIC_ROLE;
grant role POLYTOMIC_ROLE to ROLE SYSADMIN;

We assign the Polytomic role to SYSADMIN in order to also give your Snowflake admins access to this role.

2. Create a user under the above role

This is the user that Polytomic will access your warehouse with. You can skip specifying a password if you want Polytomic to use key pair authentication with Snowflake.

create user if not exists POLYTOMIC
password = 'your-own-super-secure-password'
default_role = POLYTOMIC_ROLE
default_warehouse = POLYTOMIC_WH; 

grant role POLYTOMIC_ROLE to user POLYTOMIC;

3. Create a warehouse and grant access to the Polytomic role

Creating a dedicated warehouse will ensure your Polytomic jobs execute without load issues:

create warehouse if not exists POLYTOMIC_WH
  warehouse_size = large
  warehouse_type = standard
  auto_suspend = 300
  initially_suspended = true;
   
grant USAGE 
  on warehouse POLYTOMIC_WH
  to role POLYTOMIC_ROLE;

If, instead of creating a dedicated warehouse, you choose to use an existing warehouse, be sure to grant the Polytomic role USAGE permission to it:

grant USAGE 
  on warehouse <existing warehouse>
  to role POLYTOMIC_ROLE;

4. Create a database and grant access to the Polytomic role

The last step is to create a database and grant the Polytomic role access to it:

create database if not exists POLYTOMIC_DB;

grant CREATE SCHEMA, MONITOR, USAGE
   on database POLYTOMIC_DB
   to role POLYTOMIC_ROLE;

If Polytomic will only be reading data from Snowflake, you can skip the CREATE SCHEMA permission.

Optional: restricting Polytomic to viewing certain schemas only

If you want to restrict Polytomic to certain schemas of your choosing (this is rare), you can run this query for each schema that you wish Polytomic to access:

-- Repeat this for each schema that you wish Polytomic to access
grant usage on schema $existingSchemaName to POLYTOMIC_ROLE;
grant select, insert, update on all tables in schema $existingSchemaName TO POLYTOMIC_ROLE;

Troubleshooting

Snowflake tables do not show up in Polytomic (model editor, destination list, Query Runner)

The Polytomic role that the user falls under needs to have the following permissions:

• USAGE on the relevant schemas
• SELECT on the relevant tables

Additional, verify that no schema parameter is set in the Additional parameters Polytomic Snowflake connection field, as that instructs the Polytomic user to only access the specified schema.