Rows, grain, and the shape of a dataset

Here is a claim that sounds too simple to be worth a lesson: you must be able to say what one row of your dataset represents, in one sentence, before you compute anything.

That sentence is called the grain. Getting it wrong is the single most common source of wrong numbers in this job, and it is wrong silently — nothing errors, the query runs, the chart renders, and the number is off by 40%.

The failure, concretely#

Open retail-orders. Its grain is one row per order line. An order with three items is three rows. Now consider a question a stakeholder will absolutely ask you: "what is our average order value?"

-- Wrong. This is the average LINE value.
select avg(revenue_usd) from retail_orders;

-- Right. Aggregate to the order grain first.
select avg(order_total) from (
  select order_id, sum(revenue_usd) as order_total
  from retail_orders
  group by order_id
);

The first query is not a rounding error away from the second. On this dataset it is about 35% lower, because multi-item orders are common. Both queries run. Neither warns you. The only defence is knowing the grain.

Naming the grain out loud#

Write it into the table description, the dbt model, the notebook cell, the top of the query. Real examples:

• One row per completed trip.
• One row per account, as of the extract date.
• One row per city per day.
• One row per user per experiment exposure.

Notice that the last three contain a time qualifier. That is not decoration — "one row per account" and "one row per account per month" are completely different tables that both look like account data.

The four shapes you will meet#

Event / fact tables. One row per thing that happened, with a timestamp. Append-only, large, the source of truth. ride-hail-trips is one.

Entity / dimension tables. One row per thing that exists — a customer, a product, a device. Usually mutable, usually small. The interesting question is always "as of when?".

Panel tables. One row per entity per period. world-indicators is one: country × year. Deceptively easy to aggregate wrongly, because you can average across entities, across time, or across both, and the three answers differ.

Aggregate tables. Pre-computed rollups. Fast, and dangerous — you cannot recover detail you already threw away, and you cannot correctly average an average.

Long and wide#

The same data has two layouts, and you will convert between them constantly.

Wide (one column per variable) reads well to humans and is what a spreadsheet wants:

Long (one row per observation) is what almost every tool wants for plotting and modelling:

long = wide.melt(id_vars="country", var_name="year", value_name="gdp")
wide = long.pivot(index="country", columns="year", values="gdp")

The rule: store long, present wide. Long data survives new years being added; wide data requires a schema change every January.

Nulls mean four different things#

A blank cell can mean: the value does not exist, the value exists but was not recorded, the value was recorded but has not arrived yet, or the value is genuinely zero and someone stored it badly. These need different handling, and no type system distinguishes them.

Open flight-delays: a cancelled flight has a null delay. Is that a zero-minute delay or an infinite one? Neither. It is a different kind of event, and the honest answer is to report cancellations separately rather than folding them into a delay average.