dbt Data Tests

dbt distinguishes unit tests from data tests. A unit test supplies small static inputs and expected rows to validate model SQL before materialization. A data test queries a built resource for failing records; zero failures passes. Tests turn stated assumptions into evidence, but passing checks cover only the encoded conditions.

Prerequisites

Complete dbt Models and Sources and Pipeline Observability.

Learning goals

• Match unit, generic data, and singular data tests to different risks.
• Use current data_tests: and unit_tests: syntax.
• Interpret a failed assertion as actionable records, not just a red status.
• Place quality gates before publication.

Worked example

The model properties enforce the event grain and customer relationship. The unit test exercises signed amount conversion with a static input; unit-test YAML belongs under a configured model path, not the singular-test directory.

version: 2
models:
  - name: fct_events
    columns:
      - name: event_id
        data_tests: [unique, not_null]
      - name: customer_key
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_customer')
                field: customer_key

unit_tests:
  - name: signed_amount_is_converted_to_cents
    model: stg_events
    given:
      - input: source('raw', 'events')
        rows:
          - {event_id: e-1, amount: '-2.50'}
    expect:
      rows:
        - {event_id: e-1, amount_cents: -250}

A singular data test is SQL that returns violations. For example, a mart reconciliation test can return dates where the mart differs from its fact input. Keep failures queryable, attach owner and severity, and block publication for invariants that would mislead consumers.

The raw fixture intentionally contains duplicate delivery e-101. A raw uniqueness test should fail and identify it. After deterministic deduplication, the fact-table uniqueness test should pass. Moving the test downstream without preserving the raw failure count would hide an ingestion symptom.

Limitations and common mistakes

• not_null and unique do not validate business meaning.
• A relationship test may permit nulls unless not_null is separate.
• Warning thresholds can normalize growing quality problems if nobody owns them.
• Unit fixtures can miss platform-specific SQL behavior; integration checks still matter.
• Testing only final marts makes it harder to locate the first broken boundary.

Practice

Write a singular test for daily reconciliation between fct_events and a daily mart. State whether one-cent rounding differences are allowed and why.

Next steps

Continue with Data Contracts and Lineage to coordinate model changes with downstream consumers.

Primary sources

Checked 2026-10-15: dbt data tests, unit tests, test properties, and dbt build.