dbt Testing Taxonomy

dbt has five distinct mechanisms for validating data and logic: generic tests, singular tests, unit tests, model contracts, and package-based tests. Each answers a different question.

Generic Tests

Generic tests are reusable, parameterized assertions declared in YAML. dbt ships with four built-in generic tests that form the non-negotiable baseline for every project.

unique validates that a column contains no duplicate values. Apply it to every primary key without exception. Duplicates in primary keys cause join explosions downstream that are painful to diagnose.

not_null ensures required fields are populated. Always pair it with unique on primary keys.

accepted_values constrains categorical columns to an explicit list. Particularly valuable on status fields where an unexpected upstream value (say, "cancelled" spelled differently) silently breaks CASE WHEN logic downstream.

relationships validates referential integrity, confirming foreign key values exist in their parent table. Use it on critical joins to catch orphaned records before they produce NULLs in reports.

models:
  - name: mrt__sales__orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('mrt__sales__customers')
              field: customer_id
      - name: status
        data_tests:
          - accepted_values:
              values: ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled']

You can also write custom generic tests as macros in tests/generic/ using the {% test %} block syntax. Common examples: is_positive for numeric columns, date_not_in_future for timestamps, not_empty_string for text fields where blank strings are as bad as NULLs.

Singular Tests

Singular tests are standalone SQL files in the tests/ directory. The query returns rows that violate a business rule; zero rows means a pass.

-- tests/assert_no_orphaned_orders.sql
select o.order_id
from {{ ref('mrt__finance__orders') }} o
left join {{ ref('mrt__core__customers') }} c
  on o.customer_id = c.customer_id
where c.customer_id is null

Use singular tests when validation logic is too complex for a generic test’s parameters, requires cross-table assertions, or is unique to one specific model. If you find yourself writing the same singular test for multiple models, promote it to a custom generic test instead.

Unit Tests

Native unit tests (dbt 1.8+) validate transformation logic with mocked inputs before any data touches the warehouse. They answer “is my SQL correct?” rather than “is my data healthy?”

unit_tests:
  - name: test_discount_calculation
    model: mrt__finance__orders
    given:
      - input: ref('base__shopify__orders')
        rows:
          - {order_id: 1, subtotal: 100, discount_code: "SAVE20"}
    expect:
      rows:
        - {order_id: 1, discount_amount: 20, final_total: 80}

The given block mocks input refs with static data. The expect block defines what the model should output. dbt builds the model using only mocked inputs and compares actual output to expected output. No warehouse data is queried.

Unit tests are high-effort, high-precision tools. The community consensus is roughly 5-10% of models warrant them, specifically those with:

• Complex CASE WHEN branches where each path needs explicit verification
• Window functions (ranking, running totals, lag/lead) that are notoriously tricky
• Date calculations with edge cases like fiscal year boundaries or timezone conversions
• String parsing or regex where edge cases are easy to miss

For incremental models, unit tests become especially valuable. You can override the is_incremental() macro and mock this (the current table state) to verify merge logic handles deduplication, late-arriving data, and the boundary between full refresh and incremental paths.

Exclude unit tests from production runs (dbt build --exclude-resource-type unit_test) since they use mocked data and add no value there. They belong in CI and development only.

Model Contracts

Contracts enforce schema guarantees at build time. When contract.enforced is true, dbt refuses to materialize a model if its output columns and types don’t match the YAML declaration. Unlike tests that validate after the fact, contracts prevent the table from being created at all.

models:
  - name: mrt__sales__customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: int64
        constraints:
          - type: not_null
          - type: primary_key
      - name: email
        data_type: string
      - name: lifetime_value
        data_type: numeric

A critical nuance: constraint enforcement varies by warehouse. BigQuery and Snowflake treat primary_key and unique constraints as metadata only; they will not reject bad data at insert time. Only not_null is reliably enforced across platforms. Declare informational constraints for documentation and query optimization, but always pair them with actual dbt tests for validation.

Apply contracts to public-facing mart models only: tables that BI tools query, datasets feeding reverse ETL, models shared with other teams. Contracts on base or intermediate models in the three-layer architecture add overhead without meaningful benefit. Combined with model versions, contracts enable non-breaking schema evolution where you can introduce a v2 while maintaining v1 for existing consumers.

Package-Based Tests

Three packages extend dbt’s testing capabilities in distinct ways.

dbt-utils

The official dbt Labs utility package provides tests that arguably should be built-in:

• unique_combination_of_columns: Composite key uniqueness. Essential when your grain is defined by multiple columns.
• expression_is_true: Test any SQL expression. The Swiss army knife for custom assertions like quantity > 0 or end_date >= start_date.
• recency: Verify data freshness on any model without source freshness checks.
• equal_rowcount: Compare row counts between two relations. Invaluable during refactoring to confirm transformations don’t drop rows.

dbt-expectations

Ported from the Great Expectations Python library, this package (now maintained by Metaplane) provides 60+ tests for statistical and pattern-based validation.

The highest-value tests:

• expect_column_values_to_be_between: Range validation. Catches impossible values (negative revenue, conversion rates above 1.0) before they corrupt metrics.
• expect_column_values_to_match_regex: Pattern validation for emails, SKUs, phone numbers, or any format with a known structure.
• expect_column_mean_to_be_between: Catches distribution shifts. Individual values might be valid, but a 50% drop in average order value signals a problem.
• expect_row_values_to_have_recent_data: Freshness checks on any model, not just sources. This alone justifies installing the package.
• expect_compound_columns_to_be_unique: Composite primary key validation.

The row_condition parameter is the package’s killer feature. It lets you apply any test conditionally without custom SQL: test that shipping_date is not null only where status = 'shipped', or validate email format only where email is not null.

Elementary

Elementary takes a fundamentally different approach: anomaly detection instead of static thresholds. Rather than defining “fail if more than 100 nulls,” Elementary learns patterns from your historical data and alerts when metrics deviate beyond expected ranges using Z-score calculations.

• volume_anomalies: Alerts when row counts deviate from historical patterns. No need to guess thresholds that drift as data grows.
• freshness_anomalies: Monitors time between updates adaptively.
• column_anomalies: Tracks column-level metrics (average, null count, cardinality) and flags deviations.
• schema_changes: Detects unexpected column additions, deletions, or type changes.

Elementary is best for catching “unknown unknowns” — the anomalies you wouldn’t think to write explicit tests for.

When to Use What

The decision starts with one question: are you testing logic or data?

Testing intensity should increase toward the edges of your DAG. Sources are where problems enter; marts are where they reach consumers. Base models need primary key tests and basic format checks. Intermediate models need join integrity validation. Marts deserve the heaviest investment: unit tests for complex logic, comprehensive generic tests, contracts for schema stability, and dbt-expectations for business rule enforcement.

The Maturity Path

Start (0-50 models): unique + not_null on every primary key. relationships on critical foreign keys. Source freshness checks. All severity set to error.

Grow (50-200 models): Add dbt-utils and dbt-expectations for coverage gaps. Introduce conditional severity (error in CI, warn in production). Write unit tests for the 3-5 models with the most complex business logic. Tag models with meta.owner for alert routing.

Mature (200+ models): Deploy Elementary for anomaly detection on key business tables. Enable contracts on public-facing marts. Build tiered alerting: page on-call for critical failures, Slack for high-priority, daily digest for medium. Convert every production incident into a permanent test, continuously expanding coverage based on real failures rather than speculation.

Chronically failing tests degrade the signal-to-noise ratio of the test suite. A failing test should either be fixed, have its threshold updated, or be deleted.