Testing & Documenting Models

## What the grain is, and why it needs a test The grain of a fact table is the answer to "what does exactly one row of this table mean?" — one order line, one shipment event, one account balance per account per day. It is declared before you choose dimensions or facts, and everything downstream depends on it being true: additivity, join cardinality, the meaning of `count(*)`, and whether a `SUM` returns money or nonsense. Nothing in a typical warehouse enforces it. Analytical engines generally accept a declared primary key without checking it, and they do not reject duplicate rows. So the declared grain is documentation until you write an assertion that runs on every load. ## The assertion The canonical form groups by the grain columns and looks for any group with more than one row: ```sql select order_id, order_line_number, count(*) as row_count from fct_order_line group by order_id, order_line_number having count(*) > 1 ``` Zero rows returned means the grain holds. An equivalent whole-table form compares `count(*)` against the number of distinct grain combinations; the group-by version is usually preferred because the failing rows are the diagnostic — you can look at the duplicated keys immediately. If the model already carries a single surrogate or hash key built over exactly the grain columns, testing uniqueness of that one column is fine. It is only equivalent when the key is derived from precisely the grain columns and nothing else; a hash that also folds in a load timestamp will be unique on every run while the grain is broken underneath it. ## NULLs quietly defeat naive versions `GROUP BY` treats NULLs as a single group, so the query above does catch two rows that both have a NULL line number. But other formulations do not. A uniqueness constraint permits multiple NULLs. A test built on a concatenated string key is worse: concatenating anything with NULL yields NULL in standard SQL, so several broken rows collapse to one NULL key or vanish from comparison entirely. The robust habit is to assert not-null on every grain column alongside the uniqueness test, so the two together mean "these columns are always present and always unique". ## Why grain tests fail — almost always fan-out The overwhelming cause of a broken grain is join fan-out: the model joined to something that was not unique on the join key, so one input row became several output rows. The classic case is a slowly changing dimension. A Type 2 dimension holds several rows per natural key — one per version — so joining a fact to it on the natural key without a validity predicate multiplies each fact row by the number of versions that customer has ever had. The join looks correct, the row count triples, and every revenue number triples with it. Other recurring causes: joining to a child table one level below the intended grain; at-least-once delivery from a streaming source producing repeated events; and re-running an incremental window without first removing the slice it replaces, which duplicates a day of data on every retry. ```text before: 1 order line joined to 3 dimension versions order 100 / line 1 / amount 50 after: order 100 / line 1 / amount 50 (customer version 1) order 100 / line 1 / amount 50 (customer version 2) order 100 / line 1 / amount 50 (customer version 3) sum(amount) = 150 for a 50 sale ``` ## Where to run it Every model that claims a grain gets the test: the staging model (one row per source record), each intermediate model, and the published fact. Testing only the final mart tells you something is wrong; testing each layer tells you where it went wrong, which is most of the debugging. Dimensions need the same treatment, and this is where people get it backwards. In a Type 2 dimension, the surrogate key is unique and the natural key is deliberately not — several rows share it. Asserting uniqueness on the natural key of a Type 2 dimension will fail on correct data. The right assertions there are: surrogate key unique, and the combination of natural key plus effective-from unique, with at most one row flagged current per natural key. ## Severity and cost Grain uniqueness is a blocking check. Downstream, a duplicate is not a visible error — it is a plausible, wrong number, which is far more expensive than a failed pipeline. On very large facts the group-by is a full scan, so a common compromise is to test only the slice an incremental run touched on every build, with a full-table run on a schedule. That keeps the cost bounded without letting historical duplicates accumulate unnoticed.