ADF incremental copy missed rows: why watermarks lose late commits and how to fix them

#Symptom

An incremental pipeline copies new and changed rows using a modified_at column. Every run succeeds, yet the target slowly falls behind the source. A full reload fixes it for a while, then the gap returns. The missing rows have timestamps that look like they're well inside a window that already ran.

#Why it happens

The standard pattern reads WHERE modified_at > old_watermark AND modified_at <= new_watermark, with the new watermark taken from the source. It has one hidden assumption: every row with a timestamp below the watermark is already committed and visible. That's often false.

10:00:00  Transaction T1 starts. Sets modified_at = 10:00:00. Not committed yet.
10:05:00  Pipeline runs. Reads MAX(modified_at) = 10:04:59, copies visible rows.
          Saves watermark 10:04:59.
10:06:00  T1 commits. Its row has modified_at = 10:00:00.
10:10:00  Next run reads modified_at > 10:04:59. T1's row is never copied.

Other causes produce the same result:

#Step 1: measure the gap

Compare by day, so you can see where it starts:

-- Run on both source and target
SELECT CAST(modified_at AS date) AS d,
       COUNT(*) AS row_count,
       CHECKSUM_AGG(CHECKSUM(order_id)) AS id_checksum
FROM dbo.orders
WHERE modified_at >= DATEADD(day, -14, SYSUTCDATETIME())
GROUP BY CAST(modified_at AS date)
ORDER BY d;

Then list missing keys for a bad day. A linked server, or an export to staging, lets you compare:

SELECT s.order_id, s.modified_at
FROM source_copy.orders AS s
LEFT JOIN dbo.orders AS t ON t.order_id = s.order_id
WHERE t.order_id IS NULL
  AND s.modified_at >= '2026-11-05' AND s.modified_at < '2026-11-06';

Check the run history to see which window each row should have fallen in:

az datafactory pipeline-run query-by-factory -g rg-data --factory-name adf-ops \
  --last-updated-after 2026-11-05T00:00:00Z --last-updated-before 2026-11-06T00:00:00Z \
  --filters operand="PipelineName" operator="Equals" values="pl_incremental_orders" \
  --query "value[].{run:runId,start:runStart,status:status}" -o table

Compare a missing row's modified_at with the window. If it falls inside an earlier window, you have a late commit or a timestamp problem, not an outage. Check how long transactions run on the source:

SELECT s.session_id, t.transaction_begin_time,
       DATEDIFF(second, t.transaction_begin_time, SYSDATETIME()) AS age_s, s.program_name
FROM sys.dm_tran_active_transactions AS t
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
ORDER BY age_s DESC;

#Fix option 1: overlap window with a merge

This is the simplest fix. Re-read a safety margin larger than your longest transaction, and let the sink remove duplicates.

Lookup activity to get the upper bound:

SELECT DATEADD(minute, -2, SYSUTCDATETIME()) AS new_wm

Copy activity source query:

@concat('SELECT * FROM dbo.orders WHERE modified_at > DATEADD(minute, -30, ''',
  activity('GetOldWatermark').output.firstRow.wm, ''') AND modified_at <= ''',
  activity('GetNewWatermark').output.firstRow.new_wm, '''')

Load into a staging table, and then merge:

MERGE dbo.orders AS t
USING stg.orders AS s ON t.order_id = s.order_id
WHEN MATCHED AND s.modified_at >= t.modified_at THEN
  UPDATE SET t.status = s.status, t.amount = s.amount, t.modified_at = s.modified_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, modified_at)
  VALUES (s.order_id, s.status, s.amount, s.modified_at);

Warning

An overlap without a merge creates duplicates. Never add one to an append-only sink. Size the overlap from your longest real transaction, and remember it can't cover a batch that runs for longer than the overlap.

#Fix option 2: rowversion on SQL Server

A rowversion column is set by the engine, in commit-safe order only if you bound it properly. MIN_ACTIVE_ROWVERSION() gives the lowest value still in an open transaction, so stopping below it avoids the race:

SELECT CONVERT(varchar(20), CONVERT(bigint, MIN_ACTIVE_ROWVERSION() - 1)) AS new_wm;

SELECT * FROM dbo.orders
WHERE row_ver > CONVERT(rowversion, CONVERT(bigint, @old_wm))
  AND row_ver <= CONVERT(rowversion, CONVERT(bigint, @new_wm));

Adding a rowversion column to a large table is a schema change that needs a plan; check the impact and don't assume it's instant.

#Fix option 3: use change tracking or CDC

Log-based tools read committed changes and also capture deletes:

ALTER DATABASE sales SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.orders ENABLE CHANGE_TRACKING;

SELECT ct.order_id, ct.SYS_CHANGE_OPERATION, ct.SYS_CHANGE_VERSION
FROM CHANGETABLE(CHANGES dbo.orders, @last_version) AS ct;

Which to use:

#Make the pipeline itself safe

• Update the watermark as the last step, and only on success. Connect it with a success dependency on the copy and merge.
• Set pipeline concurrency to 1, so runs can't race: "concurrency": 1 in the pipeline's properties.
• Use UTC everywhere. Use datetime2, and be clear about time zones when comparing.
• Capture the upper bound before copying, not after, and subtract a small margin from "now".
• Use a stable end value, and don't call GETDATE() inside the copy query, so the window is fixed for the run.

#Repair the existing gap

Rerun the merge with the old watermark moved back before the first missing row. For example, set the watermark back and let the pipeline catch up:

UPDATE etl.watermark SET wm = '2026-10-20T00:00:00'
WHERE table_name = 'dbo.orders';

Caution

Only do this when the sink merges by key. On an append-only target, rewinding the watermark creates duplicates. Take care with downstream systems too, which may recompute after receiving those rows.

#Prevent it next time

• Run a daily reconciliation on count and checksum, and alert on differences above a small tolerance.
• Log the old and new watermark and rows copied for each run.
• Schedule a periodic full comparison to catch deletes and anything missed.
• Related: duplicate records after reruns and choosing an ingestion pattern.