Snowflake Table

1. Overview

Snowflake is a fully managed SaaS that provides a single platform for data warehousing, data lakes, data engineering, data science, data application development, and secure sharing and consumption of real-time/shared data.

Cinchy introduced Snowflake as a source and target Connector in v5.2 of the platform.

How Connections Loads Data into Snowflake

For batch syncs of 10 records or less, single Insert/Update/Delete statements are executed to perform operations against the target Snowflake table.

For batch syncs exceeding 10 records, the operations are performed in bulk.

The bulk operation process consists of:

1. Generating a CSV containing a batch of records

2. Creating a temporary table in Snowflake

3. Copying the generated CSV into the temp table

4. If needed, performing Insert operations against the target Snowflake table using the temp table

5. If needed, performing Update operations against the target Snowflake table using the temp table

6. If needed, performing Delete operations against the target Snowflake table using the temp table

7. Dropping the temporary table

Real time sync volume size is based on a dynamic batch size up to configurable threshold.

2. Considerations

• The temporary table generated in the bulk flow process for high volume scenarios transforms all columns of data type Number to be of type NUMBER(38, 18). This may cause precision loss if the number scale in the target table is higher

3. Load Metadata

In the Load Metadata pop-up (Image 1), enter the following information and click Load:

Parameter	Description
Connection String	This will be the encrypted connection string. Unencrypted example: account=wr38353.ca-central-1.aws;user=myuser;password=mypassword;db=CINCHY;schema=PUBLIC You can review Snowflake's Connection String guide and parameter descriptions here.
Table	This will be the name of your target table. Ex: dbo.employees

Image 1: Load your Metadata

4. Basic Parameters

1. Add in information about your ID Column if applicable (Image 2).

Note that if you want to use "Delete" action in your sync behaviour configuration then you must input a value for your ID Column and ID Column Data Type.

Parameter	Description
ID Column	The name of the identity column that exists in the target (OR a single column that is guaranteed to be unique and automatically populated for every new record).
ID Column Data Type	Either: Text, Number, Date, Bool, Geography, or Geometry

Image 2: Basic parameters

5. Column Mappings

1. Add in your applicable column(s) (Image 3). See the documentation here for further details on each column type.

When specifying the Target Column in the Column Mappings section, all names are case-sensitive.

Image 3: Column Mappings

6. Filters

You may choose to use CQL to create a filter (Image 4). Review the documentation here for more on filters.

Image 4: Adding a Source Filter