Accumulating Snapshot Fact Tables: One Row Per Process, Updated in Place

A transaction fact table appends. A periodic snapshot appends on a schedule. An accumulating snapshot does neither: it inserts one row when a process begins and rewrites that same row each time the process reaches a milestone. The order placed on Monday is the same row on Wednesday when it ships and on Friday when it is delivered, with more columns filled in and the earlier ones unchanged.

The pattern is the right one for any process with a known, bounded sequence of steps and a business interest in how long each step took. It is also the only fact table type whose rows are mutable, and that mutability is the source of every difficulty in building one.

The row as a process instance

The grain of an accumulating snapshot is one row per process instance — one order, one claim, one loan application, one support ticket. That declaration is what separates it from a transaction fact table at event grain. The fact table carries one foreign key per milestone, because each milestone is a separate context: the warehouse that picked the order, the carrier that shipped it, the address it was delivered to. A transaction fact table would need three separate rows for those three events, each with one key. The snapshot puts them side by side so a single row describes the whole pipeline.

Alongside the milestone keys sit the milestone timestamps, one per step, and the lag measures derived from them: days from order to pick, from pick to ship, from ship to delivery, and from order to delivery overall.

CREATE TABLE fact_order_fulfilment (
  order_id            VARCHAR(32) NOT NULL PRIMARY KEY,  -- degenerate dimension
  order_date_key      INTEGER     NOT NULL,
  pick_date_key       INTEGER,                            -- null until picked
  ship_date_key       INTEGER,
  delivery_date_key   INTEGER,
  pick_warehouse_key  BIGINT,
  carrier_key         BIGINT,
  delivery_region_key BIGINT,
  units_ordered       INTEGER     NOT NULL,
  units_shipped       INTEGER,
  order_amount        NUMERIC(12,2) NOT NULL,
  ship_to_deliver_days INTEGER                          -- derived lag
);

The nullability is structural, not incidental. A row for an order that has not shipped has no ship date, no carrier and no lag, and the nulls record that the step has not happened rather than that the value is unknown. Those two meanings are different, and a snapshot table that cannot distinguish them is a table whose lags cannot be trusted.

Additivity inside a mutable row

Milestone keys are not additive at all; they are foreign keys and sum to nothing. The interesting measures are the ones that sit between.

units_ordered is fixed at insert and additive across orders. units_shipped is filled later and is additive across orders but not comparable to units_ordered on the same row until the process finishes, because a partially shipped order has a shipped count that is still moving. A report that sums units_shipped over a period including in-flight orders understates the period, and the understatement shrinks as the orders complete — which is exactly the behaviour that makes a snapshot table’s recent periods look different from its older ones.

The lag measures are worse. ship_to_deliver_days is an interval, additive across orders in the sense that the total elapsed days is a meaningful sum, but averaging it across orders weights every order equally regardless of size. An average delivery lag is a legitimate measure; a sum of delivery lags is rarely meaningful. And a lag computed from a row that is still in flight is a lag over a partial interval, so it is not comparable to a completed one.

The standard treatment is to report lags only over completed instances and to report in-flight counts separately, as a pipeline-state measure. The snapshot table supports this because the milestone timestamps are present; the query filters on the final milestone being non-null before computing the distribution.

A second-order problem: the lag is stored as a column, so it is fixed at the time the row was last updated. If a delivery date is later corrected by a source system, the stored lag does not follow unless the update recomputes it. Derived columns on a mutable row are only as current as the last write, and every derived column needs a recomputation rule that the loader applies on every update rather than only on insert.

Inserting before the process ends

The pattern only works if the row exists from the first milestone. If the row is inserted when the order is delivered, the intermediate milestones have already happened and their timestamps must be backfilled — which is possible only if the source retained them. If the source is an order management system whose status history is a log, the timestamps can be reconstructed. If the source only holds the current state, the earlier timestamps are gone and the row is born incomplete.

This is the constraint that separates accumulating snapshots from transaction fact tables in practice. A transaction table can be loaded whenever the events become available, including months late, because each event is an independent row. A snapshot table cannot: a row that missed its first milestone can never have that milestone’s lag, and the missing lag is not recoverable.

The consequence for the load is that the first milestone’s arrival has to be detected promptly. An order that is created and shipped within the same hour still needs its row inserted at creation, with the ship fields null, and then updated. A load that runs daily and finds both milestones at once can still produce a correct row, provided the source timestamps both events — the row is inserted with both filled, and the lags are correct. The pattern tolerates a delayed insert; it does not tolerate a source that overwrites the earlier timestamp with the later one.

The update itself has to be idempotent, because a snapshot row may be rewritten many times and a re-run must not duplicate it. The natural implementation is a merge on the grain key — the order identifier — that updates the milestone columns only when the incoming value is non-null and the existing value is null, or when the incoming value differs and the source is authoritative.

MERGE INTO fact_order_fulfilment t
USING staged_orders s
   ON t.order_id = s.order_id
WHEN MATCHED AND (t.ship_date_key IS NULL AND s.ship_date_key IS NOT NULL)
  THEN UPDATE SET ship_date_key = s.ship_date_key,
                  carrier_key   = s.carrier_key,
                  units_shipped = s.units_shipped
WHEN NOT MATCHED
  THEN INSERT (order_id, order_date_key, units_ordered, order_amount)
       VALUES (s.order_id, s.order_date_key, s.units_ordered, s.order_amount);

The WHEN MATCHED guard is what makes the merge idempotent in the presence of out-of-order deliveries: an update that arrives for a row whose ship date is already set does not overwrite it. Without the guard, a late-arriving duplicate of an earlier state can move a milestone backwards, and the lag becomes negative.

Late milestones and retroactive change

Milestones can arrive out of order relative to each other. A delivery notification can reach the warehouse before the shipping notification it depends on, especially when the two come from different source systems with different latency. A row then has a delivery date and no ship date, and the ship-to-deliver lag is uncomputable even though both events have occurred.

The fix is not to insert the delivery and wait. It is to hold the milestone until its predecessor is present, or to insert it and recompute the lags when the predecessor arrives. The second is simpler and leaves the table temporarily inconsistent — a row whose delivery date is earlier than its ship date is impossible in the world and possible in the table. A constraint that enforces ordering would reject the second update rather than the first, so the constraint belongs on the lag columns being non-negative, checked at the end of the load rather than per row.

The more serious problem is retroactive change to an earlier milestone. A ship date corrected by three days changes not only its own lag but the lag of every step after it. In a mutable row this is one update that recomputes the derived columns, which is the pattern’s advantage. In an append-only table the same correction would require rewriting history or accepting that two rows disagree. The cost of that advantage is that the table’s past is not stable: a report run last month and the same report run today can differ for a period that was thought closed, because a row from that period was updated.

That instability has to be managed rather than avoided. The usual approach is to record the last update timestamp on the row, so a report can state the as-of moment of its data, and to snapshot the table periodically if a stable historical record is needed. The snapshot of a snapshot table is a periodic snapshot fact table in its own right, which is a legitimate combination and the reason the two types are often built together for the same process.

When not to use it

The pattern is wrong when the process has no fixed sequence. A support ticket can be reopened, reassigned and escalated in any order, indefinitely; an accumulating snapshot would need an unbounded number of milestone columns, and the lags between arbitrary pairs are not the question anyone asks. Such a process is better modelled as a transaction fact table of state changes, with the current state derived.

It is also wrong when the number of instances is very large and the milestone count is very small, because the table’s row count equals the instance count and the updates rewrite rows repeatedly. A process with a hundred million instances and two milestones is a transaction table with an extra join, not a snapshot.

And it is wrong when the milestones are the interesting events in their own right. If the business asks “how many orders were picked on Tuesday”, a snapshot table cannot answer without filtering on the pick date key, which is fine — but a transaction table answers it directly and also carries the pick’s own measures, which the snapshot has no room for. The two tables can coexist on the same grain key, one describing the pipeline and one describing the events, and the conformed dimensions that both use are what let the two be compared without a fan-out.