Data Modeling Best Practices

Chapter 1 · Practice 4 of 4

Test the grain with a uniqueness check on its key columns

From Start from the business process and the grain

Why it matters

A declared grain is a claim you can test. If the grain is one row per order line, the combination of order_id and line_number must be unique.

sql
-- Fails (returns rows) if the grain is broken
select order_id, line_number, count(*) as n
from {{ ref('fct_order_lines') }}
group by 1, 2
having count(*) > 1

In dbt, a single-column key gets the built-in unique and not_null tests. For a multi-column grain, either build a single surrogate key column from the parts and test that, or keep a singular test like the one above in tests/. A broken grain usually means a join fanned out upstream.