A Complete Guide to dbt Snapshots: Track Historical Data with Slowly Changing Dimensions (SCD Type 2)

If you’ve ever built a data warehouse, you’ve probably faced this question:

How do you preserve the history of records that keep changing?

Imagine a customer changes their address, a product price increases, or an employee moves to a different department. Most operational databases simply overwrite the old values with the new ones. While this works for transactional systems, it’s a major problem for analytics.

Questions like these become impossible to answer:

• Where did this customer live six months ago?
• What was the product price during last year’s holiday sale?
• Which department was an employee in when a transaction occurred?

This is exactly the problem dbt Snapshots are designed to solve.

In this article, you’ll learn how dbt snapshots work, how they implement Slowly Changing Dimensions (SCD Type 2), how to configure them, and the performance considerations you should keep in mind when using them in production.

What Are dbt Snapshots?

A snapshot is a feature in dbt that captures changes in mutable source tables over time.

Instead of updating an existing row when data changes, dbt creates a new version of the record while preserving the previous version.

For example, suppose your source table initially contains the following data:

customer_id | customer_name | city
 101        | Alice.        | London

A month later, Alice moves to Paris.

The source table now looks like this:

customer_id | customer_name | city
 101        | Alice.        | Paris

Without snapshots, the original value (“London”) is gone forever.

With dbt snapshots, the historical record is retained.

customer_id | city  | dbt_valid_from | dbt_valid_to
    101     | London| 2025-01-01     | 2025-02-01
    101     | Paris | 2025-02-01     | NULL 

The latest version always has a NULL value for dbt_valid_to, indicating that it is the current active record.

Understanding Slowly Changing Dimensions (SCD Type 2)

A Slowly Changing Dimension (SCD) is a data warehousing technique used to manage historical changes in dimension tables.

There are multiple SCD types, but Type 2 is by far the most common because it preserves every historical version of a record.

Instead of updating an existing row, SCD Type 2 inserts a new one.

For example:

Product | Price | Valid From | Valid To
Laptop  | $999  | Jan 1      | Mar 15
Laptop  | $1,099| Mar 15     | NULL

Both versions remain available for historical analysis.

This allows analysts to answer questions such as:

• What was the product price at the time of purchase?
• How many customers changed regions this year?
• Which employee managed an account during a specific month?

Fortunately, dbt snapshots automatically implement this SCD Type 2 pattern without requiring you to write complex SQL.

Snapshot Strategies in dbt

dbt supports two strategies for detecting changes.

1. Timestamp Strategy (Recommended)

The timestamp strategy relies on an updated_at column in your source table.

Whenever the timestamp changes, dbt assumes the record has changed and creates a new snapshot version.

Example configuration:

{% snapshot customers_snapshot %}
{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at'
    )
}}
select *
from {{ source('crm','customers') }}
{% endsnapshot %}

Why it’s recommended

The timestamp strategy offers several advantages:

• Faster execution
• Less computation
• Simpler configuration
• Better scalability
• Ideal for large production datasets

As long as your source system maintains a reliable updated_at column, this should be your default choice.

2. Check Strategy

Not every source table includes an update timestamp.

In such cases, dbt provides the check strategy.

Instead of monitoring timestamps, dbt compares one or more columns.

{{
config(
    strategy='check',
    unique_key='customer_id',
    check_cols=['name','city']
)
}}

Whenever either the customer’s name or city changes, dbt creates a new snapshot record.

When should you use it?

Use the check strategy only when no trustworthy timestamp exists.

Keep in mind that it requires dbt to compare column values, making it slower than the timestamp strategy — especially for large tables.

Metadata Columns Added by dbt

Every snapshot table automatically includes metadata columns.

ColumnPurposedbt_valid_fromStart of record validitydbt_valid_toEnd of record validitydbt_updated_atTimestamp used for change detectiondbt_scd_idInternal snapshot identifier

Get Devendra’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

These columns enable point-in-time analysis and simplify historical reporting.

Querying Current Records

To retrieve only the latest version of every record:

SELECT *
FROM snapshots.customers_snapshot
WHERE dbt_valid_to IS NULL;

Querying Historical Records

To view the history of a specific customer:

SELECT *
FROM snapshots.customers_snapshot
WHERE customer_id = 101
ORDER BY dbt_valid_from;

This returns every version of the customer’s record in chronological order.

Point-in-Time Queries

One of the biggest advantages of snapshots is the ability to reconstruct data exactly as it existed on a particular date.

SELECT *
FROM snapshots.customers_snapshot
WHERE dbt_valid_from <= '2025-03-15'
AND (
      dbt_valid_to > '2025-03-15'
      OR dbt_valid_to IS NULL
);

This capability is invaluable for financial reporting, compliance, audits, and reproducible analytics.

Configuring dbt Snapshots

A typical snapshot configuration looks like this:

{{
config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
)
}}

Here’s what each parameter controls:

• target_schema — Where snapshot tables will be stored.
• unique_key — The business key that uniquely identifies each record.
• strategy — Either timestamp or check.
• updated_at — Timestamp column used with the timestamp strategy.
• check_cols — Columns to compare when using the check strategy.

Running Snapshots

Creating snapshots is straightforward.

Run every snapshot in your project:

dbt snapshot

Or execute a specific snapshot:

dbt snapshot --select customers_snapshot

During every execution, dbt compares the current source data with previously captured records and inserts new versions whenever changes are detected.

Performance Considerations

Snapshot tables grow continuously as more historical versions are added. Keeping them efficient requires a few best practices.

1. Prefer the Timestamp Strategy

Timestamp comparisons are significantly cheaper than comparing multiple columns.

If an updated_at field exists, use it.

2. Choose a Stable Unique Key

The unique_key should never change.

Changing business keys can produce duplicate histories and inaccurate snapshot records.

3. Snapshot Only Important Tables

Not every source table requires historical tracking.

Focus on tables where historical analysis provides business value, such as:

• Customers
• Products
• Pricing
• Employees
• Subscription plans

4. Minimize Checked Columns

If you’re using the check strategy, compare only the columns that truly matter.

Avoid using check_cols='all' unless absolutely necessary.

5. Optimize Your Data Warehouse

As snapshot tables grow, warehouse optimization becomes increasingly important.

Depending on your platform, consider:

• Partitioning snapshot tables
• Clustering by business keys
• Clustering by dbt_valid_from
• Clustering by dbt_valid_to

These optimizations reduce scan costs and improve query performance.

6. Schedule Snapshots Wisely

Running snapshots every few minutes rarely provides additional business value.

Instead, align your schedule with how frequently your source data changes.

Hourly, daily, or nightly runs are often sufficient.

7. Monitor Storage Growth

Unlike regular tables, snapshot tables never shrink on their own.

Every change produces another row.

As history accumulates, monitor storage costs and archive historical data if your retention policies allow.

Best Practices

Before using snapshots in production, keep these recommendations in mind:

• Prefer the timestamp strategy whenever possible.
• Use immutable business keys.
• Snapshot only high-value tables.
• Validate source data quality.
• Test snapshot logic regularly.
• Monitor runtime and storage growth.
• Document your snapshot strategy for future maintainers.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Devendra
Senior Data Engineer · Data Platforms · Cloud · Big Data

🌐 https://devendra.tech · 💻 https://github.com/devendra631997 · 🔗LinkedIn