Chapter 03 · Dimensional Modeling Foundations: Facts, Dimensions, Star Schemas, and Query Semantics
Model a Sales/Orders Process as a Star Schema and Validate Common BI Queries Against the Declared Grain
Validate the complete AtlasMart sales star with executable BI queries, control totals, orphan checks, and grain-aware distinct-order logic.
Learning outcomes
The Chapter 03 model is complete only when representative BI questions return the expected answers without violating the paid order-line grain. This lesson treats query correctness as executable acceptance criteria: raw fact controls, slice totals, distinct-order counts, orphan checks, and anti-double-counting tests must all agree.
Validate the canonical fact_sales population against the Chapter 02 controls.
Answer common date, segment, category, store, product, and order-level BI questions through dimensional joins.
Use COUNT(DISTINCT order_id) when the requested metric is order count at line grain.
Detect join multiplication and fact loss with explicit control-total assertions.
Package the star model with a repeatable acceptance test before proceeding to advanced fact-table design.
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. Re-state the acceptance contract before writing BI SQL
| Invariant | Expected value | Reason |
|---|---|---|
| fact_sales rows | 7 | One row per paid order line |
| distinct paid orders | 4 | O1001, O1002, O1003, O1005 |
| paid units | 9 | Sum of line quantities |
| paid GMV | 625 | Sum of line extended_amount |
| dimension orphans | 0 | Every fact FK resolves to one dimension row |
| pending O1004 rows in fact_sales | 0 | Pending orders are outside certified sales population |
These controls precede any dashboard result. If a query cannot reconcile to them where it should, the problem is semantic even if the SQL runs successfully.
2. Validate daily and segment slices
SELECT d.full_date, SUM(f.extended_amount) AS paid_gmv, SUM(f.quantity) AS paid_units, COUNT(DISTINCT f.order_id) AS paid_ordersFROM fact_sales f JOIN dim_date d ON d.date_key=f.date_keyGROUP BY d.full_date ORDER BY d.full_date;| Date | GMV | Units | Orders |
|---|---|---|---|
| 2026-09-18 | 325 | 4 | 2 |
| 2026-09-19 | 150 | 3 | 1 |
| 2026-09-20 | 150 | 2 | 1 |
SELECT c.segment, SUM(f.extended_amount) AS paid_gmv, SUM(f.quantity) AS paid_units, COUNT(DISTINCT f.order_id) AS paid_ordersFROM fact_sales f JOIN dim_customer c ON c.customer_key=f.customer_keyGROUP BY c.segment ORDER BY c.segment;| Segment | GMV | Units | Orders |
|---|---|---|---|
| Consumer | 350 | 3 | 2 |
| SMB | 275 | 6 | 2 |
3. Validate product/category and selling-location slices
SELECT p.category, SUM(f.extended_amount) AS paid_gmv, SUM(f.quantity) AS paid_unitsFROM fact_sales f JOIN dim_product p ON p.product_key=f.product_keyGROUP BY p.category ORDER BY p.category;| Category | GMV | Units |
|---|---|---|
| Accessories | 425 | 8 |
| Displays | 200 | 1 |
SELECT s.store_name, SUM(f.extended_amount) AS paid_gmv, COUNT(DISTINCT f.order_id) AS paid_ordersFROM fact_sales f JOIN dim_store s ON s.store_key=f.store_keyGROUP BY s.store_name ORDER BY s.store_name;| Selling location | GMV | Orders |
|---|---|---|
| Mobile Store | 350 | 2 |
| Web Store | 275 | 2 |
Assisted Sales has no paid fact rows because its
only Chapter 01 order is pending. A dimension may legitimately
contain members absent from a particular fact population.
4. Controlled failure: count fact rows as orders
At order-line grain, COUNT(*) answers “how many
fact rows?” It does not answer “how many orders?”
SELECT COUNT(*) AS wrong_order_count FROM fact_sales;-- 7 line rows, not 7 ordersSELECT COUNT(DISTINCT order_id) AS paid_order_count FROM fact_sales;-- 4 paid orders
The same grain mistake appears in averages.
AVG(extended_amount) is average line amount, not
average order value. The latter requires first aggregating each
order or dividing total paid GMV by the distinct-order count
according to the metric contract.
5. Hands-on lab — run the Chapter 03 acceptance suite
Save the canonical SQL and this validator in a disposable directory.
import sqlite3from pathlib import Pathconn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript(Path("atlasmart_star.sql").read_text())def one(sql): return conn.execute(sql).fetchone()[0]assert one("SELECT COUNT(*) FROM fact_sales") == 7assert one("SELECT COUNT(DISTINCT order_id) FROM fact_sales") == 4assert one("SELECT SUM(quantity) FROM fact_sales") == 9assert one("SELECT SUM(extended_amount) FROM fact_sales") == 625# Reconciliation after joining all dimensions: no loss or multiplication.joined=one("""SELECT SUM(f.extended_amount)FROM fact_sales fJOIN dim_date d ON d.date_key=f.date_keyJOIN dim_customer c ON c.customer_key=f.customer_keyJOIN dim_product p ON p.product_key=f.product_keyJOIN dim_store s ON s.store_key=f.store_key""")assert joined == 625# Orphan assertions.for dim,key in [('dim_date','date_key'),('dim_customer','customer_key'),('dim_product','product_key'),('dim_store','store_key')]: n=one(f"SELECT COUNT(*) FROM fact_sales f LEFT JOIN {dim} d ON d.{key}=f.{key} WHERE d.{key} IS NULL") assert n == 0, (dim,n)# Slice controls.assert conn.execute("""SELECT p.category,SUM(f.extended_amount),SUM(f.quantity)FROM fact_sales f JOIN dim_product p ON p.product_key=f.product_keyGROUP BY p.category ORDER BY p.category""").fetchall() == [('Accessories',425,8),('Displays',200,1)]assert conn.execute("""SELECT s.store_name,SUM(f.extended_amount)FROM fact_sales f JOIN dim_store s ON s.store_key=f.store_keyGROUP BY s.store_name ORDER BY s.store_name""").fetchall() == [('Mobile Store',350),('Web Store',275)]# Deliberate anti-test: line count must not equal order count.line_count=one("SELECT COUNT(*) FROM fact_sales")order_count=one("SELECT COUNT(DISTINCT order_id) FROM fact_sales")assert line_count != order_count and (line_count,order_count)==(7,4)print("Chapter 03 acceptance tests: PASS")print("rows/orders/units/gmv =",7,4,9,625)
Expected output is
Chapter 03 acceptance tests: PASS and the four
controls. Break the model deliberately by changing one fact's
product_key to another valid product key; the grand
total still passes but the category slice assertion fails. This
demonstrates why reconciliation needs both global controls and
dimensional slice tests.
Cleanup: remove the two disposable files.
6. A star schema does not eliminate semantic review
The SQL can be syntactically correct and still answer the wrong
question. Analysts must know that the fact population is paid
lines, that order count is distinct order_id, that
unit price is not additive, that store is the Chapter 03
selling-location mapping, and that historical dimension behavior
has not yet been introduced. Documentation and metadata should
expose those contracts to BI consumers.
This is why a dimensional model is not merely a faster join pattern. It is an interface for analytical meaning.
7. Production judgment and bridge
Before promoting a first star, validate event coverage, grain uniqueness, dimension lookup coverage, global controls, representative slices, source lineage, metric formulas, access sensitivity, and the failure behavior of missing context. Keep physical optimization separate so a later engine migration cannot silently redefine metrics.
Chapter 04 now deepens fact-table design: additive, semi-additive, and non-additive measures plus factless facts. The seven-line sales fact remains the baseline for those aggregation rules.
Knowledge check
Check your understanding
- Why is COUNT(*) wrong for paid_order_count in fact_sales?
- What are the expected Chapter 03 category totals?
- Why does the validation suite check both grand totals and slices?
- What does zero orphan count establish?
- What important dimension behavior has Chapter 03 not implemented yet?
Review the answers
1. Because the fact grain is order line, so COUNT(*) counts 7 lines; paid order count is 4 distinct order_id values.
2. Accessories = 425 GMV and 8 units; Displays = 200 GMV and 1 unit.
3. A wrong dimension key can preserve the grand total while moving value into the wrong slice; both levels are needed.
4. Every fact foreign key resolves to a matching dimension member for the tested dimensions.
5. Historical/slowly changing dimension versioning; Chapter 03 uses one current descriptive row per business key plus explicit Unknown members.
Summary and next step
AtlasMart's first star schema now passes executable acceptance criteria at its declared grain. Facts, dimensions, surrogate keys, unknown members, and joins preserve 625 paid GMV, 9 units, four paid orders, and the expected dimensional slices.
Next: classify measures by aggregation behavior and design fact tables that remain correct across time and other dimensions.
Authoritative references
- Kimball Group — Dimensional Modeling Techniques — Primary index for facts, dimensions, star schemas, grain, surrogate keys, and related dimensional techniques.
- Kimball Group — Fact Tables and Dimension Tables — Explains fact foreign keys, dimension primary keys, referential integrity, unknown members, and degenerate dimensions.
- Kimball Group — Dimension Surrogate Keys — Explains why warehouse-controlled surrogate keys decouple dimensions from operational natural keys.
- Kimball Group — Nulls in Fact Tables — Explains why fact foreign keys should resolve to explicit default dimension members instead of null.
- Kimball Group — Declaring the Grain — Reinforces that grain precedes dimensions and measured facts.
- Python documentation — sqlite3 — Standard-library execution harness used for deterministic local SQL verification.
- Kimball Group — 10 Essential Rules of Dimensional Modeling — Summarizes atomic detail, dimensions, surrogate keys, and conformance principles relevant to the validated star.