Chapter 02 · Requirements Engineering: Business Processes, Questions, Metrics, Dimensions, and Grain
Declare Grain in One Precise Sentence and Reject Mixed-Grain Facts
Declare one precise grain per AtlasMart fact candidate and prove with executable SQL why repeating order-level values on order-line rows corrupts totals.
Learning outcomes
Grain is the most important contract in a fact design: it states exactly what one row represents. AtlasMart can now describe order capture, inventory, and fulfillment as processes, but it must not choose measures or dimensions until each fact candidate has a precise row-level meaning.
Write grain as one precise business sentence independent of a list of columns or keys.
Prefer atomic grain when source reality supports it and explain when a separate summarized fact is appropriate.
Detect mixed-grain facts by testing whether every candidate measure and dimension is true for one row.
Demonstrate double counting caused by repeating an order-level measure on order-line rows.
Split incompatible grains into separate fact candidates and define how they can later be reconciled or compared.
The local verification scripts in this generated chapter were executed with Python 3.13.5 and SQLite 3.46.1. They use only stable standard-library/SQL features. Learners should still record the versions printed on their machine; the exercises prove semantic contracts on the synthetic fixture, not performance of a production warehouse.
1. Grain is a business sentence
For AtlasMart order capture, a precise candidate grain is:
One row represents one committed AtlasMart order line identified by (order_id, line_no), observed at the order event time from the ERP source.
This is stronger than “primary key = order_id + line_no” because it explains the business measurement event. Keys implement the grain; they do not define its meaning.
For the baseline inventory example, a candidate grain is: one row per product per inventory snapshot time. If location is introduced later, the grain changes explicitly to one row per product per location per snapshot time.
2. Grain comes before dimensions and facts
Once the order-line grain is fixed, candidate dimensions and measures can be challenged against it. Customer, product, order date, channel, quantity, and unit price are all knowable for an order line in the baseline fixture. A monthly customer revenue total is not true for one order line; it is an aggregate over many lines and therefore does not belong as a repeated fact on atomic rows.
This ordering prevents a common design inversion: choosing attractive columns first and retroactively writing a grain sentence that tries to justify them.
3. Controlled failure — repeat an order-level amount on each line
Suppose AtlasMart charges a flat shipping fee of 10 per paid order. The fee is true at order grain. If a pipeline joins it to every line and stores it on an order-line fact, orders with multiple lines repeat the same 10. A naive sum then overstates shipping.
4. Hands-on lab — prove the double count
Run the following local script. It recreates the Chapter 01 fixture in memory and adds one order-grain shipping amount per order.
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');CREATE TABLE order_shipping(order_id TEXT PRIMARY KEY, shipping_amount NUMERIC NOT NULL);INSERT INTO order_shipping VALUES('O1001',10),('O1002',10),('O1003',10),('O1004',10),('O1005',10);""")# WRONG: order-level shipping is repeated once for every line.wrong = con.execute("""SELECT SUM(ol.quantity*ol.unit_price) + SUM(s.shipping_amount)FROM orders oJOIN order_lines ol USING(order_id)JOIN order_shipping s USING(order_id)WHERE o.status='paid'""").fetchone()[0]# RIGHT: line amount and order-level shipping are aggregated at their own grains.line_value = con.execute("""SELECT SUM(ol.quantity*ol.unit_price)FROM orders o JOIN order_lines ol USING(order_id)WHERE o.status='paid'""").fetchone()[0]shipping = con.execute("""SELECT SUM(s.shipping_amount)FROM orders o JOIN order_shipping s USING(order_id)WHERE o.status='paid'""").fetchone()[0]print("wrong mixed-grain total:", wrong)print("line value:", line_value)print("order shipping:", shipping)print("correct combined total:", line_value + shipping)assert wrong == 695assert line_value == 625assert shipping == 40assert line_value + shipping == 665
Expected evidence is
wrong mixed-grain total: 695 versus the correct
665 = 625 paid line value + 40 shipping. The 30
overstatement is not a rounding or SQL bug; it is a grain
violation caused by repeating order-level shipping on multi-line
orders.
Change O1001 or O1003 to have an
additional line and observe that the wrong answer changes even
though the order-level shipping contract does not. That
sensitivity is a diagnostic signal for mixed grain.
Cleanup: the database is in memory; delete only the script.
5. Separate fact candidates for incompatible grains
| Fact candidate | Grain sentence | Examples of true facts | Do not store as repeated fact |
|---|---|---|---|
| Order line | One row per committed order line | quantity, unit_price, line_amount | order shipping total, monthly customer revenue |
| Order header / charge | One row per order for a defined order-level charge/state | shipping_amount, order-level discount if authoritative | line quantity |
| Inventory snapshot | One row per product per snapshot time | on_hand | sum of yesterday + today on_hand |
| Future shipment milestone | One row per shipment milestone event | elapsed interval derivations when evidence exists | current customer segment if historical lookup policy is undefined |
6. Grain tests for every candidate column
For every proposed fact or dimension, ask four questions: (1) is there exactly one value for this row’s measurement event, (2) is its timestamp meaning aligned with the event, (3) can a source or deterministic rule produce it, and (4) will normal aggregation preserve meaning? A “no” does not always ban the field, but it forces a redesign or explicit derivation policy.
Atomic grain is a strong default because it preserves flexible analysis. Summary fact tables can be valuable later for performance, but they should be separate structures with their own grain and reconciliation to atomic facts.
7. Production judgment and bridge
Do not approve a fact design until the grain sentence is concise enough that business and engineering reviewers agree what one row means. If a sentence needs “sometimes,” “depending on,” or several unrelated event types, the design is probably mixing grains.
With grain fixed, the next lesson can classify fields into measures, dimensions, descriptive attributes, degenerate dimensions, and operational metadata without letting data types dictate semantics.
Knowledge check
Check your understanding
- Why is a primary-key column list not sufficient as a grain declaration?
- What causes the 695 versus 665 discrepancy in the lab?
- Why is “monthly revenue” unsafe on every order-line row?
- When could a summary fact table still be appropriate?
- What is a practical warning sign that one fact table has mixed grain?
Review the answers
1. A key expresses uniqueness in an implementation; grain states the business measurement event that one row represents.
2. The 10 shipping amount is order-grain but is repeated for each order line, so multi-line paid orders contribute it more than once.
3. It describes many lines over a period, so repeating it on atomic rows causes unsafe aggregation and makes the value unrelated to the single-row event.
4. As a separate, explicitly summarized structure with its own grain, freshness, and reconciliation to atomic facts—often for performance or common access patterns.
5. Candidate facts or timestamps cannot be described as single-valued for every row, or totals change when unrelated detail rows are added.
Summary and next step
AtlasMart’s order-line and inventory grains are now explicit, and the lab proved why order-grain values cannot be repeated on line-grain rows. Grain is a binding semantic contract, not a documentation afterthought.
Next: classify fields according to analytical role rather than whether they happen to be numeric or textual.
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 — Declaring the Grain — Explains why grain must be declared early and why facts must remain true to the grain.
- Kimball Group — Keep to the Grain — Discusses mixed-granularity errors and atomic-level modeling.
- Python documentation — sqlite3 — Standard-library SQLite interface used for the deterministic mixed-grain demonstration.