Hiprup

How would you define the correct grain or level of detail for a dataset?

The grain of a table is what a single row represents, expressed as one sentence: "one row per order line per day". Declaring and verifying it is the first thing to do with any dataset, because almost every double-counting bug traces back to an assumed grain that was never checked.

  • State it as a sentence — "one row per X per Y" forces precision; if you cannot complete the sentence, you do not yet understand the table well enough to join or aggregate it.

  • Prove it with a uniqueness test — compare COUNT(*) against COUNT(DISTINCT key), or group by the candidate key and look for counts above one; never take a table name or documentation on trust.

  • Choose the finest grain the questions need — you can always aggregate up but never disaggregate down, so pre-summarising to save space permanently destroys the ability to answer detail questions.

  • Match measures to grain — a value stored at header level repeats across child rows, so summing it multiplies the total; take MAX of a repeated header value and SUM only of values that genuinely live at row grain.

  • Joins change grain — joining to a table with duplicate keys silently fans out rows and inflates every downstream sum, which is why you re-verify row counts before and after every join.

Key terms: grain, granularity, fact table, uniqueness test, fan-out, double counting, additive measure, aggregation level

-- Claim: "orders is one row per order_id." Never trust it -- prove it.
SELECT COUNT(*)                 AS total_rows,
       COUNT(DISTINCT order_id) AS distinct_orders
FROM   orders;
-- Equal      -> grain confirmed as one row per order
-- Not equal  -> the real grain is finer than the table name suggests


-- If they differ, find what actually makes a row unique
SELECT order_id,
       COUNT(*) AS rows_per_order
FROM   orders
GROUP  BY order_id
HAVING COUNT(*) > 1              -- order_line rows hiding inside an "orders" table
ORDER  BY rows_per_order DESC
LIMIT  10;


-- Grain decides which aggregate is correct.
-- order_lines is one row per order line; order_total is a HEADER value repeated on every line.
SELECT order_id,
       SUM(line_amount)  AS order_total_calc,   -- correct: line_amount lives at line grain
       MAX(order_total)  AS order_total_header, -- correct: header value repeats, so MAX
       SUM(order_total)  AS WRONG_inflated      -- WRONG: multiplies header by the line count
FROM   order_lines
GROUP  BY order_id;


-- A join to a non-unique key changes grain and inflates every downstream SUM.
-- Check the key is unique on the dimension side BEFORE joining:
SELECT COUNT(*) AS dim_rows,
       COUNT(DISTINCT customer_id) AS dim_keys
FROM   dim_customer;             -- must be equal, or the join will fan out fact rows

The first query is the entire discipline in two lines: if COUNT(*) and COUNT(DISTINCT order_id) match, the claimed grain holds, and if they do not, the table is finer than its name implies. The second query locates the offending rows so you can see what the real grain is — typically order lines stored in a table someone called orders. The third block shows why grain determines the aggregate: line_amount genuinely varies per row so SUM is right, but order_total is a header value physically repeated on every line, so SUM multiplies it by the number of lines while MAX returns the true figure.

That WRONG_inflated column is the exact bug that produces a revenue total several times too large. The final query applies the same uniqueness test to the dimension before joining, because a duplicate key on the dimension side fans out fact rows and inflates every measure downstream without raising any error.

Define grain in one sentence, then immediately say how you prove it — the COUNT(*) versus COUNT(DISTINCT key) check is the answer interviewers want and most candidates never mention. Have a war story ready about a total that came out too high because a supposedly one-row-per-order table was actually at line grain, since the diagnosis matters more than the definition.

Expect the follow-up "how do you decide what grain to build a fact table at?" — answer with the finest grain the business questions require, and explain the storage-versus-flexibility trade-off. The mistake to avoid is conflating grain with primary key: the key enforces uniqueness, the grain describes meaning.