IngestionSOURCE-SPECIFICGENERALSIMPLIFIED

Incremental Extraction

Asking a source for "everything since last time" — and the specific, silent, permanent ways WHERE updated_at > :last_run gets that wrong.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

My extract selects rows changed since the last run and every run succeeds. Which rows is it structurally incapable of returning, and how would I ever find out?

Who needs this

Anyone who will compute a total. A missing row does not announce itself as missing — it presents as a slightly smaller number, and slightly smaller numbers are the hardest thing in this domain to notice. The consumer needs the extraction predicate to have a stated, bounded set of records it cannot see, so that a check can be written for exactly that set.

What one row is

One changed row as of the moment the predicate was evaluated. That qualifier is the lesson: the same row may be extracted in several windows if it changes several times, and a row that changed may be extracted in none. The grain is not "a change" — a change is what the log gives you — it is "a row that satisfied a predicate at read time", which is a strictly weaker thing (What a CDC Event Contains).

The obvious build

SELECT * FROM orders WHERE updated_at > :last_run, with :last_run stored after each successful run. It reads like the definition of incremental, it uses a column the application already maintains, it needs nothing from the database team, and it is what almost every data platform starts with — reasonably, because for a while it is right.

Why it breaks

A transaction sets updated_at when it writes and commits three seconds later. The extract's snapshot, taken between those two moments, cannot see the row — and by the time the row is visible, :last_run has already advanced past its timestamp. The row will never satisfy the predicate again. It is not late; it is gone (MVCC: Multi-Version Concurrency Control).

How it breaks with real data

A transaction sets updated_at when it writes and commits three seconds later. The extract's snapshot, taken between those two moments, cannot see the row — and by the time the row is visible, :last_run has already advanced past its timestamp. The row will never satisfy the predicate again. It is not late; it is gone (MVCC: Multi-Version Concurrency Control).
In Postgres, now() inside a transaction returns the *transaction start* time, so a long-running transaction stamps rows with a timestamp from minutes before it commits. The gap between stamp and visibility is not milliseconds — it is the duration of the transaction (Transactions and ACID).
A row is hard-deleted. No predicate over a column can return a row that does not exist, so the warehouse keeps it forever and the row count drifts upward, permanently, with no mechanism that could ever correct it.
The extractor computes :last_run from its own clock rather than from the source's. The two clocks differ; skew in one direction skips rows, skew in the other re-reads them, and only the second one is visible (Duplicate Rows).
updated_at is maintained by application code rather than by a trigger. A bulk migration, an admin tool, or one code path that forgets updates the row without touching the column — and the extract, working perfectly, never sees the change.
The bookmark is stored as MAX(updated_at) of the rows returned. A window that returns zero rows leaves the bookmark unchanged, which is correct — but a window that returns rows and then fails during the write may leave a bookmark advanced past data that never landed. And because the predicate is > against that stored MAX, two rows sharing a timestamp to the second split across the boundary — one returned, one not, by an ordering nobody specified.

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

The root cause has one sentence: a row's timestamp is assigned when it is written, and its visibility begins when its transaction commits, and those are different instants. Every timestamp-based incremental extract filters on the first and observes the second, which means there is always a set of rows whose two values straddle the bookmark (Isolation Levels).
The size of that set is the source's commit lag — how long transactions stay open between assigning the timestamp and committing. On a source with short transactions it is milliseconds and the loss is rare; on a source with batch jobs, long transactions or lock contention it can be minutes, and the loss is routine (Locks and Deadlocks).
A monotonic sequence has exactly the same defect in a different costume. Sequence values are handed out at insert time, not at commit time, so a transaction holding value 1000 can commit after one holding 1001. Bookmarking on MAX(id) therefore closes the window over a gap that still has a writer in it.
A log position does not have the defect at all, because the log is written *in commit order* by construction — that is what makes it a valid basis for replication. Reading forward from an LSN or a binlog coordinate is the only mechanism in this list where "everything since last time" is exact rather than approximate (Write-Ahead Logging, CDC vs Polling).
Deletes are a separate structural blind spot and no amount of care with timestamps addresses it. A predicate is a filter over existing rows; a deleted row is not a row. Detecting deletion requires either the source modelling it as data — a soft-delete flag — or an out-of-band comparison of key sets.
The standard mitigation, an overlapping window, does not fix any of this. It converts a correctness problem into a probabilistic one: rows whose commit lag is shorter than the overlap are recovered, rows with longer lag are still lost, and the pipeline now produces duplicates that something downstream must absorb (Deduplication).

The row that was never late, it was simply never asked for

SOURCE-SPECIFICThe xmin form is a Postgres construction and its exact spelling matters less than the idea: ask the source for a position it considers settled. MySQL exposes an equivalent through GTID and binlog coordinates rather than through a row-visible column, and most SaaS APIs expose nothing of the kind, which is why they are stuck with option 2.

Here is the whole bug in one paragraph. A transaction begins at 08:59:58. It updates an order and sets updated_at, which resolves to the transaction start time. It does some more work, waits on a lock, and commits at 09:00:03. Your hourly extract, running at 09:00:00 with the predicate updated_at > 08:00:00 AND updated_at <= 09:00:00, takes a snapshot in which that transaction has not committed. It cannot see the row.

At 10:00 the next run asks for updated_at > 09:00:00. The row's timestamp is 08:59:58. It does not match. It will not match at 11:00 or at any future run, because the predicate window has moved permanently past it. The row is present in the source, absent from the warehouse, and there is no mechanism anywhere in the pipeline that will ever ask for it again.

Nothing failed. Both runs succeeded, both wrote files, both advanced the bookmark. The row count for the 08:00 window is one lower than it should be, which is well inside normal variation. Every dashboard built on this data is now slightly wrong, permanently, and will stay slightly wrong until somebody compares the warehouse to the source directly.

The mistake is not the code. The mistake is the belief that updated_at orders changes. It orders *assignments*. Commits happen in a different order, and the extract can only ever observe commits.

Three predicates, and what each one cannot see

1-- 1. The default. Silently at-most-once and structurally incomplete.
2SELECT * FROM orders
3WHERE  updated_at > :last_run;
4-- Cannot see: any row whose transaction committed after :last_run advanced
5--             past its timestamp; any hard-deleted row; any row updated by a
6--             path that does not set updated_at.
7 
8-- 2. Overlapping window plus safety margin. Probabilistic, not exact.
9SELECT * FROM orders
10WHERE  updated_at >= :bookmark - :lookback   -- re-read the recent past
11  AND  updated_at <  :source_now - :safety;  -- refuse to close over the present
12-- Recovers: every row whose commit lag was shorter than :lookback.
13-- Still cannot see: longer-lagging commits, and deletes.
14-- Requires: a merge on the primary key downstream, because rows in the
15--           overlap are delivered more than once by construction.
16 
17-- 3. Bounded by what the source says is settled, rather than by a guess.
18--    Postgres: no transaction older than this xmin is still in flight, so
19--    everything below it is committed and visible.
20SELECT * FROM orders
21WHERE  xmin::text::bigint < :snapshot_xmin_from_previous_run;
22-- Turns the heuristic into a statement the source is making about itself.
23-- Still cannot see: deletes. Nothing over existing rows ever can.

Read the three "cannot see" blocks rather than the SQL. Every incremental extract has such a block; the only question is whether it was written down. Note also that all three are unchanged in the delete row — no predicate over a table can report a row that has been removed from it.

Why a sequence is not the fix, and a log position is

The usual reaction to the timestamp problem is to reach for a monotonically increasing id, on the reasoning that it cannot skew and cannot repeat. Both are true and neither is the problem. Sequence values, like timestamps, are handed out when a row is written, and commits happen in whatever order transactions finish.

So a transaction can hold id 1000, be delayed, and commit after a transaction holding 1001. An extract that bookmarks on MAX(id) = 1001 will never return 1000. This is not a rarer version of the timestamp bug — it is the same bug, and on a busy source with variable transaction durations it fires at the same rate.

A write-ahead log is different in kind. The log is the record of commits, written in commit order, because that ordering is what replication depends on. Reading forward from a log position is therefore not an approximation of "everything since last time" — it is the definition of it, and it includes deletes because a delete is a log entry like any other (Write-Ahead Logging).

This is the argument for change data capture, and it is worth being precise about what it is: a correctness argument about ordering, not a latency argument about being real-time. CDC is often introduced for freshness and its actual value is that the completeness question has an answer (CDC vs Polling).

Where the two orderings diverge

UserLLMAgentToolDataDecisionHumanGuardrail

Bookmark on a value the source assigns at write time

Track `MAX(updated_at)` or `MAX(id)` across returned rows and use it as the lower bound next run. Both values are assigned while the transaction is open, so the bookmark can advance past rows that had not yet committed when it was computed.

Bookmark on a position the source assigns at commit time

Track a log sequence number, binlog coordinate or consumer offset, and read forward from it. The position advances only as commits are recorded, so nothing can be committed *behind* a position you have already passed.

The two differ on whether the ordering you bookmark against is the same ordering in which changes become visible. Assignment order and commit order are different orderings, and any bookmark over the first will eventually close a window over an in-flight transaction. Commit order is the only ordering in which "everything after position N" is a complete statement — which is precisely why the database itself uses it to keep replicas correct rather than using timestamps.

Sizing the lookback, when a log is not available

SCALE-SPECIFICThe full-snapshot row is the correct answer for small tables and becomes untenable somewhere between millions and hundreds of millions of rows depending on the source's spare capacity — and it is the row people abandon first even though it has the strongest correctness properties of anything short of a log.

Most sources do not offer a log, so the overlapping window is what you will actually run. It is a legitimate design — as long as its parameters come from a measurement rather than from a round number, and as long as its residual loss is written down where the next engineer will find it.

Two parameters do the work. The lookback decides how far into the past each run re-reads, and it must exceed the source's worst-case commit lag — which is approximately its longest transaction. The safety margin decides how far short of the present each run stops, and it protects the newest slice, where in-flight transactions are concentrated.

They are not interchangeable. The lookback recovers rows the previous run missed and costs duplicate volume. The safety margin prevents the miss happening in the first place and costs freshness. A pipeline with a generous lookback and no safety margin repeatedly rediscovers rows it should not have skipped; a pipeline with a safety margin and no lookback still loses anything that lagged unusually.

The number that turns both from guesses into engineering is the source's transaction duration distribution. If the 99.9th percentile transaction runs for two minutes, a thirty-second lookback is decoration. This is a measurement that lives in the source's monitoring, and asking for it is usually the highest-value conversation an ingestion engineer has with a database team.

Bookmark basis	Ordering it reflects	Misses in-flight commits?	Sees deletes?	What it costs
`MAX(updated_at)`, no overlap	Timestamp assignment order — not commit order.	Yes, permanently. Anything whose commit lagged past the boundary.	No.	Nothing, which is why it is everywhere.
`MAX(id)` on a sequence, no overlap	Sequence assignment order — also not commit order.	Yes, identically. Gaps below the max are closed over.	No.	Nothing, and it looks safer than it is.
Timestamp with lookback and safety margin	Assignment order, widened to cover typical commit lag.	Only those lagging beyond the lookback — reduced, not eliminated.	No.	Duplicate volume, a mandatory keyed merge, and freshness equal to the safety margin.
Source-reported settled position (e.g. oldest in-flight transaction)	A boundary the source guarantees has no writers behind it.	No — by construction, everything below it has committed.	No.	Requires the source to expose it; not all do, and the exposure is engine-specific.
Log position (LSN, binlog coordinate)	Commit order, exactly.	No.	Yes — a delete is an entry like any other.	A stateful connector, replication configuration on the source, and a retention window that bounds recovery.
Full snapshot each run	None needed — it is a state comparison, not a change feed.	No, but it sees only the state at read time and no intermediate changes.	Yes, by key-set difference.	A full read of the table, every run, whether or not anything changed.

The deletes nobody is looking for

Every mechanism discussed so far — timestamps, sequences, lookbacks, safety margins, settled positions — shares one blind spot, and it is not a matter of degree. A predicate is a filter over rows that exist. A deleted row does not exist. There is no value of any parameter that makes a query return it.

The consequence is a *monotonic* error. Missing rows can be recovered and duplicated rows can be deduplicated, but a phantom row in the warehouse stays there forever and every total including it is too high. The error only grows, it grows in the direction people question least, and no volume anomaly check will see it because the change per day is tiny.

There are exactly three ways out and all of them cost something. Ask the source to soft-delete, which is a change to somebody else's schema and application code. Read the log, where the delete is an event. Or periodically compare key sets, which is a heavy read whose frequency directly sets how long a deletion can go unnoticed.

This is also where ingestion meets a legal obligation rather than an engineering preference. A hard delete in the source is often a deletion request being honoured; an ingestion design blind to deletes means it was honoured in one system and quietly ignored in another (Deletion Requests).

Checks that can see what the predicate cannot

Check	Expresses	Catches	Still misses
Source row count minus warehouse row count, tracked over time	The two systems still agree on how many things exist.	Undetected deletes as a steady upward drift; missing rows as a downward one; a broken extract as a step.	Offsetting errors — a lost row and a phantom row cancel — and any error that does not change cardinality.
Key-set hash comparison for a closed period	The two systems agree on which things exist, not merely how many.	Everything the count check catches, plus offsetting errors, plus rows landed under a wrong key.	Value-level differences — the same keys with different amounts hash identically if only keys are hashed.
Commit-lag distribution at the source versus the configured lookback	The mitigation is sized for the source it is protecting against.	A lookback that has silently become too small as the source got busier.	Rows updated without touching the predicate column, which have no commit lag problem and are missed anyway.
Assert the predicate column is non-null and non-decreasing per key	The column ingestion depends on is actually being maintained.	A write path that forgot to set `updated_at`; a bulk migration that bypassed the trigger.	A path that sets the column to a wrong-but-plausible value, which looks entirely normal.

Only the second of these can distinguish "the right number of rows" from "the right rows". If you build one check from this lesson, build that one, and run it against a period the source has finished writing to.

How will we learn that a row was deleted?

What is the source willing to tell us, and how long can a deleted row survive downstream?

Soft deletes at the source

when You can influence the source's schema and the application reliably sets the flag on every delete path.

cost A change to someone else's system, plus a permanent obligation on every downstream model to filter on the flag — and a new silent failure when one forgets (Semantic Changes).

Read the change log

when The source has one and you can operate a connector against it.

cost A stateful component and a retention-bounded recovery window. Buys deletes, commit ordering and zero query load in one decision (Change Data Capture).

Periodic full key snapshot and diff

when No log is available, the key set fits in a readable snapshot, and a bounded detection delay is acceptable.

cost A heavy read on the source at whatever frequency you choose, and the detection delay is exactly that frequency. Simple, correct, and increasingly expensive with table size (Full Refresh vs Incremental).

Full refresh of the whole table

when The table is small, or correctness matters more than the read cost, or the incremental logic has become harder to trust than a rebuild.

cost Reads everything every time and loses intermediate states, but has no bookmark, no lookback, no skew and no delete blind spot. The strongest correctness story available without a log.

Accept the drift, explicitly

when Deletes are genuinely absent from the source's access patterns — an append-only ledger, an immutable event table.

cost Nothing, provided the assumption is asserted rather than assumed: a check that the source's row count never decreases turns a belief into a monitored fact.

How to build it

Most important first.

Prefer a log position wherever the source has one. It is exact, it is commit-ordered, it includes deletes, and it costs the source no query load. Every other option on this list is a workaround for its absence (Change Data Capture).
Where you must use a timestamp, use an overlapping window — read from bookmark - lookback to now - safety_margin — and make the downstream load idempotent on the source primary key so the overlap is free of consequence. Choose the lookback from the source's observed worst-case transaction duration, not from a round number. And never advance the bookmark to now(): advance it to now() - safety_margin, so the slice most likely to contain in-flight transactions is deliberately left open rather than closed over (Upserts and Merges).
Better still, derive the bookmark from the source's own view of what is settled. A database that exposes the oldest in-progress transaction lets you advance to a point that provably has no writers behind it, which turns a heuristic into a guarantee (Watermarks).
Use the source's clock for both ends of the window, not the extractor's. SELECT now() on the source, or a value the source returns, removes an entire class of skew that is otherwise invisible. While you are there, confirm that updated_at is maintained by a database trigger rather than by application code — or write down that some write paths are structurally invisible to ingestion.
Detect deletes explicitly: a periodic full key-set snapshot diffed against the warehouse's key set. It is a heavy read and it is the only way to bound delete drift without a log (Full Refresh vs Incremental).
Write the bookmark after the data is durable, and make the update conditional on its previous value so two runs cannot both advance it (The High-Water Mark).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A timestamp predicate with no overlap guarantees at-most-once delivery per change and no completeness at all. That is the worst combination available and it is the default.
A timestamp predicate with an overlap guarantees at-least-once for changes whose commit lag is under the overlap, and nothing whatsoever for changes above it. The guarantee is conditional on a property of the source you do not control (At-Least-Once Delivery).
A log position guarantees every committed change, once, in commit order, bounded only by log retention. It is the only option here with an unconditional completeness statement (Replication and Read Scaling).
No timestamp or sequence approach guarantees anything about deletes. This is structural, not a gap in implementation.
None of these guarantee that intermediate states are seen. A row that changes three times between two extracts is extracted once, in its final state — which is correct for a state model and silently lossy for an event one (Event vs Snapshot Modeling).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

Reconcile row counts and a key-set hash against the source for a closed period. The count alone can be satisfied by a coincidence — one row lost and one duplicated — while the key set cannot (Reconciliation).
Track the delta between the source's total row count and the warehouse's. Under hard deletes it drifts monotonically upward, and the *shape* of the drift is the evidence: a step means an incident, a steady climb means deletes are happening and nothing sees them (Volume Anomalies).
Measure the source's commit lag directly — the distribution of the gap between a row's updated_at and its visibility. Comparing that distribution with your lookback turns an assumption into a measurement, and it is the only way to know whether the lookback is sized correctly.
What all of this still misses: rows updated without updated_at being touched. They are invisible to the extract, present in the source, and identical in count to a correct row — no count-based check can ever see them. Only a value-level comparison, or a trigger that makes the column trustworthy, can.

Freshness

A safety margin trades freshness for completeness, explicitly and by a chosen amount. Extracting only up to now - 5 minutes means data is at least five minutes old and correspondingly less likely to be missing rows. This is the trade being made whether or not anyone made it deliberately.
The overlap costs nothing in freshness and everything in duplicate volume: it re-reads the past, not the future. A generous lookback is cheap on latency and expensive on source load and merge cost.
Log-based capture removes the trade entirely, because commit order and visibility are the same thing in the log. That is the strongest single argument for CDC and it is a correctness argument rather than a latency one (CDC vs Polling).
Late-arriving data does not disappear when extraction is fixed. A row with a genuinely old business timestamp that arrives today still lands in today's window and must be routed by the transformation, not by the extract (Late-Arriving Data).

When the schema or meaning changes

The predicate column is a contract with the source that nobody signed. If the application team changes when updated_at is set — or introduces a write path that does not set it — ingestion breaks silently and no schema check fires (Data Contracts).
Adding a lookback to an existing pipeline changes the duplicate profile of every subsequent window. Downstream must already deduplicate on the source key before that change is safe, or the fix for missing rows becomes a cause of double counting.
Migrating from timestamp extraction to log-based capture requires an initial snapshot stitched to the stream at a consistent position, and getting that seam wrong produces either a gap or a duplicated history — both at a single point in time that is easy to miss later (Snapshot and Stream: the Bootstrap Problem).
A source that adds soft deletes changes the meaning of a row rather than the schema: rows that used to disappear now persist with a flag, and any downstream model that did not filter on it starts counting deleted records (Semantic Changes).

How to re-run this safely

The recoverable case: you discover the loss while the source still holds the rows. A bounded re-extract over the affected range, merged on the primary key, repairs it — and because the merge is keyed, it is safe to run over a range wider than strictly necessary (Upserts and Merges).
The unrecoverable case: the rows were deleted, or the source has since overwritten them, or the source is an API that only serves recent changes. No amount of pipeline engineering recovers data that no longer exists anywhere.
Re-extracting a historical range returns rows *as they are now*, not as they were then. For a slowly-changing entity that is a silent rewrite of history, and it must be a decision with a note attached rather than a default repair (What Backfills Break).
If the bookmark advanced past unlanded data, rolling it back re-reads the range — which is safe only if the load is idempotent. This is the moment idempotency stops being a principle and starts being the thing that decides whether the incident is fixable (Idempotent Data Pipelines).

What can go wrong

Failure modes

Commit lag exceeding the lookback: the mitigation is in place, most rows are recovered, and the residual loss is now hidden behind a mechanism everyone believes is working. This is the most dangerous failure in the lesson precisely because the fix is partially effective.
Hard deletes accumulating as phantom rows, with no error and a slowly growing overcount.
Clock skew between extractor and source, silently shifting every window.
A bookmark advanced past a failed write, converting a retryable failure into a permanent gap.
An unindexed predicate column, so the "incremental" extract is a full table scan with a filter, loading the source exactly as much as a full extract would (Should I Add an Index?).
Timestamps stored with second granularity while many rows share a second, so > and >= differ by a whole second's worth of rows and neither is correct — and, separately, a row changing several times between runs, so intermediate states are lost and nobody notices until someone needs the history.

Misreads

"updated_at > :last_run is incremental extraction." It is one implementation with a named, bounded set of records it cannot return: anything whose commit lagged its timestamp past the boundary, anything deleted, and anything updated without the column being touched. Knowing that set is the difference between using it and being fooled by it.
"We added a lookback, so we are not losing rows." You are losing fewer rows. The residual set is exactly those whose commit lag exceeded the lookback, and it grows when the source gets busier — which is when the data matters most.
"An auto-incrementing id is safer than a timestamp." It has the identical defect: ids are assigned at insert, commits happen in a different order, and MAX(id) closes the window over gaps that still have writers. It is not a fix; it is the same bug with integers (The High-Water Mark).
"The counts match, so nothing is missing." A lost row and a duplicated row in the same window produce matching counts. Reconcile the key set, not just its cardinality.
"Deletes are rare, so ignore them." Deletes produce a *monotonic* error. Rare and permanent compounds into large, and it compounds in the direction that inflates every total.
"We can measure completeness from inside the pipeline." Nothing inside the pipeline knows what the source had. Every completeness answer requires reading the source again (Trusting Data).

Privacy, retention and access

Hard deletes in the source are frequently deletion requests. An extraction method structurally blind to deletes means personal data persists in the warehouse after it was removed from the system of record — a compliance failure produced by a technical blind spot (Deletion Requests).
Delete detection by key-set snapshot means retaining a full key list, which for a person-keyed table is itself personal data with its own retention obligation.

Operating it

How you see it in production

Bookmark value and bookmark lag — how far behind now the bookmark sits — per source. A bookmark that stops advancing, or advances in a jump, are both visible here and nowhere else (Pipeline Metrics).
Rows extracted per window, with the fraction attributable to the overlap separated out. A rising overlap fraction means commit lag is growing at the source, which is advance warning that the lookback is becoming too small.
Source row count minus warehouse row count, as a time series. Under correct ingestion it is flat or explainable; a monotonic climb is the signature of undetected deletes.
Source transaction duration percentiles, if you can get them. They are the direct measurement of the risk this lesson describes, and they live in the source's monitoring rather than yours (Percentiles: Which One, and How Many Users Is That?).

What changes at 10x and 100x

At 10x write volume, commit lag distributions widen — more concurrency, more lock waits, longer transactions — so a lookback that was generous becomes marginal without anything in the pipeline changing.
At 100x, the periodic full key snapshot stops being affordable, and delete detection has to come from the log or not at all. This is usually the point at which CDC stops being optional (Change Data Capture).
Table count scales the problem faster than table size does. Sizing a lookback per table requires knowing each table's transaction profile, and beyond a few dozen tables nobody does, so a single global lookback is chosen and it is wrong for the tails.
What does not change with scale: the log-based approach has no lookback to size, no skew to reason about and no delete blind spot, so its correctness story is flat in volume. Its operational story is not (CDC Failure Modes and the Retention Deadline).

What drives cost here

The overlap re-reads and re-writes rows every window. Cost scales with lookback duration multiplied by change rate, and it is paid on the source, on the network and in the merge.
A merge on the primary key is substantially more expensive than an append, because it must locate existing rows. That is the price of idempotency and it is usually worth paying (Upserts and Merges).
Periodic full key snapshots for delete detection are a heavy read on the source, and their cost scales with total table size rather than with change. Their frequency is a direct trade against how long a delete can go unnoticed.
An unindexed predicate column turns every incremental run into a full scan, which is the most common way an "incremental" pipeline costs more than the full extract it replaced (An Index Scan Is Not Automatically Faster).

What this approach costs

An overlapping window buys most of the missing rows and costs duplicate volume, a mandatory merge downstream, and — most importantly — the appearance of a solved problem. Document the residual loss or the mitigation will be mistaken for a fix.
A safety margin buys completeness with freshness, at an exchange rate set by the source's transaction profile. It is the cleanest trade in the lesson because both sides are explicit.
Log-based capture buys exactness and costs a stateful component, a dependency on the source database's replication configuration, and a failure mode that is silent until it is unrecoverable.
Full periodic snapshots buy an unconditional correctness reset — including deletes — and cost a heavy read on the source and a decision about what to do when snapshot and incremental history disagree.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

SOURCE-SPECIFICThe size of the window between timestamp assignment and commit visibility is a property of the source. Postgres now() returns transaction start time, so the gap is the whole transaction duration; a column defaulted with clock_timestamp() or set by a row trigger narrows it to the statement; an application-set field can be arbitrarily early and can be skipped entirely by some write paths.
GENERALThe underlying rule — filter on assignment order, observe commit order, lose whatever straddles the boundary — holds for any transactional source, including message stores and document databases. Only the remedies differ by what ordering the source is willing to expose.
SIMPLIFIEDThe lesson treats the source as a single node. Reading from a replica adds replication lag on top of commit lag, so the effective boundary is further back still, and a failover to a replica at a different position can move it backwards.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasemvcc isolation-levels transactions-and-acid write-ahead-logging replication should-i-add-an-index update-delete-under-mvcc

Backendread-replicas at-least-once-delivery duplicate-detection idempotency-in-backends

API Designcursor-pagination consistency-expectations

Securityaudit-logs

Observabilitypercentiles replication-lag scan-vs-index-performance

Concurrencyordering-guarantees reordering

Domains that do not exist yet

— Distributed Systems owns why commit order and assignment order differ, what a snapshot actually is, and why a clock cannot be used to order events across machines. Every claim in this lesson about ordering ultimately rests on that material.
— DevOps / Production Engineering owns getting the source's transaction-duration percentiles onto a dashboard you can see, which is what turns the lookback from a guess into a parameter.