Why does polling a source table's updated_at column miss deletes and intermediate row versions?

middlemust knowfreq 74%

answer

a query can only report what exists
absence looks exactly like no-change
five updates, one result row
soft delete turns a delete into an update
shorter interval narrows nothing structural

basics

~20 s

A poll returns rows that exist now and match its filter. A deleted row matches nothing, so it is never reported; several updates between two polls return only the newest version. Polling observes current state, never history.

solid answer

~60 s

A polling extract issues something like SELECT * FROM orders WHERE updated_at > :last_seen. The result set is a picture of the table at that instant, filtered by a column. Two gaps follow directly.

Deletes. A DELETE removes the row, so it matches no predicate on any subsequent poll. The extractor sees an absence, and an absence is indistinguishable from "this row simply did not change". The downstream copy keeps the row forever. The only pollable delete is a soft delete — the application setting deleted_at/is_deleted and touching updated_at — which turns the delete into an update the poll can see.

Intermediate versions. The poll returns the row's value at poll time, not each value it held. Five updates between two ticks yield one row. Any consumer that must react to a specific transition, keep an audit trail, or count state changes loses data that no shorter interval recovers — it only narrows the window.

The missing-delete gap is normally patched with a periodic key-set comparison between source and target, which is expensive and lags reality.

A polling extract issues something like `SELECT * FROM orders WHERE updated_at > :last_seen`. The result set is a picture of the table at that instant, filtered by a column. Two gaps follow directly. **Deletes.** A `DELETE` removes the row, so it matches no predicate on any subsequent poll. The extractor sees an absence, and an absence is indistinguishable from "this row simply did not change". The downstream copy keeps the row forever. The only pollable delete is a soft delete — the application setting `deleted_at`/`is_deleted` and touching `updated_at` — which turns the delete into an update the poll can see. **Intermediate versions.** The poll returns the row's value at poll time, not each value it held. Five updates between two ticks yield one row. Any consumer that must react to a specific transition, keep an audit trail, or count state changes loses data that no shorter interval recovers — it only narrows the window. The missing-delete gap is normally patched with a periodic key-set comparison between source and target, which is expensive and lags reality.

code

text · 9 lines

text

table state and polls (marker = updated_at)
  09:00  poll  -> last_seen = 09:00:00
  09:11  UPDATE id=7  status=PAID       updated_at=09:11
  09:26  UPDATE id=7  status=CANCELLED  updated_at=09:26
  09:38  DELETE id=7
  09:44  DELETE id=9   (unchanged since 08:00)
  10:00  poll  -> returns 0 rows

downstream after 10:00 poll: id=7 still PAID, id=9 still present

go deeper

for a junior

Remember the one-line reason: a query returns rows that exist, and a deleted row does not exist. Be able to name the soft-delete workaround.

for a middle

Explain both gaps mechanically — absence carrying no information, and the result set holding current state rather than per-change history — and say why a shorter interval closes neither.

for a senior

Bring the operational consequences: permanent downstream drift, erasure requests unsatisfied in the warehouse, and the cost profile of key-set reconciliation as a patch. Say what you would instrument to detect the drift.

for a principal

Frame it as a contract problem: decide and publish per source whether deletes are captured at all, who owns the soft-delete convention with the producing application, and what freshness the reconciliation path can honestly promise.

What a poll actually observes

A query-based extract is a query, and a query returns rows that (a) exist in the table at the moment it runs and (b) satisfy the predicate. Everything that is odd about polling follows from those two words: exist and now.

The typical extract looks like this:

sql

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

and the extractor advances :last_seen to the highest value it received. It is comparing the table against a marker, not against its own previous copy of the data — so anything the marker cannot express is invisible.

Why deletes vanish

A hard DELETE removes the row. On the next poll there is nothing to match updated_at > :last_seen, and there is also nothing to match any other predicate. The extractor's result set does not distinguish between "this row was deleted", "this row was never touched since your marker", and "this row never existed". Absence carries no information, because the query only ever reports presence.

The practical consequence is that the downstream table drifts permanently: it accumulates rows the source has removed. Aggregates computed downstream over-count, reverse-ETL pushes messages to customers whose records were deleted, and a right-to-erasure request satisfied in the source is silently unsatisfied in the warehouse — a compliance problem, not just a data-quality one.

There are three honest fixes and one dishonest one:

Soft deletes. The application never issues DELETE; it sets deleted_at = now() and touches updated_at. The delete becomes an ordinary update the poll can see. This requires the source application to cooperate, which the pipeline usually cannot mandate.
Periodic key-set reconciliation. Extract just the primary keys of the source (cheap relative to full rows), compare them against the keys present downstream, and mark the difference as deleted. This works, but it is O(table) per run, it lags by the reconciliation interval, and it is easy to get catastrophically wrong if the key extract is partial.
Stop polling. Read the change log, where a delete is an explicit event.
Dishonest fix: shortening the poll interval. It changes nothing — a deleted row is equally absent at one second and one day.

Why intermediate versions collapse

The poll returns the row's current value. If a row changed five times since the marker, five changes produce one result row carrying the fifth value. The intermediate values were real, were committed, and are gone as far as the pipeline is concerned.

When this matters:

Transition-driven logic. A consumer that must act when an order passes through CANCELLED never learns it happened if the row moved PAID → CANCELLED → REFUNDED between polls.
Audit and history tables. A slowly-changing history built from polls records only the values that happened to be current at tick boundaries, which is a sampled history, not a real one.
Counting. "How many times did this record change?" is unanswerable from polled data.

When it does not matter: a warehouse dimension or fact where the latest value is the whole requirement. Plenty of real pipelines are exactly this, and polling is a fine fit for them.

The marker column itself is a second-order risk

Even for rows that still exist, the poll only finds them if updated_at moved. Three ways it does not:

A write path — a migration script, an admin console, a bulk UPDATE — that changes data without touching the column.
Application clocks rather than database time: a row stamped by a host whose clock is behind can be written with a value below a marker the extractor already passed.
Ties at the boundary: many rows sharing an identical timestamp, where a strict > skips some and a >= re-reads them. Which side you err on is a real design decision; the safe direction is re-reading, paired with an idempotent load downstream.

How to answer this in an interview

Say the mechanism, not the symptom. "A poll matches rows that exist; a deleted row exists nowhere, so absence is indistinguishable from no-change" is the sentence being listened for. Then give the mitigations honestly — soft deletes if the application will cooperate, key reconciliation if it will not, log-based capture if you can get it — and say plainly that a shorter interval fixes neither gap.

## What a poll actually observes A query-based extract is a query, and a query returns rows that (a) exist in the table at the moment it runs and (b) satisfy the predicate. Everything that is odd about polling follows from those two words: *exist* and *now*. The typical extract looks like this: ```sql SELECT * FROM orders WHERE updated_at > :last_seen ORDER BY updated_at; ``` and the extractor advances `:last_seen` to the highest value it received. It is comparing the table against a marker, not against its own previous copy of the data — so anything the marker cannot express is invisible. ## Why deletes vanish A hard `DELETE` removes the row. On the next poll there is nothing to match `updated_at > :last_seen`, and there is also nothing to match any other predicate. The extractor's result set does not distinguish between "this row was deleted", "this row was never touched since your marker", and "this row never existed". Absence carries no information, because the query only ever reports presence. The practical consequence is that the downstream table drifts permanently: it accumulates rows the source has removed. Aggregates computed downstream over-count, reverse-ETL pushes messages to customers whose records were deleted, and a right-to-erasure request satisfied in the source is silently unsatisfied in the warehouse — a compliance problem, not just a data-quality one. There are three honest fixes and one dishonest one: 1. **Soft deletes.** The application never issues `DELETE`; it sets `deleted_at = now()` and touches `updated_at`. The delete becomes an ordinary update the poll can see. This requires the source application to cooperate, which the pipeline usually cannot mandate. 2. **Periodic key-set reconciliation.** Extract just the primary keys of the source (cheap relative to full rows), compare them against the keys present downstream, and mark the difference as deleted. This works, but it is O(table) per run, it lags by the reconciliation interval, and it is easy to get catastrophically wrong if the key extract is partial. 3. **Stop polling.** Read the change log, where a delete is an explicit event. 4. **Dishonest fix:** shortening the poll interval. It changes nothing — a deleted row is equally absent at one second and one day. ## Why intermediate versions collapse The poll returns the row's current value. If a row changed five times since the marker, five changes produce one result row carrying the fifth value. The intermediate values were real, were committed, and are gone as far as the pipeline is concerned. When this matters: - **Transition-driven logic.** A consumer that must act when an order passes through `CANCELLED` never learns it happened if the row moved `PAID → CANCELLED → REFUNDED` between polls. - **Audit and history tables.** A slowly-changing history built from polls records only the values that happened to be current at tick boundaries, which is a sampled history, not a real one. - **Counting.** "How many times did this record change?" is unanswerable from polled data. When it does not matter: a warehouse dimension or fact where the latest value is the whole requirement. Plenty of real pipelines are exactly this, and polling is a fine fit for them. ## The marker column itself is a second-order risk Even for rows that still exist, the poll only finds them if `updated_at` moved. Three ways it does not: - A write path — a migration script, an admin console, a bulk `UPDATE` — that changes data without touching the column. - Application clocks rather than database time: a row stamped by a host whose clock is behind can be written with a value below a marker the extractor already passed. - Ties at the boundary: many rows sharing an identical timestamp, where a strict `>` skips some and a `>=` re-reads them. Which side you err on is a real design decision; the safe direction is re-reading, paired with an idempotent load downstream. ## How to answer this in an interview Say the mechanism, not the symptom. "A poll matches rows that exist; a deleted row exists nowhere, so absence is indistinguishable from no-change" is the sentence being listened for. Then give the mitigations honestly — soft deletes if the application will cooperate, key reconciliation if it will not, log-based capture if you can get it — and say plainly that a shorter interval fixes neither gap.

Does polling on a monotonically increasing version column instead of a timestamp fix the missing deletes?
No. The gap has nothing to do with the marker's type; it comes from the row being absent. A version column removes clock-skew and tie-at-the-boundary problems, and makes ordering unambiguous, but a deleted row still carries no version to compare and still matches no query.
How would you repair a downstream table that has been accumulating rows the source deleted?
Run a key-set reconciliation: extract the source primary keys, compare against the keys downstream, and soft-delete the difference. Do it as a periodic job, not once, and record how stale the delete signal can be so consumers know what they are trusting. Then fix the cause — soft deletes or log-based capture.
When is losing intermediate versions genuinely acceptable?
When the consumer only ever asks for the latest state — a dimension table, a search index, a cache. It stops being acceptable the moment something downstream reacts to transitions, builds history, or counts changes, because the pipeline is then silently sampling rather than replicating.

saying these in an interview costs you the question

Polling every few seconds so deletes are not missed
Treating an empty result as evidence a row was deleted
Assuming every write path updates the timestamp column
Believing a version column solves the delete problem
Promising audit-grade history from a polled extract

Why does polling a source table's updated_at column miss deletes and intermediate row versions?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What a poll actually observes

Why deletes vanish

Why intermediate versions collapse

The marker column itself is a second-order risk

How to answer this in an interview

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics3 topics

What a poll actually observes

Why deletes vanish

Why intermediate versions collapse

The marker column itself is a second-order risk

How to answer this in an interview

answer

go deeper

context