How to implement incremental synchronization between database and any destination

Overview

In this article, we will be using high watermark change replication for incremental synchronization between a database and any destination.

Process

Step 1. Create a source database Connection.

Step 2. Create a destination Connection, which can be anything supported by Etlworks.

Step 3. Start creating a Flow in the Flows window by clicking +, and typing in database. Select any Flow where the source is a database:

Step 4. Continue by defining Flow transformations, Mapping, and parameters as you would for any other Flow when:

• the source is a relational database, and
• the destination is any Connection supported by Etlworks.

Step 5. Select the Change Replication tab and configure parameters for 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 (unless your table is very small).

Step 6. 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.