Configuring Incremental Replication in CData Sync
This entry covers how to set up incremental replication for Data Sync.
Date Entered: 2/25/2020 Last Updated: 2/28/2020
When using REPLICATE queries, Sync will try as much as possible to retrieve only records that have been recently changed, rather than the entire table. For many of our sources, this is preconfigured; you can check this by clicking on your query to check the Table Replication Settings:
Sync's incremental behavior relies on the source table having a Date or DateTime column that represents when a row was last updated. Certain connectors, such as generic database sources, will need to manually specify the column. This is configured by setting the ReplicateTimeCheckColumns property in the Other (Optional) input under the advanced connection settings for your source connection. There are two ways to set up this property:
Syntax 1: Table Mappings
This syntax allows you to select a column per table. This can give you fine-grain control over each table.
Syntax 2: List of Column Names
This syntax will use the first column found in the list for every table in your source. This can be ideal if you have many tables with the same ReplicateTimeCheckColumn name.
A good ReplicateTimeCheckColumn for a Source table satisfies three conditions:
- When a row is added to the Source table, this column's value is set to current datetime.
- When a row is modified in the Source table, this column's value is updated to current datetime.
- When querying the Source table, this column is a valid filter to be passed to the Source.
SQL Server Source Example
By default, all rows will be retrieved on every job execution:
Set the ReplicateTimeCheckColumns property:
Update a couple records in source table:
UPDATE IncrementalTable SET job_title='Software Engineer', modified=CURRENT_TIMESTAMP WHERE id=0 UPDATE IncrementalTable SET job_title='CEO', modified=CURRENT_TIMESTAMP WHERE id=5
Now in future executions, only records updated after the previous job run will be retrieved:
We appreciate your feedback. If you have any questions, comments, or suggestions about this entry, please contact our support team at firstname.lastname@example.org.