Watermarks for Incremental Extracts

A nightly load finishes successfully, yet a changed order is missing from the report. Watermarks can create that gap when the boundary moves faster than the source guarantees.

Define What Watermarks Mean

A watermark is the last source position that a pipeline has safely processed. It can be a modified timestamp, a monotonically increasing key, a rowversion value, or a change tracking version. The choice depends on what the source actually guarantees. A timestamp that is never updated for corrections is a poor change marker.

I ask who sets the marker and in which transaction. If an application updates data now but stamps the modified time later, the load can cross a boundary before the stamp appears. A query written perfectly against a weak marker still loses rows. What guarantee does your source give you?

Store the watermark in a control table with the pipeline name, last committed value, and update time. It should advance only after target changes are durable. Keep the previous value for diagnosis. A value stored only in a job parameter is difficult to recover after a failure.

Use a Closed Start and Open End

Read a range that includes the previous boundary and excludes the new upper boundary. In SQL, that is greater than or equal to the start and less than the end. This shape avoids boundary ambiguity when two consecutive runs meet at the same timestamp. Deduplicate on the business key if the inclusive start repeats rows.

Capture the upper bound before extraction. A moving upper bound can admit rows from a later point while an earlier page is still loading. Save the planned upper bound with the run record, so a retry repeats the same logical interval.

I prefer explicit boundary values in the log. A statement that says “loaded yesterday” is vague across daylight changes and late source writes. UTC timestamps and exact interval endpoints make a missing record investigation much shorter.

DECLARE @from datetime2(3) = '2025-01-01T00:00:00.000';
DECLARE @to datetime2(3) = '2025-01-02T00:00:00.000';
SELECT OrderId, ModifiedAt, Amount
FROM dbo.SourceOrders
WHERE ModifiedAt >= @from
  AND ModifiedAt < @to
ORDER BY ModifiedAt, OrderId;

Handle Equal Values and Paging With Watermarks

Timestamp precision is finite. Several rows can share the same ModifiedAt value. If you page with WHERE ModifiedAt greater than the last row’s time, remaining rows at that time disappear. Use a composite cursor of timestamp and stable key, or read an overlap and apply rows idempotently.

The same rule applies when an API exposes a cursor indirectly. Do not replace its token with a guessed timestamp. Store exactly what the source returns. For a database query, use an ORDER BY that includes the tie breaker. Without it, page order can change between calls.

I have seen a row count used as proof that a page is complete. It says how many rows came back, not which rows. Log the boundary and the last key. The pair gives you a position you can reason about after a partial failure.

DECLARE @last_time datetime2(3) = '2025-01-01T12:00:00.000';
DECLARE @last_id bigint = 1000;
SELECT TOP (500) OrderId, ModifiedAt, Amount
FROM dbo.SourceOrders
WHERE ModifiedAt > @last_time
   OR (ModifiedAt = @last_time AND OrderId > @last_id)
ORDER BY ModifiedAt, OrderId;

Know What Modified Dates Miss

A modified date can miss hard deletes because the row is gone. It can also miss changes performed by code that forgets to update the column. Backdated corrections, timestamp precision, and source clock drift create other gaps. A safety overlap helps with delayed visibility, but it cannot recover a delete that leaves no trace.

Rowversion changes when a row is updated in that database. It is useful for scanning changed rows, but it is not a date and it does not describe deletes from a base table. It also needs a consistent upper boundary. Treat it as a database-local version marker, not as a business event time.

If deletes matter, use a source feature that records them, such as Change Tracking or Change Data Capture, or receive explicit tombstones. I ask this question before the first production load. “We do not delete customers” has a habit of becoming “except for this cleanup job.”

Use Change Tracking Boundaries Correctly

Change Tracking provides versions for changed keys, including deletions. Before a run, check that the saved version is still at or above the table’s minimum valid version. If retention has removed the needed history, stop and rebuild a baseline. Continuing would silently omit changes.

Read changes between a saved version and an upper version captured for the run. Rejoin changed keys to current rows for inserts and updates. A deleted key will not join to the base table, so handle that operation separately. Save the upper version after target apply succeeds.

The database must have Change Tracking enabled, and the table must be tracked. These checks are operational prerequisites, not details to discover during an outage. I keep the minimum valid version check beside the extraction code so the failure is clear.

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS upper_version,
       CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.SourceOrders'))
           AS minimum_valid_version;

Commit the Target and Watermarks Together

The target apply and watermark update should be one transaction when possible. Stage the extracted rows first, then begin the target transaction. Apply inserts and updates, handle deletes under the agreed rule, update the control row, and commit. A rollback leaves the old watermark so the interval can be replayed.

For a cross-system load, one transaction cannot cover the API and SQL Server. Save raw pages or files with durable receipts. Then make the SQL Server apply idempotent and checkpoint only committed pages. The recovery design replaces a distributed transaction with explicit replay rules.

A retry should use the original run’s upper boundary. If it samples a new upper boundary, it is a new run and needs its own audit trail. I want the log to show which source interval produced each target change.

Reconcile and Reset Deliberately

Run periodic comparisons against a full source snapshot or source control totals. A process built on watermarks can be green while it silently misses rows. Compare business keys, not only counts. Two missing keys and two duplicates leave the count unchanged. Sample changed values as well when the source can provide them.

When a gap is found, decide whether to replay an overlap, reload a partition, or rebuild the target. Document the reset procedure and keep the old watermark in the log. Do not simply move the marker backward without considering duplicate application and deletes.

Watermarks are small state with large consequences. Keep their values visible, test retries, and test retention expiry. The load should be able to tell you exactly which interval it owns. If it cannot, the marker is just a number in a table.

Related reading on this blog: Incremental Loads: Moving Only What Changed and Introduction to Change Data Capture (CDC) in SQL Server 2008.

A watermark is not a clock reading, it is a promise about committed source changes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.