02. Tests and Snapshots
Tests and Snapshots in dbt
In real-world dbt projects, data quality and historical tracking are essential.
Tests ensure your models produce accurate results, while snapshots allow you to track changes over time.
1. Implementing dbt Tests
Common test types:
- Unique – Ensure a column contains unique values
- Not null – Ensure no missing values
- Relationships – Validate foreign key relationships
- Custom tests – SQL-based tests for specific business rules
Example YAML for a fact table:
version: 2
models:
- name: fct_orders
description: "Fact table for customer orders"
columns:
- name: order_id
description: "Primary key for orders"
tests:
- unique
- not_null
- name: customer_id
description: "Customer identifier"
tests:
- not_null
- relationships:
to: ref('dim_customers')
field: customer_id
2. Custom Tests
- Create SQL-based tests for business-specific rules
-- tests/order_amount_positive.sql
select *
from {{ ref('fct_orders') }}
where order_amount < 0
- Add to
dbt_project.ymlor reference in models for execution
3. Implementing Snapshots
Snapshots track historical changes for SCD Type 2 patterns:
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['email', 'first_name', 'last_name']
)
}}
select * from {{ source('app', 'customers') }}
{% endsnapshot %}
strategy='check'→ Detects changes in specified columnsdbt_valid_from/dbt_valid_tocolumns store historical validitydbt_updated_attracks last change
4. Running Tests and Snapshots
# Run all models
dbt run
# Execute all tests
dbt test
# Capture snapshots
dbt snapshot
- Ensure snapshots run after source tables are updated
- Test results highlight data quality issues early
Best Practices
Always define unique keys for snapshots
Schedule snapshots and tests in orchestration tools
Use custom tests for business logic validation
Monitor test failures and address them promptly
Combine tests and snapshots for robust, reliable pipelines