Your Delta Feed Misses Late Commits: Build a Trustworthy Cursor

A row shipped late because the watermark was not a commit boundary. Walk the failure, then define coverage, boundary, retention and a safe resume.

Here is a failure I have watched happen twice, in two different engines. An export runs at 10:00 and records the highest last-modified timestamp it saw. One transaction that began at 09:58 is still open, commits at 10:03, and carries a timestamp from when it started. The next export asks only for rows newer than 10:00 and never sees it. The transfer size was excellent and the data was wrong.

Walk the export that lost a row

Trace the order of events rather than the SQL. The application writes the row's modification time when the transaction begins, not when it commits. The exporter reads a wall-clock watermark while that transaction is still open. The transaction then commits with a modification time older than the saved watermark. The consumer's next batch starts after the watermark, so the row is behind the cursor and will never be selected again.

Nothing logs an error, because nothing failed. That is what makes this class of bug expensive: the delta feed is fast, the row counts look plausible, and the missing row surfaces weeks later as a reconciliation ticket nobody can reproduce.

Sequence numbers have the same shape of flaw for a different reason. A monotonic ID tells you the order in which values were allocated, not the order in which transactions committed. A transaction that grabbed a low number can commit after one that grabbed a high number, so a cursor based on allocation order can also leave a hole.

Five promises the feed has to make

Before choosing a transport, ask the source system for a contract with five clauses. If any clause is missing, you have a heuristic rather than a change feed.

  • Coverage: inserts, updates and deletions, each with the stable key needed to apply it.
  • Boundary: what a cursor value means and what a consistent batch contains.
  • Consistency: how related tables are read without mixing incompatible source states.
  • Retention: how long a disconnected consumer may resume from its saved position.
  • Reinitialization: a supported full snapshot for when that position is no longer valid.

A supported mechanism can satisfy all five. SQL Server's change-tracking documentation is one concrete published instance: it defines a minimum valid version, records deletes as well as changes, and describes a snapshot transaction that spans validation, version capture and the related change queries. It is an example of what to demand from a source, not syntax you can paste into a different engine.

Design the checkpoint as part of the pipeline

The consumer's saved position is the real state machine, so write it down as one. The cursor may only advance through the workflow that has already applied the batch and committed the target transaction.

cursor  = durable_checkpoint()          # last fully applied position
batch   = extract(cursor, upper_bound)  # one consistent batch
apply(batch)                            # idempotent at the target
commit()
checkpoint(batch.upper_bound)           # only now

If delivery fails halfway through, reconcile that same batch rather than inventing a new source position. Re-extracting with a fresher upper bound is how a partial apply becomes a permanent gap. The mirror rule holds on the other side too: idempotent application at the target prevents duplicates and does nothing for a change the extraction never saw.

Deletions need records, not conclusions

Comparing rows that still exist cannot tell a consumer which keys disappeared. A source that never records deletions leaves you with two imperfect options: keep a key history long enough to diff against, or run an explicit full reconciliation on a schedule. Both are design choices with a cost, and neither exists unless someone owns it.

Retention is the same kind of decision. Every supported change feed has a window; state it in the contract and alert when a consumer's checkpoint approaches it. A consumer that wakes up after a two-week outage should read a clear "reinitialize needed" error rather than silently receive a partial batch.

Then measure whether the delta was worth it

Falling back to comparing every key and hash is sometimes right, and it is worth knowing what it costs. You still transfer information about every row, hashes do not give you a consistent cross-table snapshot, and a hash scheme brings its own questions about representation and collisions. Fewer bytes with a verifiable boundary is the goal; fewer bytes that quietly drop a committed change is not a delta at all.

A last-modified column is a hint. A cursor that survives a late commit, a deletion and a long outage is a contract.

Mikanda mikatakata