Accumulating Snapshots for Pipeline/Workflow Milestones, Reopened Processes, and Updating Rows

Learning outcomes

AtlasMart now wants one operationally useful row that answers “where is order O1002 in fulfillment, how long did it take to reach shipment, and which orders are still open?” Reconstructing that answer from many independent events is possible, but a predictable workflow can also be represented by an accumulating snapshot whose row is intentionally revisited as milestones occur.

1. One row per fulfillment lifecycle

AtlasMart chooses one row per order fulfillment lifecycle for the teaching fixture. The row starts when the order is observed and gains paid_ts, picked_ts, shipped_ts, and delivered_ts as those predictable steps occur. current_status is a convenience state, not a replacement for the milestone fields.

2. Event-log gate plus accumulating row

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 event log provides deduplication and audit evidence for this lab. The accumulating row is the current workflow summary. Keeping both makes the semantics explicit: one table preserves accepted source events; the other is updated for convenient lifecycle analysis.

3. Idempotent application logic

def apply_event(conn, event):    event_id, order_id, seq, event_type, event_ts, ingested_at = event    try:        conn.execute(            'INSERT INTO fulfillment_event_log VALUES (?,?,?,?,?,?)', event        )    except sqlite3.IntegrityError:        return False  # replayed event: summary row must not change    conn.execute(        """INSERT OR IGNORE INTO fact_fulfillment_accum           (order_id,current_status,reopen_count,last_event_seq,updated_at)           VALUES (?,?,0,0,?)""",        (order_id, 'new', ingested_at),    )    # Then update only the named milestone for a new higher event sequence.    # The complete Chapter 10 acceptance script performs this deterministically.    return True

A duplicate event ID returns False and cannot increment reopen_count twice. This is local loader idempotency; it is not a claim of distributed exactly-once processing.

4. Deliberately wrong approach: treat the row as immutable

If AtlasMart inserts one lifecycle row at order creation and refuses to update it, every later milestone would require another row—silently converting the design into a transaction/event fact with a different grain. Conversely, overwriting one generic status_ts loses the earlier milestones. The accumulating pattern works because the same process row is updated while separate milestone columns preserve the predictable steps.

5. Reopened processes expose the limits of the summary

O1001 is delivered and then reopened on September 21. The lab preserves the original delivered timestamp, records reopened_ts, increments reopen_count, and changes the current state to reopened. If the process can reopen many times or revisit arbitrary steps, a single accumulating row cannot preserve every transition; keep an event fact/log or another history structure alongside it.

SELECT order_id,delivered_ts,reopened_ts,reopen_count,current_statusFROM fact_fulfillment_accumWHERE reopen_count > 0;-- O1001 | 2026-09-20T11:00:00 | 2026-09-21T12:00:00 | 1 | reopened

6. Lag measurements

Milestone timestamps support durations such as order-to-ship or ship-to-deliver. Store a derived lag only when its business calendar and rounding policy are governed; otherwise compute it from the authoritative milestones.

SELECT order_id,       ROUND((julianday(shipped_ts)-julianday(order_ts))*24.0,2) AS order_to_ship_hoursFROM fact_fulfillment_accumWHERE shipped_ts IS NOT NULLORDER BY order_id;

SQLite’s date arithmetic is only the local execution harness. A production business-hours SLA may require calendars, time zones, holiday exclusions, and service-specific rules that simple elapsed hours do not capture.

7. Production judgment

Accumulating snapshots are valuable when process milestones are predictable and current pipeline state plus lag analysis matter. They are a poor sole record for arbitrary event histories. Keep event identity, ordering, correction/reopen rules, and downstream restatement behavior visible; if a late event arrives with an older sequence or business time, route it through a defined correction path instead of silently moving the lifecycle backwards.

Knowledge check

Summary and next step

The accumulating snapshot intentionally revisits one process row while preserving milestone evidence. Lesson 4 compares all three fact shapes and demonstrates why combining their grains into one universal table is a modeling error.

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.