An accumulating snapshot fact table represents a business process that has a clear start, distinct milestones, and an end, such as order fulfillment or mortgage underwriting. Each row represents a single process instance from start to finish, with multiple date foreign keys and duration metrics updated as the instance moves through each milestone.
How the table works
Unlike transaction fact tables where records are inserted once and never touched, an accumulating snapshot updates the existing record whenever a new milestone occurs. Consider an order fulfillment lifecycle:
fact_order_fulfillment order_key customer_key order_date_key payment_date_key shipment_date_key delivery_date_key fulfillment_status_key order_to_payment_days payment_to_ship_days ship_to_delivery_days total_fulfillment_days
A short look at the row state over time:
- When the customer places an order, the pipeline inserts a row with
order_date_keypopulated and downstream milestone dates pointing to standard sentinel values like -1 for Not Occurred. - When payment clears two days later, an ETL job updates that existing row, populating
payment_date_keyand computing the lag metric. - When shipping and delivery complete, subsequent pipeline runs update the remaining date keys and final durations.
Milestone lag metrics
Lag measures between milestones are the primary analytical value of this design. Because all milestone dates sit side by side on a single record, calculating the elapsed days between order placement and customer delivery requires simple column subtraction. Querying this in a standard transaction fact table would instead require complex window functions or multi-table self-joins across disparate event records.
When to choose this design
Use an accumulating snapshot whenever you need to monitor pipeline velocity, process bottlenecks, or SLA compliance for workflows with predictable lifecycles. Classic examples include loan applications, insurance claims processing, employee onboarding, and eCommerce package delivery. Avoid this design for indefinite, open-ended processes without a terminal state, or for high-frequency event streams where transactions naturally append without status revisions.