Snowflake

Snowflake Connection

To connect to Snowflake, create a new Snowflake connection using DataZen Manager, and enter your connection information, including your Account Name and a database login. The database login must have 2FA authentication disabled in order to allow DataZen to operate unattended on a schedule.

Reading

Reading data from Snowflake is similar to all other relational database sources; simply type the SQL command you would like to execute, and optionally specify the @highwatermark marker to retrieve data from a specific point in time. See the Job Reader - Databases section for more information.

Writing

You can create the target schema first by using the Preview DDL Script button the preview and/or execute the table creation manually. However, if the target table does not exist, it will be created automatically. Note that the Snowflake connection does not support automatic schema drifting after the table has been created. If additional fields need to be added in the future, the table will need to be altered manually and the MERGE script updated or recreated.

Writing data to Snowflake using the Snowflake native connection is similar to all other relational database targets; you can generate the SQL command to execute using the General Upsert SQL Script link.

Two areas of interested are highlighted in the screenshot:

  • Batch Count: This setting controls how many records will be merged at a time, per SQL operation; it is used by the @sql_union() operator which will inject up to 1000 records every HTTP call in this example
  • Generate Merge SQL Script: This link allows you select which key field should be used to identify new or updated records; if a CDC Key Field is already defined on the job, it will be pre-selected but can be modified as needed. If no fields are selected, an Append-Only operation will be performed.

To preview the SQL commands that will be sent to Snowflake, click on the Preview SQL... link.