Model an Order Fulfillment Process Using Transaction + Periodic + Accumulating Facts and Compare Questions Supported

Learning outcomes

The final Chapter 10 lab treats fact-table choice as an executable contract. AtlasMart must be able to prove that sales events still reconcile to 625, each daily inventory snapshot is complete and time-aware, and fulfillment lifecycle rows update exactly once per accepted event while preserving milestone evidence. Then each representative business question is mapped to the fact shape whose grain actually supports it.

1. Decision matrix: ask what the row must mean

2. Complete deterministic setup

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fulfillment_event_log;DROP TABLE IF EXISTS fact_fulfillment_accum;DROP TABLE IF EXISTS fact_inventory_daily;DROP TABLE IF EXISTS fact_sales_transaction;CREATE TABLE fact_sales_transaction (  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  event_ts TEXT NOT NULL,  durable_customer_id TEXT NOT NULL,  product_id TEXT NOT NULL,  quantity INTEGER NOT NULL CHECK(quantity > 0),  extended_amount NUMERIC NOT NULL CHECK(extended_amount >= 0),  extended_cost NUMERIC NOT NULL CHECK(extended_cost >= 0),  source_event_id TEXT NOT NULL UNIQUE,  PRIMARY KEY(order_id,line_no));CREATE TABLE fact_inventory_daily (  snapshot_date TEXT NOT NULL,  product_id TEXT NOT NULL,  on_hand_units INTEGER NOT NULL CHECK(on_hand_units >= 0),  snapshot_batch_id TEXT NOT NULL,  PRIMARY KEY(snapshot_date,product_id));CREATE TABLE fact_fulfillment_accum (  order_id TEXT PRIMARY KEY,  order_ts TEXT,  paid_ts TEXT,  picked_ts TEXT,  shipped_ts TEXT,  delivered_ts TEXT,  reopened_ts TEXT,  current_status TEXT NOT NULL,  reopen_count INTEGER NOT NULL DEFAULT 0,  last_event_seq INTEGER NOT NULL DEFAULT 0,  updated_at TEXT NOT NULL);CREATE TABLE fulfillment_event_log (  event_id TEXT PRIMARY KEY,  order_id TEXT NOT NULL,  event_seq INTEGER NOT NULL,  event_type TEXT NOT NULL,  event_ts TEXT NOT NULL,  ingested_at TEXT NOT NULL,  UNIQUE(order_id,event_seq));

The Python fixture inserts the seven accepted sales rows, eight daily inventory rows, and the ordered fulfillment events. No cloud account, external database, credentials, or proprietary dataset is required.

3. Executable acceptance suite

import sqlite3, math# This script mirrors the deterministic fixture shown throughout Chapter 10.# Run with the same schema/data definitions, then assert the contracts below.conn = build_conn(load_events=True)assert sales_controls(conn) == (7, 4, 9, 625, 380, 245)assert inventory_controls(conn) == [    ('2026-09-19', 4, 146),    ('2026-09-20', 4, 137),]rows = accum_rows(conn)assert len(rows) == 4state = {r[0]: r for r in rows}assert state['O1001'][1] == 'reopened' and state['O1001'][8] == 1assert state['O1002'][1] == 'shipped' and state['O1002'][6] is Noneassert state['O1003'][1] == 'delivered'assert state['O1005'][1] == 'picked' and state['O1005'][5] is None# Replay the same events: event count, lifecycle states, and reopen_count are unchanged.before_events = conn.execute('SELECT COUNT(*) FROM fulfillment_event_log').fetchone()[0]before_rows = accum_rows(conn)for e in EVENTS:    assert apply_event(conn, e) is Falseassert conn.execute('SELECT COUNT(*) FROM fulfillment_event_log').fetchone()[0] == before_eventsassert accum_rows(conn) == before_rows# Negative periodic-snapshot test: a missing product row must be visible.bad = build_conn(load_events=True)bad.execute("DELETE FROM fact_inventory_daily WHERE snapshot_date='2026-09-20' AND product_id='P400'")assert bad.execute("SELECT COUNT(*),SUM(on_hand_units) FROM fact_inventory_daily WHERE snapshot_date='2026-09-20'").fetchone() == (3,112)# Negative mixed-grain arithmetic: summing inventory through time is 283, not a current balance.assert conn.execute('SELECT SUM(on_hand_units) FROM fact_inventory_daily').fetchone()[0] == 283# Every fulfillment event sequence is unique per order and current summary has one row per order.assert conn.execute('SELECT COUNT(*) FROM fulfillment_event_log').fetchone()[0] == len(EVENTS)assert conn.execute('SELECT COUNT(*),COUNT(DISTINCT order_id) FROM fact_fulfillment_accum').fetchone() == (4,4)print('python=', __import__('sys').version.split()[0])print('sqlite=', sqlite3.sqlite_version)print('sales=', sales_controls(conn))print('inventory=', inventory_controls(conn))print('fulfillment=', [(r[0],r[1],r[8]) for r in rows])print('Chapter 10 acceptance: PASS')

Expected final line: Chapter 10 acceptance: PASS. The script also prints the actual Python and SQLite versions so the local execution baseline is not hidden.

4. What the negative tests prove

The deleted P400 snapshot row drops September 20 coverage from four products to three and the total from 137 to 112; the suite catches the missing member rather than accepting a plausible-looking total. The raw sum of both snapshot days is explicitly asserted as 283 so learners can see the wrong arithmetic, while the narrative states why that value is not a current balance. Replaying all fulfillment events must leave event count, lifecycle rows, current states, and reopen count unchanged.

5. Event-to-snapshot reconciliation

Not every snapshot can be reconstructed from the sales transaction fact alone because inventory also changes through receipts, transfers, adjustments, returns, reservations, and damage. Therefore Chapter 10 does not fabricate a formula claiming sales events explain the drop from 146 to 137. A production reconciliation would start from prior balance, add every governed inventory movement class, and prove the resulting closing balance to the published snapshot. Missing movement sources are a source-readiness gap, not a reason to invent numbers.

6. Observability and lineage

Operational monitoring should separate the three surfaces: transaction duplicate/reconciliation failures; periodic snapshot freshness/completeness/restatement status; and accumulating lifecycle event lag, out-of-order events, open-process age, replay duplicates, and milestone anomalies. Lineage should connect source event or state extract → ingestion/batch/event ID → fact row → semantic metric → consuming report. A task that “ran successfully” is not evidence that these fact contracts are correct.

7. Production performance and cost

This in-memory SQLite fixture is a correctness harness, not a warehouse benchmark. Transaction scans, snapshot density, and accumulating updates have different storage and execution profiles. Before a production physical-design decision, disclose engine/runtime, row counts, update frequency, partitioning/clustering/indexes, compression, cache/warm state, concurrency, query corpus, and measured latency/bytes worked. Do not infer a universal winner from this tiny lab.

8. Change, correction, and rollback contract

Corrections affect each fact differently. A transaction correction may require restatement or adjustment; a periodic snapshot correction republishes a period/member balance; an accumulating snapshot correction may revise one milestone or replay accepted events from raw evidence. Version the correction policy, preserve source evidence, reconcile before/after outputs, identify downstream impact, and retain a rollback path. Rebuilding all history is not automatically safe merely because the lab can reset instantly.

9. Cleanup/reset

All tables live in an in-memory SQLite connection. Closing the process discards them. Re-running build_conn() recreates the accepted fixture, including the event log and lifecycle summary, from deterministic constants. The mandatory lab has no production blast radius.

Knowledge check

Summary and next step

Chapter 10 now proves three distinct fact contracts: events stay atomic, balances are measured by period with semi-additive time rules, and predictable workflows accumulate milestones through controlled updates. Chapter 11 next addresses surrogate-key assignment, late-arriving facts/dimensions, deletes, restatements, and history corrections.

Authoritative references

• Kimball Group — Transaction Fact Tables — Primary reference for atomic measurement-event fact tables and transaction grain.
• Kimball Group — Periodic Snapshot Fact Tables — Primary reference for standard-period snapshot grain.
• Kimball Group — Accumulating Snapshot Fact Tables — Primary reference for pipeline/workflow facts whose rows are revisited as milestones occur.
• Kimball Group — Lag/Duration Facts — Reference for milestone lag and duration measures in accumulating snapshots.
• Kimball Group — Fact Tables — Overview of transaction, periodic snapshot, and accumulating snapshot grains.
• SQLite — CREATE TABLE — Constraint semantics used by the deterministic local fixture.
• SQLite — UPSERT — Official SQLite upsert syntax reference; the lab uses explicit event-log idempotency rather than assuming engine-independent merge semantics.
• SQLite — Date And Time Functions — Reference for local deterministic timestamp and duration calculations.