How to implement incremental synchronization between databases

This tutorial uses high-watermark change replication to incrementally synchronize data between two databases — only rows added or modified since the last run are loaded.

How do I set this up?

1. Create source and destination database connections.
2. In Flows, click +, type change replication, and select Change replication using high watermark.

   

3. Define the transformations, mapping, and parameters as you would for any flow where the source is a relational database and the destination is a relational database.
4. On the Parameters tab, set the High Watermark Field and enable change replication.

   Important: Because the High Watermark Field is used in the WHERE clause, it should be indexed in the source database unless the table is very small.

   Tip: Use a SQL query to compute the High Watermark value — see Calculate high watermark field value.

   

5. Optional: define a Source query. The extract SQL is generated automatically; if you provide a custom Source query, Etlworks adds the High-Watermark WHERE condition to it for you.
6. Optional: enable MERGE (UPSERT) so existing destination rows are updated (default is INSERT):
   1. On Parameters, set Action to MERGE. MERGE is supported on Oracle, MS SQL Server, PostgreSQL, MySQL, DB2, Informix, and Sybase. Conditional action is also available — it picks the action per record from a JavaScript expression in Action Conditions.
   2. Set Lookup fields — a comma-separated list of fields that uniquely identify a record for MERGE. Important: on PostgreSQL and MySQL, there must be a unique index covering all lookup fields.
   3. For Conditional, use Action Conditions to enter a JavaScript expression that picks INSERT / UPDATE / DELETE / MERGE per record. Reference source fields as {"field name"}. Example:

      {Types}.equals('U') ? 'update' : 'insert';