Accumulating snapshot in Kimball Data Modeling: practical step

João Barros 19 de July de 2026 4 min read

An accumulating snapshot in Data Modeling (Kimball) records an instance of a process (e.g., order) and tracks its lifecycle with columns for key events. It is useful for measuring cycle times, identifying bottlenecks, and feeding process performance reports.

Prerequisites

Basic knowledge of SQL (inserts, updates, MERGE).
SQL Server, Azure SQL, or similar environment with MERGE support.
Basic dimension tables (for example dim_customer, dim_product).

Step 1: Define the grain for the Accumulating snapshot in Data Modeling (Kimball)

Choose exactly which unit of analysis — typically "an order" or "an order-to-cash process". The grain defines that there will be a single row in the fact table per process instance.

Step 2: Identify key events and measures

List the events that occur throughout the cycle and the measures you want to track. Example for orders: order_date, ship_date, delivery_date, order_amount, days_to_ship, days_to_deliver.

Step 3: Create the fact table structure (SQL example)

Create a fact table with a surrogate key, the business key (order_number) and columns for each event. Include audit fields to know when the row was created/updated.

CREATE TABLE fact_order_accumulating (
  fact_order_sk INT IDENTITY(1,1) PRIMARY KEY,
  order_number VARCHAR(50) UNIQUE,
  customer_sk INT,
  product_sk INT,
  order_date DATE,
  ship_date DATE NULL,
  delivery_date DATE NULL,
  order_amount DECIMAL(18,2),
  status VARCHAR(20),
  created_dt DATETIME2 DEFAULT SYSUTCDATETIME(),
  updated_dt DATETIME2
);

Step 4: Initially load the fact table (Initial load)

In the initial load insert one row per process instance with the events available at that moment (for example only order_date). Keep order_number as the natural key to identify the single row.

INSERT INTO fact_order_accumulating (order_number, customer_sk, product_sk, order_date, order_amount, status)
SELECT o.order_number, c.customer_sk, p.product_sk, o.order_date, o.total_amount, 'Ordered'
FROM staging_orders o
JOIN dim_customer c ON o.customer_id = c.customer_id
JOIN dim_product p ON o.product_id = p.product_id
WHERE NOT EXISTS (
  SELECT 1 FROM fact_order_accumulating f WHERE f.order_number = o.order_number
);

Step 5: Incremental updates of the Accumulating snapshot in Data Modeling (Kimball)

When events occur (e.g., shipping or delivery), update the existing row instead of inserting a new one. Use MERGE or UPDATE to apply changes. The logic keeps one row per process, filling event columns as they happen.

-- Example with MERGE to apply new shipping/delivery events
MERGE INTO fact_order_accumulating AS target
USING (
  SELECT order_number, ship_date, delivery_date, event_type
  FROM staging_order_events
) AS src
ON target.order_number = src.order_number
WHEN MATCHED AND src.event_type = 'Shipped' AND (target.ship_date IS NULL) THEN
  UPDATE SET ship_date = src.ship_date,
             status = 'Shipped',
             updated_dt = SYSUTCDATETIME()
WHEN MATCHED AND src.event_type = 'Delivered' AND (target.delivery_date IS NULL) THEN
  UPDATE SET delivery_date = src.delivery_date,
             status = 'Delivered',
             updated_dt = SYSUTCDATETIME();

Step 6: Calculate derived measures

Once the dates are populated, you can create measures like days_to_ship or days_to_deliver in a transformation layer or in the semantic layer (Power BI or report).

-- Examples of calculated columns in a view
CREATE VIEW vw_fact_order_metrics AS
SELECT *,
  DATEDIFF(day, order_date, ship_date) AS days_to_ship,
  DATEDIFF(day, ship_date, delivery_date) AS days_ship_to_deliver,
  DATEDIFF(day, order_date, delivery_date) AS days_total_cycle
FROM fact_order_accumulating;

Verify the result

Validate that there is only one row per order_number and that dates are updated, not replaced by new rows. Example checks:

Unique count: SELECT COUNT(*) vs COUNT(DISTINCT order_number).
Check populated events: SELECT * FROM fact_order_accumulating WHERE ship_date IS NULL AND status='Shipped'.
Test the view vw_fact_order_metrics for plausible values (negative days indicate data issues).

Conclusion

An accumulating snapshot in Data Modeling (Kimball) simplifies cycle analyses by keeping one row per process and updating events over time. Next steps: automate the ETL (schedule MERGE), handle retroactive corrections and document business rules. Tip: always validate the integrity of natural keys and handle out-of-order events (timestamps) before updating the fact table — how do you handle out-of-order events in your process?