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

ReplicateTimeCheckColumns='Table1=Updated,Table2=Modified,Table3=ChangedAt';

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

ReplicateTimeCheckColumns='Updated,Modified,ChangedAt';

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:

  1. When a row is added to the Source table, this column's value is set to current datetime.
  2. When a row is modified in the Source table, this column's value is updated to current datetime.
  3. When querying the Source table, this column is a valid filter to be passed to the Source.

SQL Server Source Example

Table definition:

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 support@cdata.com.