dbt Testing

Every model deserves at least one test. Primary keys need unique + not_null.

Workflow

1. Study Existing Test Patterns

CRITICAL: Match the project's existing testing style before adding new tests.

Find all schema.yml files with tests

find . -name "schema.yml" -exec grep -l "tests:" {} ;

Read existing tests to learn patterns

cat models/staging/schema.yml | head -100 cat models/marts/schema.yml | head -100

Check for custom tests or dbt packages

ls tests/ cat packages.yml 2>/dev/null

Extract from existing tests:

• YAML formatting style (indentation, spacing)

• Test coverage depth (all columns vs key columns only)

• Use of custom tests (dbt_utils, dbt_expectations, custom macros)

• Description style (brief vs detailed)

• Severity levels used (warn vs error)

2. Read Model SQL

cat models/<path>/<model_name>.sql

Identify: primary keys, foreign keys, categorical columns, date columns, business-critical fields.

3. Check Existing Tests for This Model

cat models/<path>/schema.yml | grep -A 50 "<model_name>"

or

find . -name "schema.yml" -exec grep -l "<model_name>" {} ;

4. Identify Testable Columns

Column Type Recommended Tests

Primary key unique , not_null

Foreign key not_null , relationships

Categorical accepted_values (ask user for valid values)

Required field not_null

Date/timestamp not_null

Boolean accepted_values: [true, false]

5. Write Tests in schema.yml

Match the existing style from step 1. Example format (adapt to project):

version: 2

models:

• name: model_name description: "Brief description of what this model contains" columns:

  • name: primary_key_column description: "Unique identifier for this record" tests:

    • unique
    • not_null

  • name: foreign_key_column description: "Reference to related_model" tests:

    • not_null
    • relationships: to: ref('related_model') field: related_key_column

  • name: status description: "Current status of the record" tests:

    • not_null
    • accepted_values: values: ['pending', 'active', 'completed', 'cancelled']

  • name: created_at description: "Timestamp when record was created" tests:

    • not_null

6. Run Tests

Test specific model

dbt test --select <model_name>

Test with upstream

dbt test --select +<model_name>

7. Fix Failing Tests

Common failures and fixes:

Failure Likely Cause Fix

unique fails Duplicate records Add deduplication in model

not_null fails NULL values in source Add COALESCE or filter

relationships fails Orphan records Add WHERE clause or fix upstream

accepted_values fails New/unexpected values Update accepted values list

Test Types Reference

Generic Tests (built-in)

tests:

• unique
• not_null
• accepted_values: values: ['a', 'b', 'c']
• relationships: to: ref('other_model') field: id

Custom Generic Tests

tests:

• dbt_utils.expression_is_true: expression: "amount >= 0"
• dbt_utils.recency: datepart: day field: created_at interval: 1

Singular Tests

Create tests/<test_name>.sql :

-- tests/assert_positive_revenue.sql select * from {{ ref('orders') }} where revenue < 0

Anti-Patterns

• Adding tests without checking existing project patterns first

• Using different YAML formatting style than existing tests

• Models without any tests

• Primary keys without both unique AND not_null

• Testing only obvious columns, ignoring business-critical ones

• Hardcoding accepted_values without confirming with stakeholders

• Adding dbt_utils tests when project doesn't use that package