Transaction Facts for Atomic Events and Immutable Business Activity

Learning outcomes

AtlasMart already has an order-line sales fact that reconciles to 625 paid GMV. The Chapter 10 question is not “how do we make a fact table?” but “what kind of measurement event does each row represent, and is that row allowed to change?” Transaction facts are strongest when the source event is atomic and the warehouse preserves that event at its declared grain.

1. The row exists because a sale happened

A transaction fact records a measurement event at a point in time. AtlasMart uses one row per paid order line. If no paid line exists, no row exists. This sparse event behavior differs from a periodic snapshot, where a row may be expected for every product every day even if nothing changed.

The report does not define the grain. The business event does. Product, customer, order identifier, event timestamp, quantity, revenue, and cost are all interpreted at that order-line event.

2. Deterministic transaction fixture

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));-- fact_sales_transaction rows are loaded by the Python fixture.SELECT COUNT(*) AS line_rows,       COUNT(DISTINCT order_id) AS orders,       SUM(quantity) AS units,       SUM(extended_amount) AS gmv,       SUM(extended_cost) AS cost,       SUM(extended_amount-extended_cost) AS gross_profitFROM fact_sales_transaction;

Expected controls are 7 lines, 4 orders, 9 units, 625 GMV, 380 cost, and 245 gross profit. The unique source_event_id is replay evidence; it does not replace the business grain key (order_id,line_no).

3. Deliberately wrong approach: mutate the old sale into the latest truth

Suppose a source correction says O1002 line 1 should be 190 rather than 200. A blind UPDATE erases the value previously published and can make old extracts impossible to reproduce. The safe policy depends on the business requirement: authorized restatement may replace the row under a versioned correction record, while an accounting workflow may append a reversal/adjustment event. The important point is to name the policy and preserve evidence.

-- Deliberately unsafe without a correction contract:UPDATE fact_sales_transactionSET extended_amount = 190WHERE order_id='O1002' AND line_no=1;-- A quality check would now show paid GMV = 615, not the accepted 625.-- Do not publish that restatement until correction authority, lineage,-- downstream impact, and rollback behavior are defined.

The failure signal is a 10-unit reconciliation delta. The query is syntactically valid; the process is semantically incomplete.

4. Transaction facts cannot answer every “as of” question

Sales events can explain units sold, but current inventory also depends on receipts, transfers, adjustments, returns, damage, reservations, and other state-changing events. Even with a perfect event ledger, reconstructing a historical balance can be expensive and policy-sensitive. AtlasMart therefore keeps a daily inventory snapshot for point-in-time state questions.

That is not duplication by accident. It is a deliberate second fact table with a different grain and a different question contract.

5. Replay/idempotency test

The source event ID is unique. Re-inserting the same transaction event must be rejected or deterministically ignored according to the loader contract; it must not create a second paid line.

import sqlite3# conn is the deterministic Chapter 10 fixture.row = ('O1001',1,'2026-09-18T09:00:00','D-CUST-001','P100',2,100,60,'SALE-O1001-1')try:    conn.execute('INSERT INTO fact_sales_transaction VALUES (?,?,?,?,?,?,?,?,?)', row)    raise AssertionError('duplicate event unexpectedly inserted')except sqlite3.IntegrityError:    passassert conn.execute('SELECT COUNT(*) FROM fact_sales_transaction').fetchone()[0] == 7

This proves duplicate suppression under the local constraints. It does not prove exactly-once delivery between distributed source, queue, transformation, and warehouse systems.

6. Production judgment

Prefer transaction facts when the measurement event itself is analytically useful and the dimensions are meaningful at event time. Keep event identity, source lineage, correction policy, and late-arriving behavior explicit. If the main question is “what was the state at the end of each day?” or “where is this order in a multi-step workflow?”, use a fact shape designed for that question rather than making the transaction table impersonate one.

Knowledge check

Summary and next step

Transaction facts preserve atomic measurement events and maximize event-level slicing, but they are not universal state tables. Lesson 2 introduces the periodic snapshot for balances measured at regular intervals.

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.