Snowflake

Source and destination

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.

  1. In Polytomic, go to ConnectionsAdd ConnectionSnowflake.

  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.

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

  1. 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.

Writing to Snowflake

Polytomic can also write to in Snowflake. Here are the requirements for that:

  • Either you must provide a polytomic schema, or permission to create a polytomic schema; and
  • Ensure the Polytomic user has full permissions to the schema.
    • this include permissions such as MONITOR and USAGE, but also schema permissions like CREATE FILE TYPE. It's best practice to grant Polytomic user full access to its own schema to minimize troubleshooting and downtime.

You should be able to create a new table using the Create new table... target for Snowflake. A new table will be created after you save the sync for the first time.

If you want to change the schema, either modify the schema by hand and reflect the updates in Polytomic, or get Polytomic to create another new table.

Before saving the sync, you will input a table name, to be used when creating the new table. The new columns will be named and typed based on the source column names.

Polytomic creates new columns in Snowflake with the largest types available to ensure the data will fit. For each selected field, Polytomic will create a table with the following column types:

Polytomic TypeSnowflake Type
BooleanBOOLEAN
StringTEXT
NumberNUMBER(38,18)
Date/TimeTIMESTAMP
ArrayARRAY
ObjectVARIANT
UnknownSTRING

Example

Suppose we want to create a new table that contains user IDs, organization IDs, the user's name, and the user's last login time.

When you attempt to create the table, Polytomic will execute the following:

CREATE SCHEMA IF NOT EXISTS `polytomic`;

CREATE TABLE `polytomic`.`last_login_info` (
	`users_id` TEXT NOT NULL,
	`orgs_id` TEXT,
	`name` TEXT,
	`last_login` TIMESTAMP,
	`polytomic_created_at` TIMESTAMP,
	`polytomic_updated_at` TIMESTAMP,
	CONSTRAINT idx_last_login_info_users_id UNIQUE (users_id));

Note the following:

  • Snowflake tables will not have an auto-incrementing key
  • A unique index is created for the Identity value.
  • Identity columns are not-nullable, but the rest of the fields are.
  • Column names are normalized to remove non-compliant characters.
  • Our models had a name conflict (same-name "Id" fields coming from Users and Organizations models respectively), so the column name includes the model name as well.
  • Metadata columns are created in the Snowflake table to track both Creates and Updates.

Permissions & Objects

Permission requirements for Polytomic may change based on whether you use it as a source or as a destination. At the 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