The Id and Watermark are attribute metadata that are used when syncing data from your Synapse Entities into Syncari. The Id attribute will designate the attribute 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 attributes that are to be used as an Id and Watermark. However, there are some synapses where we are not able to determine the attributes 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 attributes are not defined on the Synapse Entity.
Example errors you may see when your Watermark and/or ID attribute is not defined on the synapse's entity schema:
- Watermark attribute not defined for contact
- Id attribute not defined for contact
Important things to understand when selecting the Id and Watermark Attributes
The attribute you designate as the Id attribute will require the following:
- All records should have a value for the attribute
- All values are unique
The attribute you designate as the Watermark attribute will require the following:
- This attribute should change value when the record is updated (this is usually a attribute like "Last Modified")
- All records need to have a value in the attribute
- The watermark attribute should be of a Datetime / Timestamp data type
Setting an attribute with the Id or Watermark attribute
To set an attribute with the Id or Watermark attribute:
1. Go to Schema Studio and select the synapse.
2. Click the Kebab menu for the entity you need to edit and select New Draft.
3. Click on the Attributes button.
4. From the attribute view, select the attribute that will be used as an Id or Watermark and then click Edit Attribute.
5. Select the appropriate checkbox to designate the attribute as either an Id or Watermark. You should only select one of the two options.
6. Click Update Now.
7. Click Publish to publish the changes you've made to the attribute(s)
Setting a Composite Id
Composite Ids are supported for the following Synapses
- Azure SQL
- BigQuery
- DynamoDB
- PostgreSQL
- Snowflake
To set the composite Id, follow the below steps
1. Locate the attribute that will be one of the composite Ids.
2. Click on the Attribute.
3. Click Edit Attribute.
4. Select Id Attribute and then Composite Key.
5. Select the other attributes that will be part of the composite Id.
6. Click Update Now.
7. Click Publish to publish the changes you've made to the attribute(s).
Congratulations! You have updated your Synapse Entity to designate the Id and Watermark attributes.