dbt Tests & Contracts cheat sheet
Generic tests, unit tests, contracts, severity, and store_failures — the dbt quality surface in one page.
Built-in generic tests
columns: - name: order_id tests: [unique, not_null]- The two tests every model needs on its primary key. If you add nothing else, add these.
- name: customer_id tests: - relationships: to: ref('dim_customer') field: customer_id- Referential integrity that warehouses do not enforce. Catches the orphan rows that silently drop from inner joins.
- accepted_values: values: ['open', 'shipped', 'cancelled']- Guards against a new source enum value appearing without anyone noticing downstream.
Severity and thresholds
tests: - not_null: config: severity: warn- Warn instead of fail. Use it for known-imperfect sources so a real failure still means stop the line.
config: error_if: ">100" warn_if: ">0"- Row-count thresholds. Better than binary pass/fail when a small number of bad rows is a known cost of doing business.
severity: "{{ 'warn' if target.name == 'dev' else 'error' }}"- Environment-aware severity so local development is not blocked by production data quirks.
Custom tests
{% test positive_amount(model, column_name) %} select * from {{ model }} where {{ column_name }} < 0 {% endtest %}- A generic test is a macro returning failing rows. Zero rows means pass — that is the entire contract.
tests/assert_revenue_reconciles.sql- Singular tests are plain SQL files that must return no rows. Best for cross-model business invariants.
dbt_utils.expression_is_true- From dbt-utils. Covers most one-off column arithmetic checks without writing a macro.
dbt_expectations.expect_column_values_to_be_between- The dbt-expectations package ports Great Expectations semantics into dbt tests.
Unit tests
unit_tests: - name: test_discount_logic model: fct_orders given: - input: ref('stg_orders') rows: - {order_id: 1, amount: 100, tier: 'gold'} expect: rows: - {order_id: 1, final_amount: 90}- Tests transformation logic against fixed inputs, so CASE-expression bugs surface without production data.
dbt test --select test_type:unit- Unit tests run fast and need no warehouse data — put them in the pre-merge CI stage.
format: csv with fixture: my_fixture- External fixture files keep large input sets out of the YAML.
Contracts and constraints
config: contract: enforced: true- Fails the build if the model's actual columns or types drift from the declared schema. A real breaking-change guard.
constraints: - type: not_null - type: primary_key- Pushed into warehouse DDL where supported. Enforcement varies by platform, so keep the tests too.
models: - name: fct_orders latest_version: 2 versions: [{v: 1}, {v: 2}]- Model versioning lets consumers migrate on their own schedule instead of breaking on your deploy.
Debugging failures
config: store_failures: true- Writes failing rows to a table so you can query what broke instead of rerunning with guesswork.
dbt test --store-failures -s fct_orders- One-off flag for the same behavior during an incident.
dbt build --fail-fast- Stops at the first failure. Saves warehouse credits when a foundational staging model is already broken.
target/compiled/<project>/tests/...- The rendered test SQL. Paste it into a warehouse console to inspect a confusing failure directly.
From DataLane — tutorials at/blog, practice SQL live in theplayground.