What does it mean to declare the grain of a fact table before choosing its dimensions?

## What grain actually is The grain of a fact table is the answer to one question: *what does a single row of this table represent?* A good answer is a short declarative sentence in the language of the business. - "One row per product scanned on one point-of-sale ticket." - "One row per policy per month-end." - "One row per shipment carton." - "One row per candidate per stage transition in the hiring pipeline." It is the first thing decided after choosing which business process to model, and it is decided before a single column is chosen. That ordering is the whole discipline: grain is not a summary of the table you built, it is the contract the table is built to satisfy. ## Say it in business language, not as a key list "The grain is date, store, product" is a key column list, not a grain declaration. It cannot be confirmed by a domain expert, and it silently begs the question of whether two rows for the same date, store and product are legitimate. "One row per product per store per day, summarising every ticket that day" *can* be confirmed or rejected by someone who knows the business — and it makes an immediate, testable claim: those three columns are unique. The business sentence is the declaration; the column list is its implementation. ## Grain fixes what else is allowed Once the grain is stated, two design questions stop being matters of taste: **Dimension test.** A dimension attaches to the fact table only if it takes exactly one value for a single row at that grain. At order-line grain, the product qualifies; at daily-store-total grain it does not, because one row spans many products. If a descriptor can take several values for one row, it does not attach directly at that grain and needs another treatment. **Measure test.** A measure belongs on the row only if it is genuinely measured at that grain. An order-level shipping charge is not a line-level measurement; a month-end account balance is not a per-transaction measurement. Forcing a coarser measure onto finer rows is how tables come to double-count. ## Atomic grain is the default Unless there is a specific reason otherwise, the grain is the lowest level the process actually captures. Two properties follow. First, dimensionality is richest at the atomic level: the finer the row, the more descriptors take a single value for it, and every one of them is a way to slice the data. Second, atomic detail is the only level from which every coarser summary can be derived; the reverse is impossible. A model built at atomic grain absorbs questions no one has asked yet, which is the usual reason a warehouse survives its first reorganisation. ## What a declared grain buys you at query time When every row means the same thing, `SUM` over the measures is meaningful without qualification, `COUNT(*)` counts business events rather than storage rows, and a duplicate is definable — a grain violation is precisely two rows where the declaration allows one. When rows mean different things, none of those statements holds, and every query needs undocumented tribal knowledge about which rows to exclude. ## Verify the declaration against the data A declaration nobody checks becomes a comment. The direct test is a uniqueness query on the columns the grain implies: ```sql SELECT order_id, line_number, COUNT(*) FROM fact_order_line GROUP BY order_id, line_number HAVING COUNT(*) > 1; ``` Any rows returned mean either the loader is producing duplicates or the declared grain is wrong — for instance the source splits a line across shipments, so the true grain is one row per line per shipment. Both are worth knowing before a report is built on it, and running this as a standing check keeps a correct declaration correct as sources change. ## Symptoms of an undeclared or violated grain - Two analysts write the same revenue query and get different numbers, and both are defensible. - Reports carry filters like `WHERE row_type <> 'HEADER'` that nobody can explain. - A total is right at one level of a drill-down and wrong at another. - A source change silently doubles a metric, because nothing tested how many rows an event was allowed to produce. Each of these is the same defect surfacing differently: the table's rows do not all mean one thing. ## When the grain has to change Sometimes the business genuinely changes — a shipment can now be split, a subscription can now be paused mid-period — and the old declaration no longer describes reality. The honest response is a new declaration and a rebuild at the finer grain, not a patch that lets both shapes coexist in one table. Mixed shapes in a single fact table are permanently more expensive than one migration, because every future query must carry the exclusion logic. ## What an interviewer listens for They want a grain sentence, unprompted, phrased in business language, plus the two tests it enables. The strongest answers add the verification query — knowing that grain is something you *assert and enforce*, not something you hope stayed true, is what separates a candidate who has read Kimball from one who has operated a warehouse.