The first time I compared the source and target, the counts matched.
ADF told the same story. Rows read matched rows written, the target held what the Copy activity had returned, and the watermark moved forward. I had checked the part that usually catches a bad load, so I moved on.
A few days later, the counts started to drift.
I opened the latest Copy activity. Read and write counts matched. I checked the earlier runs. They matched too. The source was now ahead of the target, but I could not find a run that had lost anything.
That was the part that bothered me. Nothing was red, but this was more than a green status hiding a bad result. The numbers attached to each run were genuinely correct.
I kept looking for one broken load. There was not one.
The gap lived in an assumption I had never written down: once the watermark moved past a value, I assumed no more rows could appear there.
Every Run Had an Alibi
The pipeline used a familiar watermark pattern. It remembered the last successful value, found a new maximum, copied the rows between them, and saved that maximum for the next run. Microsoft’s ADF tutorial describes a timestamp or ID whose value normally keeps increasing as rows are created or updated.
I had interpreted “increasing” to mean the values moved forward over time. I did not think they had to be unique, and that part was reasonable. Several rows can share the same timestamp or sequence value and still be copied in one window.
The missing condition was stronger: when I saved a value as complete, the source had to be finished producing rows at or below that value.
Within that contract, ADF had done exactly what I asked. Every row returned by the source query reached the target. That explained why the read and write counts kept agreeing.
It did not explain why a later source count could include rows the target had never received.
Rerunning the normal incremental load did not help. The next query began after the saved watermark, so anything sitting on the older side of that boundary was no longer part of the search.
That changed the question. I stopped asking, “Which Copy activity dropped the row?” and started asking, “When did the row become visible?”
The Value Was Not Finished
The race became easier to see when I reduced it to two rows sharing one watermark value. Sharing the value was not the mistake. Closing it too early was.
The sequence below separates the source writer from the ADF reader. Row B enters an open transaction while ADF reads the committed rows available to its query, copies Row A, and saves Sep 4 as complete. Row B commits only after that boundary has closed.
-- idle
| row | date | written by | state |
|---|---|---|---|
| Row A | Sep 4 | tx 7690 | committed |
| Row B | Sep 4 | tx 7742 | not inserted |
| Row C | Sep 5 | tx 7711 | next window |
- An open transaction inserts Row B with a Sep 4 business date. It sits in the table, but it is not part of the committed view yet.
- The writer is still uncommitted, so Row B is not part of the committed result available to ADF.
- The reader queries committed state and stops at the Sep 4 boundary. Row B is not part of the result.
- The reader copies the one row it can see: Row A. One row read, one row written.
- The pipeline closes the window and saves the watermark at Sep 4.
- Row B commits with its original Sep 4 date — behind the watermark that was just saved.
- Reconciliation reads the source again and finds Row B. Every future incremental run starts after Sep 4, so the row stays missing.
There was no transport failure. Row B never entered the Copy activity, so ADF had nothing to reject, retry, or report as missing.
The run-level count was correct because it described the rows visible during that query. The later reconciliation count was also correct because Row B had committed by then. I was comparing two accurate counts taken against different committed states.
Several rows can safely share Sep 4 if every Sep 4 row is visible before the pipeline saves that boundary. In my case, Sep 4 was still capable of gaining another committed row after ADF had declared it finished.
The pipeline was following one clock while the database was following another. The watermark tracked the date written on the row. Visibility followed the moment the transaction committed.
A watermark value does not need to be unique. It needs to be finished before the pipeline moves past it.
Unique Would Not Have Saved It
It would be easy to blame the shared date and conclude that every row needs its own watermark value. That would solve one problem, but not this one.
Imagine Row B receives the unique sequence 103 while its transaction is still open. Another transaction commits Row C with 104. ADF can see Row C, save 104, and move on. When Row B finally commits, its unique 103 is already behind the checkpoint.
Uniqueness gives each visible row a deterministic position. It does not guarantee that rows become visible in that order.
The source used its default READ COMMITTED isolation. That protected the Copy query from reading Row B before its transaction finished. I wanted that behaviour. An unfinished change should not quietly leak into the target.
What READ COMMITTED did not promise was that no older-valued row could appear after the query ended. Its promise applied to what the statement could read, not to whether my watermark value was safe to close. Microsoft’s locking and row versioning guide makes that visibility boundary clear.
The date described the row’s place in the business timeline. I had promoted it into a progress marker. Those jobs look similar until a transaction commits late.
A composite cursor such as (ModifiedAt, PrimaryKey) is still useful. It orders visible rows that share a timestamp and prevents ambiguous pagination at the boundary. It cannot order a row the query never saw.
The property I needed was not a different value on every row. I needed watermark order to follow commit visibility.
I Wanted Snapshot Isolation to Be the Answer
The tempting answer was to make every read use one consistent snapshot. That would help if the upper-watermark lookup and Copy query were disagreeing about the source state.
It would not close this hole by itself.
ADF normally performs the watermark lookup, data copy, and checkpoint update as separate activities. Unless I deliberately place the source reads behind one transactional boundary, they may use separate connections and separate views.
Even one perfectly consistent snapshot can only answer, “What was committed when this transaction began?” It cannot promise that an open transaction will not later commit with an older business date. Microsoft’s description of snapshot isolation is useful here: it gives a transaction-level committed view, not a guarantee about future commits.
Snapshot can make the reads agree. It cannot make an unsafe watermark final.
The Practical Fix Was to Reopen the Door
In this field note, I could not change the source to expose a reliable commit token, and I could not enable native CDC. No ADF expression could manufacture commit order from a date column.
Under those constraints, I would make the load comfortable with replay. Instead of treating the last watermark as a sealed wall, each run would reopen a bounded slice of recent history and upsert those rows again.
| Defence | Why I would use it | What it cannot promise |
|---|---|---|
| Overlap window | Gives late commits another chance to be read | A row can still arrive outside the chosen window |
| Idempotent upsert | Makes repeated reads safe when a stable source key exists | It needs a trustworthy key and a delete strategy |
| Composite boundary | Orders rows that share a timestamp | It cannot order an invisible late commit |
| Safety lag | Avoids closing the newest source time immediately | A transaction can outlive any fixed lag |
| Reconciliation | Finds gaps the extraction logic missed | It needs an owned repair path, not just a dashboard |
The overlap window and upsert do most of the recovery work. The window gives Row B another opportunity to appear; the upsert prevents that replay from duplicating rows already copied. I would size the overlap from observed transaction and arrival behaviour, then keep reconciliation because every fixed window remains an assumption.
If the source later exposes change tracking or log-based CDC, I would prefer a token ordered around committed changes. Microsoft’s comparison of SQL Server change tracking and CDC explains why this is a different contract from polling a timestamp.
The Same Gap Gets Worse Across Tables
Once I stopped thinking about one missing row, a harder problem appeared.
Imagine one source transaction updates an Order, inserts an OrderLine, and posts a Payment. The source commits those changes together. Three independently checkpointed pipelines can still expose them downstream in stages:
Source transaction
├── Order updated
├── OrderLine inserted
└── Payment posted
COMMIT: one source state
Downstream exposure
├── Phase A: Order
├── Phase B: Order + Payment
└── Phase C: Order + OrderLine + PaymentPhase A and Phase B may never have existed in the source. Every pipeline can balance its own read and write counts while consumers briefly see a business state the source never committed.
Independent table watermarks do not carry a shared transaction identity, and they do not create an atomic downstream apply. If consumers need the source transaction preserved, the capture mechanism must expose transaction metadata and the target needs a coordinated promotion point.
SQL Server CDC exposes commit LSNs and ordering within a transaction, which is why CDC consumers work with LSN ranges. If the source exposes none of that metadata, I can stage and reconcile the data, but I cannot recreate a transaction boundary I never received.
What I Check Now
I now separate three questions when I review an incremental design:
- Did every row arrive? This includes late commits, equal timestamps, updates, and deletes.
- Can I tell what committed first? A business date may describe the row without describing its commit order.
- Can related changes stay together? If one source transaction touched several tables, consumers should know whether downstream can expose a partial state.
A date watermark can still be the right tool for an append-only source where dates are assigned at commit, replay is cheap, and eventual consistency is acceptable. The danger begins when those assumptions remain unspoken.
That first green run was not lying to me. It had answered a smaller question than I realised.
Did ADF write every row it read? Yes.
Was that watermark value finished before ADF moved past it? The pipeline had no way to know.
The watermark did not need a different value for every row. It needed a boundary the source could actually close.