Snapshots — dbt's SCD Type 2

This lesson on Snapshots — dbt's SCD Type 2 is hands-on and example-driven. Implement Slowly Changing Dimension Type 2 (SCD Type 2) tracking in dbt using snapshot blocks. You will configure timestamp-based change detection strategies, execute snapshot runs against mutable warehouse sources, and audit historical validity intervals using dbt metadata columns.

What You'll Be Able To Do

• Configure snapshot files using Jinja snapshot blocks with unique_key, strategy, and updated_at properties.
• Execute the dbt snapshot CLI command to generate initial baseline and incremental SCD Type 2 tables.
• Simulate source record mutations in Snowflake to trigger automatic snapshot row expiration and insertion.
• Query dbt metadata columns including dbt_valid_from, dbt_valid_to, and dbt_scd_id to filter active versus historical records.
• Inspect dbt snapshot execution logs to analyze the left join and MD5 checksum comparison logic.

Detailed Concept Walkthrough

1. SCD Type 2 and Snapshot Fundamentals

Slowly Changing Dimension Type 2 (SCD Type 2) preserves historical row states by appending new records rather than overwriting existing values. In dbt, snapshots provide an automated framework to materialize and maintain these historical audit tables over time.

• Mechanism: When source records mutate, SCD Type 2 retains the historical row state by assigning it an expiration timestamp while creating a new active row. Downstream consumers can query historical points in time or inspect point-in-time states rather than only observing the current state.
• Under the Hood: dbt implements this pattern through snapshots, which create and manage append-only tables backed by your warehouse storage. Each row in the snapshot table represents a distinct validity window for a business entity.
• Best Practice: Mutable source tables must contain a dependable business key (e.g., order_id) and a mutation timestamp to reliably power snapshot change detection.

-- Create mutable source table in Snowflake
create or replace table raw.mock_orders (
    order_id int,
    status varchar,
    updated_at timestamp_ntz
);

-- Populate initial test records
insert into raw.mock_orders (order_id, status, updated_at)
values
    (1, 'pending', '2023-01-01 10:00:00'::timestamp_ntz),
    (2, 'shipped', '2023-01-01 11:00:00'::timestamp_ntz);

Key Takeaway: Snapshots implement SCD Type 2 by retaining prior record versions with validity windows rather than performing in-place overwrites.

2. Snapshot Block Definition and Configuration

dbt snapshots are declared in .sql files within the snapshots/ directory using Jinja snapshot and config wrapper blocks around a standard SELECT query.

• Syntax Rule: A snapshot definition must open with {% snapshot <name> %} and close with {% endsnapshot %}, wrapping a standard SELECT query pointing to the source table.
• Configuration Properties: Inside config(), you must specify target_schema for destination storage, unique_key for entity identification, strategy='timestamp', and updated_at for change detection tracking.
• Execution Flow: When executed, dbt reads the source select query, checks the destination table in target_schema, and constructs the necessary DDL or DML merge operations.

{% snapshot orders_snapshot %}

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

select * from {{ source('raw', 'mock_orders') }}

{% endsnapshot %}

Key Takeaway: Snapshots combine warehouse target parameters, unique business keys, and detection strategies around a standard source query.

3. Initial Snapshot Run and Metadata Columns

The first execution of dbt snapshot creates the target table and injects internal metadata columns that track row lifecycle validity.

• Mechanism: Executing dbt snapshot for the first time builds the destination table in the target schema, loading all existing records as the initial historical baseline.
• Under the Hood: dbt generates system tracking columns: dbt_valid_from (set to updated_at), dbt_valid_to (set to NULL for active rows), and dbt_scd_id (an MD5 hash of the unique key and timestamp).
• Nuance: A value of NULL in dbt_valid_to denotes that the record represents the currently active state of that business entity.

# Execute all snapshot models in the project
dbt snapshot

# Query the resulting snapshot table in Snowflake
# SELECT order_id, status, dbt_valid_from, dbt_valid_to 
# FROM snapshots.orders_snapshot 
# WHERE dbt_valid_to IS NULL;

Key Takeaway: Initial snapshot executions establish baseline records with dbt_valid_to set to NULL and unique MD5 version identifiers.

4. Incremental Change Detection and Execution Logs

On incremental runs, dbt uses left joins and MD5 checksum comparisons between source and target tables to detect updates, expire prior records, and append new states.

• Mechanism: When source records change and advance their updated_at timestamp, re-running dbt snapshot detects the delta between active snapshot rows and source rows.
• Under the Hood: dbt executes a left join between source and target snapshot rows where dbt_valid_to IS NULL, calculating a dbt_change_type ('insert' or 'update') based on unique key matches and timestamp changes.
• Execution Flow: For updated records, dbt updates the prior row's dbt_valid_to to match the incoming updated_at value, and simultaneously inserts the new record version with dbt_valid_to = NULL.

-- Simulate a source update in Snowflake
update raw.mock_orders
set status = 'delivered', updated_at = '2023-01-02 14:00:00'::timestamp_ntz
where order_id = 1;

-- Run snapshot to process changes
-- Terminal: dbt snapshot

Key Takeaway: Incremental snapshot runs close historical rows with expiration timestamps and insert new rows for detected updates.

Topics Covered in Snapshots — dbt's SCD Type 2

1. Source Data Setup (0:00 - 2:44) — Set up a mock orders source table in Snowflake with business keys and timestamp columns.
2. Snapshot Block Configuration (2:44 - 4:47) — Define a snapshot file in dbt with target schema, unique key, and timestamp strategy.
3. Initial Snapshot Execution (4:47 - 6:42) — Run dbt snapshot to materialize the target table and review dbt metadata columns.
4. Source Data Mutation (6:42 - 7:29) — Update source rows with new status values and advanced timestamps in Snowflake.
5. Change Detection Mechanics (7:29 - 9:46) — Inspect dbt execution logs and internal SQL join logic evaluating row checksums.
6. SCD Type 2 Output Verification (9:46 - 11:23) — Query the snapshot table to verify closed validity windows on historical rows.

dbt Cheat Sheet

• dbt snapshot — Executes all snapshot models in project

  dbt snapshot
  

• {% snapshot <name> %} ... {% endsnapshot %} — Defines a dbt snapshot model block

  {% snapshot orders_snapshot %}
  select * from raw.mock_orders
  {% endsnapshot %}
  

• config(strategy='timestamp', updated_at='<col>') — Configures timestamp-based change detection strategy

  config(strategy='timestamp', updated_at='updated_at')
  

• config(unique_key='<col>') — Defines business key identifying entity identity

  config(unique_key='order_id')
  

• config(target_schema='<schema>') — Specifies destination schema for snapshot table

  config(target_schema='snapshots')
  

• WHERE dbt_valid_to IS NULL — Filters snapshot table for active records

  select * from snapshots.orders_snapshot where dbt_valid_to is null;
  

Comparison Table

Common Pitfalls

• Mistake: Updating source rows without advancing the updated_at timestamp. Avoid: Ensure source systems or ingestion pipelines advance updated_at on every row update.
• Mistake: Specifying a non-unique column as the unique_key in config. Avoid: Select a true primary key or composite column that uniquely identifies each entity.
• Mistake: Querying snapshot tables without filtering on dbt_valid_to for current data. Avoid: Add WHERE dbt_valid_to IS NULL when downstream models require only active records.
• Mistake: Manually modifying snapshot table structures or data in warehouse. Avoid: Allow dbt snapshot commands to exclusively manage table DDL, updates, and timestamps.

FAQs

• What happens to dbt_valid_to when an existing row is updated in the source? dbt sets the historical row's dbt_valid_to to the new record's updated_at value and inserts the updated record with dbt_valid_to = NULL.
• Why does dbt calculate MD5 checksums during snapshot runs? MD5 checksums allow dbt to compare the combined state of business keys and timestamps across tables quickly using a single hash value.
• How do downstream dbt models reference a snapshot table? Downstream models reference snapshots using the standard ref() Jinja function, exactly like referencing regular dbt models.
• Where in the project repository should snapshot files be placed? Snapshot files ending in .sql are stored by convention in the snapshots/ directory of your dbt project.