How we prevent stale rows in incremental fact models

A stale row is an output the model no longer knows how to revisit.

It may contain an old status, retain a deleted source entity, omit a late child, preserve a previous classification, or remain in a partition after its business date moved. The common failure isn’t that change happened. The model never defined how that kind of change reaches existing output.

Preventing stale rows therefore requires a correction mechanism, not just a wider input watermark.

Start with the target row

State the fact model’s grain and unique key. Then list every upstream event that can change, remove, or relocate that row after its first build.

For an order-line fact, that may include:

• line value or quantity corrections
• order cancellation or deletion
• a late payment or fulfilment record
• customer or product reclassification
• movement to another accounting date
• source replay with an older business timestamp

Each event needs a route to the target key. If a changed shipment can affect an old order but the model only scans recently updated orders, that path is missing.

The affected-set design is covered in Incremental models are only safe when change detection is explicit. Here the concern is what the model does once those keys are known.

Recompute complete rows for affected keys

For mutable facts, avoid patching individual target columns from whichever source happened to change. Build the current full row for every affected key from authoritative inputs, then merge or replace it.

with affected_orders as (
  select order_id from changed_orders
  union distinct
  select order_id from changed_order_lines
  union distinct
  select order_id from changed_payments
),
recomputed as (
  select
    o.order_id,
    o.customer_id,
    o.status,
    sum(l.net_amount) as net_amount,
    max(p.paid_at) as paid_at
  from source.orders as o
  left join source.order_lines as l using (order_id)
  left join source.payments as p using (order_id)
  where o.order_id in (select order_id from affected_orders)
  group by o.order_id, o.customer_id, o.status
)
select * from recomputed;

This keeps one row definition. The incremental path selects which rows to rebuild. It doesn’t invent a second, partial version of the model.

Model absence explicitly

A MERGE that only updates or inserts can’t remove target rows whose source entity disappeared or no longer qualifies.

Choose a deletion contract:

• source tombstones drive target deletes
• affected keys missing from the recomputed result are deleted
• a bounded partition is replaced wholesale
• rows remain with an explicit inactive or deleted state

The right choice depends on history requirements. The wrong choice is leaving disappearance undefined and discovering years later that cancelled facts still contribute to totals.

Be careful with filters. If a source row stops qualifying because its status changed, it vanishes from the USING query. The merge needs a separate affected-key set to know that the existing target row must be removed or recomputed.

Use partition replacement when the partition is the correction unit

When all changes are naturally bounded by a small set of partitions, replacing those partitions can be clearer than row-level merge logic.

Calculate the affected partition list, rebuild complete partitions from source truth, and atomically replace them. This handles inserts, updates, and deletes in one operation. It also makes the correction boundary visible in cost and audit.

Don’t choose partition replacement when one late child can affect an arbitrary old partition and the affected partition can’t be found cheaply. In that case, key-based recomputation is the honest mechanism.

Measure the tail before choosing a lookback

A rolling lookback is useful for sources whose corrections arrive within a stable delay. Derive the window from observed lateness and business tolerance, then monitor records outside it.

The window shouldn’t keep growing as a substitute for understanding dependencies. If 99.9 percent of changes arrive within three days but a specific source can correct records after ninety days, give that source an affected-key path or periodic reconciliation. Reprocessing ninety days every hour is an expensive way to avoid naming one exception.

Reconcile independently of the fast path

Even a well-designed incremental can drift because source contracts, deletion behavior, or change-capture logic fail.

Run a periodic reconciliation that compares source truth and target at the declared grain. It may check counts, hashes, key sets, aggregates, or fully rebuild a bounded historical slice. Record discrepancies and repair them through the same model logic.

The reconciliation frequency follows impact and recoverability. Financial facts deserve a stronger check than a low-value operational cache. No model deserves zero check merely because its daily run succeeded.

Keep repair close to the model

The model repository should own affected-key logic, recomputation, deletion semantics, and partition replacement. Orchestration may choose a backfill range, serialize runs, and retry infrastructure failures. It shouldn’t carry a hidden cleanup branch that changes what the table means.

A manual repair should invoke the same model with a larger affected set. A separate script that “fixes production” becomes an undocumented second writer and guarantees future disagreement.

Test transitions, not only first load

Fixtures should cover a row being inserted, corrected, joined to a late child, moved across partitions, disqualified, deleted, and replayed. Run the incremental twice and prove the result converges. Then compare it with a full rebuild.

The important property isn’t that the second run is fast. It’s that every supported source transition produces the same current fact the full model would produce.

Stale rows are prevented when the model can name affected output, rebuild complete truth for it, remove what no longer belongs, and detect drift independently. Anything less is an append pipeline with optimistic branding.