Modeling Accumulating Snapshot Fact Tables

In this lesson, you will learn how to design an accumulating snapshot fact table to track the progress of a business process from start to finish.

Why Do We Need Another Fact Table?

So far, you’ve learned about Transaction fact tables (which record individual events) and Periodic snapshot fact tables (which summarize activity over a fixed period like a day or month). But what happens when you want to measure the lifecycle of a long-running process, like an e-commerce order?

An order has multiple milestones: Order Placed, Payment Received, Order Picked, Shipped, and Delivered. A transaction table creates a new row for every single status change, making it difficult to calculate the total duration of the process. An accumulating snapshot solves this by using one single row per process instance that gets updated as it moves through its lifecycle.

Designing the Table

In an accumulating snapshot, the fact table contains a foreign key for each milestone date. As the process moves forward, you update the specific date column corresponding to that milestone.

CREATE TABLE fact_order_lifecycle (
    order_key INT,
    customer_key INT,
    order_placed_date_key INT,
    payment_received_date_key INT,
    shipped_date_key INT,
    delivered_date_key INT,
    total_order_amount DECIMAL(10,2),
    days_to_ship INT -- Calculated metric (shipped_date - order_placed_date)
);

Analyzing the Process

Because all the timestamps for one order live in a single row, calculating metrics becomes incredibly efficient. You don't need complex joins or window functions to figure out how long it took from the moment an order was placed until it was delivered; you simply subtract the order_placed_date_key from the delivered_date_key.

This design is perfect for identifying bottlenecks. By aggregating the duration columns across millions of rows, you can easily see if the time between Order Placed and Payment Received is growing, allowing you to pinpoint where your business process is slowing down.

Key Takeaway: Accumulating snapshot fact tables use a single row per process instance to store multiple milestone dates, making it simple to calculate total process duration and identify operational bottlenecks.