An order looks like it has two dates: placed, and delivered. Model it that way and the first split shipment breaks the table, the first late webhook corrupts the status column, and the first "how many orders are in transit right now" question has no good answer. An order isn't an event. It's a process — and a process with branches, delays, and messages that don't always arrive in the order they were sent.
The first article in this series covered the fundamentals through a coffee shop; the second applied them to SaaS subscriptions. Both dealt with processes that are, underneath the complexity, well-behaved: a loyalty journey has one milestone after another, a subscription has one plan at a time. This article is about the less well-behaved case — multi-stage processes where the stages can fork, arrive out of sequence, or simply take a while to all finish. Order fulfillment is the canonical example, and it's where accumulating snapshots earn their keep for real.
The 60-second recap
- Fact tables hold events (verbs). Transaction (atomic, append-only), periodic snapshot (regular photos), accumulating snapshot (multi-stage journeys, one row per entity, updated in place as milestones happen), factless (coverage/eligibility, no measures).
- Dimension tables hold context (nouns) — who/what/where/when.
- Grain = the exact definition of one fact row. State it in a sentence before you build anything. If two real questions need two different grains, build two fact tables — don't force one table to answer both.
-
SCD Type 2 preserves history: a new row per change, with
valid_from/valid_to/is_current, so historical facts join to the dimension row that was true then, not today.
New for this article: what happens when the "multi-stage journey" doesn't stay linear, and what to do when a measure genuinely can't be summed across time.
Meet Crate Expectations
Crate Expectations sells furniture and home goods online. The fulfillment shape that makes this article worth writing:
- Orders often contain items stocked in different warehouses — a couch from the East warehouse, a lamp from the West one. One order, two shipments, as a matter of routine, not exception.
- Some items are backordered and picked days after the rest of the order.
- Shipping is handled by two carriers (FastFreight and RoadRunner Parcel), each with its own webhook API notifying Crate Expectations of pickup, transit, and delivery events.
- Carrier webhooks are not reliable messengers: they retry, they queue behind rate limits, and — the part that actually breaks a naive model — they don't always arrive in the order the underlying events happened.
That last point is the spine of this article.
The grain fight: order, shipment, or package?
Before any table, the usual question: what's one row?
-
One row per order. Matches how the customer thinks about it. Clean for "how many orders did we place last month," "average time from order to delivery." Breaks the moment an order becomes two shipments — which date goes in
shipped_date, the first one or the last one? What if one shipment is delivered and the other is still backordered — is the order "delivered"? -
One row per shipment. Matches how the warehouse and carriers think about it. A shipment has one warehouse, one carrier, one tracking number, one ship date, one delivery date — no ambiguity. Breaks for order-level questions: "how many orders" now requires
COUNT(DISTINCT order_id), and anything about the customer's experience of the order (did they get everything, was anything late) needs to look across all of an order's shipments. - One row per package. Maximally granular — a shipment can itself split into multiple boxes. Overkill for almost every question Crate Expectations actually asks; the operational systems track it, the warehouse doesn't need it in the analytical layer.
Same lesson as the SaaS article, applied again: if order-level and shipment-level questions are both real and both common, build both fact tables. Don't pick one grain and force the other question to contort around it.
Crate Expectations builds the first two. Package-level detail stays in the operational system; nobody's asked a question that needs it in the warehouse.
fact_order_lifecycle: the accumulating snapshot
One row per order, milestone columns, updated in place as the order progresses — same pattern as the coffee shop's loyalty journey and Tabby's trial→paid→churn, just with more stages and (as you'll see shortly) a genuine reason those stages can misbehave.
CREATE TABLE fact_order_lifecycle (
lifecycle_sk BIGSERIAL PRIMARY KEY,
order_sk BIGINT NOT NULL REFERENCES dim_order(order_sk),
customer_sk BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
placed_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
payment_confirmed_date_sk INT REFERENCES dim_date(date_sk),
picked_date_sk INT REFERENCES dim_date(date_sk),
packed_date_sk INT REFERENCES dim_date(date_sk),
first_shipped_date_sk INT REFERENCES dim_date(date_sk),
all_delivered_date_sk INT REFERENCES dim_date(date_sk),
current_status TEXT NOT NULL, -- see the status ladder below
shipment_count INT NOT NULL DEFAULT 0,
is_split_shipment BOOLEAN NOT NULL DEFAULT false
);
Notice first_shipped_date_sk and all_delivered_date_sk, not shipped_date_sk and delivered_date_sk. That naming is doing real work: a split order doesn't have a ship date, it has one per shipment, so the order-level fact can only honestly report the first and the last. Anything more precise than that belongs in fact_shipment, not here — a good sign you're respecting the grain fight instead of quietly ignoring it.
Late-arriving facts: when the webhook lies about order
Here's the problem that doesn't show up in a well-behaved pipeline. Crate Expectations' carriers send webhook events — PICKED, PACKED, SHIPPED, DELIVERED — and those events are supposed to update fact_order_lifecycle as they happen. Two things go wrong in practice:
-
Retries duplicate events. A carrier's webhook fires, Crate Expectations' endpoint is briefly down, the carrier retries the same
SHIPPEDevent six hours later. Applying it twice should be harmless — but only if the update logic is written to expect it. -
Events arrive out of the order they occurred. A
PICKEDevent queued behind a rate limit can land after theSHIPPEDevent for the same order, because the carrier's own systems processed and sent them out of sequence. If the update logic just does "setcurrent_statusto whatever the latest webhook says," the order's status can visibly regress fromSHIPPEDback toPICKED— which is not just wrong, it's wrong in a way that makes the dashboard look broken to whoever's watching it.
The fix isn't to compare arrival time, and it isn't quite enough to compare the event's own timestamp either — carrier clocks skew, and a stale retried event can carry an old timestamp that still looks superficially valid. The robust fix is to stop treating current_status as "whatever the last message said" and start treating it as the furthest point reached in a known, ordered pipeline:
CREATE TABLE fulfillment_status_rank (
status TEXT PRIMARY KEY,
rank INT NOT NULL
);
INSERT INTO fulfillment_status_rank (status, rank) VALUES
('PLACED', 1),
('PAYMENT_CONFIRMED', 2),
('PICKED', 3),
('PACKED', 4),
('SHIPPED', 5),
('DELIVERED', 6);
Then every incoming event does two separate things — fill in its own milestone date unconditionally, but only advance current_status if the event represents genuine forward progress in the pipeline:
-- Applying one incoming event: (order_sk, event_status, event_date_sk)
UPDATE fact_order_lifecycle f
SET
picked_date_sk = CASE WHEN :event_status = 'PICKED'
THEN COALESCE(f.picked_date_sk, :event_date_sk)
ELSE f.picked_date_sk END,
packed_date_sk = CASE WHEN :event_status = 'PACKED'
THEN COALESCE(f.packed_date_sk, :event_date_sk)
ELSE f.packed_date_sk END,
first_shipped_date_sk = CASE WHEN :event_status = 'SHIPPED'
THEN LEAST(COALESCE(f.first_shipped_date_sk, :event_date_sk), :event_date_sk)
ELSE f.first_shipped_date_sk END,
current_status = CASE
WHEN (SELECT rank FROM fulfillment_status_rank WHERE status = :event_status)
> (SELECT rank FROM fulfillment_status_rank WHERE status = f.current_status)
THEN :event_status
ELSE f.current_status
END
WHERE f.order_sk = :order_sk;
Two design decisions worth calling out:
-
Milestone dates fill in whenever they arrive, regardless of order. A late
PICKEDevent still records when picking actually happened — that's genuinely useful data (it's what a "time from pick to pack" report needs), even though it arrived afterSHIPPEDalready updated the status.COALESCEmeans the first value to arrive for a given milestone wins and a duplicate retry can't overwrite it with a different date. -
current_statusonly ever moves forward, compared by pipeline rank, not arrival time or event timestamp. A duplicate or a late-arriving earlier-stage event can update its own milestone column without ever being able to drag the visible status backward.
This is the general shape of "late-arriving facts" in Kimball terms: the fix is almost never about buffering or re-ordering events before they land — it's about making the update logic correct regardless of what order things arrive in, because in any system with retries, queues, or multiple upstream senders, you cannot actually guarantee delivery order.
fact_shipment: the finer grain the order-level fact can't give you
"Which carrier is slowest?" and "how many packages did warehouse East ship last month?" are shipment-grain questions that fact_order_lifecycle structurally cannot answer once orders split. A second accumulating snapshot, one row per shipment:
CREATE TABLE fact_shipment (
shipment_sk BIGSERIAL PRIMARY KEY,
order_sk BIGINT NOT NULL REFERENCES dim_order(order_sk),
warehouse_sk INT NOT NULL REFERENCES dim_warehouse(warehouse_sk),
carrier_sk INT NOT NULL REFERENCES dim_carrier(carrier_sk),
packed_date_sk INT REFERENCES dim_date(date_sk),
shipped_date_sk INT REFERENCES dim_date(date_sk),
delivered_date_sk INT REFERENCES dim_date(date_sk),
package_count INT NOT NULL DEFAULT 1,
current_status TEXT NOT NULL
);
Every shipment event updates fact_shipment with the same rank-based logic above, unconditionally reliable regardless of arrival order. fact_order_lifecycle's first_shipped_date_sk/all_delivered_date_sk and shipment_count are then derived — MIN(shipped_date_sk), MAX(delivered_date_sk), COUNT(*) — from fact_shipment grouped by order_sk, kept in sync by whatever process applies shipment events. Two facts, two grains, one truth, each question answered at the grain that actually fits it.
Semi-additive measures and the question a snapshot alone can't answer
Here's a question in the same shape as "what was MRR on any given day" from the SaaS article, which a plain periodic snapshot can't cleanly answer either: how many orders were in transit — shipped but not yet fully delivered — at the end of each day?
This measure has a name worth knowing: it's semi-additive. A count like "orders in transit" is meaningful to sum across things that exist at the same instant (add up in-transit orders across every warehouse right now, and you get a real number: total orders currently moving). It is not meaningful to sum across time — Monday's in-transit count plus Tuesday's doesn't produce anything interpretable, because it's mostly the same orders being counted twice. Contrast with daily_revenue in the coffee shop's snapshot fact, which is fully additive: summing it across 30 days correctly gives you the month's revenue. Not every measure in a snapshot fact behaves the same way when you aggregate it, and treating a semi-additive measure as if it were fully additive is a quiet, easy-to-miss error — nothing throws an exception, the number just means something different than whoever's reading the dashboard assumes.
There are two honest ways to answer "in transit as of day X":
Reconstruct it from the accumulating snapshot, the same "as-of" logic used for SCD2 attributes in the earlier articles, just applied to a milestone range instead of a validity range:
SELECT COUNT(*) AS orders_in_transit
FROM fact_order_lifecycle
WHERE first_shipped_date_sk <= :as_of_date_sk
AND (all_delivered_date_sk IS NULL OR all_delivered_date_sk > :as_of_date_sk);
This works, costs nothing to set up, and is exactly right for ad hoc questions or a handful of dates.
Or materialize a periodic snapshot — fact_fulfillment_daily with one row per (date, warehouse, status) and a count — for the same three reasons the coffee shop and SaaS articles gave for building snapshots instead of always recomputing: a dashboard querying 5 years of daily in-transit counts across every warehouse shouldn't reconstruct all of it from milestone ranges on every load; a snapshot freezes what was true as of that day even if fact_order_lifecycle keeps changing; and the query becomes SELECT ... FROM fact_fulfillment_daily WHERE ... instead of a range-comparison scan. Same tradeoff as always — build the snapshot once the reconstruction query gets asked often enough to matter.
Either way: when you build that snapshot, don't add a SUM(daily_in_transit) chart across a date range and call it a meaningful total. That's the single most common way a semi-additive measure gets misused once it exists.
A real query
Average days from order placed to fully delivered, split by whether the order shipped from one warehouse or split across multiple:
SELECT
f.is_split_shipment,
COUNT(*) AS order_count,
AVG(d_delivered.full_date - d_placed.full_date) AS avg_days_to_deliver
FROM fact_order_lifecycle f
JOIN dim_date d_placed ON d_placed.date_sk = f.placed_date_sk
JOIN dim_date d_delivered ON d_delivered.date_sk = f.all_delivered_date_sk
WHERE f.all_delivered_date_sk IS NOT NULL
GROUP BY f.is_split_shipment;
Slowest carrier by average shipment transit time, at the shipment grain where that question actually lives:
SELECT
c.carrier_name,
AVG(d_delivered.full_date - d_shipped.full_date) AS avg_transit_days,
COUNT(*) AS shipment_count
FROM fact_shipment s
JOIN dim_carrier c ON c.carrier_sk = s.carrier_sk
JOIN dim_date d_shipped ON d_shipped.date_sk = s.shipped_date_sk
JOIN dim_date d_delivered ON d_delivered.date_sk = s.delivered_date_sk
WHERE s.delivered_date_sk IS NOT NULL
GROUP BY c.carrier_name
ORDER BY avg_transit_days DESC;
Try either against a raw event log of carrier webhooks directly and you're reconstructing state from scratch every time you run it. Against these two facts, both are a handful of lines.
Common mistakes
Modeling "shipped" and "delivered" as single dates on the order. Fine until the first split shipment, then silently wrong for every split order afterward — usually discovered when someone notices the numbers don't match the carrier's own dashboard.
"Latest webhook wins" status logic. The most natural-looking implementation and the one that lets status visibly move backward the first time an event arrives out of order. Rank the pipeline, compare ranks, not timestamps or arrival order.
Not guarding against duplicate events. Carriers retry. If applying the same
SHIPPEDevent twice can push a date forward a second time or double-count something downstream, the update logic isn't idempotent yet.Summing a semi-additive measure across time and presenting it as a total. "Orders in transit" summed across 30 days is not "total orders shipped this month" — it's a number that looks plausible and means nothing. If a measure can't be summed across the fact's own grain-defining dimension, say so next to it.
Forcing one fact table to serve both order-level and shipment-level questions. Padding
fact_order_lifecyclewithshipment_2_carrier,shipment_2_ship_datecolumns for the second shipment is a fixed-width hack that breaks the moment an order has three shipments. Buildfact_shipment.
Exercises
Hints hidden; full solutions in solutions.sql in the companion repo.
1. A DELIVERED webhook arrives for a shipment that has no prior SHIPPED event on file — the shipped notification appears to have been lost entirely, not just delayed. What should the update logic do, and why is this a different case from ordinary out-of-order arrival?
Hint
The rank-based status update still works (DELIVERED outranks whatever's currently on file). But shipped_date_sk would stay NULL forever unless you backfill it — consider inferring a shipped date from context (e.g., the delivered date minus typical transit time) versus just leaving the gap and flagging the row for review. There's a real tradeoff between a clean-looking dataset and an honest one.
2. Write the periodic snapshot version of fact_fulfillment_daily and the query that populates one day's rows from fact_order_lifecycle.
Hint
One row per (date, warehouse, status), COUNT(*) of orders matching the as-of reconstruction query from this article, grouped by warehouse. Populate it once per day as a scheduled job, the same way fact_daily_sales gets populated in Part 1.
3. Why is shipment_count stored directly on fact_order_lifecycle instead of always being computed with COUNT(*) FROM fact_shipment WHERE order_sk = ... at query time?
Hint
Same performance-vs-recompute tradeoff that motivated periodic snapshots in the first place — cheap to store, expensive to keep re-deriving on every query that touches order-level counts. The cost is that it can drift if the process updating fact_shipment doesn't also update the count; consider what would keep them in sync.
What's next
Everything so far has been one-to-one or one-to-many in a clean, hierarchical way: one order to many shipments, one account to many months. Part 4 breaks that assumption with bridge tables — the pattern for genuine many-to-many relationships, using healthcare claims (one claim, several diagnosis codes) as the running example, and the double-counting trap that shows up the moment you fan a dollar amount out across a bridge without thinking about weights.
Resources
- Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — the source for late-arriving fact handling as a named, documented pattern, not something this article invented.
- Kimball Group — Late Arriving Fact — the canonical short reference for the general pattern this article's out-of-order webhook handling is one instance of.
The companion repo has the schema, seed data, and exercises for this part. Part 4 is next.
Top comments (0)