Fact Grains: The Accumulating Snapshot and UPDATE-in-Place

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard32 minsnowflake-warehousefact-table grainsaccumulating snapshotUPDATE-in-placemilestone lag durationsidempotent rebuild

The three fact grains, and the one that mutates: build an accumulating-snapshot fact with NULLable milestone dates, computed lags, and an idempotent rebuild.

The three fact grains, and the one that updates in place

You told these three grains apart in Level 4's additivity lesson (transaction, periodic snapshot, accumulating snapshot); the one-line reminder is that a fact table's grain answers "what does one row mean". Here they are again before we build the grain that mutates in place:

Grain	One row per	Changes after insert?
Transaction	event (an order, a click, a payment)	No, append only
Periodic snapshot	entity per period (account per day)	No, one new row each period
Accumulating snapshot	process instance (an order's lifecycle)	Yes, updated in place as it advances

The accumulating snapshot is the one that updates. It models a process with a known set of milestones (order, picked, shipped, delivered) as one row per instance, with a NULLable date column per milestone and computed lag columns between them. As each milestone lands you fill in its date and recompute the lags. It is the most-asked advanced-fact modeling question because it is the only grain where a row is mutable.

Building it

Two ways, and both come up in an interview:

UPDATE in place. Open a row when the order is placed (later milestone dates NULL), then UPDATE it as each milestone arrives.
Rebuild from source. Pivot the milestone event log into one row per order with MIN(CASE WHEN milestone = 'shipped' THEN event_ts END) per milestone, and DELETE then INSERT the whole table each run.

The rebuild is naturally idempotent (run it twice, same table), which is why column-store warehouses prefer it. The lag columns are date differences: days_order_to_ship = julianday(shipped_ts) - julianday(order_ts), left NULL while the later milestone has not happened.

Interview nuance: a not-yet-reached milestone is NULL, and its lag is NULL, never zero. An order that shipped but has not been delivered has a real days_order_to_ship and a NULL days_ship_to_deliver. Coalescing those NULLs to 0 would silently claim instant delivery.

In the warehouse this differs. The UPDATE becomes a MERGE in Snowflake and BigQuery, and column-store warehouses often rebuild the whole table from source each run rather than mutate rows, because a row-level UPDATE is expensive on columnar storage. The grain and milestone-lag reasoning is identical.

Sample data for this example

CREATE TABLE raw_order_events (order_id INTEGER, milestone TEXT, event_ts TEXT);
INSERT INTO raw_order_events VALUES
  (1, 'order', '2026-03-01'), (1, 'picked', '2026-03-02'), (1, 'shipped', '2026-03-03'), (1, 'delivered', '2026-03-06'),
  (2, 'order', '2026-03-02'), (2, 'shipped', '2026-03-05');   -- order 2 not delivered yet

Worked example (SQL)

-- Pivot the event log into the accumulating-snapshot row. Order 2's delivered_ts and lag stay NULL.
SELECT order_id,
  MIN(CASE WHEN milestone = 'order'     THEN event_ts END) AS order_ts,
  MIN(CASE WHEN milestone = 'shipped'   THEN event_ts END) AS shipped_ts,
  MIN(CASE WHEN milestone = 'delivered' THEN event_ts END) AS delivered_ts,
  CAST(julianday(MIN(CASE WHEN milestone = 'delivered' THEN event_ts END))
     - julianday(MIN(CASE WHEN milestone = 'shipped'   THEN event_ts END)) AS INTEGER) AS days_ship_to_deliver
FROM raw_order_events
GROUP BY order_id;

Apply

Your turn

The task this lesson builds to.

Write a script that rebuilds fct_order_pipeline as an accumulating-snapshot fact, one row per order, from the raw_order_events(order_id, milestone, event_ts) log.

Pivot each order's milestones into the NULLable columns order_ts, picked_ts, shipped_ts, delivered_ts, and compute the lag columns days_order_to_ship and days_ship_to_deliver as whole-day differences (left NULL while the later milestone has not happened). Lead with DELETE FROM fct_order_pipeline; so re-running the script rebuilds instead of duplicating.

3 hints and 3 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a script that backfills a late-arriving delivered_ts into an already-built fct_order_pipeline and recomputes the delivery lag, without changing the grain.

The late_deliveries(order_id, delivered_ts) table holds deliveries that landed after the fact was built. For each, set delivered_ts on the matching pipeline row and recompute days_ship_to_deliver from the new delivered_ts and the existing shipped_ts. Touch only those orders, and keep exactly one row per order.

3 hints and 3 automated checks are waiting in the workspace.