/testing-dbt-models

name: testing-dbt-models
description: |
  Adds schema tests and data quality validation to dbt models. Use when working with dbt tests for:
  (1) Adding or modifying tests in schema.yml files
  (2) Task mentions "test", "validate", "data quality", "unique", "not_null", or "accepted_values"
  (3) Ensuring data integrity - primary keys, foreign keys, relationships
  (4) Debugging test failures or understanding why dbt test failed
  Matches existing project test patterns and YAML style before adding new tests.

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