test-generator

Generates dbt tests including schema tests, data quality tests, and freshness checks. Use when adding tests to dbt models or implementing data quality validation.

⭐19stars🔀4forks📁View on GitHub🕐Updated Dec 2, 2025

When & Why to Use This Skill

The dbt Test Generator skill automates the creation of comprehensive testing suites for dbt models, including schema validations, data quality checks, and source freshness monitoring. It streamlines the data engineering workflow by generating YAML configurations and SQL assertions, ensuring high data integrity and reliable analytics pipelines.

Use Cases

  • Automating the generation of standard schema tests like unique, not_null, and relationship constraints for new staging models.
  • Implementing advanced data quality checks using dbt_utils for range validation, cardinality, and row count equality across tables.
  • Configuring source freshness checks to proactively monitor data latency and alert on stale data within the warehouse.
  • Creating custom SQL-based data tests to validate complex business logic and specific edge cases within the data transformation layer.
nametest-generator
descriptionGenerates dbt tests including schema tests, data quality tests, and freshness checks. Use when adding tests to dbt models or implementing data quality validation.

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