Skip to main content

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.yml or 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 columns
  • dbt_valid_from / dbt_valid_to columns store historical validity
  • dbt_updated_at tracks 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