Why can an Airbyte incremental sync on an updated_at cursor silently miss rows?
answer
- the sync only sees what the column advertises
- a write that skips the column is invisible forever
- nulls never satisfy the comparison
- committed late, stamped early
- nothing left to read after a hard delete
basics
~20 sAn incremental sync only returns records whose cursor value is at or past the stored one. Rows changed without bumping updated_at, rows with a null cursor, rows committed late with an older timestamp, and hard deletes are therefore never read.
solid answer
~50 sAirbyte stores the maximum cursor value seen and asks the source for records at or past it on the next run, so anything the cursor does not advertise is invisible. Four common causes: an application path that updates a row without touching `updated_at` (bulk SQL, a migration, a trigger that does not fire); rows whose cursor is **null**, which fail the comparison and are never selected; a long transaction that commits *after* the sync advanced the cursor but stamped an earlier timestamp, so the row falls permanently behind the saved value; and hard deletes, which no cursor read can express. The fixes are source-side or mode-side, not clever query-side: use a cursor the source genuinely maintains for every write path, prefer a CDC-enabled source where the connector supports it, schedule a periodic full refresh of the stream, and use the deduped write mode so that re-reading data is harmless.
code
sql · 5 lines-- application path: cursor advances, sync sees the row
UPDATE orders SET status = 'shipped', updated_at = now() WHERE id = 7;
-- incident fix: cursor untouched, sync never sees the row again
UPDATE orders SET status = 'shipped' WHERE id IN (8, 9, 10);go deeper
Recall that incremental reading is driven entirely by one column, and that anything not reflected in that column — a bypassed update, a delete — never reaches the destination.
Explain the comparison itself: the stored maximum, the at-or-past predicate, why nulls drop out, and why a transaction that commits after the cursor advanced is lost permanently rather than delayed.
Show how you would detect the drift in production and bound the repair: reconciliation checks, a periodic full refresh, a lookback paired with deduped writes, and knowing when CDC is the right escalation.
Own the standard for what qualifies as a cursor across the estate, and the reconciliation you require before a stream may be trusted for reporting — including who pays for the periodic full refreshes that keep cursor-based streams honest.
## How the cursor actually drives the read When a stream is configured incrementally, Airbyte remembers the highest cursor value the sync observed and stores it as the stream's state. The next run asks the source for records at or past that value — for a database source that becomes a predicate on the cursor column, for an API source a `since`-style parameter. Everything the pipeline knows about *what changed* comes from that one column. If the column is a lie, the sync is quietly wrong; nothing errors, nothing lags, dashboards simply drift. ## Failure 1: the cursor does not move The most common cause. `updated_at` is maintained by the ORM on the ordinary write path, and then something bypasses it: a manual `UPDATE` during an incident, a backfill script, a data migration, a bulk correction, a second service writing to the same table, or a database-level trigger that was never installed on that column. The row changes, its cursor does not, and no future sync will ever consider it again — the state has already advanced past it. This is permanent silent loss, not delay. ## Failure 2: null cursor values A record whose cursor column is null cannot satisfy a 'greater than or equal' comparison, so it never appears in an incremental read. Nullable cursors are common in tables where the column was added late and old rows were left null, or where the column is only written on update and never on insert. Choose a non-nullable cursor, or backfill the column before enabling incremental. ## Failure 3: transactions that commit out of timestamp order A transaction stamps `updated_at` when it writes the row but the row becomes visible only when it commits. A statement that stamped 10:00 and committed at 10:07 is invisible to a sync that ran at 10:03 and saved a cursor of 10:05. The row exists, its cursor is below the saved value, and it is gone for good. Clock skew between application servers produces the same shape. This is why long-running writers and cursor-based extraction interact badly, and why some connectors expose a lookback window that re-reads a trailing slice of time on every sync — where it exists, use it, and pair it with a deduplicating write mode so the re-reads do not become duplicates. ## Failure 4: deletes A hard delete removes the row; there is nothing left to carry a cursor value. No cursor-based extraction of any kind can see it. Airbyte's CDC-enabled database sources escape this because deletions arrive as change records carrying a deletion timestamp; cursor-based sources do not. If deletes matter and CDC is unavailable, either the source adds a soft-delete flag that bumps the cursor, or you periodically full-refresh the stream and accept the cost. ## The opposite failure: duplicates at the boundary Worth knowing because interviewers pair the two. Connectors generally re-read records *at* the saved cursor value rather than strictly past it, precisely so that ties are not dropped. The price is that boundary records are emitted again. Under `Incremental | Append` those become duplicate rows the consumer must resolve; under `Incremental | Append + Deduped` the primary key absorbs them. That asymmetry — Airbyte prefers a duplicate to a loss — is a deliberate design choice worth naming. ## Choosing a defensible cursor Rank candidates by how hard they are to bypass. A database-maintained modification timestamp or a change-tracking column beats an application-maintained one. A monotonic sequence works for insert-only streams but never reflects updates, so it is wrong for mutable tables. A business date such as `order_date` is almost always wrong: it does not move when the row is corrected. And be careful with the source-defined cursors some connectors impose — read what the connector documents that the column actually tracks before assuming it means 'last modified'. ## Detecting the drift Do not trust the sync's green status. Reconcile: compare row counts and, where you can, a checksum of a mutable column between source and destination on a schedule. Alert on a stream whose incremental read returns zero records for longer than its business rhythm allows. And when you find drift, remember that the fix is a bounded re-sync — clearing the stream's state and re-reading — not editing rows in the destination by hand.
- Would an autoincrementing id be a safer cursor than updated_at?Only for insert-only streams. An id advances on insert and never on update, so every later modification of an existing row is invisible. For a mutable table it trades one silent-loss mode for a worse one; for an append-only event log it is excellent, because it is monotonic and immune to clock skew.
- How would you detect that a stream has been silently losing rows for weeks?Reconcile independently of the sync's status: compare source and destination row counts per period, and compare an aggregate over a mutable column such as a status distribution or a sum. Alert on streams whose incremental read returns nothing for longer than their normal rhythm. A green sync history proves only that extraction ran.
- What do you do about the duplicates a lookback window creates?Pair the lookback with Incremental | Append + Deduped so the primary key collapses re-read versions into one current row. Under plain append the duplicates persist and every consumer must deduplicate; that is the wrong place to solve it.
The cursor is a signed visitors' book at the door: anyone who slips in without signing, or signs an earlier time after you have closed the page, is simply not in your records.
saying these in an interview costs you the question
- Assumes a green sync status proves no rows were lost
- Believes incremental reads catch hard deletes
- Picks a business date such as order_date as the cursor
- Ignores rows with a null cursor value
- Thinks re-running the sync recovers rows already behind the saved cursor