Syncing from Snowflake

Polytomic requires a read-only Snowflake account with access to all the tables and schemas you want to query. Though not required, we recommend you create a dedicated Snowflake user for Polytomic. There are instructions on how to do so below.

In Polytomic, go to Connections → Add Connection → Snowflake.
Enter your Snowflake connection parameters in the relevant fields. Any additional parameters can be added through the Additional parameters field as a query string.

To use key pair authentication, you can tick the Use key pair authentication box above and upload the user's private key:

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.

Permissions & Objects

Permission requirements for Polytomic are different based on whether you use Snowflake as a source or as a destination. At a minimum, it is recommended that Polytomic is given its own User and Role. Optionally, you may set up Polytomic its own database and warehouse; these are particularly recommended if you plan on using Polytomic to sync data into Snowflake.

The sections below should be followed in-order for new warehouse setup, or can be cherrypicked if you already have some setup complete. You may change any of the resource names, but be sure to update the resource names in subsequent commands.

Setting up a role

It is a good idea to give Polytomic its own role so you can give the Polytomic user its own set of permissions. You can create a suitable role as follows.

use role securityadmin;

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

We will grant permissions to this role and assign it to the Polytomic user. We assign the role to SYSADMIN to give regular Snowflake Admins access to its objects. You may wish to grant PUBLIC if you want everyone to access the tables.

If you are planning on creating a separate database for Polytomic, you may skip setting up additional permissions. If you are planning on using an existing database for Polytomic, you will need to give access to the database, schema, and tables for Polytomic.

You can use the GRANT ROLE syntax as above if there is another role that is suitable for Polytomic to reuse. If you wish to grant Polytomic permission individually, you must do so on the database, schema, and table level. For example:

-- `usage` is required to generally use/access a given database
-- `create schema` allows Polytomic to create its own schema if you wish to use the Polytomic to create tables in Snowflake.
grant usage, create schema on database $existingDatabaseName to POLYTOMIC_ROLE;

-- 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;

Polytomic needs SELECT permissions on tables that you want to sync from, and INSERT/UPDATE permissions that you want to sync to. These permissions can be given individually, or they can be assigned by assigning another role to the POLYTOMIC_ROLE.

Polytomic should have its own schema in a database if you are using Snowflake as a destination. It is recommended that simply grant CREATE SCHEMA on the database so that Polytomic can create its own schema (which will be called POLYTOMIC) and have ownership of its own tables. Your users may also want to create new schemas from Polytomic.

Setting up a Warehouse

Though not required, a dedicated warehouse is recommended for Polytomic, especially if you plan on loading data into your warehouse or have large datasets that Polytomic will query. This will ensure your 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;

Setting up a user

This is the user that Polytomic will access your warehouse with. Please customize the password in the snippet below.

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;

Creating a database (optional)

You may create a database if you want the data Polytomic writes to your Snowflake instance to live in its own database, though this will restrict Polytomic's ability to see data in your other databases.

create database if not exists POLYTOMIC_DB;

Grant the Polytomic role permissions to your database

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

Troubleshooting

Tables do not show up in Polytomic (Model picker, Destination picker, SQL Runner)

The role the Polytomic user is using 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 configuration details

Syncing from Snowflake