dbt Tests: dbt test, Data Test, and Unit Test Guide

dbt tests are SQL assertions that return failing rows. Use dbt to declare them as generic (schema test) rules in YAML or as singular SQL files, then execute with dbt test or as part of dbt build. Built-in generic tests cover uniqueness, nulls, relationships, and accepted values; you can extend these with a custom generic test (macro) or a one-off singular test. If a test fails under dbt test or dbt build, the step fails and CI should block; dbt run does not execute tests. Below, we’ll add data tests to a realistic dbt project, run dbt tests selectively, tune them for large models, and use a dbt testing package to expand coverage—without fluff.

What are tests in dbt?

Tests in dbt are simple: a query that identifies records violating an assertion. Zero rows means pass; any rows means fail. A generic test (also called a schema test) is declared in YAML and implemented as a macro so you can reuse it across many resources. A singular test is a raw SQL file that encodes a one-off assertion. Both run via the same command: dbt test.

# Example: run all tests
$ dbt test

# Run tests for a specific model
$ dbt test -s models/fct_orders.sql

# Run a specific test by name or path
$ dbt test -s test_name:accepted_values -s path:tests/no_future_orders.sql

# Run everything (build models, then tests)
$ dbt build

In practice, you’ll layer assertions:

• Built-in generic tests for wide coverage on columns: unique, not_null, accepted_values, relationships.
• A custom generic test for reusable rules you apply in YAML (e.g., timestamps not in the future, bounded row counts).
• A singular test for cross-model, aggregate, or edge-case logic you want to encode directly in SQL.

Types of tests in dbt (and when to reach for each)

Generic (schema) tests: the 80/20 of coverage

Generic tests attach to a model or column in YAML. They’re the fastest way to enforce data quality across a domain. Here’s a setup for an orders dbt model with ~40M rows, enforcing keys, relationships, and allowed states. This lives next to your dbt model in a YAML file, often under models/.

# models/orders.yml
version: 2

models:
  - name: fct_orders
    description: Fact table at order_id grain
    config:
      tags: [core, finance]
    columns:
      - name: order_id
        description: Surrogate key for each order
        tests:
          - unique
          - not_null

      - name: customer_id
        description: FK to dim_customers.id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: id
              # Only check recent partitions to keep test fast
              where: "order_date >= date_trunc('month', current_date - interval '2 months')"

      - name: order_status
        description: Current order state
        tests:
          - accepted_values:
              values: ['pending','shipped','canceled','refunded']
              severity: warn  # don't block deploy; still surface in CI

      - name: order_date
        description: Warehouse-local timestamp when the order was placed
        tests:
          - not_null

Notes:

• relationships protects referential integrity between data models. On big tables, filter with where to keep runtime manageable.
• accepted_values is perfect for enumerations. You can choose severity: warn for softer enforcement early on.
• Table-level checks: use a dbt package like dbt_utils.unique_combination_of_columns for composite keys.

Built-in generic tests you should start with

• unique: every value is distinct.
• not_null: no NULLs allowed.
• accepted_values: whitelist of legal values.
• relationships: column references a primary key in another model.

These built-in tests are the foundation for data quality checks. You get wide coverage for very little code, and each schema test is declarative and auditable in Git.

Create a custom generic test (reusable YAML-powered assertion)

When built-ins don’t cover your rule, write a custom generic test once as a macro, then reuse in YAML across many resources. Example: block records where a timestamp is in the future.

-- macros/tests/not_in_future.sql
{% test not_in_future(model, column_name) %}
select
  {{ column_name }}
from {{ model }}
where {{ column_name }} > current_timestamp
{% endtest %}

Use it in YAML:

# models/payments.yml
version: 2
models:
  - name: fct_payments
    columns:
      - name: paid_at
        tests:
          - not_in_future

Another custom generic test: row counts in a range for load sanity.

-- macros/tests/row_count_between.sql
{% test row_count_between(model, min_rows, max_rows, where=None) %}
select 1
from (
  select count(*) as cnt from {{ model }}
  {% if where %} where {{ where }} {% endif %}
) x
where x.cnt < {{ min_rows }} or x.cnt > {{ max_rows }}
{% endtest %}

# models/staging.yml
version: 2
models:
  - name: stg_orders
    tests:
      - row_count_between:
          min_rows: 1000
          max_rows: 5000000
          where: "order_date = current_date"

Because a generic test lives in YAML, it scales elegantly: apply the same rule to dozens of columns with a few lines. If you’re authoring test macros with Jinja, see our deep-dive on macros in dbt Macros & Jinja Tips Every Analytics Engineer Should Know.

Singular tests: any SQL you can write

Singular tests are SQL files under your test paths. They’re perfect for multi-model assertions and anything that doesn’t fit neatly in a YAML-defined generic test. A singular test fails when the query returns one or more rows.

-- tests/no_future_orders.sql
-- Fail if any order_date is in the future (across all orders)
select order_id, order_date
from {{ ref('fct_orders') }}
where order_date > current_date

More complex example: ensure every shipped order has a corresponding shipment record within 3 days. On a table with 40M rows, push filtering into SQL and limit the date window to keep it fast.

-- tests/orders_have_timely_shipments.sql
with orders as (
  select order_id, order_date
  from {{ ref('fct_orders') }}
  where order_status = 'shipped'
    and order_date >= date_trunc('month', current_date - interval '1 month')
),
shipments as (
  select order_id, shipped_at
  from {{ ref('fct_shipments') }}
  where shipped_at >= date_trunc('month', current_date - interval '1 month')
)
select o.order_id
from orders o
left join shipments s using (order_id)
where s.shipped_at is null
   or s.shipped_at > o.order_date + interval '3 day'

Singular tests are flexible and expressive—great for data transformation invariants that cross models. Use them to encode business logic that can’t be declared as a schema test.

How to run dbt tests (selection and CI)

Common ways to run dbt tests efficiently:

# All tests in the project
$ dbt test

# Only tests for a specific model
$ dbt test -s ref:fct_orders

# Only generic tests (exclude singular)
$ dbt test -s test_type:generic

# Only singular tests in a folder
$ dbt test -s path:tests/shipments

# Run a specific named test (as defined in YAML or macro)
$ dbt test -s test_name:accepted_values

# Run impacted models + their tests in a PR (state-aware)
$ dbt build -s state:modified+ --defer --state path/to/artifacts

In CI, prefer dbt build so models are built and then tests execute against fresh artifacts. This mirrors production. It also ensures failures are isolated to your change set. In dbt Cloud, create a job with a dbt build step and enable PR checks so CI will automatically run the tests on pull requests.

What happens when a dbt test fails?

When a test fails under dbt test or dbt build, the command exits non-zero and your pipeline should stop. If a test fails (test fails) in CI, block merges until it’s fixed. You can persist failing rows for triage:

# Persist failing rows for investigation
# models/schema.yml (per-test) or dbt_project.yml (default for all tests)
version: 2
models:
  - name: fct_orders
    columns:
      - name: order_status
        tests:
          - accepted_values:
              values: ['pending','shipped','canceled','refunded']
              severity: error
              store_failures: true

By contrast, dbt run does not execute tests, so it won’t fail due to a test. If you only run dbt run and later run the tests via dbt test or dbt build, failures show up in that step. That’s the answer to “dbt run — what happens when a test fails?”: nothing, until you actually run the tests.

Scale and performance: fast tests on big models

On a 40M-row table, naive assertions can get expensive. Practical techniques:

• Filter heavy tests with where to a recent slice (e.g., last 1–2 months) for relationships and aggregations.
• Lean on uniqueness and not_null—they vectorize well and use native warehouse operators.
• Keep singular tests targeted to only the subset at risk (e.g., shipped orders this month).
• Use severity: warn temporarily for emerging rules while backfilling historical fixes.
• Incremental patterns: test only newly loaded partitions by filtering on load date.

If you need continuous coverage beyond assertions in testing in dbt, see Data Observability Explained; dbt tests and observability work well together.

Source tests and freshness on inbound data

dbt’s sources feature lets you test constraints at the boundary with source data before it flows further into your models. You can also define freshness checks to catch late-arriving data in the data pipeline.

# models/sources.yml
version: 2
sources:
  - name: stripe
    schema: raw_stripe
    tables:
      - name: charges
        loaded_at_field: created_at
        freshness:
          warn_after: {count: 2, period: hour}
          error_after: {count: 4, period: hour}
        columns:
          - name: id
            tests: [not_null, unique]
          - name: status
            tests:
              - accepted_values:
                  values: ['succeeded','failed','pending']

Use dbt source freshness to run freshness checks, or rely on dbt build with sources referenced by downstream models. This ensures upstream constraints are enforced early, keeping later dbt data clean.

Governance via tests: metadata, documentation, and contracts

Tests can enforce basic governance: keys, nullability, and relationships. For broader governance (ownership, docs coverage, lineage, and discoverability), pair dbt with a data catalog and CI policy checks. For a high-level comparison of catalogs, see Automated Data Catalogs: DataHub vs Amundsen vs Atlan.

If your warehouse supports column comments, you can even write a singular test to flag undocumented columns—simple but effective:

-- tests/columns_have_docs.sql
-- Fail if any column in core models has a null/empty description
with cols as (
  select table_schema, table_name, column_name, comment as description
  from information_schema.columns
  where table_schema ilike 'ANALYTICS'  -- adjust
    and table_name in ('FCT_ORDERS','FCT_PAYMENTS','DIM_CUSTOMERS')
)
select *
from cols
where coalesce(trim(description), '') = ''

For enforcing contracts and Jinja-level standards, “unit test” your macros or lint your project via CI steps. Our macro guide (Jinja Tips) covers practical patterns you can adopt.

Using a dbt testing package (dbt-utils, expectations)

A dbt testing package expands what you can assert without writing SQL from scratch. Two common choices are dbt-utils and expectations-style packages that add dozens of assertions. They provide extra built-in generic tests such as composite key uniqueness, multi-column relationships, regex matches, and statistical distributions. Add a dbt package in packages.yml, run dbt deps, then call the tests in YAML.

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.0.0"

# models/orders.yml (snippet)
models:
  - name: fct_orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [order_id, store_id]

This path is often the fastest way to complete coverage with minimal custom code.

Where do tests live? Organizing files

• Generic tests are declared in YAML next to models (e.g., models/*.yml) as a schema test.
• Singular tests are SQL files under your configured test paths—each a standalone query, i.e., your data test file.

You can change the tests directory by editing dbt_project.yml:

# dbt_project.yml
name: my_project
version: 1.0.0
config-version: 2

test-paths: ["tests", "assertions"]
model-paths: ["models"]
macro-paths: ["macros"]

So yes—you can store tests outside the default tests directory by configuring test-paths. Generic schema tests stay in YAML regardless of that setting.

dbt Core vs dbt Cloud for testing workflows

Both dbt Core and dbt Cloud execute the same tests; the difference is developer experience and orchestration. dbt Cloud adds job scheduling, UI-based artifacts, and native PR checks. If you want a full comparison, see dbt Cloud vs Core: Feature Comparison 2025. Either way, the test definitions and behavior are identical. If you prefer the CLI, dbt core offers everything you need to run the tests locally and in your own CI.

How tests relate to your data pipeline

In a typical data pipeline, raw sources land, staging models clean and type them, and marts aggregate for analytics. Tests gate each stage. Use built-in generic tests for keys and enums in staging, custom checks for business rules in marts, and singular tests for cross-model guarantees. For streaming or near-real-time patterns, run a subset of fast checks continuously; our dbt & Kafka streaming guide covers patterns for when data arrives in micro-batches.

Selecting, scoping, and naming tests

Good naming and selection save time:

• Use descriptive filenames: tests/orders_have_timely_shipments.sql.
• Tag critical tests in YAML: tags: ['critical'] and run them in canary jobs: dbt test -s tag:critical.
• Use graph selection: dbt test -s ref:fct_orders or scope to resource types: dbt test -s test_type:singular.
• In dbt Cloud jobs or your CI, run dbt tests on only impacted nodes using state:modified+ to keep runs fast.

Edge cases: data type drift, composite keys, and partial history

• Data type drift: add a singular test querying information_schema.columns to verify types for critical columns (e.g., currency as NUMERIC). This catches upstream schema changes before they break downstream tools.
• Composite keys: if you need uniqueness across multiple columns, reach for a community test from a dbt package that supports multi-column checks.
• Partial history backfills: keep older partitions in warn mode while enforcing error on fresh data via where filters.

Testing in dbt: best practices you can adopt today

• Start with generic tests on every primary and foreign key column; add accepted_values to categorical fields.
• Write a custom test as a reusable macro for any business rule you’ll repeat; keep singular tests for true one-offs.
• Scope heavy checks with where. Prioritize fast constraints (nulls, uniqueness) on large tables.
• Use severity: warn strategically. Flip to error once the team is ready to enforce.
• Persist failures for triage on flaky inputs; automate notifications through your orchestrator.
• Pair dbt tests with observability for continuous monitoring beyond the warehouse.

Commands cheat sheet

# Run all tests
$ dbt test

# Run models and then tests (recommended in CI)
$ dbt build

# Run tests for a model + its children
$ dbt test -s ref:fct_orders+

# Run only generic tests
$ dbt test -s test_type:generic

# Run only singular tests
$ dbt test -s test_type:singular

# Run tests modified in this branch (state comparison)
$ dbt build -s state:modified+ --defer --state path/to/prod/artifacts

Unit tests in the dbt ecosystem (macro harness)

While dbt is centered on data tests, you can also do a unit test-style check on macros and Jinja logic. One pragmatic approach is to render a macro with known inputs and compare the generated SQL or result set to an expected outcome in a dedicated dev schema.

-- macros/sql_safe_divide.sql
{% macro sql_safe_divide(numerator, denominator) %}
case when {{ denominator }} = 0 then null else {{ numerator }} / {{ denominator }} end
{% endmacro %}

-- tests/unit_sql_safe_divide.sql (singular)
with data as (
  select 10 as num, 2 as den union all
  select 5, 0
)
select *
from (
  select {{ sql_safe_divide('num','den') }} as val, den
  from data
)
where (den = 0 and val is not null) or (den != 0 and val is null)

This pattern keeps logic quality high without introducing external tooling. It complements your table- and column-level data tests.

Deep dive: build vs test order, severity, and artifacts

dbt build runs in dependency order: seeds, source freshness, models, snapshots, then tests. A test’s severity can be warn or error and is set per test in YAML. Results are written to target/run_results.json for downstream reporting. Many teams ingest this into a dashboard for trend analysis of pass rates.

Runbook snippet: adding and operating tests

1. Add data tests in YAML for every new model: keys, FKs, enums.
2. For nontrivial business rules, prefer a custom generic test over repeating logic in multiple singular tests.
3. For one-time or cross-model logic, write a singular SQL test file and place it under tests/ (or your configured path).
4. Tag critical tests and wire a canary job that runs hourly to run the tests on fresh partitions.
5. In CI, run dbt tests via dbt build using state selection to avoid long, full-project runs.
6. When a test fails, persist failures, triage the rows, and decide whether to fix upstream logic, patch data, or expand allowed values.

FAQ

What are tests in dbt?

Tests are SQL assertions that return failing rows. Generic tests are declared in YAML as a schema test and reuse macros; singular tests are raw SQL queries. A pass returns zero rows; a failure returns one or more rows.

What happens when a dbt test fails?

The dbt test or dbt build command exits with a failure status. Your CI should block merges or deployments. You can mark some tests as warnings via severity: warn so they don’t fail the build but still show up.

Is the dbt exam difficult?

It’s practical: be comfortable modeling, writing dbt tests, and using selection syntax. If you can build and test a small dbt project end-to-end and reason about CI, you’re on track. Browse the dbt topic hub for curated learning paths.

How to run a specific dbt test?

Use selection syntax: by test name, path, tags, or resource type. Examples: dbt test -s test_name:accepted_values, dbt test -s path:tests/no_future_orders.sql, or dbt test -s tag:critical.

“dbt run” — what happens when a test fails?

Nothing during dbt run; it doesn’t execute tests. Failures occur only when you run dbt test or dbt build. Many teams run dbt build in CI so tests execute right after models build.

Another common use case for a custom data test?

Currency sanity checks (e.g., amount >= 0 for refunds), SCD conformance (no overlapping validity ranges), and event sequence validation (signup before purchase). Each is easy to encode as a custom data test—generic macro or singular SQL—depending on reuse.

Can I store my data tests in a directory other than the tests directory in my project?

Yes. Set test-paths in dbt_project.yml to include additional directories. Generic tests remain in YAML files next to models.

Comparing dbt Core and dbt Cloud?

They run the same SQL and the same tests. Cloud adds a UI, jobs, and managed CI; Core is the open-source CLI. See the complete breakdown in dbt Cloud vs Core.

Does dbt testing cover all your governance bases?

No. dbt tests are excellent for warehouse-level data quality checks, but governance also needs metadata standards, lineage, access control, and observability. Combine dbt with a catalog and monitoring to complete the picture.

How do I enforce metadata and documentation standards across a dbt project?

Use CI checks and singular tests against your warehouse’s information schema to ensure descriptions exist, and enforce owners via YAML meta. For macro-level standards, lint or unit test Jinja. See our macro tips guide for patterns.

Concrete patterns for large enterprises

• Contracts + tests: Enable model contracts for critical marts and back them with not_null, unique, and relationship tests.
• Runtime budget: Cap expensive tests using where and schedule a nightly job for full checks.
• PR-level coverage: Run only impacted models with state:modified+, then run a smaller set of critical tests across the domain regardless of changes.
• Test freshness SLAs: Add source freshness on every authoritative upstream, tuned to business SLAs.

Putting it together: a minimal, high-value test suite

Here’s a crisp starter checklist you can drop into a new domain:

• All primary keys: unique + not_null.
• All foreign keys: relationships (filtered to recent partitions if needed).
• All enums: accepted_values (warn until backfilled).
• Two custom generic tests: no future timestamps, row count band for daily loads.
• Two singular integrity tests across models (e.g., timely shipments, non-overlapping SCD ranges).
• Source freshness on critical upstreams.

Troubleshooting common failures

• False positives on relationships: Ensure the parent key is actually unique; otherwise the test can misbehave. Consider deduping the parent model or switching to a composite key test from a package.
• Slow tests on large tables: Push filters down with where, limit the window, or run those assertions in a nightly job.
• Inconsistent timezone handling: Normalize timestamps in staging and test against a canonical zone.
• Backfills causing spikes: Temporarily set severity: warn and add where excluding backfill windows.

Related learning

• dbt topic hub for curated dbt content and patterns.
• dbt Cloud vs Core for platform-level decisions about running tests and CI.
• Data Observability Explained for continuous monitoring beyond assertions.

This article focused on hands-on testing. For macro authoring, selectors, and advanced Jinja patterns, defer to our expert macro guide. For interview-style questions on types of tests and selection syntax, see the brief roundups in our resources, but avoid duplicating that depth here. Whether you prefer dbt Cloud or the dbt core CLI, the mechanics of dbt tests are the same: define assertions, select the right scope, and make failures actionable. That’s how teams use dbt to sustain data quality across evolving models.

Want to drill these skills with realistic prompts and grading? Try the free practice exercises at /practice.