dbt Testing skill for Codex

Done well means: each model’s grain is protected by a uniqueness test, business rules that have broken before are encoded, tricky transformation logic has unit tests with edge cases, consumer-facing models have enforced contracts, and failing tests tell the reader which rows failed and why.

Before writing tests

1. Read AGENTS.md, dbt_project.yml, and packages.yml. dbt --version decides syntax:

   Version	Syntax notes
   < 1.8	Tests under tests:; no unit tests
   1.8 - 1.10.4	data_tests: key, unit_tests: available
   1.10.5+ and Fusion	Generic test inputs nested under arguments:, configs under config:
   Match whatever the project already uses unless asked to migrate.

2. Find the model’s grain and consumers: YAML description, exposures, BI dashboards.
3. Run existing tests first so you know the baseline: dbt test -s <model>.

What to test, by layer

Layer	Required	Add when relevant
Sources	Freshness on tables feeding SLAs; unique + not_null on the source key	accepted_values on enums you branch on
Staging	PK unique + not_null	Type sanity via dbt_utils.expression_is_true
Intermediate	Grain uniqueness after joins (the most common fan-out bug)	Row count vs parent
Marts	Grain test, relationships to dimensions, contract if consumed by other teams	Reconciliation against source totals; unit tests for business logic

Do not blanket every column with not_null. Each test costs warehouse time and alert attention; test what someone downstream relies on.

Generic data tests

# models/marts/_marts.yml  (1.10.5+ syntax)
models:
  - name: fct_orders
    description: One row per order.
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns: [order_id, line_number]
    columns:
      - name: order_id
        data_tests: [not_null]
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'shipped', 'delivered', 'returned']
              config:
                severity: warn
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_customers')
                field: customer_id
              config:
                where: "created_at >= current_date - 30"   # skip legacy rows with known gaps

On versions before 1.10.5, put values, to, field and combination_of_columns directly under the test name instead of under arguments.

Test configs that keep alerts meaningful:

Config	Use
severity: warn	Known-noisy checks you still want visible
warn_if: ">10", error_if: ">100"	Tolerate a handful of bad rows, fail on many
where:	Scope to recent or relevant rows
store_failures: true	Persist failing rows to an audit schema for debugging
limit:	Cap rows returned by large failures

Singular and custom generic tests

A singular test is a SQL file in tests/ that returns failing rows:

-- tests/assert_order_totals_match_payments.sql
select o.order_id, o.amount, p.paid
from {{ ref('fct_orders') }} o
join (select order_id, sum(amount) as paid from {{ ref('stg_payments') }} group by 1) p
  using (order_id)
where abs(o.amount - p.paid) > 0.01

Promote it to a generic test once the same shape appears on several models:

-- tests/generic/is_non_negative.sql
{% test is_non_negative(model, column_name) %}
select {{ column_name }} from {{ model }} where {{ column_name }} < 0
{% endtest %}

Unit tests (1.8+)

Unit tests check transformation logic on fixed inputs without touching real data. Write them for CASE ladders, date math, window functions, and incremental branches.

unit_tests:
  - name: fct_orders_tier_boundaries
    model: fct_orders
    given:
      - input: ref('stg_shop__orders')
        rows:
          - {order_id: 'o1', amount: 99.99,  created_at: '2025-01-01'}
          - {order_id: 'o2', amount: 100.00, created_at: '2025-01-01'}
          - {order_id: 'o3', amount: null,   created_at: '2025-01-01'}
      - input: ref('dim_customers')
        rows: []
    expect:
      rows:
        - {order_id: 'o1', tier: 'low'}
        - {order_id: 'o2', tier: 'mid'}
        - {order_id: 'o3', tier: 'unknown'}

• Only list the columns the logic needs; dbt fills the rest with nulls.
• Test the incremental branch with overrides: {macros: {is_incremental: true}} and a given input for this holding existing rows.
• dbt needs the parents’ column types: in CI build them first with dbt run -s +fct_orders --empty, then dbt test -s test_type:unit.
• Exclude unit tests from production runs: dbt build --exclude-resource-type unit_test.

Contracts

models:
  - name: dim_customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: varchar
        constraints:
          - type: not_null
          - type: primary_key
      - name: signup_date
        data_type: date

• Every column the model returns must be listed with data_type; the build fails on a missing, extra or mistyped column. That is the point: schema changes become deliberate.
• Platforms differ in which constraints they enforce. not_null is enforced widely; primary and foreign keys are metadata only on Snowflake and BigQuery. Keep unique data tests too.
• Removing or retyping a column on a contracted model is a breaking change; slim CI with state:modified flags it. Use model versions: to ship v2 alongside v1.

Source freshness

sources:
  - name: shop
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: orders
      - name: currency_rates
        freshness: null          # static reference table

Run dbt source freshness on a schedule before dbt build; results land in target/sources.json, and dbt build -s source_status:fresher+ --state <prev> rebuilds only what new data affects. Newer versions also accept freshness and loaded_at_field under config:; follow the project’s existing style.

Debugging a failing test

1. dbt test -s <model> --store-failures and query the audit table, or run the compiled SQL in target/compiled/.../<test>.sql directly.
2. Decide: data bug upstream, logic bug in the model, or an outdated rule. Fix the cause; never loosen a threshold just to go green without telling the user why.
3. Re-run dbt build -s <model>+ to confirm downstream tests still pass.

Gotchas

• unique on a column with nulls passes for the nulls; pair it with not_null.
• relationships fails on late-arriving dimensions; scope with where or warn, not delete.
• accepted_values is case-sensitive; normalise in staging.
• Tests run after the model builds in dbt build, so a failing test on a table has already replaced the previous data. Use blue-green or dbt build in a staging schema when that matters.
• dbt_expectations is now maintained by Metaplane under a new package name; check packages.yml before suggesting it. dbt deps needs network.

Verification and report

dbt build -s +fct_orders+ --exclude-resource-type unit_test
dbt test -s test_type:unit
dbt source freshness -s source:shop

**Models covered:** fct_orders, dim_customers
**Added:** grain test (order_id, line_number), 2 relationships, accepted_values (warn), 3 unit tests for tier logic, enforced contract on dim_customers
**Freshness:** shop source warn 6h / error 24h
**Results:** 41 data tests pass, 3 unit tests pass; 1 warn: 12 orders with status 'pending_review' (new value, raised with owners)