CtrlK
BlogDocsLog inGet started
Tessl Logo
PluginPublic

testland/dbt-testing

Use for any question or task about testing dbt models, including adding tests to a model's YAML and bad data such as duplicate IDs that a passing dbt build missed: generic, singular and custom-macro tests, model assertions, reading failures from run_results.json and gating dbt build on the results in CI.

Quality

89%

Does it follow best practices?

Run evals on this skill

Adds up to 20 points to the overall score

View guide
SecuritybySnyk

Low

Low-risk findings worth noting

Overview
Quality
Evals
Security
Files
name:
dbt-testing
description:
Use for any question or task about testing dbt models, including adding tests to a model's YAML and bad data such as duplicate IDs that a passing dbt build missed: generic, singular and custom-macro tests, model assertions, reading failures from run_results.json and gating dbt build on the results in CI.

dbt-testing

Overview

dbt distinguishes data tests (assertions on rows, e.g. unique / not_null) from unit tests (logic tests on transformations); this skill targets data tests. The YAML key was renamed from tests: to data_tests: to disambiguate; tests: remains a back-compat alias but you cannot have both on the same resource at the same time (data-tests).

When to use

  • The repo contains a dbt_project.yml or a models/ directory with .sql files referencing {{ ref(...) }} / {{ source(...) }}.
  • The user asks about generic tests (unique, not_null, accepted_values, relationships) - the four built-in generics shipped with dbt (data-tests).
  • The user mentions dbt build failures, schema drift, or wants assertions on data flowing through models.
  • A CI workflow needs a quality gate that fails the pipeline when a model ships bad data.

Authoring tests

Generic tests in schema.yml

Use data_tests: (canonical) - tests: is the legacy alias and cannot coexist with data_tests: on the same resource (data-tests).

version: 2
models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('customers')
                field: id

The arguments: key under parameterized generics (accepted_values, relationships) is the canonical syntax in current dbt (data-tests).

Singular tests

Singular tests live as .sql files under tests/ (or a custom test-paths config). Each file is one select that returns failing rows - empty result set means pass (data-tests).

tests/assert_payment_total_non_negative.sql:

-- Returns rows where the aggregated payment total is negative;
-- empty result set = test passes.
select order_id, sum(amount) as total_amount
from {{ ref('fct_payments') }}
group by 1
having sum(amount) < 0

Trap: dbt requires you to omit trailing semicolons in singular test SQL - a ; at the end can cause the test to fail spuriously (data-tests). This is unlike most SQL editors which add the semicolon by default.

Custom generic tests via macros

Reusable assertions are defined as Jinja {% test %} blocks that take model and column arguments and return a select of failing rows (data-tests):

{% test column_value_in_range(model, column_name, min_value, max_value) %}
select *
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}
{% endtest %}

Reference once defined, then call by name in data_tests:. See references/macros.md for the canonical macro shapes (range checks, conditional uniqueness, recency / freshness, external referential integrity).

What a green dbt build still lets through

Check these before calling a model "tested". Each one passes silently by design, so a model can ship bad data with every test green.

GapWhy the tests passFix
NULLs in an accepted_values columnThe test "validates that all of the non-null values in a column are present" in the list, so NULLs are never checked (data-test-props)Add not_null beside it. Map unknown codes to a value the list rejects (else status_code), not to NULL, so a new code fails a test
NULL foreign keysrelationships "automatically excludes NULL values from validation" (data-test-props)Add not_null; if NULL is legitimate (guest checkout), map it to an explicit key or flag and test that
A source that stopped loadingdbt build "does not include source freshness checks" (freshness)Configure freshness on the source and run dbt source freshness as its own CI step before dbt build
Late rows in an incremental modelA filter on the event's own timestamp (where viewed_at > (select max(viewed_at) from {{ this }})) drops rows that arrive late; data tests on the target never see the missing rowsFilter on the load timestamp (or a lookback window deduplicated by unique_key) and prove it with an incremental-mode unit test (below)

Source freshness, with both keys under config: (freshness moved to config in v1.9, loaded_at_field in v1.10) (freshness-config):

sources:
  - name: shop_raw
    config:
      loaded_at_field: _loaded_at
      freshness:
        warn_after: {count: 12, period: hour}
        error_after: {count: 24, period: hour}
    tables:
      - name: orders
dbt source freshness   # fails the job when error_after is exceeded
dbt build

Unit tests for incremental models

Unit tests check transformation logic on fixed input rows (unit-tests). For an incremental model, test both modes by overriding the is_incremental macro, and give this as an input holding the rows already in the table. The expect rows are "the result of the materialization (what will be merged/inserted), not the resulting model itself" (unit-tests), so list only the rows the model's SELECT returns in that run:

unit_tests:
  - name: fct_page_views_includes_late_arrivals
    model: fct_page_views
    overrides:
      macros:
        is_incremental: true
    given:
      - input: ref('stg_page_views')
        rows:
          - {view_id: 10, viewed_at: 2026-03-02 08:00:00, loaded_at: 2026-03-02 08:01:00}
          - {view_id: 11, viewed_at: 2026-02-27 17:30:00, loaded_at: 2026-03-02 09:00:00}  # arrived late
      - input: this
        rows:
          - {view_id: 10, viewed_at: 2026-03-02 08:00:00, loaded_at: 2026-03-02 08:01:00}
    expect:
      rows:   # only what this run inserts or merges
        - {view_id: 11, viewed_at: 2026-02-27 17:30:00, loaded_at: 2026-03-02 09:00:00}

Run unit tests in development and CI only. dbt Labs "strongly recommends only running unit tests in development or CI environments" because their inputs are static; exclude them from production builds (unit-tests):

dbt build --exclude-resource-type unit_test   # production job
dbt test --select "test_type:unit"            # CI or local, on demand

Running

dbt test runs data tests defined on models, sources, snapshots, seeds, plus unit tests on SQL models (dbt-test).

# Run every data test in the project
dbt test --select test_type:data

# Run every test (data + unit) on a specific model
dbt test --select orders

# Run only singular tests
dbt test --select test_type:singular

# Run only generic tests
dbt test --select test_type:generic

# Combine selectors with comma (intersection)
dbt test --select "orders,test_type:data"

# Run tests for everything in a package
dbt test --select "my_package.*"

dbt build is the integrated command that runs models, tests, snapshots, seeds, and user-defined functions in DAG order. Critically, a failing upstream test causes downstream resources to skip - so dbt build is the right command for a pipeline that should not propagate bad data (dbt-build):

dbt build                                    # full project, DAG order
dbt build --select state:modified+           # changed nodes + descendants
dbt build --empty                            # zero-row dry run for validation
dbt build --select "test_type:unit"          # unit-test-only build subset

Severity can be relaxed from error to warn on a per-test basis to prevent a single test from cascading skips downstream (dbt-build).

Parsing failures

Both dbt test and dbt build produce run_results.json in the dbt artifacts directory. Each result entry is a node-level record with (run-results-json):

FieldMeaning
unique_idNode identifier (e.g. test.<project>.<test_name>); maps to manifest.json.
statusdbt's interpretation of runtime success / failure / error.
execution_timeTotal node execution duration (seconds).
timingPer-phase breakdown (compile, execute).
messageHuman-readable CLI message based on adapter output.
failuresCount of test failures (rows returned by the test SELECT).
adapter_responseDatabase-returned metadata (varies by warehouse adapter).
compiledWhether the node compiled successfully.
compiled_codeThe rendered SQL string (useful for debugging).
relation_nameFully-qualified database object name.

A failing data test typically appears as status: "fail" with failures: <N> (rows returned). A test that errored before producing rows (e.g. a SQL compilation error) appears differently - handle both when parsing.

Quick triage with jq:

# List all failing test nodes
jq -r '.results[] | select(.status=="fail") | .unique_id + " (" + (.failures|tostring) + " failures)"' \
  target/run_results.json

# Surface the first 5 failure messages
jq -r '.results[] | select(.status=="fail") | .message' target/run_results.json | head -5

CI integration

Use dbt build (not dbt test alone) so the pipeline halts at the first failed assertion in the DAG and skips downstream nodes. Always upload target/run_results.json (and target/manifest.json) as an artifact so the failing-test list survives the run.

See references/ci-integration.md for ready-to-paste workflow snippets for GitHub Actions, GitLab CI, and Jenkins, plus the --store-failures and slim-CI patterns that scope tests to changed nodes.

References

Workspace
testland
Visibility
Public
Created
Last updated
Source
GitHub Actions
Repository
https://github.com/testland/qaVerified
Badge
testland/dbt-testing badge