What is an accumulating snapshot fact table, and why are its rows updated in place?
answer
- one row per what — event or instance?
- a date key per milestone
- how do you report days from order to ship?
- the row changes as the order moves
- what fills a milestone not yet reached?
basics
~20 sAn accumulating snapshot holds one row per pipeline instance — an order, a claim, an application — with a date key per milestone and lag measures between them. The row is updated as the instance advances, because the whole point is one current row per pipeline.
solid answer
~50 sAn **accumulating snapshot fact table** models a process with a defined beginning, middle and end: order to cash, claim to settlement, application to hire. Its grain is **one row per pipeline instance**, and it carries a foreign key to the date dimension for each milestone — ordered, picked, shipped, invoiced, paid — plus measures for the lags between them and any quantities that accumulate. The row is written when the instance starts, with future milestone dates unknown, and is **updated in place** each time a milestone is reached. That is deliberate: the value of the table is that a single row shows the current state and elapsed time of every instance, so "average days from order to ship this quarter" and "how many orders are stuck before picking" are single scans with no self-joins. The cost is a mutable fact table, which most analytical stores handle less cheaply than appends, so loads are usually batched by rewriting the affected partitions.
code
text · 5 linesorder_line 8801 over its life (ONE row, rewritten)
after order: order=20260302 pick=UNKNOWN ship=UNKNOWN days_order_to_ship=NULL
after pick: order=20260302 pick=20260303 ship=UNKNOWN days_order_to_ship=NULL
after ship: order=20260302 pick=20260303 ship=20260305 days_order_to_ship=3go deeper
Recall the definition: one row per pipeline instance with a date key per milestone, updated as the instance advances. Order-to-cash is the standard example.
Explain why in-place updates are inherent to the design, what fills a milestone not yet reached, and which queries — cycle time, work in progress — the shape is built to make trivial.
Show how you load it in practice: batched merge or partition rebuild, idempotency, cancelled instances that never complete, and keeping the transaction fact table as the audit record.
Weigh whether the convenience table earns its maintenance cost against deriving the same answers from atomic facts, and decide who owns the milestone and cycle-time definitions across teams.
## The shape An accumulating snapshot is the third fact-table type, alongside transaction and periodic snapshot. It is built for **workflows with a predictable set of milestones**: order fulfilment, insurance claims, mortgage applications, recruitment funnels, manufacturing work orders, support-ticket lifecycles. Its grain is one row per instance of the pipeline — one order line, one claim, one application. Unlike a transaction fact, which would spread that instance across several event rows, the accumulating snapshot keeps everything about the instance's progress in one place. A typical row carries three families of column: 1. **A date foreign key per milestone.** `order_date_key`, `pick_date_key`, `ship_date_key`, `invoice_date_key`, `payment_date_key`. Milestones not yet reached point at a designated "not yet" row in the date dimension rather than being left NULL, so joins do not silently drop rows. 2. **Lag measures.** `days_order_to_ship`, `days_ship_to_payment`, computed when the later milestone lands. These are what makes cycle-time reporting a simple average rather than a chain of self-joins. 3. **Accumulating quantities and status.** Quantity ordered, quantity shipped, quantity returned, amount invoiced, plus the current status. ```sql CREATE TABLE fct_order_fulfillment ( order_line_key BIGINT NOT NULL, order_date_key INTEGER NOT NULL, pick_date_key INTEGER NOT NULL, -- "unknown" row until picked ship_date_key INTEGER NOT NULL, invoice_date_key INTEGER NOT NULL, product_key INTEGER NOT NULL, customer_key INTEGER NOT NULL, order_number VARCHAR(20) NOT NULL, -- degenerate dimension quantity_ordered INTEGER NOT NULL, quantity_shipped INTEGER NOT NULL, days_order_to_ship INTEGER, days_ship_to_invoice INTEGER ); ``` ## Why the row is updated rather than appended The design promise is *one current row per pipeline instance*. That promise is what makes the useful queries trivial: - **Cycle time**: `AVG(days_order_to_ship)` filtered on ship month. - **Work in progress**: count rows whose `ship_date_key` still points at the unknown row. - **Bottleneck analysis**: compare the lag distributions between consecutive milestones. If you appended a new row at every milestone instead, each of those questions becomes a self-join or a window function over the instance's event rows, and "where is it now" requires finding the latest row per instance. That is precisely what the transaction fact table already gives you, so an append-only accumulating snapshot would be a duplicate of it with none of its own benefits. The cost is real: fact rows mutate. Warehouses are optimised for appending and scanning far more than for updating individual rows, so the practical load pattern is a batched merge — collect the day's milestone events, apply them in one statement, and let the store rewrite whatever it needs to rewrite. ```sql MERGE INTO fct_order_fulfillment t USING stg_milestones s ON t.order_line_key = s.order_line_key WHEN MATCHED THEN UPDATE SET t.ship_date_key = s.ship_date_key, t.quantity_shipped = s.quantity_shipped, t.days_order_to_ship = s.ship_day_number - s.order_day_number WHEN NOT MATCHED THEN INSERT (order_line_key, order_date_key, ship_date_key) VALUES (s.order_line_key, s.order_date_key, s.unknown_date_key); ``` A common alternative, where updates are especially awkward, is to **rebuild** the table (or the recent partitions of it) from the transaction facts on each run. That trades update cost for recompute cost and keeps the loader idempotent; the resulting table is identical in shape and use. ## What it does not replace An accumulating snapshot is a *derived, convenience* fact table. It shows the current state of each instance, so it does not retain the full history of how the instance got there — if a shipment date was corrected twice, only the final value survives unless you deliberately keep the correction as a separate event. The atomic transaction fact table remains the audit record. Similarly it is not a periodic snapshot: it has no row per period, so "how many orders were open on 12 March" needs either a status-per-day snapshot or a reconstruction from the milestone dates. ## Handling unreached and unreachable milestones Two modelling decisions matter. First, use an **unknown/not-yet member** in the date dimension for milestones not reached, keeping the foreign key non-nullable and the joins inner. Second, decide what happens to instances that never finish — a cancelled order will sit forever with unreached milestones and will skew cycle-time averages unless the status column is used to filter them, so carry a status and a cancellation date rather than pretending the pipeline is only ever completed. ## Common mistakes Saying the grain is one row per milestone gets the type wrong — that is a transaction fact table. Leaving milestone date keys NULL breaks inner joins and quietly drops in-flight instances from reports. Computing lags at query time from raw dates instead of storing them makes every cycle-time question a date-arithmetic exercise and loses the calendar-aware version (working days versus calendar days) that the business usually means.
- What do you put in a milestone date key before that milestone is reached?A designated unknown or not-yet member of the date dimension, referenced by a reserved surrogate key. Keeping the column non-nullable means inner joins to the date dimension do not drop in-flight rows, and counting rows whose ship date points at that member is the cleanest work-in-progress query. NULLs achieve neither and force outer joins everywhere.
- Why store days_order_to_ship instead of computing the difference at query time?Because the business definition rarely equals a raw date subtraction — it usually means working days, or excludes holidays and hold periods. Storing the lag makes that definition computed once by the loader and identical for everyone, and it makes cycle-time reporting a plain average rather than date arithmetic repeated in every query and BI tool.
saying these in an interview costs you the question
- Says its grain is one row per milestone event
- Leaves unreached milestone date keys NULL
- Treats it as the audit record instead of the transaction fact table
- Claims it can answer how many orders were open on a given past date
- Thinks it can be loaded append-only like a transaction table