"Grain Before Glory" — Dimensional Modeling

Skim Card

One thing to remember: decide what one row means before anything else; most wrong numbers are grain bugs, and a declared grain gives you the test that catches them.

• Kimball’s order: business process, grain, dimensions, facts. The atomic grain (one basket line) answers coarser questions; a coarser table can’t answer finer ones.
• Never sum a coarser table’s measure after joining it to finer rows: Q3 discounts read £549,590.78 instead of £165,051.86.
• Date events by their own date: refunds as their own fact, dated by refund, move UK revenue by the board deck’s £42,511.91, to the penny.
• Stock on hand is semi-additive: summed across days it showed 97.2% availability; counted per store-SKU-day, 91.1%.
• Type 2 keeps history, Type 1 restates it, Type 6 gives both. Join by surrogate key; group history by the durable key.
• Look dimensions up as of the event: the promotion version in force explained all 391,079 discounts; “latest version” got 35,259 wrong.
• Facts arrive before their dimensions: placeholder rows, updated in place later, kept 248,451 rows an inner join would have dropped.

Use …	When …
Transaction fact	each event is a row: sale lines, refunds, shrink
Periodic snapshot fact	levels or balances per period: stock on hand per day
Accumulating snapshot fact	a process with milestones: PO line ordered, expected, delivered
SCD Type 1 / 2 / 6	corrections / history as it was / both views from one table

Verified against: DuckDB 2.0.0 (ASOF JOIN, excel extension) · docs: Kimball Group techniques, dbt 1.9+, SQLMesh, Databricks Lakeflow, Snowflake, Trino 484 · 28 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

The discount report Ingrid had pushed over had passed review. Q3 discounts on UK sterling sales: £549,590.78. Rohan rebuilt it from the till lines and got £165,051.86.

The report joined the marketing team’s basket table, one row per receipt with its “You saved” total, to the basket’s lines to find a category. Rohan tried a DISTINCT, then a GROUP BY before the join. Each gave a different number.

“You’re fixing the query,” Ingrid said. “The problem is that nobody wrote down what one row of each table means. A basket row and a line row can’t be added together, and SQL won’t stop you. Decide the grain first. Then the joins write themselves, and so do the tests.”

The Quick Map

Concepts, with use cases

Grain, and Kimball’s four steps

The grain is the exact meaning of one row: “one line on one POS receipt”, “one store, one product, one day”. Kimball’s dimensional design process has four steps, in order: select the business process, declare the grain, identify the dimensions, identify the facts. Anything that doesn’t fit the grain goes to another table, not squeezed in.

What breaks without it. A table that mixes grains (receipt totals on line rows, weekly targets on daily rows) answers one question right and the next one silently wrong.

Larkspur. The four steps for selling:

The atomic grain is deliberate: lines group up into baskets, but baskets never split into categories.

Where else you’ll see this. A mobile network keeps one row per call or data session, not per subscriber-day, because fraud, roaming and network teams slice the records differently.

Facts, dimensions and the star

Facts measure a business event: quantities, amounts. Dimensions are the context you filter and group by: when, where, what, who. A star schema puts a fact table in the middle with a key to each dimension. Dimensions are denormalised: the product row carries category and subcategory as text, not keys into more tables (a snowflake schema, which adds joins and saves little).

What breaks without it. Analysts query the OLTP shape (orders, lines, products, price lists, promotion versions) and each reinvents the joins slightly differently. Larkspur: finance, merchandising and store operations query one fct_sales_line with the same dimensions. Elsewhere: an airline’s revenue warehouse has a ticket-coupon fact surrounded by flight, airport, fare class, passenger and date dimensions.

Additive, semi-additive, non-additive

An additive measure can be summed across every dimension (units, amounts). A semi-additive one can be summed across some dimensions but not time: stock on hand, account balances, patients in beds. Kimball’s own example is the balance, “additive across all dimensions except time”. A non-additive measure can’t be summed at all: unit prices, percentages, ratios. Store their additive ingredients and compute the ratio last.

What breaks without it. Summing a balance across days hides stock-outs: the stores dashboard did (Hands-On step 4).

Larkspur. on_hand_qty sums across stores and products on one day; across days, average it or take the closing value. Elsewhere: a bank’s average daily balance is meaningful; the sum of a month’s daily balances is not. A conversion rate is non-additive: average the daily rates and you weight a quiet Tuesday like Black Friday.

Four kinds of fact table

Only the accumulating snapshot updates rows; it answers lag questions (“which PO lines are late, by how much?”). A factless coverage table answers “what was promoted but didn’t sell”, since what didn’t happen leaves no sales row.

A header/line pair needs care. fct_basket, one row per receipt, is right for basket questions (average spend, items per basket). Kimball’s guidance is to carry header-level dimensions down to the line fact and, when a header measure must be sliced by line attributes, to allocate it to lines by a business rule.

Conformed dimensions and the bus matrix

A conformed dimension means the same thing, with the same keys and attribute values, in every fact table that uses it. Then you can drill across: compute units sold and shrink per category from their own facts, separately, and line the results up. Never join two fact tables row to row.

The bus matrix is the planning grid: business processes as rows, dimensions as columns. Larkspur’s, as a design table (sql/part04/bus_matrix.sql derives the same marks from the star’s key columns):

Refunds have no product column: most refund a whole basket, so the grain is the refund, not a line.

Larkspur. One dim_product for sales, stock, shrink and purchasing: “Dairy & Chilled” is the same SKUs in every report. Elsewhere: a hospital group conforms ward and date across admissions, bed census and theatre use (the Design Drill).

Keys: surrogate, natural, degenerate, junk, role-playing

• Natural key: the source’s ID (sku). Surrogate key: an integer the warehouse assigns (product_key). Two versions of a product need two rows, so the natural key can’t be the primary key; surrogates also survive a source reformatting its IDs.
• Degenerate dimension: an identifier with no attributes worth a table, kept on the fact: basket number, order number, PO number.
• Junk dimension: low-cardinality flags in one small table. dim_sale_profile holds the 14 combinations of channel, tender type and loyalty card that occur.
• Role-playing dimension: one dimension used several times in one fact under different names. A refund has a refund date and an original purchase date, both pointing at dim_date.
• Bridge table: sits between a fact and a dimension when one fact row has many dimension values (several diagnoses per hospital stay), with a weighting column if measures must be split.

Elsewhere: a logistics company’s shipment fact has pickup, promised and delivered dates (role-playing) and the waybill number as a degenerate dimension.

Slowly changing dimensions

When an attribute changes, Kimball names eight responses:

Type 2 is the workhorse: each version row carries valid_from, valid_to and is_current, and each fact row points at the version valid when the event happened. Type 6 copies the current values onto every version row, so one table answers “by category as sold” and “by today’s categories”.

Larkspur. On 1 August, 56 products moved category. Merchandisers judging the old range want sales “as sold”; planners want history restated to the new categories; Type 6 serves both. Elsewhere: an insurer prices claims by the policyholder’s address at the time (Type 2) but fixes a misspelt street name in place (Type 1).

Early-arriving facts, late-arriving dimensions

Sometimes the fact arrives first: Larkspur sells products on launch day, but the catalogue lists them from the next day’s extract. Kimball’s technique: a placeholder dimension row with the natural key and default attributes takes the facts, and is updated with a Type 1 overwrite when the attributes arrive; no fact row changes. A retroactive Type 2 change (a version effective in the past) is harder: its facts must be restated.

What breaks without it. An inner join drops the facts: revenue reconciles in the raw zone and not in the warehouse, with no error. Elsewhere: a card network sees transactions for a merchant terminal hours before the onboarding feed registers the merchant.

The audit dimension

An audit dimension holds the load metadata known when a row was written: which run, which sources, how many rows were loaded, rejected or dropped as duplicates. Every fact row carries an audit_key, so “which load made this row?” is one join away; Part 21’s control tables grow from it.

What breaks without it. A questioned number can’t be traced to its file or run, and silently dropped rows stay invisible. Larkspur: the purchase-order fact’s audit row counts the supplier rows it rejected (step 7). Elsewhere: a card-scheme clearing house stamps each settlement fact row with the file and batch that produced it, so a disputed total traces to one file.

Under the Hood

A join across grains multiplies

Joining one row per basket to its lines makes the discount report compute

$\text{reported} = \sum_\text{baskets} \text{discount}_\text{basket} \times \text{lines}_\text{basket}$

and DISTINCT can’t undo it: the rows aren’t duplicates, they’re different lines carrying the same number.

Type 2 from daily extracts: gaps and islands

The catalogue arrives as a full extract every day: 243,784 rows over the window (step 5 counts them). Turning them into versions is a classic window-function pattern (sql/part04/dimensions.sql):

1. Hash the tracked attributes for each SKU and day.
2. Flag a day whose hash differs from the previous day’s (lag).
3. A running sum of the flags numbers the versions.
4. One row per SKU and version: valid_from is its first day, valid_to the next version’s valid_from.

The result is 2,056 rows: 2,000 products plus one extra row for each of the 56 that moved. Intervals are half-open (Tips & Gotchas).

Finding the key: range joins and as-of joins

A fact row finds its product version with a range condition, business_date >= valid_from AND business_date < valid_to; products change at day boundaries. A promotion edited after launch changes at an instant, so the fact needs the latest version whose version_from is at or before the sale: an as-of join, ASOF JOIN in DuckDB:

-- sql/part04/facts.sql (excerpt)
ASOF LEFT JOIN dim_promotion pr ON pr.promo_id = l.promo_id AND l.event_ts >= pr.version_from

Prices change mid-day too (389 changes at 13:00 local), so the price the till charged stays on the fact (step 6).

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-4). No containers needed: DuckDB builds the star into data/lab/part04/larkspur_star.duckdb from data/raw (drop --local to read the bucket, with make up-core). Iceberg versions come in Part 7. The build peaked at 5.7 GiB resident on the host. Outputs drop DuckDB’s column-type and border lines.

Step 1: build the star

scripts/part04_star.py build runs sql/part04/: staging, dimensions, placeholders, facts, the catalogue catching up, the delivery milestone.

uv run python scripts/part04_star.py build --local

The output, trimmed (progress lines, the “before” and PO-status tables, and most row counts left out):

== early-arriving facts: rows naming a product the catalogue doesn't list yet
│       source        │ fact_rows │ skus  │ first_day  │  last_day  │
│ purchase order line │       869 │   133 │ 2026-06-01 │ 2026-09-21 │
│ sale line           │      3629 │   131 │ 2026-06-13 │ 2026-09-23 │
│ stock adjustment    │       887 │    88 │ 2026-07-01 │ 2026-09-18 │
│ stock snapshot      │    243066 │   102 │ 2026-07-01 │ 2026-09-23 │

== SKU-00273 after (placeholder updated in place, Type 1)
│ product_key │    sku    │ version │ category  │ subcategory │ valid_from │  valid_to  │ is_inferred │
│        2074 │ SKU-00273 │       0 │ Beverages │ Juice       │ 2026-06-01 │ 2026-07-17 │ true        │
│         279 │ SKU-00273 │       1 │ Beverages │ Juice       │ 2026-07-17 │ 9999-12-31 │ false       │

== the star (7.7s, 378 MiB)
│ fct_sales_line       │  6658738 │
│ fct_stock_daily      │ 11659200 │

Early-arriving facts come from four sources: 3,629 sale lines sold on a launch day (the manifest’s 3,391 counts POS lines only, by UTC date), stock held before launch, and purchase orders weeks ahead of it. SKU-00273 first appears in the 17 July extract; its placeholder, key 2074, carried every earlier fact as “(not yet in catalogue)” and, after the update above, reads Beverages / Juice with no fact row changed. Without placeholders the inner joins in facts.sql would have dropped all 248,451 rows.

The delivery update filled in 23,387 of 24,657 purchase-order rows; the other 1,270 are missing deliveries, deliveries after 4 October, and malformed supplier rows (step 7).

Step 2: the discount bug, reproduced

sql/part04/discount_grain_bug.sql runs the old report’s join beside the line-grain query (Q3, GBP sales):

WITH report AS (
    SELECT p.category, sum(b.discount_minor) AS discount_minor
    FROM fct_basket b
    JOIN fct_sales_line l ON l.transaction_id = b.transaction_id   -- one basket row becomes n line rows
    JOIN dim_product p ON p.product_key = l.product_key
    JOIN dim_date d ON d.date_key = b.date_key
    WHERE d.quarter = '2026-Q3' AND b.currency = 'GBP'
    GROUP BY p.category
),
line_grain AS (...)   -- the same, summing l.discount_minor from fct_sales_line alone

Queries run with the same script (five of ten categories trimmed):

uv run python scripts/part04_star.py query --local sql/part04/discount_grain_bug.sql

│    category     │  report_gbp   │ line_grain_gbp │ overstated_x │
│ Personal Care   │      89739.03 │       58571.22 │         1.53 │
│ Beverages       │      84125.65 │       34866.35 │         2.41 │
│ ...five more categories...                                   │
│ Electronics     │      36858.05 │        3233.32 │         11.4 │
│ Pantry          │      57195.86 │        1423.11 │        40.19 │
│ Dairy & Chilled │      31179.60 │         642.25 │        48.55 │

│  report_gbp   │ line_grain_gbp │ receipts_gbp  │
│     549590.78 │      165051.86 │     165051.86 │

The total is 3.3 times too high, and categories are worse: Dairy & Chilled gave £642.25 of discounts and the report credits it with £31,179.60, borrowed from other lines in the same baskets. The receipts table alone is right (£165,051.86); it goes wrong only when joined below its grain.

Step 3: refunds dated by the refund

The board deck put each refund in the quarter of the purchase it refunded. fct_refund has its own grain (one refund) and its own date, with the purchase date as a second role of dim_date. sql/part04/refund_dating.sql joins dim_date twice, once per role, and compares the two datings for UK refunds, with euros at the report-day rate as the board deck converted them (Part 5 fixes that):

│            dates             │ by_purchase_date │ by_refund_date │ board_deck_overstated_by │
│ UTC dates (waterfall step 3) │        609136.66 │      651648.57 │                 42511.91 │
│ London business dates        │        609194.88 │      651168.64 │                 41973.76 │

The first row is the incident’s step 3 to the penny, 0.1427% of the correct UK figure: dated by purchase, July refunds of June purchases vanish from Q3, and fewer early-October refunds of September purchases are pulled in. The waterfall still cuts the quarter at UTC midnight at step 3; the star adopts London business dates now, and Part 5 explains why (DST and the cut-off). The £538.15 between the rows is only the refund side of that fix; the manifest’s step 5 (−£298.10) nets sales and refunds, which Part 5 reconciles.

Step 4: stock is a balance

sql/part04/stock_weekly.sql first shows one store-SKU-week from the periodic snapshot, then the same week aggregated four ways:

│    date    │ day_name  │ on_hand_qty │
│ 2026-08-10 │ Monday    │           5 │
│ 2026-08-11 │ Tuesday   │           0 │
│ 2026-08-12 │ Wednesday │           0 │
│ 2026-08-13 │ Thursday  │           0 │
│ 2026-08-14 │ Friday    │          11 │
│ 2026-08-15 │ Saturday  │          10 │
│ 2026-08-16 │ Sunday    │           8 │

│ sum_over_week │ avg_daily_on_hand │ closing_on_hand │ days_in_stock │
│            34 │               4.9 │               8 │ 4/7           │

Store GB-0126 had no SKU-01684 from Tuesday to Thursday, yet the dashboard’s “sum above zero” rule calls the week fully available. Across Q3:

│ dashboard_weekly_sum_pct │ store_sku_days_in_stock_pct │
│                     97.2 │                        91.1 │

Counting store-SKU-days fixes the semi-additive half of the stock incident. Part 0’s 88.5% needs a time fix, Part 5’s: 216 stores snapshot at 06:00, after the overnight delivery, showing stock that wasn’t on the shelf at closing. The query groups weeks by sku, not product_key (Tips & Gotchas).

Step 5: as sold, or as today

sql/part04/category_as_of.sql counts what the dimension is made of, shows one moved product, then compares units sold per category from the Type 2 column and the Type 1 current_category column (six of ten rows trimmed):

│ extract_rows │ type_2_rows │ placeholders │ products_moved │
│       243784 │        2056 │          133 │             56 │

│ product_key │    sku    │    category     │ current_category │ valid_from │  valid_to  │
│          92 │ SKU-00089 │ Pantry          │ Dairy & Chilled  │ 2026-06-01 │ 2026-08-01 │
│          93 │ SKU-00089 │ Dairy & Chilled │ Dairy & Chilled  │ 2026-08-01 │ 9999-12-31 │

│    category     │ units_as_sold │ units_restated │ difference │
│ Personal Care   │        666322 │         607383 │     -58939 │
│ Bakery          │        412298 │         359906 │     -52392 │
│ ...six smaller differences...                            │
│ Fresh Produce   │        783885 │         817915 │      34030 │
│ Dairy & Chilled │        669467 │         722214 │      52747 │

Both columns are right; they answer different questions. 56 of 2,000 products moved, shifting up to 13% of a category’s units (Bakery), and the differences sum to zero (a test checks it). A report that doesn’t say which one it shows is the next incident.

Step 6: as-of promotions and prices

Fifteen promotions were edited after launch. sql/part04/promotion_as_of.sql asks of every promoted sale line whether the discount implied by “the promotion” equals what the till charged (its first statement, listing one promotion’s versions, is left out):

│              join_on              │  rows  │ rows_matching_till │
│ promo_id alone                    │ 494499 │             391079 │
│ promo_key, version as of the sale │ 391079 │             391079 │
│ promo_id, latest version          │ 391079 │             355820 │

The as-of version explains all 391,079 discounts; “the latest version” gets 35,259 wrong, every line sold before an edit. Joining on the natural key alone repeats step 2’s bug: 103,420 extra rows, one per extra version.

sql/part04/price_as_of.sql does the same for prices from the raw price history (euro lines excluded; they have their own price list):

│           price_list           │  lines  │ wrong_price │
│ price at the time of sale      │ 6640473 │           0 │
│ daily price, at local midnight │ 6640473 │        1694 │

A daily price list gives 1,694 lines the morning’s price.

Step 7: the accumulating snapshot, and its audit row

sql/part04/supplier_fill.sql computes Q3 supplier fill rate by expected date from fct_po_line, and reads the audit dimension’s row for that load:

│ fill_rate_pct │ on_time_in_full_pct │ lines_not_delivered │
│          93.1 │                77.7 │                 782 │

│ table_name  │                      sources                      │ rows_loaded │ rows_rejected │ rows_duplicate │
│ fct_po_line │ procurement/purchase_orders, suppliers/deliveries │       24657 │            83 │            119 │

The generator’s clean truth is 93.4% and 78.0% (docs/injected-defects.md). The feed is that clean data plus injected defects, so the rejected rows, which leave delivered lines looking undelivered, are the whole difference. The 83 are the manifest’s 81 malformed rows plus 2 re-sent copies of them; the other 119 of its 121 duplicates were valid repeats, dropped. Part 15 ingests these files properly, with quarantine.

Step 8: the tests

tests/test_part04.py builds the star from the small generated world and checks every claim above against its manifest: unique grains, no fact row lost, the grain bug’s exact size, step 3, both stock figures, moved products, as-of joins, audit counts. Converting euros as of each sale (Part 5’s fix), fct_basket and fct_refund also give exactly the correct UK figure, so nothing was lost or doubled on the way in. The last test rebuilds from data/raw and checks this post’s figures, including Part 0’s £29,790,674.06.

uv run pytest -q tests/test_part04.py

...........                                                              [100%]
11 passed in 17.76s

The Landscape

Product names as of 28 October 2026. Cells that couldn’t be verified say so.

Tips & Gotchas

Gotcha — join a Type 2 dimension on its surrogate key, or on its natural key plus a date. On the natural key alone, every fact row matches every version (step 6).

Gotcha — group history by the durable key. Weekly stock or “first purchase” queries must group by sku or customer_id, not the surrogate key, or each version change splits one history in two.

Gotcha — inner joins drop early-arriving facts silently. Give every fact a key to point at (a placeholder or an “unknown” row) and assert row counts match the source after every load.

Gotcha — BI tools’ default aggregation is SUM. Set it per measure: never SUM a balance across days or AVG a ratio; aggregate the ingredients and divide last.

Tip — write the grain as the first comment of every fact table, and test it. “One row per basket line” becomes a uniqueness test on (transaction_id, line_no). Larkspur’s facts.sql does this for every table, and the tests check the keys.

Tip — half-open validity intervals. valid_from inclusive, valid_to exclusive, a far-future date for the open row: no gaps, no overlaps, and the same predicate works for dates and timestamps.

Troubleshooting

Design Drill

The question: “A hospital network runs 40 hospitals. The board wants a reporting schema for bed occupancy, patient admissions and length of stay, by hospital, ward, specialty and day, comparable across hospitals and over five years. Patient administration systems differ by hospital, and wards are reconfigured a few times a year. Design it.”

Clarify first

• What is “occupied”: the midnight census, any use during the day, or bed-hours? Against staffed or physical beds?
• When does a stay start and end, and does a transfer between wards or hospitals start a new one?
• How late do records arrive or change (coding days later, unidentified emergency patients registered after admission)?
• Which attributes are reported as they were (ward specialty, bed count), which restated, and who may see patient-level rows?

The shape of a strong answer

• Processes and grains first: admissions and transfers (transaction fact, one row per movement), bed census (periodic snapshot, one row per ward per day at a defined census time), inpatient stay (accumulating snapshot, one row per stay with admit, transfer and discharge dates).
• Occupancy is semi-additive: occupied beds add across wards on one day; across days the sum is bed-days, not patients. Store occupied and available bed-days and compute occupancy % last; “patients this month” is never a sum of daily censuses.
• Length of stay: additive bed-days on the stay fact plus open/closed status; average LOS = bed-days ÷ closed stays, never an average of hospital averages.
• Conformed dimensions: date (admit, discharge, census roles), hospital, ward, specialty, clinician, patient (pseudonymised; identity in a restricted table), diagnosis through a bridge table.
• Ward as Type 2, so last year’s occupancy uses last year’s capacity; hospital groupings as Type 6 if the board compares old and new structures.
• An unidentified emergency patient gets a placeholder patient row, updated in place at registration; late coding updates the stay row.
• An audit dimension on every fact: which hospital’s feed and load made each row, and what it rejected.

Trade-offs to say out loud

• A midnight census is standard but misses day cases; bed-hours from movements is accurate but heavier.
• An accumulating snapshot updates rows (harder on immutable storage); deriving stays from movements on every run is simpler to load, slower to query.

Common weak answers

• One wide “encounters” table at no declared grain, with monthly patient counts made by summing daily censuses.
• Averaging per-hospital LOS or occupancy percentages to get a network figure.
• Last year’s occupancy against today’s bed counts.

Further Reading

1. Kimball Group, “Dimensional Modeling Techniques” (every technique named in this part). https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
2. Ralph Kimball and Margy Ross, The Data Warehouse Toolkit, 3rd edition (Wiley, 2013).
3. Kimball Group, Design Tip #152, “Slowly Changing Dimension Types 0, 4, 5, 6 and 7”. https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
4. Kimball Group, Design Tip #164, “Have You Built Your Audit Dimension Yet?”. https://www.kimballgroup.com/2014/03/design-tip-164-built-your-audit-dimension/
5. DuckDB documentation, “As-Of Joins”. https://duckdb.org/docs/2.0/sql/query_syntax/from.html

Story Closing

Rohan’s change list was short. The discount report reads fct_sales_line. Refunds have their own fact and date, moving the UK figure by £42,511.91, one of the five revenue defects fixed. The stores dashboard counts store-SKU-days, and 97.2% became 91.1%. Every fact table declares its grain and the tests check it: of the five promises, this part kept correctness.

Farah’s question came the next morning: “Whose midnight cuts the quarter? And which rate converts the euro orders?” Ingrid pointed at the £538.15 between the refund query’s two rows, and at 216 stores counting stock at six in the morning.

In Part 5, Rohan meets other ways to model the same data (Data Vault, wide tables, activity schemas) and the primitives under all of them: money, time zones, business dates and FX as of the event, which take stock to 88.5% and close two more revenue defects.

This is Part 4 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.