What is the difference between a full-refresh and an incremental extract from a source table?

## The two shapes of a batch extract Every scheduled pull from a source system is one of two things. A **full-refresh extract** reads the whole source table on each run — `SELECT * FROM orders` — and replaces whatever the target held. An **incremental extract** reads only the rows the source has touched since the previous run — `SELECT * FROM orders WHERE updated_at >= :watermark` — and merges that slice into the target. The words are ordinary, so be precise about what "replaces" and "merges" mean. A full refresh normally lands into a staging table and then swaps or overwrites the target atomically, so readers never see a half-loaded table. An incremental load applies its slice with a keyed write — an upsert on the primary key, or a delete-and-reinsert of the affected partition — because a blind append would duplicate rows the moment anything reran. ## What full refresh buys **Self-correction.** The target is a pure function of the source at read time. If last week's run had a bug, dropped rows at a boundary, or missed an update that the application made without bumping its timestamp column, tonight's snapshot silently repairs all of it. This is worth more than teams expect. **No state.** There is no bookmark to store, no boundary to reason about, no question of when to advance anything. The job has no memory, so it cannot have a corrupt memory. **Deletes come free.** A row deleted in the source is simply absent from the next snapshot, so it disappears from the target with no tombstone mechanism. **Rerun safety.** Running it twice produces the same table, so retries after a timeout are harmless. ## What full refresh costs **Read amplification.** Cost is proportional to table size, not to change volume. Re-reading 400 million rows to capture 20 thousand changes is the normal steady state of a mature table. **Source impact.** A full scan against an OLTP primary competes with production traffic and holds a long read snapshot, which on MVCC engines keeps old row versions alive. Teams commonly move full refreshes onto a read replica for exactly this reason. **Target churn.** Rewriting a large table every night consumes warehouse compute, rewrites every partition, and invalidates downstream caches and incremental models even when nothing changed. **A latency floor that keeps rising.** The job takes longer every month until it no longer fits the window it is scheduled in. That day is when most pipelines convert to incremental — under time pressure, which is why the conversion is so often buggy. ## What incremental buys and what it charges Incremental makes cost proportional to change. In exchange you now own four problems, and interviewers ask about all four: 1. **A trustworthy change column.** The extract is only as correct as the column it filters on. A bulk `UPDATE` or a backfill script that does not bump `updated_at` is invisible forever, because a watermark filter can never look backwards. 2. **A boundary.** "Since last time" is ambiguous at the edge: rows written inside a transaction that commits after your read carry a timestamp inside the window you already closed, and ties at the exact boundary value are dropped or duplicated depending on whether you use `>` or `>=`. 3. **Deletes.** A deleted row cannot satisfy any filter, so hard deletes are structurally invisible and the target keeps stale rows forever. 4. **Idempotency.** The target is now assembled from many partial writes, so the write must be keyed and the run must take explicit window bounds rather than "whatever is new right now." ## Choosing between them The inputs to the decision are table size and growth, the change ratio (changed rows per run divided by total rows), the source's tolerance for scans, the freshness requirement, whether the source maintains a change column you actually trust, and whether deletes matter to the consumers. A workable rule of thumb: stay on full refresh while it comfortably fits the schedule and the source tolerates the read — the operational simplicity and self-healing are real money. Move to incremental when scan time, source impact or target cost stops fitting, and pay the four taxes above deliberately rather than discovering them in production. ## The hybrid that most mature pipelines run You rarely have to choose forever. Common combinations: incremental nightly plus a full reconcile weekly or monthly, so drift and missed rows have a bounded lifetime; full refresh for small reference and dimension tables where the scan is trivial, incremental only for the few large ones; and a routine replay of the last N days incrementally, which self-heals recent damage cheaply because the keyed write makes re-reading harmless.