Pipeline to Dashboardnihalsulaiman.com

Week 5 · Model it in SQL › Day 29

Day 29. Dimensional modelling and grain

⏱ 4h · learn 90m · lab 115m · ship 20m · review 15m🏷 multi-brand restaurantsBefore: Week 4

By the end of today you can

  • Model a business process as facts and dimensions at a declared grain
  • Choose transaction, periodic snapshot, accumulating snapshot and factless facts
  • Build a bus matrix with conformed dimensions across sectors
  • Spot fan-out and double-counting before they reach a dashboard

1. Why this matters

Power BI, Metabase and every analyst's SQL get easier and faster on a good star schema, and wrong in subtle ways on a bad one. Dimensional modelling (Ralph Kimball's method) is still the core skill of a BI developer, and it's what interviewers test with "how would you model…?".

2. The four-step design

  1. Pick the business process: "customers order food", "guests book rooms", "we buy ads". Not a department, not a report.
  2. Declare the grain: one row = one order line / one booking night / one campaign-day. Say it in a sentence before choosing any column.
  3. Identify dimensions: the who/what/where/when that describe a row: date, restaurant, menu item, channel.
  4. Identify facts: the numbers measured at that grain: quantity, revenue, food cost.
            dim_date ─┐
     dim_restaurant ──┤
       dim_menu_item ─┼──► fct_order_lines (grain: one line of one order)
         dim_channel ─┘      qty, gross_revenue, discount, food_cost

3. Four kinds of fact table

Type Grain Example Additive over time?
Transaction One event Order line, ad click, booking created Yes
Periodic snapshot One entity per period Inventory per store per day; OTB per hotel per stay date as of each day No (semi-additive)
Accumulating snapshot One process instance, updated as it moves A booking: created → confirmed → arrived → departed, with dates for each milestone Mostly no
Factless An event with no measure Promotion was active for item X on day Y; a store was open Count rows

4. The bus matrix

Rows = business processes, columns = dimensions. A ✓ where they meet. Dimensions shared across rows (date, store/location, channel) are conformed: same keys and attributes everywhere, so you can compare across processes.

Process Date Location Product/Menu Channel Campaign Guest
Retail sales ✓ ✓ (store) ✓ (SKU)
QSR orders ✓ ✓ (restaurant) ✓ (menu item) ✓
Hotel bookings ✓ (created, arrival) ✓ (hotel) ✓ ✓
Ad performance ✓ ✓ ✓
Maths and principle

Principle: a measure can be summed along a dimension only if the dimension partitions the events without overlap. Otherwise it's semi- or non-additive.

Equation: additive: \(\sum_{d} m(d) = m(\text{all})\). A snapshot measure (balance, OTB) is additive across locations but not across time: total OTB for a week is not \(\sum_{\text{days}}\) OTB; use the last value, or an average.

Analogy: extensive vs intensive quantities in thermodynamics. Mass adds when you combine systems; temperature doesn't. Revenue is extensive; ADR is intensive; inventory is extensive in space but a state in time.

Try it: a store's stock on hand: Mon 100, Tue 80, Wed 120. "Stock for the 3 days"? (Not 300. Usually the last value, 120, or the average, 100, depending on the question.)

5. Fan-out: the silent double-count

Joining a fact to anything at a finer grain multiplies rows. Join order lines to a "promotions" table with two active promotions per item and revenue doubles. Guard: always know the grain on both sides of a join, and test sum(measure) before and after.

The 5-year lens

The grain sentence is the most valuable line in a model. Write it in the table's description and the dbt docs: "fct_order_lines: one row per line item on a POS order, after removing re-sent batches; business date uses the 05:00 cut-off." When a number is questioned, that line plus the tests is half the answer. And when someone asks for a column that doesn't fit the grain (order-level delivery fee on a line-level fact), the answer is a second fact table, not a hack that allocates or repeats it.

Lab

  1. For the restaurant group, write grain sentences for: order lines, orders (header level), daily restaurant sales (periodic), and menu price history.
  2. Draw the bus matrix for all four sectors in docs/bus-matrix.md.
  3. Design (on paper/Excalidraw) the star for QSR: fct_order_lines, fct_orders, dim_date, dim_restaurant, dim_brand (or brand as an attribute?), dim_menu_item, dim_channel. Decide and justify each.
  4. Write SQL in DuckDB that shows a fan-out: join order lines to a made-up promotions table with overlapping rows. Show revenue before and after. Then fix it.
  5. Design an accumulating snapshot for hotel bookings with milestone dates and lag measures (lead time, days from booking to cancellation).

Ship

New repo restaurant-dbt. Commit docs: grain statements, bus matrix, star designs.

Check yourself

1. What should you decide first when designing a fact table?

2. Hotel OTB room nights per stay date, as of each day, is which fact type?

3. Revenue doubles after adding a join. Most likely cause?

My notes