dbt Tests Overview

dbt tests are automated data quality checks that verify your data meets specific expectations. Running tests catches problems before bad data reaches dashboards, reports, or business decisions. A dbt test is either a SQL query that returns zero rows when data is good, or a YAML-defined check that dbt translates into SQL automatically.

Why Testing Data Matters

Raw data arrives with problems. Orders come in with no customer ID. Customer IDs appear in the orders table but not in the customers table. Product prices are negative. Revenue figures double because the pipeline ran twice. Without tests, these problems reach analysts silently and produce wrong numbers that look correct.

Pipeline without tests:
  Raw data → Models → Dashboard
  (bad data passes through undetected)

Pipeline with tests:
  Raw data → Sources → Models → TESTS → Dashboard
                        ↓ test fails here, alert fired, dashboard not updated

Two Types of Tests

Generic Tests

Generic tests are pre-built checks you apply to columns using YAML configuration. You do not write SQL. You just declare which test to apply to which column. dbt ships with four built-in generic tests.

Singular Tests

Singular tests are custom SQL queries you write inside .sql files in the tests/ folder. Any row returned by the query counts as a test failure. Use singular tests for complex business rule checks that the generic tests cannot express.

The Four Built-In Generic Tests

not_null

Checks that a column has no NULL values.

columns:
  - name: order_id
    tests:
      - not_null

unique

Checks that every value in a column is different. No duplicates allowed.

columns:
  - name: order_id
    tests:
      - unique

accepted_values

Checks that every value in a column belongs to a defined list.

columns:
  - name: status
    tests:
      - accepted_values:
          values: ['pending', 'completed', 'cancelled', 'refunded']

relationships

Checks referential integrity. Every value in a foreign key column must exist in the referenced table's column.

columns:
  - name: customer_id
    tests:
      - relationships:
          to: ref('stg_customers')
          field: customer_id

Where Tests Live

Test Type         Location
-----------       --------
Generic tests     Declared in .yml files inside models/
Singular tests    .sql files inside tests/
Source tests      Declared in sources.yml inside models/

A Complete schema.yml with Tests

# models/staging/schema.yml
version: 2

models:
  - name: stg_orders
    description: "One row per order from the ecommerce platform"
    columns:
      - name: order_id
        description: "Primary key"
        tests:
          - not_null
          - unique
      - name: customer_id
        description: "Foreign key to stg_customers"
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: status
        description: "Current order status"
        tests:
          - not_null
          - accepted_values:
              values: ['pending', 'completed', 'cancelled']
      - name: amount_dollars
        description: "Order total in USD"
        tests:
          - not_null

Running Tests

# Run all tests in the project
dbt test

# Run tests for a specific model
dbt test --select stg_orders

# Run tests for all models in a folder
dbt test --select staging

# Run only one test type
dbt test --select test_type:not_null

# Run source tests only
dbt test --select source:ecommerce

Reading Test Output

$ dbt test

Running 8 tests

1 of 8 PASS unique_stg_orders_order_id .................. [PASS in 0.3s]
2 of 8 PASS not_null_stg_orders_order_id ................ [PASS in 0.2s]
3 of 8 PASS not_null_stg_orders_customer_id ............. [PASS in 0.2s]
4 of 8 FAIL relationships_stg_orders_customer_id ........ [FAIL 3 in 0.4s]
5 of 8 PASS accepted_values_stg_orders_status ........... [PASS in 0.3s]
...

Failure in test relationships_stg_orders_customer_id:
  Got 3 results, configured to fail if != 0
  Query: select * from dbt_dev.stg_orders where customer_id not in
         (select customer_id from dbt_dev.stg_customers)

Test 4 failed because three orders reference customer IDs that do not exist in the customers table. dbt shows the exact SQL it ran so you can investigate in the warehouse directly.

Test Severity: warn vs error

By default, a failing test stops your pipeline. You can configure tests to produce a warning instead of an error so the pipeline continues running:

columns:
  - name: customer_id
    tests:
      - not_null:
          severity: warn       ← warns but does not fail the run
      - relationships:
          to: ref('stg_customers')
          field: customer_id
          severity: error      ← fails the run (default behavior)

dbt build: Run + Test Together

The dbt build command runs models AND tests in the correct DAG order. If a model's tests fail, dbt skips all downstream models. This prevents broken data from propagating.

dbt build

# What it does in DAG order:
# 1. Run stg_orders → test stg_orders → run fct_orders (only if tests pass)
# 2. Run stg_customers → test stg_customers → run dim_customers (only if tests pass)

Storing Test Results

You can configure dbt to save test failure rows to the warehouse for later analysis:

# dbt_project.yml
tests:
  +store_failures: true
  +store_failures_as: table

When a test fails, dbt creates a table in your warehouse containing the exact failing rows. This makes it easy to investigate the root cause without re-running the test manually.