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.
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.
Writing to Snowflake
Polytomic can also write to in Snowflake. Here are the requirements for that:
- Either you must provide a
polytomicschema, or permission to create a
- 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 Type||Snowflake Type|
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;
SELECT permissions on tables that you want to sync from, and
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 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;
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:
USAGEon the relevant schemas
SELECTon the relevant tables
Additional, verify that
schemaparameter is set in the additional configuration details
Updated about 2 months ago