Build a Requirements-to-Model Traceability Matrix Linking Every KPI and Slice to Source and Grain

Learning outcomes

A requirements document is useful only if the eventual model can be traced back to it. AtlasMart therefore closes Chapter 02 with a requirements-to-model matrix that connects decisions and KPIs to business processes, grain, dimensions, source fields, formulas, freshness, control totals, and unresolved source gaps.

1. The traceability chain

For a certified metric, a reviewer should be able to follow:

Missing links are not paperwork defects; they are design risks. If a KPI has no authoritative event, or a slice cannot be known at the stated grain, the model should not pretend otherwise.

2. AtlasMart requirements-to-model matrix

3. Hands-on lab — execute the supported contracts and fail the unsupported one safely

Run the following script. It recreates the established Chapter 01 fixture in memory and validates all five requirements. Four produce reconciled query evidence; the fifth must remain blocked.

import sqlite3, sysprint("Python:", sys.version.split()[0])print("SQLite:", sqlite3.sqlite_version)con = sqlite3.connect(":memory:")con.executescript(r"""PRAGMA foreign_keys = ON;CREATE TABLE customers (  customer_id TEXT PRIMARY KEY,  customer_name TEXT NOT NULL,  segment TEXT NOT NULL,  region TEXT NOT NULL,  updated_at TEXT NOT NULL);CREATE TABLE products (  product_id TEXT PRIMARY KEY,  product_name TEXT NOT NULL,  category TEXT NOT NULL,  list_price NUMERIC NOT NULL,  updated_at TEXT NOT NULL);CREATE TABLE orders (  order_id TEXT PRIMARY KEY,  customer_id TEXT NOT NULL REFERENCES customers(customer_id),  order_ts TEXT NOT NULL,  status TEXT NOT NULL,  channel TEXT NOT NULL,  updated_at TEXT NOT NULL);CREATE TABLE order_lines (  order_id TEXT NOT NULL REFERENCES orders(order_id),  line_no INTEGER NOT NULL,  product_id TEXT NOT NULL REFERENCES products(product_id),  quantity INTEGER NOT NULL CHECK (quantity > 0),  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),  PRIMARY KEY (order_id, line_no));CREATE TABLE inventory (  product_id TEXT PRIMARY KEY REFERENCES products(product_id),  on_hand INTEGER NOT NULL CHECK (on_hand >= 0),  snapshot_ts TEXT NOT NULL);INSERT INTO customers VALUES('C001','Ada Retail','SMB','North','2026-09-20T08:00:00Z'),('C002','Ben Home','Consumer','West','2026-09-20T08:00:00Z'),('C003','Cyra Labs','Enterprise','East','2026-09-20T08:00:00Z'),('C004','Dara Studio','Consumer','South','2026-09-20T08:00:00Z');INSERT INTO products VALUES('P100','Keyboard','Accessories',50,'2026-09-20T08:00:00Z'),('P200','Mouse','Accessories',25,'2026-09-20T08:00:00Z'),('P300','Monitor','Displays',200,'2026-09-20T08:00:00Z'),('P400','Dock','Accessories',100,'2026-09-20T08:00:00Z');INSERT INTO orders VALUES('O1001','C001','2026-09-18T09:15:00Z','paid','web','2026-09-18T09:16:00Z'),('O1002','C002','2026-09-18T11:30:00Z','paid','mobile','2026-09-18T11:31:00Z'),('O1003','C001','2026-09-19T15:00:00Z','paid','web','2026-09-19T15:01:00Z'),('O1004','C003','2026-09-19T16:40:00Z','pending','sales','2026-09-19T16:40:00Z'),('O1005','C004','2026-09-20T07:10:00Z','paid','mobile','2026-09-20T07:11:00Z');INSERT INTO order_lines VALUES('O1001',1,'P100',2,50),('O1001',2,'P200',1,25),('O1002',1,'P300',1,200),('O1003',1,'P400',1,100),('O1003',2,'P200',2,25),('O1004',1,'P300',2,200),('O1005',1,'P100',1,50),('O1005',2,'P400',1,100);INSERT INTO inventory VALUES('P100',40,'2026-09-20T08:00:00Z'),('P200',60,'2026-09-20T08:00:00Z'),('P300',12,'2026-09-20T08:00:00Z'),('P400',25,'2026-09-20T08:00:00Z');""")# Q1: paid GMV and paid orders control totalsq1 = con.execute("""SELECT SUM(ol.quantity*ol.unit_price), COUNT(DISTINCT o.order_id)FROM orders o JOIN order_lines ol USING(order_id)WHERE o.status='paid'""").fetchone()assert q1 == (625, 4), q1print("Q1", q1)# Q2: paid orders by channelq2 = dict(con.execute("""SELECT channel, COUNT(*) FROM ordersWHERE status='paid' GROUP BY channel ORDER BY channel""").fetchall())assert q2 == {"mobile":2, "web":2}, q2print("Q2", q2)# Q3: paid units by product categoryq3 = dict(con.execute("""SELECT p.category, SUM(ol.quantity)FROM orders oJOIN order_lines ol USING(order_id)JOIN products p USING(product_id)WHERE o.status='paid'GROUP BY p.category ORDER BY p.category""").fetchall())assert q3 == {"Accessories":8, "Displays":1}, q3print("Q3", q3)# Q4: inventory snapshot by product categoryq4 = dict(con.execute("""SELECT p.category, SUM(i.on_hand)FROM inventory i JOIN products p USING(product_id)WHERE i.snapshot_ts='2026-09-20T08:00:00Z'GROUP BY p.category ORDER BY p.category""").fetchall())assert q4 == {"Accessories":125, "Displays":12}, q4assert sum(q4.values()) == 137print("Q4", q4, "total=", sum(q4.values()))# Q5: intentionally blocked — no shipment milestone/carrier evidence exists.available_tables = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}required = {"orders", "shipment_milestones"}missing = sorted(required - available_tables)assert missing == ["shipment_milestones"], missingprint("Q5 BLOCKED missing=", missing)

Expected evidence:

• Q1 (625, 4)
• Q2 {'mobile': 2, 'web': 2}
• Q3 {'Accessories': 8, 'Displays': 1}
• Q4 {'Accessories': 125, 'Displays': 12} total=137
• Q5 BLOCKED missing=['shipment_milestones']

Do not “fix” Q5 by creating an empty shipment table. The requirement needs an authoritative source contract, event identity, timestamp semantics, and data—not merely a table with the desired name.

Cleanup: the SQLite database is in-memory; delete only validate_traceability.py.

4. Why these five queries exercise different design surfaces

5. Controlled failure: a green dashboard with no lineage

A team can make every dashboard card “green” by hard-coding a value or by using a convenient but semantically wrong source column. Without traceability, later reviewers cannot tell whether a KPI came from an authoritative event, whether its grain matches the calculation, or which source change would invalidate it.

The repair is to make each metric and slice point to source evidence and a test. Traceability does not guarantee truth—business definitions can still be wrong—but it makes the assumptions inspectable, testable, and changeable.

6. Change impact: use traceability before model changes

If AtlasMart later introduces discounts, returns, payment settlement, multiple currencies, product category history, or location-level inventory, the matrix shows which requirements are affected. A change to the paid-GMV definition should version the metric contract and update reconciliation tests rather than silently changing existing history.

Likewise, onboarding shipment milestone evidence can move Q5 from blocked_source_gap to supported, but only after the source contract identifies event key, event time, carrier semantics, late corrections, freshness, and ownership.

7. Chapter 02 acceptance checklist

• Every candidate fact process is named in business language.
• Every candidate fact has one grain sentence.
• Every metric is true to its grain and has explicit aggregation semantics.
• Every requested slice maps to descriptive context that can be sourced at the relevant event time.
• Every KPI maps to source evidence and a control/reconciliation test.
• Known source gaps remain visible and are not replaced by invented fields.
• Operational metadata is separated from business semantics.
• The Chapter 01 control totals still reconcile: paid GMV 625, paid orders 4, AOV 156.25, paid units 9, on-hand 137.

8. Production judgment and bridge to Chapter 03

Requirements-to-model traceability is the handoff from discovery to dimensional design. It gives reviewers an evidence chain and gives engineers explicit acceptance tests. Keep the matrix versioned with semantic changes; do not let the physical warehouse become the only documentation of business meaning.

Chapter 03 can now introduce facts, dimensions, star schemas, keys, and query semantics without guessing the grain or inventing KPIs. The design target is AtlasMart’s order-capture and inventory requirements established here, plus explicit gaps that remain outside the model until authoritative source evidence exists.

Knowledge check

Summary and next step

Chapter 02 converts AtlasMart stakeholder needs into evidence-backed process, grain, field-role, and metric contracts. Four representative questions reconcile to the established source fixture; fulfillment remains correctly blocked by a source gap.

Next: use these contracts to design fact and dimension tables and evaluate star-schema query semantics.

Authoritative references

• Kimball Group — Four-Step Dimensional Design Process — Select business process, declare grain, identify dimensions, then identify facts.
• Kimball Group — Business Processes — Business processes are measurement-generating operational activities and define a design target.
• Kimball Group — Grain — Grain is the binding statement of what one fact row represents and must precede dimensions/facts.
• Kimball Group — Enterprise Data Warehouse Bus Matrix — Shows process/dimension planning and how detailed matrices can record grain and facts.
• Kimball Group — Design Tip #41 — Discusses expanding bus-matrix rows into more detailed process/fact/grain planning.
• Python documentation — sqlite3 — Runtime used for deterministic source-fixture reconciliation tests.