How to implement incremental synchronization between databases

Overview

In this article, we will be using high watermark change replication for incremental synchronization between two databases.

Process

Step 1. Create source and destination database Connections.

Step 2. Start creating a change replication Flow in the Flows window by clicking +, 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 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 Field 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 a custom SQL in the Source query field, Etlworks 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 Etlworks 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 which is 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';