Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

Polling and the completeness gap

What can polling never see, and why can no method report its own blind spot?

Query/diff-based CDC and the completeness gap

The cheapest rung

Poll for rows whose updated_at is greater than the last (the highest timestamp you have already processed):

SELECT * FROM orders
WHERE updated_at > :last_watermark
ORDER BY updated_at;

It needs no special privileges, no invasive installation, and works against anything with a SQL interface. This is Tariq's first attempt.

Polling is sampling

It inherits the arithmetic of . : you observe the final state and lose the intermediate ones.

Example (Tariq's audit problem). An order moves confirmed → packed → shipped within 4 minutes. The poller runs every 15 minutes. It sees only shipped. The packed state is gone forever.

Whether this matters depends on the consumer:

  • A sink (an into a table) only cares about the final state. is harmless.
  • A path-dependent sink (an audit trail, a history-keeping table, an action triggered by each event) needs every step. For it, aliasing is silent data loss.

Two failures worse than aliasing

Both are structural, not accidental.

  1. Hard deletes are invisible in principle. A row that no longer exists cannot appear in a query for rows that changed. This is Tariq's 212 cancelled orders. (A full comparison of key lists between source and target can find deleted rows. But that is a comparison, a different and costlier method, not watermark polling.)
  2. The watermark column is maintained by application code. Any writer that forgets to set it (a DBA correction, a migration script, an ORM that bypasses the field) produces a change that is unobservable forever, not merely late. This is Lena's price-correction script.

Add two more traps. Clock skew: the clocks of different application servers differ slightly, so updated_at is not perfectly ordered. Long transactions: a transaction that started before your watermark but after it inserts a row with a timestamp that is already in your past. In PostgreSQL, for example, now() returns the time the transaction started. A transaction that starts at 10:00:00 and commits at 10:07:00 writes updated_at = 10:00:00. If your poll ran at 10:05 and moved the watermark to 10:05, the row appears at 10:07 with a timestamp before the watermark, and the next poll will never select it. Overlapping windows (always re-read the last few minutes) mitigate this. Nothing eliminates it.

Committed after the poll, stamped before it

Generalizing: every method has a bypass set

Every detection mechanism has a set of writes that bypass it:

  • bulk or direct-path loads that skip the log,
  • TRUNCATE, which is rather than and may leave no per-row records,
  • restores from backup,
  • replica rebuilds,
  • DDL-driven rewrites.

Each mechanism has a different bypass set, and none of them can report the size of its own blind spot.

This is the general independence-assumption problem applied to completeness: your pipeline assumes it sees everything, and it cannot verify that from the inside.

In one line: polling detects transitions the application chose to advertise; everything else is invisible by construction, not by bug.