dbt Snapshot

What Is a dbt Snapshot?

A dbt snapshot is a declarative configuration that tells dbt to detect changes in a source table, copy the modified rows into a managed table, and record when each version became valid. Unlike an incremental model, a snapshot keeps every historical version of a row, enabling point-in-time analysis and slowly changing dimension (SCD) use cases.

Why Snapshots Matter

Analysts and data engineers frequently need to answer questions such as “What did the customer record look like last month?” or “How many active subscriptions did we have on January 1st?” Traditional ETL pipelines overwrite rows, making historical states hard to reconstruct. dbt snapshots solve this by:

• Preserving history — every update is recorded with dbt_valid_from and dbt_valid_to timestamps.
• Simplifying SCD logic — dbt generates the SQL needed for slowly changing dimension Type 2 storage.
• Enabling auditing — snapshots provide a fully auditable trail of changes, vital for finance, compliance, and debugging.
• Reducing boilerplate — no need to hand-roll CDC SQL or manage triggers; you describe the rules and dbt does the rest.

How Snapshots Work Under the Hood

A snapshot is defined in a snapshots/ directory using Jinja + YAML. During a dbt snapshot run, dbt performs these steps:

1. Compute a unique key per source row (e.g., id).
2. Hash the selected columns (or the whole row) to detect changes.
3. Compare the hash to the most recent version stored in the snapshot table.
4. If the hash differs, close the existing record (dbt_valid_to = current_timestamp) and insert the new version (dbt_valid_from = current_timestamp, dbt_valid_to = null).

Because dbt executes SQL directly in your warehouse, snapshots scale with your database's native performance and partitioning features.

Key Configuration Options

target_schema & target_database

Tell dbt where to create the physical snapshot table. Best practice is to store snapshots in their own schema (e.g., snapshots) to simplify permissioning.

unique_key

The business key that identifies a logical row. Without it, dbt cannot tell which historical chain a record belongs to.

strategy

• timestamp: Detect change when a source updated_at column advances.
• check: Detect change by comparing a list of columns or a hash of the entire row.

Column Selection

Use the updated_at or check_cols parameters to restrict change detection to only the fields you care about, reducing churn and storage cost.

End-to-End Example

Suppose we need to capture historical versions of a customers table that gets nightly backfills.

{% snapshot customers_snapshot %}
{{
config(
target_database='analytics',
target_schema='snapshots',
unique_key='id',
strategy='check',
check_cols=['name', 'email', 'plan', 'company_size']
)
}}
select * from {{ source('stripe', 'customers') }}
{% endsnapshot %}

Running dbt snapshot creates snapshots.customers_snapshot with four extra fields:

• dbt_scd_id — surrogate key for the version
• dbt_updated_at — load timestamp
• dbt_valid_from
• dbt_valid_to

Querying Snapshots

To find the state of a customer on a specific date:

select *
from snapshots.customers_snapshot
where id = 42
and '2024-03-01' between dbt_valid_from and coalesce(dbt_valid_to, '9999-12-31');

If you use the Galaxy SQL editor, you can save the query in a Collection named “Customer History” and let teammates endorse it for downstream analytics.

Best Practices

• Always define a stable unique_key — composite keys are acceptable as long as they never change.
• Use the narrowest column set for change detection to minimize storage.
• Schedule snapshots after source loads to avoid capturing intermediate states.
• Add tests to ensure dbt_valid_to is null for only one current row per unique_key.
• Partition & cluster large snapshots on dbt_valid_from or unique_key for query speed.

Common Misconceptions

• “Snapshots are the same as incremental models.” Incremental models overwrite old rows; snapshots preserve every version.
• “I need CDC logs to build SCD tables.” dbt snapshots work with plain tables; no database-level CDC is required.
• “Snapshots are slow and expensive.” When configured with selective columns and proper partitioning, they are usually only a few seconds per run and cost pennies.

Integrating Snapshots with Galaxy

After a dbt job completes, your warehouse contains snapshot tables like snapshots.customers_snapshot. Open Galaxy, connect to the warehouse, and:

1. Use the sidebar metadata explorer to find the snapshot table.
2. Write point-in-time queries with Galaxy’s AI Copilot to autocomplete date predicates.
3. Save the query to a team Collection and endorse it, ensuring everyone uses a single, trusted definition of “customer on date X.”

Conclusion

dbt snapshots offer a simple, declarative route to historical data tracking. By combining snapshots with modern tooling like Galaxy, teams gain both trusted history and a collaborative environment to explore it.