dbt Snapshots Behind the Scenes: A Deep Dive into SCD Type 2

dbt Snapshots Behind the Scenes: A Deep Dive into SCD Type 2

As a data engineer, having in-depth knowledge of how SCD Type 2 (Slowly Changing Dimension) tables actually behave is crucial. It helps us understand data at a deeper level and enables the business to draw accurate and trustworthy insights from historical data.

In my experience working across multiple companies and building data pipelines from scratch, SCD2 snapshots have always been a core part of the analytics architecture. In most modern data stacks, dbt snapshots are used to implement SCD Type 2 logic in a clean and reliable way.

In this blog, I want to go beyond the high-level explanation and explain how dbt snapshots actually work behind the scenes — how inserts, updates, and deletes are detected, how metadata columns are created, and how MERGE plays a key role internally.

This is a deep technical blog, aimed at data engineers who want to truly understand dbt snapshot internals.

dbt Snapshot Strategies Overview

In dbt, snapshots support two strategies:

1. Timestamp strategy
2. Check strategy

I’ve already covered the high-level differences between these strategies in a previous blog (link here). In short:

• Timestamp strategy: Changes are detected using a reliable updated_at timestamp column from the source.
• Check strategy: Changes are detected by comparing the actual values of selected columns.

Although the end goal is the same (SCD Type 2), dbt behaves differently internally for each strategy.

Before diving deeper, remember this:

dbt snapshots are SCD Type 2 tables, and they require a unique_key — a business key that can have multiple historical versions over time.

dbt Snapshot Metadata Columns

Regardless of strategy, dbt creates four metadata columns in every snapshot table:

• dbt_scd_id
• dbt_updated_at
• dbt_valid_from
• dbt_valid_to

How these columns are populated depends on the snapshot strategy.

Check Strategy — Behind the Scenes

The check strategy is used when there is no reliable timestamp in the source to detect changes.

Snapshot Configuration Example

{% snapshot table_snapshot %}
{{
  config(
    alias = "table_alias",
    unique_key = "source_col_1_tmp_sk",
    strategy = "check",
    check_cols = [
      "source_col_1",
      "source_col_2",
      "source_col_3",
      "source_col_4"
    ],
    invalidate_hard_deletes = true
  )
}

Metadata Column Behavior (Check Strategy)

• **dbt_scd_id** → MD5 hash of unique_key + hash of check_cols
• **dbt_updated_at** → Snapshot run timestamp
• **dbt_valid_from** → Snapshot run timestamp
• **dbt_valid_to** → NULL for active records

Since no timestamp is available, dbt relies entirely on hash comparison of selected columns.

First Snapshot Run

On the very first run, the snapshot table does not exist.

So dbt:

• Creates the snapshot table
• Inserts all rows from the source
• Adds only the metadata columns

There is no comparison logic on the first run — everything is treated as an insert.

Subsequent Runs (Real SCD2 Logic)

From the second run onwards, dbt compares new source data with the existing snapshot table.

Internally, dbt:

• Uses the **unique_key** to identify matching records
• Compares hashes of check_cols to detect changes
• Identifies three possible scenarios:
• Insert
• Update
• Delete (optional)

Based on this classification, dbt updates dbt_valid_to for old records and inserts new versions.

Timestamp Strategy — Behind the Scenes

The timestamp strategy relies on a trusted updated_at column from the source system.

Snapshot Configuration Example

{% snapshot sage_jc_job_snapshot %}
{{
  config(
    alias = "jc_job",
    unique_key = "jc_job_tmp_sk",
    strategy = "timestamp",
    updated_at = "date_updated",
    invalidate_hard_deletes = true
  )
}}

select * from {{ ref('stg_sage_jc_job') }}

{% endsnapshot %}

Metadata Column Behavior (Timestamp Strategy)

• **dbt_scd_id** → MD5 hash of unique_key + updated_at
• **dbt_updated_at** → updated_at column
• **dbt_valid_from** → updated_at
• **dbt_valid_to** → NULL for active records

First Run vs Subsequent Runs

Just like the check strategy:

• First run → All rows are inserted
• Subsequent runs → dbt compares updated_at values to detect changes

If updated_at is greater than the stored value, dbt treats it as an update and creates a new SCD2 version.

How dbt Uses MERGE Internally

After the first run, dbt does not recreate the snapshot table on every execution.

Instead, dbt snapshots compile into a MERGE statement, especially on Snowflake.

Simplified MERGE Logic

MERGE INTO snapshot_table AS tgt
USING snapshot_staging AS src
ON tgt.unique_key = src.unique_key
AND tgt.dbt_valid_to IS NULL

WHEN MATCHED AND src.change_type = 'update'
  THEN UPDATE SET tgt.dbt_valid_to = current_timestamp

WHEN NOT MATCHED
  THEN INSERT (...);

What Happens Internally

1. dbt creates a staging table
2. Rows are classified as:

• insert
• update
• delete

3. MERGE applies:

• UPDATE → expire existing records
• INSERT → add new versions

All of this happens in a single atomic SQL statement.

The Most Important Concept (Key to Understanding Snapshots)

🔑 dbt NEVER compares against the entire snapshot table

👉 dbt compares ONLY against active records, meaning:

WHERE dbt_valid_to IS NULL

Historical records are completely ignored during comparison.

Actual SCD Type 2 Behavior Explained

Using only active records, dbt applies the following logic:

Insert

• unique_key exists in source but not in snapshot
• dbt inserts a new row with:
• dbt_valid_from = current_timestamp
• dbt_valid_to = NULL

Update

• unique_key exists in both
• dbt detects a change using:
• column hash (check strategy), or
• **updated_at (timestamp strategy)**
• Old record is expired
• New version is inserted

Delete (Hard Delete Handling)

• unique_key exists in snapshot but not in source
• If invalidate_hard_deletes = true:
• dbt expires the record by setting dbt_valid_to

➡️ This produces true SCD Type 2 behavior with full history.

Important Considerations & Common Pitfalls (Read This Before Using Snapshots in Production)

While dbt snapshots provide a clean and reliable abstraction for implementing SCD Type 2, there are a few critical concepts that are often overlooked. Understanding these will help you avoid incorrect history, false updates, and production issues.

1. Snapshots Are State-Based, Not Event-Based

dbt snapshots do not capture every change event. They capture the state of the data at the time the snapshot runs.

This means:

• If a record changes multiple times between two snapshot runs, only the latest state is captured.
• Snapshots are not a replacement for CDC or event streaming.
• Snapshot accuracy depends heavily on how frequently the snapshot job runs.

In short, dbt snapshots answer the question:

“What did the data look like at this point in time?”

— not “What events happened in between?”

2. Source Data Must Be Stable at Snapshot Runtime

A common misconception is that dbt snapshots can safely read from continuously updating source tables.

In reality:

• dbt assumes the source data is complete and stable at runtime
• Continuous ingestion tools (for example, Fivetran syncing every few minutes) can introduce:
• false updates
• partial reads
• incorrect delete detection

Best practice:

• Always snapshot from staging models, not raw tables
• Apply ingestion boundaries using fields like _fivetran_synced
• Avoid running snapshots while ingestion is actively in progress

Snapshots do not protect you from partial ingestion states — orchestration matters.

3. Why invalidate_hard_deletes Defaults to false

By default, dbt does not expire records when they disappear from the source.

This is an intentional design choice because:

• Many source systems do not emit deletes reliably
• Temporary ingestion gaps are common
• False deletes are far more dangerous than missing deletes

You should enable invalidate_hard_deletes = true only when:

• The source reliably represents deletes
• Ingestion timing is well controlled
• You fully understand the downstream impact

4. Uniqueness of unique_key Is Mandatory

Snapshots assume that unique_key is truly unique per snapshot run.

If the source produces duplicate keys:

• The internal MERGE will fail
• Snapshot execution will break

Always ensure:

• Deduplication happens in staging
• One row per unique_key per run

5. Snapshots Are Best Suited for Dimensions

Although snapshots are powerful, they are not a universal solution.

They work best for:

• Slowly changing dimension tables
• Moderate data volumes
• Business attributes that require historical tracking

They are not ideal for:

• Large fact tables
• High-frequency metric updates
• Append-only event data

Using snapshots in the wrong place can lead to performance and storage issues.

Understanding these nuances ensures that dbt snapshots are not only easy to use, but also correct, predictable, and production-safe.

Final Thoughts

Understanding dbt snapshots at this level helps you:

• Debug incorrect history
• Avoid false deletes
• Design ingestion-safe pipelines
• Explain snapshot behavior confidently in interviews

Snapshots may look simple from the outside, but internally they combine set-based logic, hashing, and atomic MERGE operations to deliver reliable SCD Type 2 history.

If you truly understand this flow, you don’t just use dbt snapshots — you master them.