❄️ Warehouse & Modeling · Chapter 2

Dimensional modelling: facts and dimensions

Every analytics question is a number sliced by a noun. Facts hold the numbers, dimensions hold the nouns — get that split right and every dashboard is one join away.

Study this lesson free in the app →

The basics

Dimensional modelling is a way of laying out warehouse tables so that business questions become simple queries: a central table of measurements (the fact table) surrounded by tables of descriptive context (the dimensions).

Why it exists: the app database for Meghalaya Cabs is built to record rides quickly — dozens of normalised tables, each row updated in place. An analyst asking "revenue by city by week for premium drivers" would need eight joins and would hammer the production database. Dimensional modelling reshapes the same data for reading: few joins, predictable shapes, history kept.

The mental model: a sentence. "How much fare (fact) did we earn, by which driver, in which city, on which day (dimensions)?" The verbs and numbers become facts; the who/what/where/when become dimensions.

Explained

Meghalaya Cabs' ops head asks: "Average fare per km for premium drivers in Shillong, weekends only, last quarter."

On the app database that is 8 joins across rides, ride_status_log, drivers, driver_tiers, vehicles, zones, cities, payments — and a date calculation nobody agrees on.

On a dimensional model it is one fact table and three dimensions, and every analyst writes it the same way.

The shop receipt

Look at a supermarket receipt. Each line is a fact: 2 × milk, ₹54 each, ₹108.

Everything else on it is context: which store, which till, which cashier, what time, which loyalty card. Nobody adds up cashier names. Everybody adds up rupees.

Now imagine the shop's head office. They keep one huge ledger of lines (numbers + codes) and small reference books: store book, product book, calendar. To answer "dairy sales in Guwahati stores on Sundays", they flip to the codes in the reference books and add up the ledger.

That ledger is the fact table. The reference books are dimensions.

Facts and their measures, precisely

A fact table row = one occurrence of a business process at a declared grain. It holds:

• Foreign keys to each dimension (driver_key, rider_key, pickup_date_key, city_key)
• Degenerate dimensions — ids with no attributes worth a table (ride_id)
• Measures, classified by how they aggregate:

• Additive — sum across every dimension: fare_inr, distance_km
• Semi-additive — sum across some, not time: a driver's wallet balance. Summing Monday + Tuesday balances is nonsense; use the last value or an average
• Non-additive — never sum: surge_multiplier, fare_per_km. Store the parts (fare_inr, distance_km) and divide at query time

Three fact shapes:

• Transaction — one row per event (per ride). Most common
• Periodic snapshot — one row per entity per period (driver per day: rides, earnings, hours online)
• Accumulating snapshot — one row per process instance, updated as milestones happen (requested → accepted → picked up → dropped → paid)

Dimensions, precisely

A dimension row describes one thing the business talks about. dim_driver: name, tier, vehicle type, joining date, home city, rating band.

• Surrogate key — driver_key INT, generated by the warehouse. The source id (driver_id = 'DRV-0042') is kept as the natural key but never used for joins. Why: if Ramesh moves from Standard to Premium you may want two rows for him (history — topic SCD), and two rows cannot share one id
• Wide and denormalised — put city_name, state, region right on the driver row. Disk is cheap; joins are not
• Conformed — the same dim_city is used by fact_rides, fact_payments, fact_complaints, with identical keys and labels. That is what makes drill-across (rides vs complaints by city) possible
• Role-playing — one dim_date joined twice to the same fact as request_date_key and payment_date_key
• Junk dimension — bundle low-cardinality flags (is_airport, payment_mode, is_shared) into one small table instead of five tiny ones

Meghalaya Cabs, ride process

              dim_date (role: pickup_date)
                      │
 dim_rider ──┐        │        ┌── dim_driver
             │        │        │   driver_key PK
             ▼        ▼        ▼   driver_id (natural)
        ┌──────────────────────────┐ tier, vehicle,
        │        fact_rides        │ home_city …
        │ ride_id     (degenerate) │
        │ rider_key   FK           │
        │ driver_key  FK           │
        │ pickup_date_key FK       │
        │ pickup_zone_key FK       │
        │ ride_flags_key  FK (junk)│
        │ fare_inr      additive   │
        │ distance_km   additive   │
        │ wait_seconds  additive   │
        └──────────────────────────┘
             ▲               ▲
        dim_zone        dim_ride_flags
        zone, city,     payment_mode,
        state           is_airport, is_shared

One row in the middle = one completed ride. Every arrow is an integer join.

The question becomes boring SQL

-- Avg fare per km, premium drivers, Shillong, weekends, Q3 2026
SELECT  d.vehicle_type,
        SUM(f.fare_inr) / NULLIF(SUM(f.distance_km), 0) AS fare_per_km
FROM    fact_rides f
JOIN    dim_driver d  ON d.driver_key = f.driver_key
JOIN    dim_zone   z  ON z.zone_key   = f.pickup_zone_key
JOIN    dim_date   dt ON dt.date_key  = f.pickup_date_key
WHERE   d.tier         = 'Premium'
  AND   z.city         = 'Shillong'
  AND   dt.is_weekend  = TRUE
  AND   dt.fiscal_qtr  = '2026-Q3'
GROUP BY d.vehicle_type;

Note SUM(fare) / SUM(distance) — not AVG(fare_per_km). A ₹60 two-km hop and a ₹900 thirty-km airport run do not deserve equal weight. That is the non-additive rule in practice.

At 2 billion rides

• Facts dominate storage. fact_rides grows by millions a day; dim_driver is maybe 80,000 rows. Optimise the fact: narrow integer keys, cluster/partition by date, no text columns you can push to a dimension
• Dimensions are broadcast. In Spark or Snowflake the small dimension is shipped to every worker, so a star join costs about the same as scanning the fact alone
• Snapshots beat recomputation. "Driver earnings per day" recomputed from 2B rides on every dashboard load is waste; a periodic snapshot fact is 80k × 365 rows a year
• Conformed dimensions are an org problem, not a SQL one. Two teams each building their own dim_city with different spellings of Jowai is how you get two revenue numbers in one board meeting. One owner, one table

Gotchas

• Storing ratios and averaging them — AVG(surge_multiplier) or AVG(fare_per_km) is wrong almost always. Store the components
• Summing a semi-additive measure over time — "total wallet balance this month" = sum of 30 daily balances = garbage
• Joining facts on natural keys — the moment you keep history, driver_id matches several dimension rows and your revenue multiplies
• Text in the fact table — city_name VARCHAR repeated 2 billion times; move it to the dimension
• One giant fact for everything — rides, payments and ratings happen at different grains and times. One fact per business process
• Skipping dim_date — computing is_weekend, fiscal quarter and Diwali week in every query guarantees three definitions

Words to know

In two minutes

• A dimensional model splits data into facts (measurements of business events) and dimensions (the context you filter and group by).
• Fact tables are long and narrow: foreign keys to dimensions plus numeric measures; dimensions are short and wide: lots of descriptive text attributes.
• Measures are additive (fare), semi-additive (wallet balance — not across time) or non-additive (surge multiplier, ratios — store the parts instead).
• Three fact-table shapes: transaction (one row per ride), periodic snapshot (driver earnings per day), accumulating snapshot (one row per ride updated as it moves through milestones).
• Dimensions use surrogate keys so the warehouse can keep history and survive source-system id changes.
• Conformed dimensions (one dim_date, one dim_city shared by every fact) are what let you compare rides with payments with complaints on one dashboard.
• Declare the grain first; every other design choice follows from it.

Test yourself

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.