this technique works only if the destination is a database.
Etlworks Integrator automatically tracks the changes in the source and replicates them in the destination. While doing this it automatically calculates the highest peak in value of the high watermark field and uses it to pull only the changed records from the source. It all works perfectly as long the destination table is updated only by running that specific change replication flow. Unfortunately, that is not always the case.
To make a change replication flow bullet proof you can specify a SQL query which will be calculating the high watermark field value on the fly, based on the current state of the destination table. Basically, it will be pulling only the records in the source which do not exist in the destination or have changed in the source since the last run of the flow.
Just enter a SQL query which returns the current maximum field value in the High Watermark Field value:
In this example the high watermark field value is calculated using the SQL query below:
select max(audit_trail_id) from audit_trail_updates
the query is executed on the destination connection.
Ignoring exceptions when executing HWM query
The default behavior when executing an HWM query is to throw an exception if the quire is executed with an error.
There is an edge case when the system is trying to execute an HWM query against a table that does not exist yet. For example, if you configured a transformation to automatically create a destination table and the table doesn't exist yet at the time when HWM query is executed.
To handle the edge case use the field High Watermark Exception to Ignore. You can enter a string (for example,
object doesn't exist) which, if it is found in the exception, will set the HWM value to null instead of throwing an exception.