Tests — Built-in and Custom

Tests are dbt's safety net. Models without tests are the ones that silently break.

Generic tests (YAML)

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

      - name: order_status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'shipped', 'cancelled', 'refunded']

Four built-ins:

• unique — column values are unique.
• not_null — no NULL values.
• accepted_values — only listed values appear.
• relationships — FK existence check.

Run with dbt test. Failure = rows returned by the test SELECT.

Custom SQL tests

-- tests/no_orders_with_negative_revenue.sql
SELECT * FROM {{ ref('fct_orders') }} WHERE revenue < 0

Zero rows = pass. Custom tests encode business invariants:

-- tests/orders_match_payments.sql
SELECT order_id
FROM {{ ref('fct_orders') }} o
LEFT JOIN {{ ref('fct_payments') }} p USING (order_id)
WHERE p.payment_id IS NULL AND o.order_status = 'paid'

Custom generic tests (macros)

{% test no_negative_values(model, column_name) %}
    SELECT * FROM {{ model }} WHERE {{ column_name }} < 0
{% endtest %}

Use in YAML:

columns:
  - name: revenue
    tests:
      - no_negative_values

What's worth testing

1. Primary key uniqueness on dims and facts.
2. Not-null on join keys.
3. Referential integrity via relationships.
4. Domain constraints via accepted_values.
5. Recency via dbt_utils.recency.
6. Row count plausibility.
7. Business invariants via custom SQL.

What's NOT worth testing

• Trivial casts the engine validates.
• Specific row counts (fragile).
• Warehouse-enforced constraints.

Severity

tests:
  - not_null:
      severity: warn      # warn only
  - unique:
      severity: error     # default — fail the build

Source tests

Catch issues BEFORE they propagate:

sources:
  - name: shopify
    tables:
      - name: orders
        columns:
          - name: id
            tests:
              - unique
              - not_null

dbt build vs dbt run + dbt test

dbt build interleaves run + test per model in DAG order. Failed upstream tests stop downstream models. Use this in production.

Common mistakes

1. No tests at all.
2. Only generic tests (no business invariants).
3. Tests only on sources (marts get no validation).
4. severity: warn everywhere (gets ignored).
5. Slow tests (full-table on billions of rows).

Takeaway

Every model gets tests. Generic for column-level; custom SQL for business invariants. dbt build runs them in DAG order. Aim for tests that catch real bugs.

dbt 1.8+ Native Unit Testing: Testing SQL Logic Without Warehouse Compute

Historically, dbt tests were exclusively data tests (generic tests like unique and not_null, or singular SQL tests). Data tests run queries against physical tables in your data warehouse. While essential for validating live data integrity, they have two fundamental limitations:

1. They require warehouse compute and time to run against large datasets.
2. They cannot easily validate tricky edge cases (e.g. complex regex, boundary conditions, leap-year calculations) unless those edge cases already happen to exist in your warehouse data.

Beginning in dbt 1.8, dbt introduced native Unit Testing. Unit tests allow analytics engineers to test the SQL logic of a model in isolation using static mock inputs defined directly in YAML or CSV fixtures, without querying raw warehouse tables.

How Unit Tests Work

You declare unit tests in a YAML file in your models/ directory:

unit_tests:
  - name: test_is_high_value_order
    description: "Verify high-value threshold classification and NULL handling"
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, amount_cents: 50000, status: 'completed'}  # Above threshold
          - {order_id: 2, amount_cents: 9999,  status: 'completed'}  # Below threshold
          - {order_id: 3, amount_cents: 50000, status: 'cancelled'}  # Cancelled (should be false)
          - {order_id: 4, amount_cents: null,  status: 'completed'}  # NULL handling
    expect:
      rows:
        - {order_id: 1, is_high_value: true}
        - {order_id: 2, is_high_value: false}
        - {order_id: 3, is_high_value: false}
        - {order_id: 4, is_high_value: false}

Running Unit Tests

# Run all unit tests
dbt test --select "test_type:unit"

# Run unit tests for a specific model
dbt test --select "fct_orders,test_type:unit"

Unit Tests vs Data Tests

Dimension	Unit Tests (dbt 1.8+)	Data Tests (Generic / Singular)
What it validates	SQL transformation logic & business calculations	Quality and integrity of data in warehouse tables
Input data	Mocked static rows in YAML or CSV	Real warehouse data loaded by EL pipelines
Execution speed	Fast (tiny mock datasets)	Slower (scans warehouse tables)
Edge cases	Trivial to test synthetic scenarios	Difficult (requires real rows with anomalies)
When to use	Complex window functions, CASE statements, regex	Uniqueness, foreign keys, not-null invariants