Choosing Multiple Fact Tables for One Business Process Instead of Forcing One Universal Grain

Learning outcomes

AtlasMart now has three legitimate analytical needs: atomic sales events, end-of-day inventory state, and order-fulfillment lifecycle progress. A common anti-pattern is to force them into one “fact_activity” table because they share dates, products, or orders. Chapter 10 instead keeps each measurement process at its own grain and aligns answers only after each fact has been aggregated safely.

1. Three facts, three row meanings

The same enterprise can legitimately maintain all three. The design question is whether each row has one clear measurement meaning, not whether the warehouse has “too many tables.”

2. Deliberately wrong universal table

CREATE TABLE fact_everything (  row_type TEXT NOT NULL,  order_id TEXT,  line_no INTEGER,  snapshot_date TEXT,  product_id TEXT,  quantity INTEGER,  revenue NUMERIC,  on_hand_units INTEGER,  shipped_ts TEXT,  delivered_ts TEXT);-- Sales rows need order/line/revenue but not snapshot balance.-- Inventory rows need snapshot_date/on_hand but not order/line.-- Lifecycle rows need one row per order and milestone updates.-- NULLs are not the main problem: the row meaning is inconsistent.

Now a query such as COUNT(*) has no stable business meaning, date columns mix event and snapshot roles, and consumers can accidentally sum revenue beside repeated inventory balances. A discriminator does not restore one declared grain.

3. Safe alignment happens after aggregation

Suppose a dashboard wants September 20 paid GMV, end-of-day inventory, and count of orders not yet delivered. Compute each measure from its own fact first, then place the scalar or date-level results side by side. Do not atomic-join order lines to product snapshots: that would repeat the daily balance once per matching sales line.

WITH sales AS (  SELECT substr(event_ts,1,10) AS d, SUM(extended_amount) AS paid_gmv  FROM fact_sales_transaction  GROUP BY substr(event_ts,1,10)), inventory AS (  SELECT snapshot_date AS d, SUM(on_hand_units) AS on_hand  FROM fact_inventory_daily  GROUP BY snapshot_date), open_fulfillment AS (  SELECT '2026-09-20' AS d, COUNT(*) AS not_delivered  FROM fact_fulfillment_accum  WHERE delivered_ts IS NULL     OR delivered_ts > '2026-09-20T23:59:59')SELECT s.d,s.paid_gmv,i.on_hand,o.not_deliveredFROM sales sLEFT JOIN inventory i USING(d)LEFT JOIN open_fulfillment o USING(d)WHERE s.d='2026-09-20';

Each CTE reduces its source fact to the requested report grain first. This is the same safety principle used for cross-process drill-across in Chapter 06.

4. Conformed dimensions still matter

Product/date/customer conformance makes independent facts comparable, but every fact includes only dimensions meaningful at its grain. Inventory has no customer dimension. An order-level lifecycle may not carry product when one order contains multiple products; if product-level fulfillment is required, choose a line-level lifecycle grain or a separate bridge/child process rather than stuffing multiple product keys into one order row.

5. Storage and performance tradeoffs

Transaction facts grow with event volume. Dense periodic snapshots grow with periods × covered members. Accumulating snapshots grow with process instances but incur repeated updates. None of those statements implies a universal performance winner. Physical design depends on the target engine, workload, data size, update behavior, partitioning, clustering, compression, concurrency, and retention policy. This chapter evaluates semantic fit first; later chapters address physical/columnar design and performance with measured workloads.

6. Migration and rollback

If AtlasMart previously published a mixed-grain table, migration should run old and new outputs in parallel, map each legacy metric to its new authoritative fact, reconcile historical periods, update semantic/report dependencies, and define rollback criteria. Deleting the old table before consumers prove semantic equivalence converts a modeling improvement into an operational incident.

Knowledge check

Summary and next step

Multiple fact tables are a semantic strength when they preserve distinct measurement processes. Lesson 5 assembles the three shapes into one reproducible acceptance suite and a question-to-fact decision matrix.

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.