How can we help?

Search

Send data from Snowflake to Cordial

Overview

When Snowflake as a data source is enabled in your account, Cordial automatically queries one or multiple Tables and/or Views in your Snowflake warehouse on a regular interval. Cordial automatically detects any new or updated customer records—and updates those records in our system. This keeps your customer data in sync with Snowflake cloud data for real-time segmentation and personalization, eliminating the need for a separate integration between your cloud warehouse and messaging platform.

Additional benefits

  • Full refresh syncs used by one-time data jobs as well as incremental syncs using recurring data job automations to leverage real-time data.
  • Load contact and supplement data into Cordial.
  • Customize data refresh intervals.
  • Write transformation scripts to further modify data as it comes into Cordial.

Use cases

You can send data from Snowflake to Cordial via the UI by selecting Snowflake as a destination for:

Create a Table or View in your Snowflake account

In your Snowflake account, create a Table or View to store the customer data you want to use in Cordial. Here are some important settings to keep in mind when creating your Table in Snowflake:

  • At minimum, you must have read-only access to the Table in Snowflake and access to the specific Table or View that will send data to Cordial.
  • Cordial is unable to ingest binary Table columns.
  • Set a specific maximum length in your Table/View for VARCHAR columns. Column size limit is 8kb or VARCHAR(8192).
  • Your Table/View must be set to convert nanoseconds (default in Snowflake) into microseconds (default in Cordial) in order to successfully transfer date attributes.

Geo attributes

Geo attributes should be represented as separate columns. For example, geo attribute homeaddress would be broken up into columns such as:

  • homeaddress.street_address
  • homeaddress.city
  • homeaddress.state
  • homeaddress.postal_code

Array attributes

Array attributes will be updated as a whole unless additional syntax is included to add or remove an item from an array, as follows:

  • Remove from array: { "remove": ["apple"] }
  • Add to array: { "add": ["apple"] }

Your Table/View must be set to convert nanoseconds (default in Snowflake) into microseconds (default in Cordial) in order to successfully transfer date attributes.

Enable Snowflake as a data source

1. Log in to Cordial, navigate to Integrations > Partners, and select Enable on the Snowflake card.

2. Fill in the required information:

  • Snowflake account identifier (format should be [organization]-[identifier])
  • Warehouse name
  • Username
  • Authentication method: Saved key
  • Role name
  • Database name
  • Schema name
  • Table or View name

Connection parameters are case sensitive and should match the exact case of the named objects—warehouse, user, role, database, Table/View, or schema—in Snowflake.

3. To add a Table/View, click Add Table/View.

4. Click Test Connection.

5. If it's successful, select Continue.

Saved key authentication

Select this method if you want to use key-pair authentication in Snowflake.

1. You first need to create a secret (aka saved key) in Cordial by navigating to Settings > Account Settings > Secrets Vault > New.

2. Your new secret will include a Public key (PEM format).

3. Update the user in your Snowflake account using the Public key (PEM format) of the secret you created.

Don't include the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- that appear with your PEM key. These are markers for where the key begins and ends, not part of the PEM key.

4. Set the type of user in your Snowflake account to SERVICE. Example process:

ALTER USER example_user SET 
TYPE=SERVICE 
RSA_PUBLIC_KEY='ExAmPlE_PUblIC_keY';

5. In the export job from Cordial, select Saved Key as the authentication method and choose your Cordial secret under Saved key.

Send data from Snowflake to Cordial

1. Once you've successfully enabled Snowflake as a source, navigate to Data Jobs > Create New Data Job, select the appropriate Data Job, and fill in the required fields.

2. Select Edit in the Data Source pane.

3. Choose External under Data Source.

4. Choose Snowflake under Data location.

5. Choose the appropriate Snowflake connection and Snowflake Table/Views.

Cordial will automatically query Snowflake at a specified interval, updating modified records and inserting any new records since the last sync. You can configure your data refresh interval to occur every 15 minutes, every 30 minutes, every hour, every week, or every month to optimize access to real-time data.

6. Choose the sync type: Full refresh or Incremental.

Sync types

One-time Data Jobs only support full refresh syncs. Data Job Automations support both full refresh and incremental syncs.

Full refresh

  • Used in one-time data jobs and recurring Data Job automations.
  • Selects all records from the Snowflake Table.
  • Query doesn't have WHERE and ORDER elements.

Incremental

  • Used in recurring Data Job automations.
  • Cordial stores sequence (last updated date from last imported row) as {prevValue}
  • Select the desired Cursor field from the dropdown.
  • The cursor value is not repeated between syncs option controls how Cordial selects records for the incremental sync.

If the cursor field has unique values, such as unique IDs, we can simplify the process and select all the new records. But if the cursor field is not unique, such as a timestamp or date, Cordial skips one or more records during that sync with the maximum value.

In the examples below, UPDATED_AT and ORDER_ID are cursor fields:

  • If you select The cursor value is not repeated checkbox, Cordial creates a query like this:
SELECT ... WHERE ORDER_ID > {prevValue}
  • If the checkbox is not selected, Cordial runs a query like this:
SELECT ... WHERE UPDATED_AT > {prevValue} AND UPDATED_AT < (SELECT max(UPDATED_AT) FROM {Table})

The second example means Cordial doesn't import at least one row with max(UPDATED_AT).

Example scenario

In this example, the value {prevValue} was 2024-10-11T11:45 for the last sync of a Table. When queried, this Table contains three new rows, each with a timestamped UPDATED_AT cursor value. These are:

  • 2024-10-11T12:00
  • 2024-10-11T12:15
  • 2024-10-11T12:30

The third row at 12:30 was inserted just as the Table was queried by the Data Job, and there are multiple other 12:30 timestamped records that were not inserted in time for this sync to pick them up.

  • If The cursor value is not repeated between syncs is not checked: The row 2024-10-11T12:30 is ignored because it's the maximum UPDATED_AT date in the Table. When the job runs next, the timestamp stored in {prevValue} is 2024-10-11T12:15. On the next sync, all of the records at 2024-10-11T12:30 are picked up, as well as any further ahead in time. Although the record is not immediately processed, this removes the risk that records are skipped due to overlap in syncing/insertions to the Table. Skipped records are always picked up on the next sync.
  • If The cursor value is not repeated between syncs is checked: The row 2024-10-11T12:30 is read and processed. On the next run, any records that share this timestamp will be ignored and never processed.

API support

The following Cordial endpoints support Snowflake as a data source:

  •   {
   "source":{
      "transport":"snowflake",
      "connection":"same_name_used_in_marketplace_setup",
      "tableview":"same_table_view_name_used_in_marketplace_setup"
   },
   "hasHeader":true,
   "confirmEmail":"msmith@example.com"
}

Disable connection

To stop the sync, you can disable the connection by clicking Configure on the Snowflake Integrations card and selecting the Disable button. When the connection is re-enabled, the job will continue looking for all records that were inserted or updated since the last sync.

Comments

0 comments

Please sign in to leave a comment.