Chapter 04 · Fact Table Design: Additive, Semi-Additive, Non-Additive Measures, and Factless Facts
Audit a Metric Catalog for Double Counting, Incorrect Grain, and Unsafe Aggregation
Audit the AtlasMart metric catalog so every metric declares grain, aggregation class, components, time behavior, ownership, reconciliation controls, and unsafe operations.
Learning outcomes
The safest warehouse does not leave aggregation behavior implicit in dashboards. This lesson turns Chapter 04 into a small metric contract that can be reviewed, tested, and versioned before consumers build derived logic of their own.
Audit a metric for grain, aggregation class, components, time rule, and ownership.
Detect double counting caused by mixed grain, unsafe SUM, average-of-averages, and overlapping distinct sets.
Require atomic numerator/denominator lineage for ratios and percentages.
Attach deterministic controls to additive, semi-additive, non-additive, and factless metrics.
Run a Chapter 04 acceptance test that reconciles every new fixture to earlier AtlasMart controls.
The Chapter 03 sales fact remains one row per paid order line: 7 rows, 4 paid orders, 9 units, and 625 paid GMV. Chapter 04 does not change that grain. It adds explicitly documented measurement fixtures only where this chapter needs them: synthetic cost-at-sale evidence, a two-day inventory periodic snapshot, channel-session denominators, and promotion-eligibility coverage. Every new structure is reconciled back to the established source/control values before it is used.
The mandatory labs use Python's standard-library
sqlite3 module and synthetic local data. They are
semantic correctness labs, not performance benchmarks. Record
the actual Python and SQLite versions from your machine; query
speed, optimizer behavior, decimal representation, and
physical storage behavior are engine-specific.
1. A metric catalog is an executable contract, not a glossary
A metric entry should contain enough information for a reviewer to answer: What business event or state is being measured? At what grain? Which dimensions can be added over? Which dimensions need a special rule? What atomic components reproduce the value? Who owns the definition? What control total or invariant detects drift?
| Audit field | Why it matters | Example |
|---|---|---|
| grain | Prevents mixed-detail metrics | paid order line |
| aggregation class | Defines legal rollups | additive / semi-additive / ratio / distinct / factless count |
| components | Makes derived metrics recomputable | profit and revenue for margin |
| time rule | Prevents summing repeated state | latest snapshot for ending inventory |
| owner | Creates a decision path for ambiguity | Finance Analytics |
| control | Makes semantic regression observable | paid_gmv = 625 on fixture |
2. Version the AtlasMart Chapter 04 metric catalog
{ "catalog_version": "ch04-v1", "metrics": [ { "id": "paid_gmv", "grain": "paid order line", "class": "additive", "formula": "sum(extended_amount)", "safe_dimensions": [ "date", "customer", "product", "store" ], "control": 625, "owner": "Finance Analytics" }, { "id": "gross_profit", "grain": "paid order line", "class": "additive", "formula": "sum(extended_amount - extended_cost)", "safe_dimensions": [ "date", "customer", "product", "store" ], "control": 245, "owner": "Finance Analytics" }, { "id": "gross_margin_pct", "grain": "query result", "class": "non_additive_ratio", "numerator": "gross_profit", "denominator": "paid_gmv", "formula": "sum(gross_profit)/sum(paid_gmv)", "control": 0.392, "owner": "Finance Analytics" }, { "id": "ending_on_hand_units", "grain": "product snapshot date", "class": "semi_additive", "formula": "sum(on_hand_units) at selected latest snapshot", "unsafe_dimensions": [ "date:SUM" ], "control": 137, "owner": "Inventory Analytics" }, { "id": "conversion_rate", "grain": "query result", "class": "non_additive_ratio", "numerator": "paid_orders", "denominator": "sessions", "formula": "sum(paid_orders)/sum(sessions)", "control": "4/170", "owner": "Growth Analytics" }, { "id": "unique_paid_customers", "grain": "requested query scope", "class": "non_additive_distinct", "formula": "count distinct customer_key", "control": 3, "owner": "Customer Analytics" }, { "id": "promotion_eligible_combinations", "grain": "promotion-product-store-date coverage row", "class": "factless_count", "formula": "count coverage rows", "control": 8, "owner": "Marketing Analytics" } ]}3. Audit rules that reject common warehouse errors
import jsonfrom pathlib import Pathcatalog=json.loads(Path("metric_catalog.json").read_text())ids=set()for m in catalog["metrics"]: assert m["id"] not in ids, m["id"] ids.add(m["id"]) assert m.get("grain") and m.get("class") and m.get("formula") and m.get("owner") if m["class"] == "non_additive_ratio": assert m.get("numerator") and m.get("denominator") if m["class"] == "semi_additive": assert m.get("unsafe_dimensions"), m["id"]print("catalog structural audit: PASS")This structural test cannot prove a business definition is correct; it proves that required semantic fields are present. Reconciliation tests provide the next layer of evidence.
4. Controlled failure matrix
| Tempting operation | Failure | Repair |
|---|---|---|
| SUM daily/period balances | Repeated states are double counted | Select/aggregate time using the governed snapshot rule |
| SUM or AVG percentages | Groups receive wrong weights | SUM numerator / SUM denominator |
| AVG group averages | Unequal group sizes disappear | Carry sums and counts, then divide |
| SUM lower-level distinct counts | Sets overlap across groups | COUNT DISTINCT at requested scope or use a governed approximate-set method |
| DISTINCT after a bad join | May hide duplicates without restoring the intended grain | Fix join/cardinality and reconcile to the atomic control |
| Add order-header metrics to order-line rows | Header values repeat per line | Allocate by explicit business rule or use a separate fact table at header grain |
DISTINCT is especially dangerous as a universal
patch. It can remove duplicate values rather than duplicate
business events, and it may conceal a join error instead of
preserving the fact grain.
5. End-to-end Chapter 04 acceptance lab
import sqlite3, math, jsonconn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_sales;\nDROP TABLE IF EXISTS dim_store;\nDROP TABLE IF EXISTS dim_product;\nDROP TABLE IF EXISTS dim_customer;\nDROP TABLE IF EXISTS dim_date;\nCREATE TABLE dim_date (\n date_key INTEGER PRIMARY KEY,\n full_date TEXT NOT NULL UNIQUE,\n calendar_year INTEGER NOT NULL,\n calendar_month INTEGER NOT NULL,\n day_of_month INTEGER NOT NULL,\n day_name TEXT NOT NULL\n);\nCREATE TABLE dim_customer (\n customer_key INTEGER PRIMARY KEY,\n customer_id TEXT NOT NULL UNIQUE,\n customer_name TEXT NOT NULL,\n segment TEXT NOT NULL,\n region TEXT NOT NULL\n);\nCREATE TABLE dim_product (\n product_key INTEGER PRIMARY KEY,\n product_id TEXT NOT NULL UNIQUE,\n product_name TEXT NOT NULL,\n category TEXT NOT NULL\n);\nCREATE TABLE dim_store (\n store_key INTEGER PRIMARY KEY,\n store_id TEXT NOT NULL UNIQUE,\n store_name TEXT NOT NULL,\n store_type TEXT NOT NULL\n);\nCREATE TABLE fact_sales (\n sales_key INTEGER PRIMARY KEY,\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n quantity INTEGER NOT NULL CHECK (quantity > 0),\n unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0),\n source_batch_id TEXT NOT NULL,\n UNIQUE(order_id, line_no)\n);\nINSERT INTO dim_date VALUES\n(20260918,'2026-09-18',2026,9,18,'Friday'),\n(20260919,'2026-09-19',2026,9,19,'Saturday'),\n(20260920,'2026-09-20',2026,9,20,'Sunday');\nINSERT INTO dim_customer VALUES\n(0,'__UNKNOWN__','Unknown customer','Unknown','Unknown'),\n(101,'C001','Ada Retail','SMB','North'),\n(102,'C002','Ben Home','Consumer','West'),\n(103,'C003','Cyra Labs','Enterprise','East'),\n(104,'C004','Dara Studio','Consumer','South');\nINSERT INTO dim_product VALUES\n(0,'__UNKNOWN__','Unknown product','Unknown'),\n(201,'P100','Keyboard','Accessories'),\n(202,'P200','Mouse','Accessories'),\n(203,'P300','Monitor','Displays'),\n(204,'P400','Dock','Accessories');\nINSERT INTO dim_store VALUES\n(0,'__UNKNOWN__','Unknown selling location','Unknown'),\n(301,'S-WEB','Web Store','Digital'),\n(302,'S-MOBILE','Mobile Store','Digital'),\n(303,'S-SALES','Assisted Sales','Assisted');\nINSERT INTO fact_sales VALUES\n(1,20260918,101,201,301,'O1001',1,2,50,100,'BATCH-CH03-001'),\n(2,20260918,101,202,301,'O1001',2,1,25,25,'BATCH-CH03-001'),\n(3,20260918,102,203,302,'O1002',1,1,200,200,'BATCH-CH03-001'),\n(4,20260919,101,204,301,'O1003',1,1,100,100,'BATCH-CH03-001'),\n(5,20260919,101,202,301,'O1003',2,2,25,50,'BATCH-CH03-001'),\n(6,20260920,104,201,302,'O1005',1,1,50,50,'BATCH-CH03-001'),\n(7,20260920,104,204,302,'O1005',2,1,100,100,'BATCH-CH03-001');")conn.executescript('-- Chapter 04 migration: preserve the Chapter 03 order-line grain.\nALTER TABLE fact_sales ADD COLUMN extended_cost NUMERIC;\nUPDATE fact_sales\nSET extended_cost = CASE product_key\n WHEN 201 THEN quantity * 30 -- P100 Keyboard synthetic cost-at-sale\n WHEN 202 THEN quantity * 10 -- P200 Mouse\n WHEN 203 THEN quantity * 140 -- P300 Monitor\n WHEN 204 THEN quantity * 60 -- P400 Dock\n ELSE 0\nEND;\n\n')conn.executescript("DROP TABLE IF EXISTS fact_inventory_snapshot;\nCREATE TABLE fact_inventory_snapshot (\n snapshot_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n on_hand_units INTEGER NOT NULL CHECK (on_hand_units >= 0),\n source_batch_id TEXT NOT NULL,\n PRIMARY KEY (snapshot_date_key, product_key)\n);\nINSERT INTO fact_inventory_snapshot VALUES\n(20260919,201,42,'INV-20260919'),\n(20260919,202,63,'INV-20260919'),\n(20260919,203,13,'INV-20260919'),\n(20260919,204,28,'INV-20260919'),\n(20260920,201,40,'INV-20260920'),\n(20260920,202,60,'INV-20260920'),\n(20260920,203,12,'INV-20260920'),\n(20260920,204,25,'INV-20260920');")conn.executescript('DROP TABLE IF EXISTS fact_channel_funnel_daily;\nCREATE TABLE fact_channel_funnel_daily (\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n sessions INTEGER NOT NULL CHECK (sessions >= 0),\n paid_orders INTEGER NOT NULL CHECK (paid_orders >= 0),\n PRIMARY KEY (date_key, store_key)\n);\n-- Synthetic demand-denominator fixture; paid-order numerators reconcile to fact_sales.\nINSERT INTO fact_channel_funnel_daily VALUES\n(20260918,301,60,1),\n(20260918,302,30,1),\n(20260919,301,40,1),\n(20260919,303,20,0),\n(20260920,302,20,1);')conn.executescript("DROP TABLE IF EXISTS fact_promotion_eligibility;\nDROP TABLE IF EXISTS dim_promotion;\nCREATE TABLE dim_promotion (\n promotion_key INTEGER PRIMARY KEY,\n promotion_id TEXT NOT NULL UNIQUE,\n promotion_name TEXT NOT NULL\n);\nCREATE TABLE fact_promotion_eligibility (\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n promotion_key INTEGER NOT NULL REFERENCES dim_promotion(promotion_key),\n source_batch_id TEXT NOT NULL,\n PRIMARY KEY (date_key, product_key, store_key, promotion_key)\n);\nINSERT INTO dim_promotion VALUES (401,'PROMO-ACCESSORY','Accessory Boost');\nINSERT INTO fact_promotion_eligibility VALUES\n(20260918,201,301,401,'PROMO-COVERAGE-001'),\n(20260918,202,301,401,'PROMO-COVERAGE-001'),\n(20260918,201,302,401,'PROMO-COVERAGE-001'),\n(20260919,204,301,401,'PROMO-COVERAGE-001'),\n(20260919,202,301,401,'PROMO-COVERAGE-001'),\n(20260920,201,302,401,'PROMO-COVERAGE-001'),\n(20260920,202,302,401,'PROMO-COVERAGE-001'),\n(20260920,204,302,401,'PROMO-COVERAGE-001');")# Additive sales controls.rows,orders,units,revenue,cost,profit=conn.execute("""SELECT COUNT(*),COUNT(DISTINCT order_id),SUM(quantity),SUM(extended_amount),SUM(extended_cost),SUM(extended_amount-extended_cost) FROM fact_sales""").fetchone()assert (rows,orders,units,revenue,cost,profit)==(7,4,9,625,380,245)assert math.isclose(profit/revenue,0.392)# Semi-additive inventory: prove wrong sum and governed ending balance.wrong_inventory=conn.execute("SELECT SUM(on_hand_units) FROM fact_inventory_snapshot").fetchone()[0]ending_inventory=conn.execute("""SELECT SUM(on_hand_units) FROM fact_inventory_snapshotWHERE snapshot_date_key=(SELECT MAX(snapshot_date_key) FROM fact_inventory_snapshot)""").fetchone()[0]assert wrong_inventory==283 and ending_inventory==137# Non-additive ratio/distinct controls.funnel=conn.execute("SELECT SUM(paid_orders),SUM(sessions) FROM fact_channel_funnel_daily").fetchone()assert funnel==(4,170)assert math.isclose(funnel[0]/funnel[1],4/170)assert conn.execute("SELECT COUNT(DISTINCT customer_key) FROM fact_sales").fetchone()[0]==3# Factless coverage controls.coverage=conn.execute("SELECT COUNT(*) FROM fact_promotion_eligibility").fetchone()[0]misses=conn.execute("""SELECT COUNT(*) FROM fact_promotion_eligibility e LEFT JOIN fact_sales sON s.date_key=e.date_key AND s.product_key=e.product_key AND s.store_key=e.store_keyWHERE s.sales_key IS NULL""").fetchone()[0]assert (coverage,misses)==(8,2)print('sales=',(rows,orders,units,revenue,cost,profit))print('inventory wrong/ending=',(wrong_inventory,ending_inventory))print('conversion=',funnel[0]/funnel[1],'distinct customers=3')print('promotion coverage/misses=',(coverage,misses))print('Chapter 04 acceptance tests: PASS')Expected evidence: all assertions pass. The test intentionally contains the wrong inventory sum as evidence of an operation that must not be published as a metric.
6. Production judgment: publish legal operations, not only formulas
Two teams can implement the same formula and still disagree if they use different grains, time windows, identity rules, or denominator populations. A production metric contract therefore needs semantic versioning, ownership, lineage, freshness expectations, access policy, tests, and deprecation rules. Chapter 20 will develop semantic layers more fully; Chapter 04 establishes the mathematical foundation they must preserve.
Chapter 05 now turns from fact-table mathematics to dimension design: descriptive context, business/natural versus surrogate identity, hierarchies, denormalization choices, and analytical usability.
Knowledge check
Check your understanding
- What fields are essential for auditing a metric beyond its name and formula?
- Why is DISTINCT not a universal repair for duplicated facts?
- What is the Chapter 04 ending inventory control?
- What components reproduce gross margin?
- What decomposition proves the promotion coverage fixture is internally consistent?
Review the answers
1. At minimum: grain, aggregation class, atomic components or identity rule, time behavior, ownership, lineage/source, and a testable control or invariant.
2. It operates on returned values/rows, not on business-event semantics; it can hide a bad join or remove legitimately repeated values.
3. 137 units at the September 20 snapshot.
4. Summed gross profit (or revenue minus cost) divided by summed revenue; the fixture gives 245 / 625 = 39.2%.
5. Eight eligible combinations decompose into six with matching paid-sale activity plus two with no sale.
Summary and next step
Chapter 04 now has explicit aggregation contracts: additive sales components, a semi-additive inventory balance, recomputable ratios/distinct counts, and factless promotion coverage. Each metric is tied to grain and deterministic evidence rather than a dashboard default.
Next: Chapter 05, Dimension Design: Descriptive Context, Hierarchies, Attributes, and Analytical Usability.
Authoritative references
- Kimball Group — Keep to the Grain in Dimensional Modeling — Why mixed grain undermines fact-table semantics.
- Kimball Group — Factless Fact Tables — Event and coverage patterns.
- Kimball Group — Additive, Semi-Additive, and Non-Additive Facts — Definitions and the guidance to retain additive components for derived non-additive facts.
- Kimball Group — Fact Table Structure — Fact-table grain and measurement-event framing.
- SQLite — Aggregate Functions — Exact behavior of SUM, AVG, COUNT, and DISTINCT in the local execution harness.
- Python sqlite3 documentation — Standard-library API used by the deterministic labs.