What is the grain of a table, and what is join fan-out?

Summary

The grain is what one row represents (one order, one order line, one daily total). Fan-out happens when you join a coarser table to a finer one: the coarse rows repeat for every match, so summing a coarse measure double-counts.

Longer Answer

Example: order totals joined to order lines repeats each order's total per line. Prevent it by declaring and testing every table's grain, joining facts to dimensions many-to-one, and computing measures at their own grain or allocating them down with an agreed rule.

Code Example

-- wrong: order-level total repeated per line
SELECT SUM(t.total) FROM order_totals t JOIN order_items i ON i.order_id = t.order_id;
-- right: sum at the line grain
SELECT SUM(i.quantity * i.unit_price) FROM order_items i;

Key Points

• Grain = what one row means.
• Coarse-to-fine joins repeat rows.
• Summing repeated measures double-counts.
• Test grains and reconcile totals.

Deep dive: ETL and Data Pipelines: Star Schemas and Grain

What interviewers look for

A concrete fan-out example and prevention techniques.