The high watermark field value goes out of sync
There are multiple reasons why the high watermark field can go out of sync (see below).
If the destination of the change replication Flow is a database, the preferred solution, which makes the Flow bullet-proof, is to use a SQL query to calculate the high watermark field value on the fly.
If the destination is not a database or using a solution above is not an option, below are the causes and solutions for the High Watermark Fields going out of sync:
Rename the Flow
- Cause: metrics, including the value of the High Watermark Field, are stored per Flow, using Flow name as a key. Changing a Flow name creates a new metrics file which essentially resets the high-watermark to null.
- Solution: do not rename the Flow.
Change the order of transformations
- Cause: changing the order of transformations in a Flow (unless each transformation has its own name) can cause the renaming of the per-transformation metrics. Hence, leading the high-watermark to go out of sync.
- Solution: set the unique name for the transformation.
Enable/ disable transformations in a Flow
- Cause: enabling/ disabling transformations in a Flow (unless each transformation has its own name) can cause the renaming of the per-transformation metrics. Hence, leading the high-watermark to go out of sync.
- Solution: set the unique name for the transformation.
Change source or destination name
- Cause: changing source or destination name (FROM/ TO) (unless each transformation has its own name) can cause the renaming of the per-transformation metrics. Hence, leading the high-watermark to go out of sync.
- Solution: set the unique name for the transformation.
Get an error when executing the Flow for one or multiple transformations, when others are succeeded
- Solution: set the Auto Commit for the destination database Connection to yes.
Reset the change replication to full extract, followed delta extract
Once change replication is set, Etlworks will continuously update values for the High Watermark Field while the Flow is being executed (manually or by the scheduler).
From time to time, you might need to reset the value for the High Watermark Field. For example, if a new column is added to the table, you will need to rerun a full extraction to include the new column.
Reset a high watermark
Step 1. Open the Flow in the Flows window, select the transformation, and disable change replication. Save the Flow.
Step 2. Run the Flow manually.
Step 3. Once again, enable change replication, and save the Flow.
Reset the change replication to new fixed HWM
Once change replication is set, Etlworks will continuously update values for the High Watermark Fields while the Flow is being executed (manually or by the scheduler).
From time to time, you might need to reset the value for the High Watermark Field to the new hardcoded value.
Reset a high watermark
Step 1. Open the Flow in the Flows window, select the Transformation, and manually set the High Watermark Field Value. Save the Flow.
Step 2. Run the Flow manually.
Step 3. Clear the High Watermark Field Value and let the Flow run manually or by the scheduler.