Snowflake Read/Write Integration

An alternative to using a reader share is to set up permissions for Thematic to access Snowflake resources directly. This allows for Thematic to both read from and write to tables.


Because Snowflake has very granular permissions controls there are a variety of ways to accomplish this and this article demonstrates only one of these approaches, with a focus on allowing Thematic to export data into Snowflake.

Connecting the Integration

Step 1: Create a new role in your Snowflake account

Make sure to write down the name of the role as you will need it later

Step 2: Create a new user in your Snowflake account

There are two options for providing authentication information to Thematic

Option A: Keyfile authentication (recommended)

The password is optional as we will not be using it but make sure to NOT check the 'Force user to change password on first login' button.

Now we need to assign a public/private key to this user and ensure it is marked as a service. To do this we will need to run two SQL commands and create a key

  1. Set the user as a service
    1. ALTER USER <username> SET TYPE = SERVICE;
  2. Create a public/private key
    1. Instructions can be found in Snowflake's documentation
    2. Example to create a private key (in a terminal window):
      1. openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out thematic_snowflake.p8 -nocrypt
    3. Example to create a public key (in a terminal window):
      1. openssl rsa -in thematic_snowflake.p8 -pubout -out thematic_snowflake.pub
  3. Set the contents of the public key as one that can access the user
    1. ALTER USER <username> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
    2. Ensure this is the contents of the public key created above! Note that it does not require the "BEGIN/END" lines
  4. Ensure you retain the private key to provide in Step 6

Option B: Password authentication

The password field is important and you will need to copy the value entered to provide to Thematic in Step 6. Make sure to NOT check the 'Force user to change password on first login' button

We will also need to mark this user as a legacy service to allow password login without MFA

  1. Set the user as a legacy service
    1. ALTER USER <username> SET TYPE = LEGACY_SERVICE;

Step 3: Assign the role from Step 1 to the user from Step 2

On the role page choose 'Grant to User' and select the user from Step 2

Step 4: Create a table that will be written to

Within the database and schema that you want Thematic to write to, create a table. The schema for this table will be altered by Thematic so for now it can be as simple as possible. In the screenshot below there is only one column 'ID' created.

Step 5: Grant permissions for the role from Step 1 to the resources needed

In particular Thematic will need to be able to:

  • Enumerate the database
  • Enumerate the schemas within that database
  • Enumerate the tables within that schema
  • Have 'usage' of a warehouse for inserting data
  • Have 'ownership' of the target table we will be altering and writing to


These permissions will need to be executed through a worksheet and example permissions would be:

GRANT USAGE ON DATABASE;

GRANT USAGE ON SCHEMA <customer_db.schema_name> TO ROLE THEMATIC_WRITER;

GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE THEMATIC_WRITER;


To write to a table you also need to change ownership to this role. This is so Thematic can determine the schema

GRANT OWNERSHIP ON TABLE <customer_db.schema_name.table_name> TO ROLE THEMATIC_WRITER;


If you want to use a table to read from you will also need to provide access

GRANT SELECT ON TABLE <customer_db.schema_name.table_name> TO ROLE THEMATIC_WRITER;

Step 6: Enter details into Thematic to connect to Snowflake

You will need:

  • Account Identifier: this can be found in various ways
  • Username: for the user created in Step 2
  • Password: for the user created in Step 2
  • Compute Warehouse: the name of the warehouse granted usage in Step 5

On the "Manage Integrations" page choose "Snowflake" as the integration and press connect.

Using the connection to read data from Snowflake

To pull data from Snowflake:

  1. Ensure that the Role created in Step 1 above is provided the necessary access
    1. For example GRANT SELECT ON TABLE <customer_db.schema_name.table_name> TO ROLE THEMATIC_WRITER;
  2. Set up a new Dataset on the Manage Datasets page.
  3. Choose 'Snowflake' from the list of sources
  4. Select the Database, Schema and Table from the enumerations. This will only show entries that the user created above has been allowed access to

  5. Verify the row count matches the expected
  6. Press 'Import Data'
  7. You can now follow the normal flow for setting up a dataset

Using the connection to write data to Snowflake

To write data to Snowflake

  1. Ensure that the Role created in Step 1 above is provided the necessary access
    1. GRANT OWNERSHIP ON TABLE <customer_db.schema_name.table_name> TO ROLE THEMATIC_WRITER;
  2. Find the Dataset in the Manage Datasets page in Thematic
  3. Choose 'Manage Dataset' on the correct Dataset
  4. Choose 'Dataset Export' from the menu
  5. Set up a new Data Destination selecting 'Snowflake' as the destination

  6. Select the Database, Schema and Table from those enumerated
  7. Configure the remaining options for a periodic dataset export

Why does Thematic need table ownership?

As a balance between usability and security we do not ask for permissions to create tables but we still need to be able to ALTER the table to ensure the schema matches the export.

By providing ownership of the destination table we can change the schema as and when the export configuration changes.

Tips / Suggestions

  • You may want to create an explicitly separate database and or schema for where data that Thematic will write to will reside