The grain uniqueness test

The rule#

Every model gets a uniqueness test on its declared grain, before anything else. One test per model, no exceptions.

It is the cheapest test to write, it directly encodes the grain sentence, and it catches the class of bug that produces the most spectacularly wrong numbers — a fan-out join, a source that starts double-delivering, a merge whose source was not deduplicated.

The pattern#

models:
  - name: fct_order_lines
    description: One row per order line.
    columns:
      - name: order_line_key
        tests: [unique, not_null]

Two lines of YAML. That is the whole intervention.

The bare SQL, if your stack has no test framework:

select order_line_key, count(*) as n
from fct_order_lines
group by 1
having count(*) > 1

Any rows returned is a failure. Wire it to fail the run, not warn.

Why it works#

The model's description says one row per order line. The test says the same thing in a form the machine can check. If the sentence is true, the test passes. If somebody later breaks it, the test fails on the next run rather than six weeks later in a board deck.

That coupling is the point: documentation that cannot drift from reality, because reality is asserted.

The next three tests#

Once every model has its grain test, add these in order:

Not null on foreign keys. A null customer_key is a row that will silently vanish from every join.

Referential integrity. Warehouses do not enforce foreign keys; nothing stops an orphan except you looking for one.

select f.customer_key
from fct_order_lines f
left join dim_customers d on d.customer_key = f.customer_key
where d.customer_key is null

Accepted values on every categorical column that appears in a case statement. This is what catches semantic drift — the day an enum gains a value, you find out immediately rather than when the else branch has been swallowing 4% of revenue for a month.