Snowflake

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.

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.

📘

Troubleshooting Connection Issues

If you unable to save the connection, you can try and determine which part of your configuration is wrong by logging into Snowflake and looking for the database, schema, and compute warehouse.

To sign into snowflake take your account id and create a URL that looks like this: https://<accountid>.snowflakecomputing.com/console/login. You can login using the username and password you supplied above. If login fails, or if you cannot find the database, schema, or compute warehouse after logging in, then you will need to resolve those specific access issues.

Writing to Snowflake

Polytomic can also write to tables 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 tables inside of the schema.

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:

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.