dbt Tests and Data Contracts: Catching Upstream Breakage Before Your Stakeholders Do

You will build a layered defence for one dbt model: generic tests for structural sanity, a singular test for a business rule, a unit test for transformation logic, and an enforced data contract that makes the model’s schema a promise the build itself has to keep. By the end you will know which layer catches which kind of breakage, and which kinds slip through all of them if you are not careful.

Tests and contracts are different tools

Tests and contracts get mentioned in the same breath, but they answer different questions.

A test asks: does the data in this model right now satisfy a condition I care about? It runs against rows. A test can pass today and fail tomorrow because the data changed.

A data contract asks: does this model still have the shape I promised its consumers? Column names, data types, and (on warehouses that support them) constraints like primary keys and not-null guarantees. A contract does not care about your rows; it cares about your interface.

You need both. Contracts cannot tell you that revenue went negative overnight. Tests cannot stop an upstream engineer from renaming customer_id to cust_id and silently breaking six downstream dashboards. Layer them deliberately.

Generic tests: the baseline nobody should skip

Generic tests are the built-in assertions you attach in YAML: unique, not_null, accepted_values, and relationships. They are cheap, declarative, and cover the failure modes that embarrass teams most often.

models:
  - name: fct_orders
    description: One row per order, at the order grain.
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned', 'cancelled']
      - name: gross_amount
        tests:
          - not_null

A few judgements separate a useful test suite from a decorative one:

• Test the grain, not every column. unique + not_null on the primary key of every model is the single highest-value habit in dbt testing. It catches fan-outs from bad joins, which are the most common cause of inflated metrics.
• relationships tests catch orphaned foreign keys, but only in one direction. They prove every customer_id in orders exists in customers. They say nothing about customers with no orders, which is usually fine — know which direction you actually care about.
• accepted_values is a tripwire, not a schema. When upstream adds a legitimate new status, the test fails and forces a conversation. That friction is the point. If your team responds by rubber-stamping value additions, the test has stopped doing its job.

Singular tests: business rules in SQL

When a rule cannot be expressed as one of the four generic tests, write a singular test: a SQL query in your tests/ directory that returns the rows that violate the rule. The test passes when the query returns zero rows.

-- Order totals must equal the sum of their line items.
-- A tolerance handles floating-point rounding in the source system;
-- anything beyond it means a join or aggregation upstream went wrong.
 
select
    o.order_id,
    o.gross_amount,
    sum(li.line_amount) as lines_total
from {{ ref('fct_orders') }} as o
inner join {{ ref('fct_order_lines') }} as li
    on o.order_id = li.order_id
group by o.order_id, o.gross_amount
having abs(o.gross_amount - sum(li.line_amount)) > 0.01

Singular tests are where domain knowledge lives: refunds must not exceed the original payment, a subscription cannot end before it starts, inventory cannot go negative. Write them for the invariants your business actually runs on, not for everything you can think of. A suite with two hundred singular tests nobody reads is worse than twenty that page someone when they fail.

One structural warning: singular tests that join two large models run a full query on every build. On big tables, that cost adds up. Scope them with where clauses against an incremental window when the rule allows it, or accept the cost consciously because the invariant matters that much.

Unit tests: verifying logic before data exists

Generic and singular tests both need data to exist before they can judge it. Unit tests in dbt flip that around: you supply small, hand-written input rows (“given”) and the expected output rows (“expect”), and dbt runs your model’s SQL against the fixtures. They are the right tool for gnarly transformation logic — a CASE statement with nine branches, a deduplication window function, a currency conversion — where the failure mode is “the logic is wrong” rather than “the data is dirty.”

unit_tests:
  - name: test_order_status_mapping
    description: Raw status codes map to the reporting taxonomy.
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, raw_status: 'SHP'}
          - {order_id: 2, raw_status: 'RTN'}
          - {order_id: 3, raw_status: 'CAN'}
    expect:
      rows:
        - {order_id: 1, status: 'shipped'}
        - {order_id: 2, status: 'returned'}
        - {order_id: 3, status: 'cancelled'}

Unit tests run fast, run in CI before a full build, and pin behaviour so a well-meaning refactor cannot quietly change what a model computes. Their limit is equally clear: fixtures are toy data. They will not show you what happens with real-world nulls, duplicates, or encoding surprises unless you deliberately write those cases into the fixtures. Do that for the cases that have burned you before.

Data contracts: the schema as a promise

A data contract in dbt is declared in the same YAML, with contract: {enforced: true} and an explicit data_type on every column. When enforced, dbt compares the contract against what your model’s SQL actually produces during the build, and the build fails if they diverge.

models:
  - name: fct_orders
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: integer
        constraints:
          - type: primary_key
          - type: not_null
      - name: customer_id
        data_type: integer
        constraints:
          - type: not_null
      - name: ordered_at
        data_type: timestamp
      - name: gross_amount
        data_type: numeric(18, 2)
      - name: status
        data_type: string

Two things to understand before you enforce contracts everywhere:

First, dbt checks the contract predominantly at build time against your model’s output definition, and on supported warehouses it also bakes the declared types (and constraints, where the platform supports them) into the materialized table. Rename a column, change a type, or drop a column, and the build stops. That is the contract earning its keep: breakage surfaces in the producer’s pull request instead of the consumer’s incident.

Second, enforcement support is not uniform across warehouses. Some platforms fully support declared constraints; others support the type checking but treat constraints as documentation. Check what your warehouse actually enforces before you tell stakeholders the contract guarantees more than it does.

How changes interact with each layer

Read the gaps in that table carefully. No single layer covers everything, and two classic breakages — silent logic errors and fan-outs — are only caught because someone wrote a specific test for them.

Severity, freshness, and not crying wolf

A test suite that fails constantly gets ignored, which is worse than no suite. Use severity deliberately:

• error (the default) for invariants: keys, reconciliation, contract compliance.
• warn for heuristics and thresholds you are still calibrating, such as “row count should not drop sharply versus the previous run” style checks from packages like dbt-expectations. Promote to error once the threshold has proven it only fires on real problems.

Also test sources for freshness. dbt’s source freshness checks warn or error when a source table has not been updated within a window you define. A stale source makes every downstream test pass on old data while stakeholders read a dashboard that quietly stopped moving — the worst failure mode in analytics, because nothing looks broken.

When a test does fail, make the failure debuggable. dbt can store the offending rows for a failing test in a dedicated schema (store_failures: true in the test’s config), so whoever picks up the failure can inspect the actual bad records instead of re-deriving them with ad-hoc queries. This matters most for the reconciliation and invariant tests, where “the test failed” without the rows is the start of an investigation rather than the end of one. Pair that with a convention that every error-severity test has an owner — a person or team, not a channel nobody watches — and that the owner’s first job on a failure is to decide whether the data or the test is wrong, because both happen and they need opposite fixes.

Finally, run tests in CI on the models a pull request touches (dbt’s state selection, state:modified+, is built for exactly this) rather than only on a schedule. A failing test discovered at merge time is a review comment. The same failure discovered on a schedule is an incident.

Verdict

Adopt the layers in this order. Generic tests on keys and critical columns first — an afternoon of work that catches the most damaging class of bugs. Singular tests next, but only for invariants the business genuinely depends on. Unit tests wherever transformation logic is complicated enough that you would not trust a refactor without them. Enforced contracts on the models other teams consume — your published marts, your semantic layer inputs — where the schema is a real interface with real consumers.

Skip contracts on internal staging models; the ceremony buys nothing where producer and consumer are the same person. And whatever you adopt, write the failing-test response into your team norms: who gets paged, what “fix forward versus roll back” means for a data pipeline, and how a contract change gets communicated. The tooling catches breakage. Only the norms decide whether catching it early actually changes anything.