testing-dbt-models

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