An accumulating snapshot fact table models a business process with a defined beginning and end - an order that moves from placed to picked to shipped to delivered, a mortgage application that runs from submission to underwriting to funding. Each process instance gets one row, and that row is revisited and updated as the instance hits each milestone. This is the one Kimball fact pattern that doesn't follow insert-only semantics, and that single difference shapes everything about how it's loaded and queried.

One row per process, many date keys

The defining feature is a set of date foreign keys, one per milestone in the workflow. An order-fulfillment table carries order_date_key, pick_date_key, ship_date_key, and delivery_date_key on the same row. When the order is first placed, only the first date is known; the rest point to a "not yet" row in the date dimension. As the order progresses, the loader updates the row, filling in each date as the event occurs.

fact_order_fulfillment (
  order_key,                    -- one row per order (the grain)
  order_date_key,
  pick_date_key,                -- NULL/unknown until picked
  ship_date_key,
  delivery_date_key,
  customer_key, warehouse_key,
  order_to_ship_days,           -- lag measures, recomputed on update
  ship_to_delivery_days,
  quantity, order_amount
)

The grain is the process instance - one order, one row, forever. That's what makes the in-place update legal: there's exactly one place to record where the order is right now.

Lag measures are the payoff

Because every milestone date lives on the same row, the durations between steps are trivial to store and query. order_to_ship_days, ship_to_delivery_days, total cycle time - these are the questions accumulating snapshots exist to answer, and they'd require painful self-joins against an event-grain transaction fact table. The loader recomputes the lag columns each time it updates a milestone, so "average days from order to delivery, by warehouse" is a plain aggregate instead of a windowed comparison across rows.

Loading: update in place

Each ETL cycle does an upsert keyed on the process instance: insert the row when the order first appears, then UPDATE the matching row as later events land. Most of the table's rows are "in flight" with several null date keys; rows reach their final state once the process completes and are rarely touched again. This pattern only fits a process with a short, predictable, well-defined set of steps - a handful of milestones, not an open-ended event stream. If milestones are unbounded or vary per instance, an accumulating snapshot stops being a clean fit and an event-grain table serves better.

Frequently asked questions

How is it different from a periodic snapshot? A periodic snapshot writes a new row per entity per time period and never updates existing rows; an accumulating snapshot keeps one row per process instance and updates it in place as the instance advances through its milestones.

Why does it update rows when fact tables are supposed to be insert-only? Because the grain is the whole process, not a single event. There's one correct row for an order, and recording that the order shipped means updating that row - the alternative (a row per event) is just a transaction table.

What goes in the unfilled date keys? A surrogate key pointing to a special "unknown / not yet" row in the date dimension, not a literal NULL, so joins to the date dimension never drop rows.

When should I avoid this pattern? When the process has no fixed end, the milestones vary by instance, or the number of steps is large and open-ended - those belong in a transaction fact table, optionally summarized with an aggregate fact table.