dbt Test Generator

Quick Start

Generate comprehensive tests for dbt models including schema tests, data quality tests, and freshness checks.

Instructions

Step 1: Add schema tests

# models/schema.yml
version: 2

models:
  - name: stg_orders
    description: Staging orders table
    columns:
      - name: order_id
        description: Unique order identifier
        tests:
          - unique
          - not_null
      
      - name: customer_id
        description: Customer reference
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      
      - name: order_status
        description: Order status
        tests:
          - accepted_values:
              values: ['pending', 'processing', 'shipped', 'delivered', 'cancelled']
      
      - name: order_total
        description: Total order amount
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0

Step 2: Create custom data tests

-- tests/assert_positive_revenue.sql
select
    order_id,
    order_total
from {{ ref('fct_orders') }}
where order_total < 0

Step 3: Configure freshness checks

# models/sources.yml
version: 2

sources:
  - name: raw
    database: analytics
    schema: raw_data
    tables:
      - name: orders
        description: Raw orders data
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
        loaded_at_field: created_at

Step 4: Run tests

# Run all tests
dbt test

# Run tests for specific model
dbt test --select stg_orders

# Run specific test type
dbt test --select test_type:unique
dbt test --select test_type:not_null

Test Types

Schema tests:

• unique
• not_null
• accepted_values
• relationships

dbt_utils tests:

• accepted_range
• at_least_one
• cardinality_equality
• equal_rowcount
• expression_is_true
• recency
• unique_combination_of_columns

Custom tests:

• SQL queries that return failing rows
• Placed in tests/ directory

Best Practices

1. Test all primary keys (unique, not_null)
2. Test foreign key relationships
3. Test accepted values for categorical columns
4. Add range tests for numeric columns
5. Configure freshness for critical sources
6. Create custom tests for business logic
7. Run tests in CI/CD pipeline
8. Document test expectations