skip to content

What breaks in an incrementally refreshed rollup when a backfill rewrites last month's fact rows?

level: seniorimportance: should knowfreq 48%

answer

  1. the merge can add but never subtract
  2. the watermark hides the changed region
  3. event-time watermarks lose late-arriving rows
  4. recompute only the touched partitions
  5. reconcile a trailing window on a schedule

basics

~20 s

Incremental refresh only adds contributions from rows above a watermark, so it never sees a rewrite of old rows and cannot subtract the old contributions. The aggregate keeps the pre-backfill numbers and drifts silently until affected partitions are recomputed or the whole thing is rebuilt.

solid answer

~50 s

Incremental refresh is correct **only for append-only inserts**. It reads whatever is new above a watermark, aggregates it, and merges the deltas into the stored rows — a merge that can add but not subtract. A backfill that updates or deletes already-aggregated rows breaks both halves: the changed rows sit below the watermark so they are never re-read, and even if they were, there is no record of what the old rows contributed, so the old value cannot be removed. The aggregate silently keeps stale numbers. The standard remedies are to make the aggregate **partition-scoped** — recompute exactly the partitions the backfill touched and swap them in — or to fall back to a full rebuild, and to run a reconciliation job that compares the aggregate against the fact table for a trailing window. Related but distinct is late-arriving data: if the watermark tracks event time rather than load time, ordinary late events are skipped too.

code

sql · 13 lines
sql
-- Repair only the partitions a backfill touched
DELETE FROM agg_orders_daily
WHERE order_day BETWEEN DATE '2025-06-01' AND DATE '2025-06-30';

INSERT INTO agg_orders_daily
SELECT CAST(order_ts AS DATE) AS order_day,
       country,
       sum(amount) AS sum_amount,
       count(*)    AS row_count
FROM fact_orders
WHERE order_ts >= DATE '2025-06-01'
  AND order_ts <  DATE '2025-07-01'
GROUP BY 1, 2;

go deeper

for a junior

Know that an incremental refresh only processes new rows, so changes to data that was already summarized are not picked up and the aggregate can be silently out of date.

for a middle

Explain the watermark and the additive merge, and why that merge can add contributions but not remove them — which is why updates and deletes, not just late inserts, break the pattern.

for a senior

Show the production playbook: load-time watermarks, a trailing re-processing window, partition-scoped recompute after a declared backfill, and a reconciliation job that turns silent drift into an alert.

for a principal

Own the upstream contract — append-only corrections, backfill jobs that declare the partitions they touch — and the policy for when an aggregate is rebuilt wholesale versus repaired in place.

## How incremental refresh actually works An incremental refresh avoids re-scanning the whole fact table by processing only the rows that arrived since the last run. Two things make that possible: a **watermark** (a value such as a load timestamp, an ingestion sequence or a change-log position that separates processed from unprocessed rows) and a **merge step** that folds the newly aggregated deltas into the stored aggregate rows — adding to `sum_amount` and `row_count` for keys that already exist, inserting rows for keys that do not. The merge is *additive by construction*. That is the design assumption, and everything that goes wrong follows from it. ## Why a backfill breaks it A backfill that rewrites last month's rows violates the assumption twice. **The rows are never re-read.** If the watermark is above the backfilled region, incremental refresh skips it. The engine has no reason to look at data it already processed. **Even re-reading would not be enough.** Suppose you widen the window and re-aggregate last month. Merging those results *adds* them on top of contributions that are already there, doubling the affected numbers. Undoing the old contribution requires knowing what it was — either by keeping a change log with before-images so the refresh can emit retractions, or by discarding the stored rows for that region entirely and recomputing them. The result is drift with no error: dashboards keep serving pre-backfill totals, and nobody notices until a downstream reconciliation or a finance review disagrees. ## Late-arriving data is the same failure, quieter Even without a backfill, the choice of watermark column matters. If the watermark tracks **event time**, a row that was generated on Monday and loaded on Wednesday has an event time below the watermark and is skipped forever — a permanent, small undercount that grows with your ingestion latency. If the watermark tracks **load or ingestion time**, every row is processed exactly once regardless of its event time; the aggregate's own *event-time* buckets for old days then get updated later, which is exactly what you want, provided the merge can reach those old buckets. When event-time grain and load-time watermarking are combined, incremental refresh works for late inserts but still not for updates or deletes. ## The fixes, in the order to reach for them **1. Design the fact table to be append-only.** If corrections are written as offsetting rows rather than in-place updates, incremental refresh remains exactly correct — a reversal is just another row that sums to a negative contribution. This is the cleanest option and worth arguing for at design time. **2. Partition-scoped refresh.** Keep the partition column (usually the date) in the aggregate's grouping keys, determine which partitions the backfill touched, delete the aggregate rows for exactly those partitions and recompute them from the fact table. Cost is proportional to the affected region, not the whole history. This requires a reliable way to identify changed partitions — a job that records what it rewrote, a change-tracking column, or in the worst case a checksum comparison per partition. **3. Bounded re-processing window.** Rather than tracking changes, unconditionally recompute the trailing N days on every refresh, sized to cover normal lateness and correction latency. Simple, robust, and cheap when the fact table is partitioned on that column so the scan prunes. It handles routine lateness but not a backfill older than the window. **4. Full rebuild.** Always correct, sometimes the honest choice for small or medium aggregates, and the right lever after a large historical correction. Schedule it, do not improvise it. ## Detecting drift before someone else does Whatever strategy you choose, add reconciliation: periodically recompute the aggregate directly from the fact table for a trailing window and compare it, key by key, with the stored rows. Alert on any mismatch above a tolerance. This is cheap when the comparison window is partitioned-pruned, and it is the only thing that turns a silent correctness bug into a page. Pair it with an operational rule that any backfill job must declare which partitions it touched, so the refresh pipeline can act on the declaration rather than guessing.

  • Why is a watermark on load time safer than one on event time?
    A load-time watermark guarantees each row is processed exactly once whatever its event time, so a row generated Monday and loaded Wednesday is still folded into Monday's bucket. An event-time watermark skips anything that arrives after the boundary has moved past its timestamp, producing a permanent undercount that grows with ingestion latency and is almost impossible to notice from the numbers alone.
  • What does partition-scoped refresh require of the aggregate's design?
    The partition column — normally the date — must be one of the aggregate's grouping keys, so the affected rows can be isolated and replaced. You also need a reliable signal of which partitions changed: a backfill job that records what it rewrote, a change-tracking column, or a per-partition checksum comparison. Then delete and recompute exactly those partitions and swap them in atomically.
  • How would you detect that a rollup has silently drifted from its fact table?
    Run a scheduled reconciliation that recomputes the aggregate directly from the fact table for a trailing window and compares it key by key with the stored rows, alerting past a tolerance. Partition pruning keeps that cheap. Without it, drift surfaces only when a downstream consumer disagrees, which is usually a finance or compliance conversation rather than an engineering one.
  • How does an append-only correction pattern keep incremental refresh valid?
    Instead of updating a row, you insert a compensating row that carries the negative of the original contribution plus the corrected values. Every change is then a new row above the watermark, so the additive merge stays exactly correct with no re-processing at all. The cost is a larger fact table and consumers that must always aggregate rather than read individual rows.

saying these in an interview costs you the question

  • Assumes incremental refresh notices updates and deletes automatically
  • Re-processes an old window and merges it again, double-counting
  • Watermarks on event time and never accounts for late arrivals
  • Believes a stale aggregate will produce an error rather than wrong numbers
  • Reaches for a full rebuild without considering partition-scoped recompute

context

resolving links…