Chapter 03 · Dimensional Modeling Foundations: Facts, Dimensions, Star Schemas, and Query Semantics
Why Star Schemas Optimize Understandability and Analytical Access Patterns
Build AtlasMart’s first star schema at paid order-line grain and prove that dimensional joins preserve the established sales controls.
Learning outcomes
Chapter 02 ended with an explicit requirement contract: AtlasMart's certified sales population is the set of paid order lines, with 7 line events, 4 distinct paid orders, 9 units, and paid GMV 625. Chapter 03 now turns that semantic contract into a dimensional model. The model is not allowed to change the event population just because a table layout is convenient.
Explain why a star schema organizes one measurement process around a fact table and descriptive dimensions.
Declare the AtlasMart sales grain as one row per paid order line before selecting keys or measures.
Map order date, customer, product, and selling location into dimensions while retaining order_id as transaction identity.
Build and query a deterministic star schema without confusing logical semantics with engine-specific physical tuning.
Prove that dimensional joins preserve the Chapter 02 control totals.
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. The star is a semantic shape, not a drawing style
A star schema places a measurement table—the fact table—at the center and connects it directly to dimensions that describe the measurement's business context. For AtlasMart, the measurement event is a paid order line. The fact therefore stores keys identifying the date, customer, product, and selling location plus measurements such as quantity and extended amount.
The surrounding dimensions answer the who/what/where/when questions with human-readable attributes. They are not merely lookup tables added for normalization. Their job is to make analytical grouping/filtering stable and understandable while keeping the fact row true to one declared grain.
2. Freeze the grain before choosing the columns
The binding sentence for this chapter is:
One row in fact_sales represents one paid
AtlasMart order line identified by
(order_id, line_no) at the business event date.
Pending orders are outside this certified sales population.
This sentence determines what can appear safely on the row.
Quantity and line extended amount belong to the line.
Customer/product/store/date keys describe that line's context.
The parent order_id remains useful as a degenerate
transaction identifier for distinct-order counts and
drill-through, but an order-level total must not be copied onto
every line.
| Design question | Answer for Chapter 03 | Why it matters |
|---|---|---|
| Business process | Paid sales / order capture | Keeps pending O1004 out of the certified sales population |
| Atomic grain | One paid order line | Prevents header and line measures from sharing one row ambiguously |
| Dimensions | Date, customer, product, store/selling location | Defines the descriptive context of each line |
| Degenerate identity | order_id + line_no | Preserves source transaction lineage without inventing an order dimension |
| Measures | quantity, extended_amount; unit_price as a non-additive rate | Defines what may be aggregated and how |
3. Chapter 03 adds one governed reference-data contract
Chapters 01–02 exposed orders.channel values
web, mobile, and sales,
but they did not define a store master. Prompt 03 requires a
store dimension. Rather than silently pretending a store source
existed, this chapter introduces a tiny synthetic reference
contract: web → S-WEB, mobile →
S-MOBILE, and sales → S-SALES. These
members represent selling locations/channels, not physical
buildings.
This is an additive model contract. It does not change the seven paid line events or their values. A future real store source could replace the synthetic mapping through a controlled migration while preserving the warehouse surrogate keys exposed to facts.
4. Create the first AtlasMart star
Save the following as atlasmart_star.sql. The
dimension surrogate keys are warehouse-controlled integers. The
date dimension uses a stable calendar key. Key 0 is this lab's
explicit Unknown member policy; the numeric choice is
local convention, not a universal rule.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fact_sales;DROP TABLE IF EXISTS dim_store;DROP TABLE IF EXISTS dim_product;DROP TABLE IF EXISTS dim_customer;DROP TABLE IF EXISTS dim_date;CREATE TABLE dim_date ( date_key INTEGER PRIMARY KEY, full_date TEXT NOT NULL UNIQUE, calendar_year INTEGER NOT NULL, calendar_month INTEGER NOT NULL, day_of_month INTEGER NOT NULL, day_name TEXT NOT NULL);CREATE TABLE dim_customer ( customer_key INTEGER PRIMARY KEY, customer_id TEXT NOT NULL UNIQUE, customer_name TEXT NOT NULL, segment TEXT NOT NULL, region TEXT NOT NULL);CREATE TABLE dim_product ( product_key INTEGER PRIMARY KEY, product_id TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, category TEXT NOT NULL);CREATE TABLE dim_store ( store_key INTEGER PRIMARY KEY, store_id TEXT NOT NULL UNIQUE, store_name TEXT NOT NULL, store_type TEXT NOT NULL);CREATE TABLE fact_sales ( sales_key INTEGER PRIMARY KEY, date_key INTEGER NOT NULL REFERENCES dim_date(date_key), customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key), product_key INTEGER NOT NULL REFERENCES dim_product(product_key), store_key INTEGER NOT NULL REFERENCES dim_store(store_key), order_id TEXT NOT NULL, line_no INTEGER NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price NUMERIC NOT NULL CHECK (unit_price >= 0), extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0), source_batch_id TEXT NOT NULL, UNIQUE(order_id, line_no));INSERT INTO dim_date VALUES(20260918,'2026-09-18',2026,9,18,'Friday'),(20260919,'2026-09-19',2026,9,19,'Saturday'),(20260920,'2026-09-20',2026,9,20,'Sunday');INSERT INTO dim_customer VALUES(0,'__UNKNOWN__','Unknown customer','Unknown','Unknown'),(101,'C001','Ada Retail','SMB','North'),(102,'C002','Ben Home','Consumer','West'),(103,'C003','Cyra Labs','Enterprise','East'),(104,'C004','Dara Studio','Consumer','South');INSERT INTO dim_product VALUES(0,'__UNKNOWN__','Unknown product','Unknown'),(201,'P100','Keyboard','Accessories'),(202,'P200','Mouse','Accessories'),(203,'P300','Monitor','Displays'),(204,'P400','Dock','Accessories');INSERT INTO dim_store VALUES(0,'__UNKNOWN__','Unknown selling location','Unknown'),(301,'S-WEB','Web Store','Digital'),(302,'S-MOBILE','Mobile Store','Digital'),(303,'S-SALES','Assisted Sales','Assisted');INSERT INTO fact_sales VALUES(1,20260918,101,201,301,'O1001',1,2,50,100,'BATCH-CH03-001'),(2,20260918,101,202,301,'O1001',2,1,25,25,'BATCH-CH03-001'),(3,20260918,102,203,302,'O1002',1,1,200,200,'BATCH-CH03-001'),(4,20260919,101,204,301,'O1003',1,1,100,100,'BATCH-CH03-001'),(5,20260919,101,202,301,'O1003',2,2,25,50,'BATCH-CH03-001'),(6,20260920,104,201,302,'O1005',1,1,50,50,'BATCH-CH03-001'),(7,20260920,104,204,302,'O1005',2,1,100,100,'BATCH-CH03-001');5. Query the star by business language
A star query starts with the fact population and joins dimensions for descriptive context. It does not need to reconstruct the operational order/customer/product schema just to answer a category/segment question.
SELECT d.full_date, c.segment, SUM(f.extended_amount) AS paid_gmv, COUNT(DISTINCT f.order_id) AS paid_ordersFROM fact_sales AS fJOIN dim_date AS d ON d.date_key = f.date_keyJOIN dim_customer AS c ON c.customer_key = f.customer_keyGROUP BY d.full_date, c.segmentORDER BY d.full_date, c.segment;| Date | Segment | Paid GMV | Distinct paid orders |
|---|---|---|---|
| 2026-09-18 | Consumer | 200 | 1 |
| 2026-09-18 | SMB | 125 | 1 |
| 2026-09-19 | SMB | 150 | 1 |
| 2026-09-20 | Consumer | 150 | 1 |
The output is only meaningful because every fact row has the same grain. A dimension join adds context; it does not redefine what a fact row means.
6. Hands-on lab — prove the star preserves the control totals
Place atlasmart_star.sql and the following script
in an empty disposable directory, then run
python verify_star.py.
import sqlite3, sysfrom pathlib import Pathconn = sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys = ON")conn.executescript(Path("atlasmart_star.sql").read_text(encoding="utf-8"))row = conn.execute("""SELECT COUNT(*) AS fact_rows, COUNT(DISTINCT order_id) AS paid_orders, SUM(quantity) AS units, SUM(extended_amount) AS gmvFROM fact_sales""").fetchone()print("Python", sys.version.split()[0], "SQLite", sqlite3.sqlite_version)print("fact_rows, orders, units, gmv =", row)assert row == (7, 4, 9, 625)joined = conn.execute("""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""").fetchone()[0]assert joined == 625print("joined control total =", joined)
Expected evidence is (7, 4, 9, 625) followed by
joined control total 625. These checks prove the
fixture's semantic population and join coverage. They do not
prove production query latency, concurrency behavior,
compression, or cloud cost.
Cleanup: delete only the two lab files.
7. Controlled failure: design from operational tables
A common shortcut is to mirror orders,
order_lines, customers, and
products into an analytical database and call it a
warehouse. That preserves operational normalization but leaves
every BI author responsible for remembering status filters,
header/line cardinality, and customer/product join semantics.
Another shortcut flattens all descriptors into the fact and
calls the result a star; this repeats descriptive context on
every line and makes independent attribute maintenance harder.
The repair is not “always denormalize.” It is to preserve the atomic measurement grain, place reusable descriptive context in dimensions, keep transaction identity where it belongs, and then measure any physical performance decision separately.
8. Production judgment and bridge
A useful star schema should make common analytical questions easy to express without hiding the event population. It should also preserve source lineage, tolerate future history techniques, and give the loading process a deterministic way to resolve every dimension foreign key. Those are semantic properties; partitioning, indexes, clustering, and columnar layout come later and are engine-specific.
The next lesson examines fact and dimension roles more closely—especially keys, measures, descriptive width, and different growth patterns.
Knowledge check
Check your understanding
- What exactly does one AtlasMart fact_sales row represent?
- Why is order_id retained even though there is no dim_order in this chapter?
- Why is the Chapter 03 store mapping described as a new contract?
- What does the 625 joined total prove?
- Does a star schema by itself guarantee faster queries on every engine?
Review the answers
1. One paid order line identified by order_id and line_no at its order date.
2. It is a useful transaction identifier for grouping, distinct-order counts, and drill-through even though it has no separate descriptive dimension here.
3. Because earlier chapters only supplied channel values, not a store master; the mapping is explicitly added rather than retroactively invented.
4. It proves the canonical star joins do not lose or multiply the paid-sales amount in this fixture.
5. No. It improves semantic usability; physical performance depends on engine, data size, storage layout, statistics, workload, cache, and other factors that must be measured.
Summary and next step
The first dimensional model is now grounded in the same source evidence and grain contract as Chapter 02. The star organizes paid order-line measurements around reusable descriptive dimensions without changing the certified sales population.
Next: distinguish fact and dimension characteristics so table roles are chosen from semantics rather than data type or width.
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.