SQL for Analytics
Advanced 13 min read SQL for Analytics

Grain and Granularity

The row-level contract behind every number

Grain is the level of detail of a table — one row per order, per user-day, per event. Every analysis must state and respect grain; combining tables at different grains is the classic source of doubled or missing rows.

graingranularitydata modelingone row perfactsaggregation
Prerequisites: SQL Aggregations
Before we start

What you will learn

  • State a table's grain precisely
  • Detect and fix grain mismatches across queries and joins
  • Design aggregation that preserves the intended grain
Introduction

What is this?

Grain defines what one row in a table represents: one transaction, one user-day, one event. Analytics goes wrong when tables are joined across mismatched grains — one-side values get multiplied (fan-out) or swallowed (summary collapse). Competent analysis begins by naming the grain and ends by verifying it survived the query.

Motivation

Why do you need it?

  • Every dashboard total is only as trustworthy as the grain it was computed at.
  • Grain mismatches produce the 'revenue doubled' and 'users vanished' mystery bugs.
  • Clear grain contracts let two analysts arrive at the same number.
  • Model design (facts vs dimensions) is a grain decision at heart.
Think about it

Mental model

Imagine

Counting guests vs counting rows of tables

Think of

Grain

'We served 40 guests' is a different grain than '36 place settings by table'. Mix them and your catering order is wrong either way — one person per table-row when you meant guests.

Mechanics

How it works

  • Express grain as 'one row per <what>': per order, per user per day, per event.
  • Check grain with group-by-the-key count: SELECT key, COUNT(*) HAVING COUNT(*)>1 reveals non-unique rows.
  • Joining to a coarser table inflates; joining to a finer table duplicates.
  • Pre-aggregate finer data to the target's grain before combining.
  • Document grain in the model so future queries respect it.

Grain mismatch in a join.

orders (per order)
order_items (per line)
Naive JOIN
Summing o.total
Correct: pre-agg items
Hands-on

Examples

Stating and respecting grain

Stating and respecting grain
-- Verify a table's grain:
SELECT user_id, day, COUNT(*) AS rows_per_key
FROM user_daily
GROUP BY user_id, day
HAVING COUNT(*) > 1;   -- any rows = grain violated
 
-- Aggregate items first, then join at order grain
SELECT o.order_id, o.total
FROM orders o
JOIN (SELECT order_id, SUM(qty) FROM items GROUP BY 1) i
  ON i.order_id = o.order_id;

The HAVING probe proves the grain; pre-aggregated items let the join happen at the order level so totals stay correct.

Real world

In production

  • Revenue cubes defined 'one row per order' feeding the SKU drill-down correctly.
  • User-daily tables power DAU without double-counting sessions.
  • A model documented with grain in the description header, so consumers check before querying.
Guidelines

Best practices

  • Name the grain in table metadata: 'one row per order_line'.
  • Add grain tests (unique on key) as part of data quality.
  • When grain must change, aggregate explicitly rather than letting joins infer it.
  • For many-to-many analysis, build a clear association model instead of hoping joins work.
Watch out

Common mistakes

  • JOINing two differently-grained tables and never checking rows.
  • Summarizing a metric from the coarser table after a fan-out.
  • Assuming 'one row per user' when the table is actually per event.
  • Comparing metrics computed at different grains ('3 orders/user' vs '3 orders/session').
When things break

Debugging tips

  • Row count jumped? Suspect a grain mismatch and probe keys with COUNT(*).
  • Dollar total stable but unit count tripled? The join multiplied rows, sums were at wrong grain.
  • Users disappeared after a join? INNER at mismatched grain dropped rows — use LEFT and compare.
  • Publish a grain probe query with each model; it doubles as documentation.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

A table has 3 rows per order on average. Its grain is:

2

Grain issues are automatically resolved by the query engine.

Go deeper

Official documentation

Kimball grain definition
docs