Configure the Id and Watermark fields for a Synapse Entity

The Id and Watermark are field attributes that are used when syncing data from your Synapse Entities into Syncari. The Id attribute will designate the field to be used as an Id for the entity record. The Watermark attribute will help the sync keep track of which records were recently updated and need to be retrieved from the Synapse (source).

For most Synapses, we will automatically identify the appropriate fields that are to be used as an Id and Watermark. However, there are some synapses where we are not able to determine the fields to be designated as an Id or Watermark. This is true for database synapses (i.e MySQL, PostgreSQL, Redshift) and synapses that read files (Google Sheets, SFTP, Amazon S3 and Import CSV File). If using these types of synapses, you will expect to see an error when publishing your pipeline that indicates one of the fields are not defined on the Synapse Entity.

Example errors you may see when your Watermark and/or ID field is not defined on the synapse's entity schema:

- Watermark field not defined for contact

- Id field not defined for contact

Important things to understand when selecting the Id and Watermark fields

The field you designate as the Id field will require the following:

• All records should have a value for the field
• All values are unique

The field you designate as the Watermark field will require the following:

• This field should change value when the record is updated (this is usually a field like "Last Modified")
• All records need to have a value in the field
• The watermark field should be of a Datetime / Timestamp data type

Setting a field with the Id or Watermark attribute

Step 1: Create a New Draft of the Entity

1. Go to Schema Studio
2. Select the Synapse
3. Click on the kebab menu icon for the Entity you need to edit and select New Draft
4. Click on the Fields button

Step 2: From the field view, select the field that will be used as an Id or Watermark

1. Locate the field that will be used as an Id or Watermark
2. Click on the Field
3. Click Edit Field

Step 3: Select the appropriate checkbox to designate the field as either an Id or Watermark. You should only select one of the two options. 

1. Select the appropriate option for the field "Id Field" or "Watermark Field"
2. Click Save

Step 4: Click Publish to publish the changes you've made to the field(s)

Setting a Composite Id

Composite Ids are supported for the following Synapses

• BigQuery
• DynamoDB
• PostgreSQL
• Azure SQL

To set the composite Id, follow the below steps

1. Locate the field that will be one of the composite Ids
2. Click on the Field
3. Click Edit Field
4. Select Id Field and then Composite Key
5. Select the other fields that will be part of the composite Id
6. Click Publish to publish the changes you've made to the field(s)

Congratulations! You have updated your Synapse Entity to designate the Id and Watermark fields.