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.
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.
| name | test-generator |
|---|---|
| description | Generates 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
- Test all primary keys (unique, not_null)
- Test foreign key relationships
- Test accepted values for categorical columns
- Add range tests for numeric columns
- Configure freshness for critical sources
- Create custom tests for business logic
- Run tests in CI/CD pipeline
- Document test expectations