Accumulating Snapshot Fact Table

An accumulating snapshot fact table tracks each instance of a process with a definite beginning and end — an order being fulfilled, a loan application being approved, a claim being settled — in a single row that is updated as the instance moves through its steps. The Kimball Group describes each row as summarizing the measurement events that occur at predictable steps between the beginning and end of the process.

How it works

A row is inserted when the instance starts — when the order line is created, for example — and then revisited and updated each time a milestone is reached. The Kimball Group points out that this revisiting is unique among the three types of fact table: transaction and periodic snapshot rows are written once and not changed.

The table carries a date for each milestone. An invented order-fulfillment example:

Because every milestone is on one row, the lag between any two steps is a subtraction, and “which orders have been picked but not shipped for more than two days” is a simple filter. That is why this shape suits pipeline, fulfillment, and workflow analysis.

What it cannot answer

The row holds the current state of the instance, along with the milestone dates it has accumulated. Those dates reconstruct more than they first appear to: if A-2 ships on 6 March, then picked_date <= '2026-03-05' AND shipped_date > '2026-03-05' still identifies it as picked-but-unshipped on 5 March. A pipeline-state-as-of query is therefore available for milestones that are reached once and whose dates are kept.

What is genuinely lost is everything the dates do not record. An instance that goes backwards or repeats a step — returned to picking, shipped twice — keeps only the latest date for that milestone, so the earlier passage disappears. Attributes updated in place, such as a status code or the assigned handler, retain no previous value. And the table cannot say what was known at the time: a milestone date entered three days late makes the row look, in hindsight, as though the information had been there all along. Those questions need their own structure — a periodic snapshot of pipeline state, or the milestone events kept as transactions with their own arrival times — rather than a workaround on this table.

When it fits

• The steps are predictable. A known, mostly linear set of milestones. Processes that loop, branch, or go backwards — an order returned to picking — are awkward, because one column per milestone has nowhere to put a second visit.
• The questions are about duration and bottlenecks, not about history of intermediate states.
• Updates are acceptable. The load has to find and modify existing rows, which is more work than appending and needs the table format and pipeline to support it.

An accumulating snapshot usually complements a transaction fact table over the same process rather than replacing it. How the three fact table shapes divide the questions is worked through in Grain, Facts, and the Arithmetic That Quietly Goes Wrong.

References: Kimball Group, Accumulating Snapshot Fact Tables.