Testing data like you test code

Software engineering solved a version of this problem decades ago: you write tests, they run automatically, and a failure blocks the release. Data teams adopted the tooling and mostly skipped the discipline, because data has a property code does not — it changes underneath you without anyone editing anything.

Your SQL can be perfectly correct on Monday and produce garbage on Tuesday because a source system started sending a new enum value. No unit test catches that. You need tests that run against the data, every time it lands.

The four tests that catch most of it#

Start here. In my experience these four cover the large majority of real incidents.

Uniqueness. The primary key is unique. This sounds trivial and it is the test that fires most often, because a duplicate key is what an upstream replay produces.

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

Not null. The columns that must be populated, are. Especially foreign keys — a null customer_key means a row that will silently vanish from every join.

Referential integrity. Every foreign key resolves.

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

Warehouses do not enforce foreign keys. Nothing stops an orphan except you looking for one.

Accepted values. Categorical columns contain only the values you expect. This is the test that catches semantic drift — the day status gains a fifth value, you find out immediately rather than when a case statement's else branch quietly swallows 12% of rows.

Freshness and volume: the two nobody writes#

Freshness. The most recent row is recent enough.

select max(_loaded_at) as latest,
       datediff('hour', max(_loaded_at), current_timestamp) as hours_stale
from raw_orders
having hours_stale > 6

A pipeline that runs successfully and loads nothing is the worst failure mode there is, because every dashboard keeps rendering yesterday's numbers as though they were today's. Nothing errors. Nothing alerts. Somebody notices three days later.

Volume. Today's row count is in the range of a normal day.

with daily as (
    select order_date, count(*) as rows_loaded
    from fct_order_lines
    where order_date >= current_date - 28
    group by 1
),
stats as (select avg(rows_loaded) as mu, stddev(rows_loaded) as sd from daily where order_date < current_date)
select d.order_date, d.rows_loaded, s.mu, (d.rows_loaded - s.mu) / nullif(s.sd, 0) as z
from daily d cross join stats s
where d.order_date = current_date and abs((d.rows_loaded - s.mu) / nullif(s.sd, 0)) > 3

Compare against the same weekday, not the previous day — Monday and Sunday have wildly different volumes in most businesses, and a naive day-over-day check will page you every Monday until you turn it off.

Severity, and the on-call cost of getting it wrong#

Not everything should stop the pipeline. Grade every test:

• Error — stop the run and do not publish. Reserve for correctness violations: duplicate keys, null keys, broken referential integrity. If it would make a published number wrong, it is an error.
• Warn — notify, keep running. Distributional oddities, a new category value, a mild freshness lag.

The failure mode of over-erroring is a pipeline that halts nightly and a team that starts rerunning it without looking. The failure mode of under-erroring is wrong numbers in a board deck. Both are bad; the first is more common in teams that just discovered testing.

Reconciliation: the test that catches what tests cannot#

Structural tests verify shape. They cannot verify that the number is right. For that you need an independent source to agree with.

Pick your handful of load-bearing metrics and reconcile them, on a schedule, against a system that computed them differently: warehouse revenue against the billing system's, warehouse user counts against the application database's, warehouse order counts against the fulfilment system's.

Agreement within a defined tolerance is a genuinely strong signal, because two independent implementations rarely produce the same wrong answer. This is the check that finds the errors your tests were never designed to look for.

Where tests live#

In the repository, next to the models, in version control, running in CI on every pull request and again on every production run. Not in someone's saved queries folder.

# dbt-style, but the idea is tool-independent
models:
  - name: fct_order_lines
    description: One row per order line. Grain confirmed by the unique test below.
    columns:
      - name: order_line_key
        tests: [unique, not_null]
      - name: customer_key
        tests:
          - not_null
          - relationships: {to: ref('dim_customers'), field: customer_key}
      - name: channel
        tests:
          - accepted_values: {values: [web, ios, android, marketplace]}
      - name: revenue_usd
        tests:
          - dbt_utils.accepted_range: {min_value: 0, max_value: 100000}

The rule I would give a new team#

Every model gets a uniqueness test on its declared grain, before anything else. One test per model, non-negotiable, no exceptions. It is the cheapest test to write, it directly encodes the grain sentence from module three, and it catches the class of bug that produces the most spectacularly wrong numbers.

Everything else can be added as you learn what breaks.