Incremental Processing
Process what is new instead of recomputing ten years — and inherit, in exchange, every problem of state: watermarks, late data and two eras in one table.
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.
Which records does this run actually need to process, and what happens to the ones that should have been in the last run but were not there yet?
Consumers who need the pipeline to finish inside its window. Incremental processing is invisible to them until it is wrong, at which point they experience it as a table that is missing yesterday evening (Missing Rows).
The unit is the change set for an interval: the records whose relevant timestamp or log position falls inside a bounded range. The definition of "relevant" — event time, update time, commit position — is the entire lesson, because choosing it wrongly loses rows silently (Event Time).
Recompute everything every night. CREATE OR REPLACE TABLE fct_orders AS SELECT ... FROM stg_orders over all history. It is trivially correct, it is idempotent by construction, it repairs any past bug automatically on the next run, and for a dataset that fits inside the window it is the right answer and remains so far longer than most teams believe (Full Refresh vs Incremental).
History grows and the full rebuild stops fitting in the window. The job that took part of the night now takes most of it, and the first thing consumers notice is that the morning number is late (The Freshness SLO).
- History grows and the full rebuild stops fitting in the window. The job that took part of the night now takes most of it, and the first thing consumers notice is that the morning number is late (The Freshness SLO).
- The obvious fix —
WHERE updated_at > (SELECT max(updated_at) FROM target)— silently misses every row whose transaction committed after itsupdated_atwas assigned. The clock read at the start of a long transaction; the row became visible minutes later; the watermark had already moved past it (Incremental Extraction). - A source row is updated with a backdated timestamp, or a partner file is re-delivered for last week. The incremental filter never sees it, and the affected interval is wrong forever unless someone re-runs it (Late-Arriving Data).
- A bug is found in the transformation. Under a full rebuild it would have fixed itself on the next run; under incremental processing every historical partition still holds the old logic and must be re-run explicitly (Reprocessing vs Retrying).
- A new column is added upstream. Rows processed after the change have it and rows processed before do not, so the table now contains two eras and any aggregate over the boundary is quietly wrong (Schema Evolution).
- Two runs overlap during a backfill, both advance the same watermark, and the final value reflects whichever finished last rather than what was actually processed (Reasoning About Races: A Method, Not an Instinct).
What is actually happening
- Incremental processing replaces "recompute the world" with "compute a bounded change set and merge it into what exists". That trade converts a compute problem into a state problem: something must remember what has already been processed (The High-Water Mark).
- There are three common ways to bound the change set, and they are not equivalent. By interval, where the run's own data interval defines the range and no state is needed at all. By watermark, where a stored
last_processedvalue defines the lower bound. By log position, where an offset into an append-only log defines it exactly (Offsets and Commits). - Interval-bounded is the strongest and the most underused: because the range comes from the run rather than from stored state, the task stays a pure function of its interval and remains fully re-runnable (Idempotent Data Pipelines).
- Watermark-bounded introduces a dependency on a clock that belongs to somebody else.
updated_atis assigned by the source at some moment during a transaction and becomes visible at commit, and the gap between those two events is where rows disappear (MVCC: Multi-Version Concurrency Control). - The merge side needs as much care as the read side. A change set applied with
INSERTaccumulates; applied with a merge on the business key it converges; applied as a partition overwrite it converges and also repairs deletions within the slice (Upserts and Merges). - Incremental output must still be idempotent per slice, otherwise the recovery path for late data — re-run the affected interval — is itself unsafe, and the pipeline has traded a compute problem for an unfixable one.
What incremental actually buys, and what it charges
The argument for incremental processing is usually made as a cost argument, and cost is the smaller half of it. The real driver is that a full rebuild eventually stops fitting in the time available, and a pipeline that cannot finish before people read the numbers has failed regardless of what it costs.
The bars below name the drivers on both sides of the trade. Read them as an ordering rather than as measurements: the work saved is proportional to how much of history is unchanged, and the work added is proportional to how much of the target a merge must examine to find the rows it is replacing.
The last two drivers are the ones teams forget to price. Incremental output arrives in small pieces, and small pieces become small files that every downstream query pays for; and the only way to know the incremental table still matches reality is to periodically rebuild it and compare, which reintroduces exactly the cost you were avoiding — on a slower cadence, deliberately (File Compaction).
The cost being eliminated. Its size is proportional to history rather than to change, which is why it grows every day even when the business does not.
Added cost. Proportional to how much of the target the engine must scan to find matching keys — bounded by partition pruning, unbounded without it (Partition Pruning).
Added deliberately. This is the premium paid to tolerate rows that commit after their timestamp was read, and it is the cheapest correctness you can buy here.
Added, and paid by every downstream reader rather than by the pipeline. It compounds until a compaction job exists (File Size and the Small-Files Problem).
Added on purpose and on a slower cadence. It is the only thing that tests the claim that incremental output equals a rebuild.
Small in compute and disproportionate in risk: the cheapest driver here is the one that loses data when it goes wrong (The High-Water Mark).
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a typical append-mostly warehouse table, shown to establish an ordering rather than to be transferred to any specific workload. The teaching is the shape: the saving scales with history and the added costs scale with change rate and target size.
What a run actually reads
Incremental processing only pays off if the engine can avoid touching the partitions that did not change, and that requires the physical layout to match the bounding predicate. A pipeline that filters on event time against a table partitioned by load date is incremental in intent and full-scan in practice.
The layout below shows a daily-partitioned fact table under a run responsible for the 11th, with a trailing overlap of two days to absorb late arrivals. Three partitions are read; the rest of history is skipped by pruning alone, which is the entire mechanism (Partition Pruning).
Note the last row. The partition for a day three months back is skipped by every ordinary run, which is correct and is also why a record that arrives three months late will never be picked up by the schedule. Absorbing that case is a deliberate re-run of that interval, not something the incremental predicate can do (Late-Arriving Data).
- fct_orders/order_date=2026-03-11/the interval this run owns · 6 files · read
- fct_orders/order_date=2026-03-10/yesterday, already published · 6 files · read
- fct_orders/order_date=2026-03-09/two days back · 5 files · read
- fct_orders/order_date=2026-03-08/three days back · 6 files · skipped
- fct_orders/order_date=2026-02-*/the previous month · 168 files · skipped
- fct_orders/order_date=2025-*/last year · 2100 files · skipped
The overlap window is the design decision worth arguing about. Too narrow and late rows are lost until someone notices; too wide and every run pays for repeated work. It only works at all because the write replaces each partition rather than adding to it.
The two eras problem
A full rebuild has one property that is easy to miss until it is gone: the whole table is always produced by one version of the code against one version of the schema. Incremental output has no such property. Every slice is a fossil of the code and schema in force when it was written, and the table becomes a stratigraphy.
The schema change below is the ordinary kind — a discount column added upstream, handled correctly, breaking nothing. From the day it lands, new partitions carry it and old ones do not. Every technical check passes: the column exists, its type is right, the pipeline is green.
The impacts are where the damage is. An analyst summing net revenue across the boundary gets a number that is right for recent months and wrong for older ones, with no error and no warning, because a missing column reads as null and null sums to nothing. The only repairs are to re-run the affected intervals with current code, or to state the boundary explicitly in the dataset's documentation so consumers can see it (Dataset Documentation).
- order_id
- order_date
- customer_id
- amount_minor
- produced_by
- order_id
- order_date
- customer_id
- amount_minor
- discount_minor
- produced_by
change The source began emitting discount_minor on 2026-03-11. The incremental model was updated the same day. Partitions from that date forward carry the column; every earlier partition has it as null, because nothing re-ran them.
| Consumer | Effect | How it shows up |
|---|---|---|
| Net revenue metric (`amount_minor - discount_minor`) | Correct from 2026-03-11 onward. Before that date the discount reads as null, so net equals gross and revenue is overstated for all of history. | Silently — no error, wrong result |
| Year-over-year comparison | Compares a net figure against a gross one across the boundary and reports a decline that did not happen. | Silently — no error, wrong result |
| A downstream mart built by full refresh | Recomputes from the fact table each night, so it inherits the same boundary but presents it as a single consistent series. | Silently — no error, wrong result |
| A strict-schema reader (Avro or a typed loader) | Fails on the older partitions if the field is declared required, which is the one loud failure in this table and therefore the useful one. | Loudly — it raises |
| Data test asserting the column is not null | Fails immediately on historical partitions, which is why such a test should be scoped to the intervals the column is expected in (Data Tests). | Loudly — it raises |
| A model trained on the fact table | Learns from a feature that is structurally absent before a date, and treats that absence as signal. | Silently — no error, wrong result |
How to build it
Most important first.
- Do not go incremental until the full rebuild genuinely does not fit. The rebuild is correct by construction and self-healing; every step away from it is a step towards state you must maintain (Full Refresh vs Incremental).
- Prefer an interval-bounded change set over a stored watermark wherever the data has a usable event or partition time. It needs no state, it backfills naturally, and it makes each run independent of every other.
- Where a watermark is unavoidable, bound the read with overlap: re-read a trailing window behind the watermark on every run — or simply recompute the last few intervals outright — and rely on an idempotent merge to absorb the repeats. Overlap costs a little repeated work and buys tolerance for both the commit-visibility gap and ordinary late arrivals, without anyone filing an incident (Late-Arriving Data).
- Use a log position instead of a timestamp when the source offers one. A position is exact, monotonic and replayable; a timestamp is an approximation of an ordering that was never guaranteed (The High-Water Mark).
- Reconcile against the source on a schedule. Incremental pipelines drift, and the only reliable detector of drift is comparing totals with the system of record for a closed period (Reconciliation).
- Record the code version per partition, because incremental output accumulates eras and you will need to know which slices predate a fix (Metadata: Technical, Operational and Business).
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.
- Interval-bounded incremental processing guarantees that the slice for an interval reflects the input available at run time for that interval. It does not guarantee that the input was complete then (Late-Arriving Data).
- Watermark-bounded processing guarantees only that rows whose timestamp exceeded the stored value at read time were considered. It explicitly does not guarantee that all rows belonging to that range were visible, and that gap is not detectable from inside the pipeline (Incremental Extraction).
- A merge on the business key guarantees convergence for keys it sees. It cannot see deletes at the source unless deletes are represented as records, so a deleted row lives forever in the target (What a CDC Event Contains).
- Nothing here guarantees that the incremental result equals what a full rebuild would produce. That equality is a property you must test, not one you receive.
- Ordering between the change set and the merge is guaranteed only within a run. Concurrent runs over overlapping ranges have no defined outcome unless serialised (Ordering Guarantees: Four Levels, Four Prices).
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 matters is a periodic full recomputation compared against the incremental table for a closed period: rebuild into a shadow location and diff row counts, key sets and summed measures. It is the only check that directly tests the claim incremental processing is making.
- It misses anything wrong in both — a shared logic bug appears identically in the rebuild — and it costs a full rebuild, which is the thing incremental processing exists to avoid, so it runs weekly rather than nightly (Reconciliation).
- A cheaper standing check is per-interval row count against the source for closed intervals, which catches watermark gaps at the interval where they happened rather than months later (Volume Anomalies).
- The gain is real and it is the entire motivation: work proportional to what changed rather than to what exists, so the run finishes in time and can therefore run more often.
- The cost is that freshness becomes a per-interval property rather than a whole-table one. A table can be current for today and permanently wrong for last Tuesday, and a single freshness number hides exactly that (Freshness Monitoring).
- A trailing rebuild window trades a fixed amount of repeated work for absorbing late arrivals silently. The width of that window is the real freshness decision, and it should be chosen from the observed lateness distribution rather than from a round number (Late Events).
- Incremental tables accumulate eras. A column added upstream exists only in slices processed after the change, and a metric definition changed in code exists only in slices processed after the deploy. The table is internally inconsistent and structurally valid (Semantic Changes).
- Repairing an era means re-running its intervals with current code, which is exactly the operation incremental processing makes expensive — and exactly the operation idempotency makes possible (Reprocessing vs Retrying).
- A type change upstream is worse than an added column: old slices hold the old type and new slices the new one, and a union across the boundary either fails or casts silently (Nullability & Defaults).
- Recovery is re-running an explicit interval range with current code. That is straightforward if the write replaces the slice and impossible if it appends, which is why this lesson depends on the previous one (Idempotent Data Pipelines).
- If the watermark is the thing that broke — advanced past unprocessed rows — recovery is to reset it to a known-good position and re-process forward, accepting the duplicate work that the merge will absorb (The High-Water Mark).
- Backfilling a wide range needs bounded concurrency and, usually, ordering: intervals that merge into a shared target must not race, and running them in order is the simplest way to guarantee that (Planning a Backfill).
- Validate before publishing. A re-run of six months of corrected logic is exactly the operation that should be compared against the current table before it replaces it (Validating a Backfill Before You Publish).
- Keep the raw landing immutable, or the re-run has nothing faithful to read and recovery is bounded by whatever the source still remembers (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- A watermark advancing past rows that had not yet committed, losing them permanently and silently (Incremental Extraction).
- Late-arriving records falling outside every future change set, so an interval stays wrong until someone re-runs it.
- A merge that never sees deletes, so removed source rows persist in the target indefinitely (What a CDC Event Contains).
- Concurrent backfill runs racing on a shared watermark or a shared partition.
- Two schema eras in one table, discovered when an aggregate that spans the boundary produces an implausible number (Schema Evolution).
- The mitigation failing: an overlap window sized for the usual case, against a source whose worst case is a monthly batch job that backdates its rows.
- "Incremental is the mature choice." It is the choice forced by volume. A nightly full rebuild that fits comfortably is strictly better: correct by construction, self-healing, and stateless (Full Refresh vs Incremental).
- "
WHERE updated_at > last_runis incremental extraction." It is an approximation of it that loses rows whenever commit time differs from timestamp assignment, which is every source under load (Incremental Extraction). - "Late data is rare." Late data is a distribution, not an exception. Every source has a tail, and pipelines are usually designed against its median (Late-Arriving Data).
- "The incremental table matches what a rebuild would produce." That is a hypothesis. Until something rebuilds and diffs, it is untested, and the gap grows quietly with every schema change and every missed row.
Operating it
- Rows processed per run against rows that existed in the source for that range — the direct measure of whether the change set was complete (Pipeline Metrics).
- Watermark lag: the gap between the stored value and the source's newest record. A watermark that stops moving is an outage; one that jumps is a gap (Freshness Monitoring).
- Observed arrival lateness distribution — how far behind event time records actually land — which is the input to every window-width decision (Late Events).
- Per-interval row counts over time, which reveal both gaps and the double-counting that a failed backfill leaves behind (Volume Anomalies).
- At 10x history, incremental stops being an optimisation and becomes the only option, and the validation rebuild becomes the expensive part of the design.
- At 100x, the merge itself needs partition and sort-order pruning to avoid reading the whole target for a small change set (Clustering and Sort Order).
- At high change rates the state becomes contended: a single watermark row updated by many parallel workers is a serialisation point, and per-partition state replaces it (Partitioning).
- The saving is the point: compute proportional to change rather than to history. For an append-mostly dataset with years of history, that is the difference between a pipeline that fits in its window and one that does not (Compute Waste).
- The costs added are a merge read against the target, state to maintain, and the periodic validation rebuild that proves the whole thing still works (Scan Cost).
- Small intervals produce small files, and small files cost list operations and scan overhead on every downstream query. Incremental pipelines are the usual source of a compaction problem (File Size and the Small-Files Problem, File Compaction).
- Incremental processing buys a run that fits in its window and costs you the self-healing property of a full rebuild. Every bug now needs an explicit repair, and every repair needs idempotency.
- An overlap window buys tolerance for late and slow-committing data at the price of repeated work on every run. It is almost always worth it, and the width is a genuine judgement call.
- A stored watermark buys independence from the schedule and costs a piece of mutable state that can be advanced wrongly, raced on, or lost — none of which the interval-bounded approach can suffer.
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.
- GENERALThe trade of compute for state, and the resulting exposure to late data and clock semantics, is universal. What differs is which bounding mechanism the source can support: a log position where there is a log, an event-time partition where events carry one, and a mutable timestamp where neither exists.
- SOURCE-SPECIFICA Postgres source can be read by WAL position, which is exact; a MySQL binlog gives file and position; a SaaS API usually gives only a modified-since filter whose semantics are undocumented and whose clock is the vendor's. The strength of your incremental guarantee is capped by the weakest of these, not by your code.
- SCALE-SPECIFICBelow the point where a full rebuild misses its window, incremental processing adds state and failure modes for no benefit. The threshold is about rebuild duration against the schedule, not about row counts, and a wide table of modest length can cross it before a narrow table of billions does.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a source's commit order and its timestamp order are different orderings, and why no single clock reading can be treated as a cut across a concurrent system. That is the precise reason a timestamp watermark loses rows.
- — DevOps / Production Engineering owns the deploy half of the two-eras problem: a transformation change is a release, and knowing which build produced which partition requires the same versioning discipline as any other artefact.