dbt Testing Tutorial: How to Test Your dbt Models

dbt (data build tool) lets teams write transformations as SQL models inside the warehouse, and it ships with a testing framework. If your project uses dbt, dbt testing is how most data quality checks are written and run. This tutorial covers the test types, the YAML and SQL syntax, and how an ETL tester can use them.

Note: this page explains dbt itself. Our Udemy course does not teach dbt; it teaches the SQL validation thinking that dbt tests are built on.

What Is dbt Testing?

In dbt, a data test is a SQL query that returns the rows that fail an assertion. If the query returns zero rows, the test passes. That is the same pattern ETL testers already use: write a query that finds bad data and expect it to be empty. dbt adds a standard way to declare, run and report those tests. Recent dbt versions also add unit tests, which check model logic against small mocked inputs.

Generic Data Tests (Built-in)

dbt has four built-in generic tests you declare in a model's YAML file: unique, not_null, accepted_values and relationships.

# models/marts/schema.yml
version: 2

models:
  - name: fact_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              values: ['PLACED', 'SHIPPED', 'DELIVERED', 'CANCELLED']
      - name: customer_id
        data_tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_id

Older projects use the key tests: instead of data_tests:; both work in current versions. The relationships test is dbt's referential integrity check: it fails for any customer_id in the fact that does not exist in dim_customer.

Singular Tests (Custom SQL)

For business rules, write a SQL file in the tests/ folder. It fails if it returns any rows:

-- tests/assert_amount_usd_matches_rule.sql
SELECT o.order_id,
       ROUND(s.amount * s.fx_rate, 2) AS expected_usd,
       o.amount_usd                   AS actual_usd
FROM {{ ref('fact_orders') }} o
JOIN {{ ref('stg_orders') }} s ON s.order_id = o.order_id
WHERE ROUND(s.amount * s.fx_rate, 2) <> o.amount_usd

{{ ref() }} is dbt's way of referencing another model, so the test runs against the right schema in every environment.

Package Tests: dbt_utils and dbt-expectations

Community packages add many more generic tests. Install them in packages.yml and run dbt deps:

# packages.yml (pin versions that match your dbt version)
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]

      - name: fact_orders
        data_tests:
          - dbt_utils.unique_combination_of_columns:
              combination_of_columns: ['order_id', 'line_number']
          - dbt_utils.expression_is_true:
              expression: "amount_usd >= 0"

The dbt-expectations package ports many Great Expectations-style checks (row counts, value ranges, regex) to dbt. If you are not on dbt, see our Great Expectations tutorial.

Unit Tests (dbt 1.8+)

Data tests check real data after a build. Unit tests check the model's SQL logic before it touches real data, using mocked input rows and expected output rows:

unit_tests:
  - name: test_amount_usd_conversion
    model: fact_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount: 100, fx_rate: 1.1}
          - {order_id: 2, amount: 50.555, fx_rate: 1}
    expect:
      rows:
        - {order_id: 1, amount_usd: 110.00}
        - {order_id: 2, amount_usd: 50.56}

Unit tests are ideal for tricky rules: rounding, date logic, CASE expressions and edge cases that rarely appear in real data.

Severity, Thresholds and Storing Failures

      - name: email
        data_tests:
          - not_null:
              config:
                severity: warn          # warn instead of failing the run
                warn_if: ">0"
                error_if: ">100"        # fail only above 100 null emails
                store_failures: true    # save failing rows to a table for review

store_failures is especially useful for testers: the failing rows are written to a table, which becomes the evidence for your defect report.

Running dbt Tests

dbt test                          # run all data and unit tests
dbt test --select fact_orders     # tests for one model
dbt test --select test_type:unit  # only unit tests
dbt build                         # run models and tests in dependency order

In CI, dbt build on every pull request stops a bad model from being merged, which is regression testing built into the workflow.

What dbt Testing Means for ETL Testers

dbt does not replace testing judgement: someone still has to decide which rules to test and what the expected results are. That is the ETL tester's job, and the SQL skills transfer directly. For the full set of checks to cover, see the ETL testing checklist.

ETL testing tool tutorials: ETL testing SQL queries · QuerySurge ETL testing · Informatica ETL testing · Great Expectations tutorial · ETL testing tools · Python ETL testing framework

Frequently Asked Questions

What is dbt testing?

dbt testing is the built-in framework in dbt for validating models. Data tests are SQL queries that return failing rows, declared in YAML (generic tests) or written as SQL files (singular tests). Unit tests check model logic with mocked inputs.

What are the four generic tests in dbt?

unique, not_null, accepted_values and relationships. They cover duplicate keys, mandatory columns, valid code lists and referential integrity.

What is the difference between dbt data tests and unit tests?

Data tests run against real data after models are built and return rows that break a rule. Unit tests run before, using small mocked input rows and expected output rows to check the model SQL logic.

Can ETL testers use dbt?

Yes. dbt tests are SQL, so ETL testers can write singular tests for business rules, add generic tests in YAML and review failing rows stored with store_failures.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures