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.

bash

# 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

bash

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

bash

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):

yaml

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

bash

# 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)

yaml

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

Custom Generic Tests

yaml

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:

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