Grain
What does exactly one row represent?
One customer? One order? One status change?Foundations · Module 02
Data Foundations.
Before you join tables or ask AI to query them, understand what one row means. Most expensive analytics mistakes begin with misunderstood grain.
Core idea
Before asking “how do I join these tables?” ask “what does one row represent?”
01
Context
The cancellation lead from Module 01 needs data.
You found that cancellation rate increased sharply. Now the operations team wants a city-level breakdown with payment and cancellation reasons.
This sounds straightforward: join orders, customers, cities, status events, payments and promotions.
But three of those tables can contain multiple rows for a single order. A technically valid join can therefore produce a mathematically wrong answer.
02
Concept
Grain is the contract of a table.
Primary key
What should uniquely identify that row?
customer_id, order_id, event_id.Foreign key
Which field links this row to another entity?
orders.customer_id → customers.customer_id.Relationship
How many rows can exist on each side?
One-to-one, many-to-one, or one-to-many. Rule
If you cannot state the grain in one sentence, you are not ready to aggregate or join the table.
03
Schema
Read the business model through its tables.
customers
customer_id1 row per customer
customer_idsignup_datecity_idacquisition_channel orders
order_id1 row per order
order_idcustomer_idcreated_atcompleted_atstatusgross_value order_status_events
event_id1 row per order status change
event_idorder_idstatusevent_timereason_code payments
payment_id1 row per payment attempt
payment_idorder_idattempt_nopayment_statusamount cities
city_id1 row per city
city_idcity_namecountrylaunch_date promotions
order_promo_id1 row per promotion applied to an order
order_promo_idorder_idpromo_codediscount_amount Facts, dimensions and events
orders behaves like a business fact table: it records measurable transactions. customers and cities are dimensions that describe those transactions. order_status_events is an event history: many events can belong to one order.
LIVE
Grain shift · interactive visual
Watch one order turn into multiple rows.
orders1 row / order
1001$25
1002$40
GMV$65
JOIN
order_items + orderKeep order measure once1 row / item
1001 · item A$25
1001 · item B$25duplicated order value
1002 · item C$40
SUM(gross_value) $65 $90
✓ Grain preserved × Grain changed underneath the metric
Aggregate item-level facts separately or keep the order-level measure at order grain.
The SQL can be syntactically perfect while GMV is now overstated by 38%.
04
Join risk
A correct join can still create a wrong metric.
| Join | Relationship | What can go wrong? |
|---|---|---|
| orders → customers | many-to-one | Safe if customer_id is unique in customers. |
| orders → cities | many-to-one | Safe if each city_id appears once in cities. |
| orders → status events | one-to-many | Duplicates order rows unless you aggregate or choose one event first. |
| orders → payments | one-to-many | Multiple payment attempts can multiply order value. |
| orders → promotions | one-to-many | Multiple promos per order can duplicate revenue if joined naively. |
One order
orders
order_id gross_value
1001 25.00Three status events
order_status_events
1001 created
1001 assigned
1001 cancelledNaive joined sum
$75.00 The order did not suddenly become worth three times more. The join changed the grain.
05
Challenge
Design the safe analysis table.
Exercise · 15–20 minutes
One row per order.
- Which table should define the final analysis grain?
- How would you attach the latest cancellation reason without duplicating the order?
- How would you represent payment success if there were multiple attempts?
- Would you join promotions before or after aggregating them to order level?
- Which dimensions are safe to join directly?
- What checks would prove the final table still has one row per order?
Reveal a safe modelling approach
01orders
Start from the required grain: one row per order.
02status_summary
Reduce events to one row per order: final status, cancelled_at, reason.
03payment_summary
Reduce attempts to one row per order: attempts, paid flag, paid amount.
04promo_summary
Aggregate discounts to one row per order.
05dimensions
Join customer and city attributes after uniqueness checks.
06
AI assist
Make the AI explain the grain before writing SQL.
Schema review prompt
I need an analysis table with exactly one row per order.
Tables:
- orders: one row per order
- customers: one row per customer
- cities: one row per city
- order_status_events: one row per order status change
- payments: one row per payment attempt
- promotions: one row per promotion applied to an order
Before writing SQL:
1. State the grain of every table.
2. Describe the expected relationship for every join.
3. Identify which joins can duplicate order rows.
4. Propose any pre-aggregation needed to preserve one row per order.
5. List validation checks I should run after joining.
Do not write the final SQL until the grain plan is explicit. AI rule
If an AI assistant starts writing joins without discussing grain, keys and cardinality, stop it and ask for the data model first.
07
Validation
Every join needs a before-and-after check.
01
What does one row represent in every table?
02
What column should uniquely identify a row?
03
Is the key actually unique in the data?
04
What is the expected relationship between the tables?
05
Will this join increase the number of rows?
06
Should I aggregate before joining?
07
After the join, do totals still reconcile to the source?
08
Worked solution
Preserve the grain deliberately.
The final analytical table should contain exactly one row per order because completed-order and cancellation metrics are order-level metrics.
Customers and cities can be joined directly only after confirming that their keys are unique.
Status events, payments and promotions should be reduced to one row per order before being joined to orders.
Compare row count, distinct order count and total gross value before and after the joins. Unexpected changes are a warning.
Analyst habit
Before every join, say the relationship out loud: “many orders to one customer,” “one order to many status events.” That five-second habit prevents a surprising amount of bad analytics.
PRACTICE THIS NOW ↓
Interactive case · L01
Grain Under Pressure
Join order-level revenue to item-level rows and watch a perfectly valid SUM become wrong.