How to implement incremental synchronization between databases

We will be using high watermark change replication for incremental synchronization between two databases.

Step 1. Create a source and destination database connections.

Step 2. Start creating a change replication flow in the Flows window by clicking the + button and typing in change replication. Select "Change replication using high watermark".

Step 3. Continue by defining flow transformations, mapping, and parameters as you would for any other flow where:

• the source is a relational database, and
• the destination is a relational database.

Step 4. When configuring parameters for transformations, set the High watermark field and enable the change replication.

Since we will be using a high watermark field in the WHERE clause, it is important to have an index for this field in the source database, unless your table is very small.

It is recommended to use the SQL query to calculate the value of the high watermark filed, as explained in this article.

Step 5. Optionally, define the Source Query.

Typically the extract SQL used for change replication is generated automatically. If you have entered custom SQL in the Source Query field, Integrator will be able to automatically modify it by adding a condition (in a WHERE clause) for the high watermark field. 

Step 6. If needed, you can configure Integrator to perform MERGE (UPSERT) on the destination database (the default is INSERT):

1. On the Parameters screen, set the Action to MERGE. MERGE is supported for Oracle, MS SQL Server, PostgreSQL, MySQL, DB2, Informix, and Sybase databases. This action can also be conditional, which requires entering conditions, using JavaScript, in the Action Conditions fields.

2. Set the Lookup Fields - a comma-separated list of fields used to uniquely identify a record for the MERGE action. 

For PostgreSQL and MySQL, there must be a unique index that includes all "lookup fields".

3. If needed - specify Action Conditions. When a conditional action is selected, use this field to enter a JavaScript expression that identifies which action (INSERT, UPDATE, DELETE, MERGE) should actually be used. You can reference field values from the current source record as {"field name"}, as in this example: 

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