◐ ai-teacher

❄️ Warehouse & Modeling · Chapter 5

Grain: the question to answer before writing a table

"One row per what?" If you can't answer that in five words, your table will double-count something — you just don't know what yet.

Study this lesson free in the app →

The basics

Grain is the precise definition of what one row in a table represents: "one row per completed ride", "one row per driver per day", "one row per payment attempt".

Why it exists: almost every wrong number in a warehouse comes from mixing grains. Join a one-row-per-ride table to a one-row-per-payment-attempt table and every ride with two attempts now counts its fare twice. The query runs, the dashboard renders, and revenue is quietly 7% too high.

The mental model: grain is the table's primary key, said in business words. If the grain is one row per ride, then ride_id must be unique. If it is one row per driver per day, then (driver_id, date) must be unique. Declare it first, write it in the table's description, and test it.

Words you must know 4

Atomic grain
the lowest level of detail the source captures (each ride, each GPS ping).
Aggregated grain
a roll-up (rides per city per day).
Fan-out
a join that multiplies rows because the other side has many matches per key.
Uniqueness test
a check that the grain's key has no duplicates.

Explained

Meghalaya Cabs' October revenue: ₹2.14 crore on the finance dashboard, ₹2.29 crore on the ops dashboard.

Same rides. Same month. The ops table joined rides to payment_attempts to show the payment method — and 7% of rides had a failed UPI attempt before a successful card one.

Two rows, one ride, fare counted twice. Nobody declared the grain.

The restaurant bill

A table of four orders dinner. The bill has a header — Table 7, ₹2,400, 10% service — and lines: four mains, two desserts, one bottle of water.

Ask "how much did we take tonight?" and add up the headers: correct. Add up the lines: also correct. But copy the ₹2,400 header total onto each of the seven lines, then add up the lines, and Table 7 suddenly spent ₹16,800.

That's a grain error. The header lives at one row per bill; the lines live at one row per item. A number only belongs on rows at the level where it is true.

The four-step design order

Kimball's order, and why it is that order:

  1. Choose the business process — ride completion, not 'everything about rides'
  2. Declare the grain — one row per completed ride. Before any column exists
  3. Choose dimensions — only those with exactly one value per row at that grain: pickup date, driver, rider, pickup zone. A ride has one driver — fine. A ride can have several promo codes — not a plain dimension here
  4. Choose facts (measures) — only those true at that grain: fare, distance, wait time

The grain then becomes a testable promise:

Fan-out, drawn

rides (grain: ride)          payment_attempts (grain: attempt)
ride_id  fare_inr            ride_id  attempt  method  status
R-1      300                 R-1      1        UPI     failed
R-2      450                 R-1      2        Card    success
                             R-2      1        UPI     success

rides JOIN payment_attempts ON ride_id
ride_id  fare_inr  method
R-1      300       UPI     ← R-1's fare
R-1      300       Card    ← counted twice
R-2      450       UPI

SUM(fare_inr) = 1050   (true revenue: 750)

The join changed the grain from ride to attempt without anyone saying so.

Aggregate to the grain, then join

-- WRONG: grain silently becomes 'attempt'
SELECT SUM(r.fare_inr)
FROM   rides r JOIN payment_attempts p USING (ride_id);

-- RIGHT: bring attempts up to ride grain first
WITH pay AS (
  SELECT ride_id,
         COUNT(*)                                        AS attempts,
         MAX_BY(method, attempt_no)                      AS final_method,
         COUNT_IF(status = 'failed')                     AS failed_attempts
  FROM   payment_attempts
  GROUP  BY ride_id                     -- now one row per ride
)
SELECT p.final_method, SUM(r.fare_inr) AS revenue_inr
FROM   rides r LEFT JOIN pay p USING (ride_id)
GROUP  BY p.final_method;

-- The grain test that would have caught it (dbt: unique test)
SELECT ride_id, COUNT(*) FROM fact_rides
GROUP BY ride_id HAVING COUNT(*) > 1;   -- must return 0 rows

MAX_BY (Snowflake, Databricks SQL) picks the method of the highest attempt number — the final, successful one.

Atomic first, roll-ups second

Gotchas

One row per what? Say it, write it, test it.

The grain is the primary key in business words.

Aggregate fine to coarse before joining — or the join will change your grain behind your back.

Words to know

GrainThe exact meaning of one row in a table, equivalent to its primary key expressed in business terms.
Atomic grainThe most detailed level captured by the source process, from which every roll-up can be derived.
Fan-outRow multiplication caused by joining to a table with many matching rows per key, leading to double counting.
Aggregate tableA pre-summarised table at a coarser grain, derived from the atomic fact for performance.
Bridge tableA table resolving a many-to-many relationship between a fact and a multi-valued dimension (ride ↔ promo codes).
AllocationSplitting a header-level amount across line-level rows so the parts sum exactly to the whole.

In two minutes

Test yourself

1. What is the grain of a table?

  1. The level of compression used for its columns
  2. What exactly one row represents
  3. The number of rows it holds
  4. The frequency at which it is loaded
Show the answer

What exactly one row represents — Grain is the meaning of a row — 'one row per completed ride'. It is independent of load frequency or size.

2. In Kimball's design order, what comes immediately after choosing the business process?

  1. Choosing measures
  2. Choosing dimensions
  3. Declaring the grain
  4. Building aggregate tables
Show the answer

Declaring the grain — Grain is declared before any column, because the grain decides which dimensions are single-valued and which measures are true per row.

3. Why prefer the atomic grain for a fact table?

  1. It always uses less storage
  2. It is the only grain BI tools accept
  3. It loads faster than aggregates
  4. Every coarser roll-up can be derived from it, but you can never drill below what you stored
Show the answer

Every coarser roll-up can be derived from it, but you can never drill below what you stored — Atomic detail keeps every future question answerable. Aggregates are added later for speed, derived from atomic data.

4. rides (one row per ride) is joined to ride_ratings (rider and driver each rate — two rows per ride). SUM(fare_inr) on the result shows what?

  1. Correct revenue
  2. Roughly double revenue for rides rated by both parties
  3. Half the revenue
  4. An error because of duplicate keys
Show the answer

Roughly double revenue for rides rated by both parties — Each ride with two ratings appears twice, so its fare is summed twice. Aggregate ratings to ride grain (or filter one role) before joining.

5. A fare-components table has rows for base, surge, toll, tip — with total_fare copied onto each row. What's the correct way to report revenue?

  1. SUM(total_fare)
  2. AVG(total_fare) * 4
  3. SUM(component_amount), or use the ride-level fact for total fare
  4. MAX(total_fare) across the whole table
Show the answer

SUM(component_amount), or use the ride-level fact for total fare — total_fare is true at ride grain, not component grain. Sum the components (which add up to the total), or take total fare from the ride-grain fact.

6. fact_driver_daily is meant to be one row per driver per day, but rows exist only for days with rides. What breaks?

  1. Average rides per day and days-online counts are overstated or wrong
  2. Total fare is overstated
  3. Nothing — missing days are equivalent to zero
  4. The uniqueness test fails
Show the answer

Average rides per day and days-online counts are overstated or wrong — Averages divide by the number of rows; skipping zero days shrinks the denominator. Sums still work, which is why this bug survives so long.

There are 8 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.

Learn it so it stays. Read it, test yourself, then recall it on two different days. The app schedules that for you, free.

Start this lesson →