Snapshots: Type 2 Slowly Changing Dimensions

A Mutable Source Table Only Ever Shows You Right Now

Most operational source tables — the ones a production application writes to — are mutable. When a customer upgrades their subscription tier, the application runs an UPDATEagainst the row for that customer. The old tier value is overwritten. It does not move anywhere, it is not archived automatically, it is simply gone. The next time anything reads that row, the only value it can possibly see is the new one. This is completely correct behavior for the application itself — an app showing a customer's account page has no reason to care what tier they were on last year. But it is a serious problem the moment you need to answer a historical question.

Consider a simple, extremely common analytics question: what plan was this customer on when they made this purchase? If your customers table only stores current state, and a customer has since changed plans, you cannot answer this question at all — not approximately, not with a clever query, not ever — because the information required to answer it was overwritten the moment the plan changed. The purchase record still exists, timestamped correctly, but the only version of "what plan were they on" you can join against is whatever plan they happen to be on today. Revenue-by-plan reporting, churn analysis segmented by historical tier, and any audit trail that needs to reconstruct "what did we know and when did we know it" are all quietly broken by this, usually without anyone noticing until a stakeholder asks a question the data literally cannot answer.

This is exactly the gap dbt snapshots exist to close. A snapshot is a mechanism for periodically comparing a mutable source table's current state against the last state you captured, and recording every change as a new, permanent row — building up a full history of every value a column has ever held, for every record, indexed by exactly when each version was true. Once that history exists, "what plan was this customer on when they made this purchase" becomes an ordinary join against a point in time, instead of an unanswerable question.

A Snapshot Is a Special .sql File dbt Runs on Its Own Schedule

A dbt snapshot is a .sql file that lives in a snapshots/ directory at the root of your project — a separate location from models/, because snapshots are not models in the ordinary sense. A model is re-run every time and its output is recomputed from scratch (or incrementally, per the incremental-models module). A snapshot is run on a recurring schedule via a dedicated command, dbt snapshot, and its job every time it runs is not to recompute anything — it is to compare the current state of a source query against the snapshot's own last-known state, and append rows for whatever changed.

The result is a table that only ever grows. Nothing in a snapshot table is ever overwritten or deleted by dbt during normal operation (hard deletes are the one exception, covered in Part 07). Every run either finds nothing new to record, or appends new rows representing whatever changed since the last run. This growing, append-only table is what lets you answer "what did this record look like on any given date" — a query pattern known as Slowly Changing Dimension Type 2, or SCD Type 2, a decades-old data warehousing pattern that dbt snapshots implement for you without hand-written merge logic.

{% snapshot customers_snapshot %}

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

select
    customer_id,
    plan_tier,
    email,
    updated_at
from {{ source('app_db', 'customers') }}

{% endsnapshot %}

Notice the shape: the query inside a snapshot is an ordinary SELECT against a source or another model, exactly like a normal dbt model. What makes it a snapshot is the surrounding{% snapshot %} block and the config() call, which tells dbt how to detect changes (the strategy), which column uniquely identifies a record (theunique_key), and where to store the resulting history table (thetarget_schema).

Running a snapshot

dbt snapshot

Running 1 snapshot node

SNAPSHOT customers_snapshot ................................ [INSERT 12 in 0.84s]

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

dbt snapshot is a separate command from dbt run deliberately — models get rebuilt on every invocation, but a snapshot's entire value comes from its history being preserved across runs, so it would make no sense to fold it into the same command that rebuilds transformations from scratch. In production, dbt snapshot is typically scheduled to run before dbt run, on a cadence frequent enough to catch changes between runs — often hourly or on every scheduled job, so that no change to the source data happens and reverses (an upgrade followed immediately by a downgrade) entirely between two snapshot runs, which would leave that intermediate state uncaptured.

timestamp: Detecting Change via a Reliable updated_at Column

dbt supports two strategies for detecting whether a row has changed since the last snapshot run. The timestamp strategy is the simpler and generally preferred one: it compares a designated updated_at column's value for each record against what was recorded on the previous snapshot run. If the timestamp is newer, dbt considers the row changed and records a new version. If the timestamp is unchanged, dbt considers the row unchanged and does nothing for it.

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

This strategy has exactly one hard requirement: the source system must maintain a genuinely reliable updated_at column — one that is guaranteed to be bumped on every single write to a row, with no exceptions. If even one code path in the source application updates a row without touching updated_at (a bulk backfill script, a direct database migration, an admin panel that writes through a different code path than the main application), that change is completely invisible to a timestamp-strategy snapshot. dbt will compare the unchangedupdated_at value, conclude nothing happened, and silently miss a real change to the row's other columns.

check: Detecting Change by Comparing Columns Directly

Not every source table has a trustworthy updated_at column. Some legacy systems never added one. Some have one that is unreliable for the reasons in Part 03. Some tables simply do not track when they were last modified at all. For all of these cases, dbt offers thecheck strategy: instead of trusting a single timestamp column to signal change, it directly compares the current value of a specified list of columns against the values recorded on the previous snapshot run. If any of those columns differ, the row is considered changed.

{% snapshot customers_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='check',
      check_cols=['plan_tier', 'email', 'billing_country'],
    )
}}

select
    customer_id,
    plan_tier,
    email,
    billing_country
from {{ source('app_db', 'customers') }}

{% endsnapshot %}

check_cols can also be set to the literal string "all", which tells dbt to compare every column selected by the snapshot's query rather than an explicit list. This is convenient for a narrow table where you genuinely want any change at all to trigger a new snapshot row, but it comes with a real cost: adding a new column to the snapshot's query later, or having an upstream column's formatting change in a way that is not actually meaningful (whitespace, case), will trigger what looks like a change to every single row the next time the snapshot runs — a spurious explosion of new history rows that do not represent any real business change.

dbt_valid_from, dbt_valid_to, and dbt_scd_id

Every snapshot table dbt builds automatically gains three extra columns beyond whatever yourSELECT query returned. These are the mechanics that make SCD Type 2 querying possible, and understanding exactly what each one means is the single most important thing to get right about snapshots — most snapshot bugs in practice come from querying these columns incorrectly.

The single most load-bearing fact here: dbt_valid_to IS NULL marks the current record for a given natural key — the one version of that key's row that is true right now, as of this moment. A non-null dbt_valid_to marks a historical, superseded record — one that used to be current but has since been replaced by a newer version. At any given moment, exactly one row per unique_key value should have a null dbt_valid_to (barring the hard-delete edge case covered in Part 07) — all of that key's other rows are history.

customer_id | plan_tier | email                | dbt_valid_from       | dbt_valid_to         | dbt_scd_id
------------+-----------+----------------------+----------------------+----------------------+-----------
42          | free      | ana@example.com      | 2026-01-03 09:00:00  | 2026-04-11 14:22:00  | a1b2c3...
42          | pro       | ana@example.com      | 2026-04-11 14:22:00  | 2026-08-02 10:15:00  | d4e5f6...
42          | enterprise| ana@example.com      | 2026-08-02 10:15:00  | NULL                 | g7h8i9...

# Reading this row by row:
# - customer 42 signed up on "free" on Jan 3, and stayed there until Apr 11
# - on Apr 11, they upgraded to "pro" -- the free row's dbt_valid_to closes at that instant
# - on Aug 2, they upgraded again to "enterprise" -- the pro row's dbt_valid_to closes
# - the enterprise row's dbt_valid_to is NULL -- it is the current, still-active version

Every one of these three rows shares the same customer_id (the unique_key— it identifies the person), but each has a distinct dbt_scd_id (it identifies this specific version of that person's record at that specific point in time). Confusing these two keys — joining on dbt_scd_id when you meant the entity's natural key, or expectingunique_key alone to identify one row in the snapshot table — is one of the most common mistakes newcomers make querying a snapshot for the first time.

Current State vs Point-in-Time History

A snapshot table supports two fundamentally different kinds of query, and picking the right one depends entirely on the question you are actually asking.

Question 1: "What is true right now?"

Filter for the row where dbt_valid_to IS NULL. This gives you exactly one row perunique_key — the current, still-active version of every entity, which is exactly what a normal current-state dimension table would show you.

select
    customer_id,
    plan_tier,
    email
from {{ ref('customers_snapshot') }}
where dbt_valid_to is null

Question 2: "What was true as of a specific past date?"

Filter for the row whose valid window contains the date you care about — wheredbt_valid_from is on or before that date, and dbt_valid_to is either after that date or still null (meaning it was still current then and remains current now).

select
    customer_id,
    plan_tier,
    email
from {{ ref('customers_snapshot') }}
where '2026-05-01' between dbt_valid_from and coalesce(dbt_valid_to, '9999-12-31')

The coalesce(dbt_valid_to, '9999-12-31') pattern is the key idiom here: it treats a currently-active row (null dbt_valid_to) as valid all the way out to a date far in the future, so the BETWEEN comparison correctly includes the current row for any date up to and including today, without needing a separate OR dbt_valid_to IS NULL clause.

Answering the original question: joining a fact table against a point-in-time snapshot

This is exactly what makes "what plan was this customer on when they made this purchase" answerable for the first time. Instead of comparing a fixed literal date, join each purchase's own timestamp against the snapshot's valid window.

select
    p.purchase_id,
    p.purchased_at,
    p.amount,
    cs.plan_tier as plan_tier_at_time_of_purchase
from {{ ref('fct_purchases') }} p
left join {{ ref('customers_snapshot') }} cs
  on p.customer_id = cs.customer_id
  and p.purchased_at between cs.dbt_valid_from and coalesce(cs.dbt_valid_to, '9999-12-31')

Without the snapshot, this join is not possible at all — there is no other place in the warehouse where "what plan_tier was true at this exact past instant" is recorded. This single join is the entire payoff of building a snapshot in the first place.

invalidate_hard_deletes: Closing Out History for Rows That Vanish

Both the timestamp and check strategies detect change by comparing a value from the source query against the last snapshot run. But what happens when a row simply disappears from the source entirely — a customer account is hard-deleted from the application database, not soft-deleted with a flag? Neither strategy has anything to compare, because there is no longer a row in the source at all. Without additional configuration, that customer's last snapshot row is simply left open forever: dbt_valid_to stays null, silently implying that customer's last known plan is still "current," indefinitely, even though the customer no longer exists in the source system at all.

{% snapshot customers_snapshot %}

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

select
    customer_id,
    plan_tier,
    email,
    updated_at
from {{ source('app_db', 'customers') }}

{% endsnapshot %}

With invalidate_hard_deletes=True, dbt compares the full set of unique_keyvalues currently present in the source query against the set of keys that currently have an open (dbt_valid_to IS NULL) row in the snapshot table. Any key present in the snapshot but missing from the source is treated as deleted: dbt closes out that row by setting itsdbt_valid_to to the time of the snapshot run, and also sets adbt_is_deleted flag column to 'True' on that closed row, so downstream queries can distinguish "superseded by a newer version" from "the underlying record no longer exists in the source at all."

# Before: customer 99 has one open row, still active
customer_id | plan_tier | dbt_valid_from       | dbt_valid_to | dbt_is_deleted
------------+-----------+----------------------+--------------+---------------
99          | pro       | 2026-02-01 08:00:00  | NULL         | False

# Customer 99's account is hard-deleted from the source app_db.customers table
# on 2026-06-15. The next dbt snapshot run notices customer_id=99 is now
# missing from the source query entirely.

# After: the row is closed out, not deleted from the snapshot table itself
customer_id | plan_tier | dbt_valid_from       | dbt_valid_to         | dbt_is_deleted
------------+-----------+----------------------+----------------------+---------------
99          | pro       | 2026-02-01 08:00:00  | 2026-06-15 03:00:00  | True

# The snapshot table itself never loses this row -- it correctly records
# that this customer existed, was on the pro plan, and stopped existing
# in the source as of 2026-06-15. Nothing in the snapshot is ever deleted;
# a hard delete in the source is recorded as a closed, flagged row here.

The distinction to hold onto: invalidate_hard_deletes does not delete anything from the snapshot table. It closes out the open row for a key that has disappeared from the source, so the "current state" query in Part 06 (dbt_valid_to IS NULL) correctly stops returning a customer who no longer exists, while the full history — including the fact that they existed and what their last known values were — remains permanently intact in the snapshot table.

Snapshotting customers End to End, With a Plan Change Walkthrough

Putting every prior part together: here is a complete, realistic snapshot of acustomers source table using the timestamp strategy, followed by exactly what happens, row by row, across three consecutive snapshot runs as one customer changes plans.

{% snapshot customers_snapshot %}

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

select
    customer_id,
    plan_tier,
    email,
    billing_country,
    updated_at
from {{ source('app_db', 'customers') }}

{% endsnapshot %}

Run 1 — Monday, initial snapshot

# source app_db.customers on Monday:
customer_id=42 | plan_tier=free | email=ana@example.com | updated_at=2026-01-03 09:00:00

# dbt snapshot run on Monday -- no prior snapshot row exists for customer 42,
# so this is a plain insert with an open dbt_valid_to

# snapshots.customers_snapshot after Run 1:
customer_id | plan_tier | dbt_valid_from       | dbt_valid_to
------------+-----------+----------------------+-------------
42          | free      | 2026-01-03 09:00:00  | NULL

Run 2 — Thursday, the customer upgrades to pro

On Thursday, the customer upgrades from free to pro inside the application, which correctly bumps updated_at as part of that same write.

# source app_db.customers on Thursday:
customer_id=42 | plan_tier=pro | email=ana@example.com | updated_at=2026-01-06 11:30:00

# dbt snapshot run on Thursday -- compares this row's updated_at
# (2026-01-06 11:30:00) against the value recorded on the open row from Run 1
# (2026-01-03 09:00:00). The timestamp is newer -- this is a change.

# dbt does two things in this one run:
# 1. closes the existing open row: sets its dbt_valid_to to the current
#    snapshot run's timestamp
# 2. inserts a brand new row with the new values, dbt_valid_from set to
#    this run's timestamp, and dbt_valid_to left NULL (it is now current)

# snapshots.customers_snapshot after Run 2:
customer_id | plan_tier | dbt_valid_from       | dbt_valid_to
------------+-----------+----------------------+----------------------
42          | free      | 2026-01-03 09:00:00  | 2026-01-06 11:30:00
42          | pro       | 2026-01-06 11:30:00  | NULL

Run 3 — the following Monday, nothing changes

# source app_db.customers the following Monday:
customer_id=42 | plan_tier=pro | email=ana@example.com | updated_at=2026-01-06 11:30:00

# dbt snapshot run compares this updated_at against the open row's
# recorded value -- they are identical. No change detected. Nothing happens.

# snapshots.customers_snapshot after Run 3 -- unchanged from Run 2:
customer_id | plan_tier | dbt_valid_from       | dbt_valid_to
------------+-----------+----------------------+----------------------
42          | free      | 2026-01-03 09:00:00  | 2026-01-06 11:30:00
42          | pro       | 2026-01-06 11:30:00  | NULL

After these three runs, both of the questions from Part 06 are now fully answerable for customer 42. "What plan is this customer on right now?" filters to the pro row viadbt_valid_to IS NULL. "What plan were they on when they made a purchase on January 4th?" joins the purchase's timestamp against the valid window and correctly lands on thefree row, since January 4th falls between 2026-01-03 09:00:00 and2026-01-06 11:30:00 — exactly the information that would have been permanently destroyed the moment the application's UPDATE ran, had nothing been snapshotting it.

A Snapshot Table Only Ever Grows — What That Means Years In

Every earlier Part treats the growing, append-only nature of a snapshot table as a feature, and it is — that is precisely what makes point-in-time history possible at all. But "only ever grows" is also, unavoidably, a cost curve, and it is worth confronting directly before it becomes a surprise. A snapshot with a fine-grained change-detection strategy against a high-churn source, running for years without any retention policy, accumulates history at a rate that is easy to underestimate the first time you actually compute it.

# customers_snapshot: 2 million customers, timestamp strategy,
# snapshotted hourly, and roughly 3% of customers have some field
# change in a given day (plan changes, address updates, email changes)

2,000,000 customers × 3% changing per day  ≈ 60,000 new history rows / day
60,000 rows/day × 365 days                 ≈ 21,900,000 new rows / year

# After 5 years of continuous operation, with no pruning at all:
2,000,000 (original rows)
+ 5 × 21,900,000 (five years of accumulated change history)
≈ 111,500,000 total rows in customers_snapshot

# The table that started at 2 million rows is now 55x larger, and every
# one of Part 06's point-in-time queries -- and every dbt snapshot run
# itself, which has to scan the current open rows to compare against --
# is now scanning a table over fifty times its original size.

Two distinct costs grow from this, and they are easy to conflate. The first is storage cost, which is usually the smaller concern — cloud warehouse storage is cheap, and a hundred million narrow rows is not, by itself, an alarming number. The second, more consequential cost is query and maintenance cost: every dbt snapshot run has to identify the currently-open row per key to compare against (typically dbt_valid_to IS NULL), and every downstream query — including the "current state" query from Part 06, which should be cheap — pays a scan cost that grows with total accumulated history rather than staying proportional to the number of distinct entities being tracked.

Retention and pruning strategies teams actually use

dbt itself has no built-in retention or pruning mechanism for snapshot tables — a snapshot is deliberately designed to keep everything, forever, by default, since dbt cannot know which historical rows a given team's analytical needs will require years from now. Retention is something a team layers on top, deliberately, once the growth curve above starts to matter in practice.

-- A scheduled job (outside of dbt itself, e.g. a dbt post-hook or a
-- separate orchestrated task) that moves closed rows older than a
-- retention cutoff out of the actively-queried snapshot table:

create or replace table snapshots.customers_snapshot_archive as
select * from snapshots.customers_snapshot
where dbt_valid_to is not null
  and dbt_valid_to < dateadd('year', -3, current_date());

delete from snapshots.customers_snapshot
where dbt_valid_to is not null
  and dbt_valid_to < dateadd('year', -3, current_date());

-- Point-in-time queries needing more than 3 years back union the archive
-- back in explicitly; routine queries (current state, recent history)
-- only ever scan the smaller, hot snapshot table.

Whether a team needs this at all depends entirely on how far back real analytical questions actually reach. Some domains genuinely need unbounded history (regulatory audit trails, financial reporting that can be re-opened years later); others realistically never query further back than twelve to twenty-four months, in which case archiving anything older keeps the hot table small without losing anything anyone actually uses. The decision to prune should be a deliberate, business-driven one — never done reflexively just because the row count looks large, since deleting history that turns out to be needed later is unrecoverable in exactly the same way the original mutable-table overwrite from Part 01 was.

Why Reach for dbt snapshot Instead of Building the Same Logic in an Incremental Model?

Nothing about SCD Type 2 tracking is exclusive to dbt's snapshot feature — the underlying pattern (an dbt_valid_from/dbt_valid_to-style window per version of a row) is a decades-old data warehousing technique that predates dbt entirely, and it is entirely possible to hand-build the same behavior inside an ordinary incremental model using the mechanics from the incremental-models module: a merge strategy, a unique_key, and custom Jinja to open and close validity windows manually. Understanding why most teams reach for the built-in dbt snapshot mechanism instead of hand-rolling this is really a question about which parts of the problem are genuinely custom to your business versus which parts are pure, reusable mechanics that a well-tested built-in feature already handles correctly.

{{
  config(
    materialized='incremental',
    unique_key='customer_id_version',
    incremental_strategy='merge'
  )
}}

with source_data as (
    select customer_id, plan_tier, email, updated_at
    from {{ source('app_db', 'customers') }}
),

-- Manually detect which rows changed since the last run by comparing
-- against the CURRENT open version already in this table...
current_versions as (
    select * from {{ this }} where dbt_valid_to is null
),

changed as (
    select s.*
    from source_data s
    left join current_versions c on s.customer_id = c.customer_id
    where c.customer_id is null                  -- brand new customer
       or s.plan_tier != c.plan_tier              -- or something changed
       or s.email != c.email
),

-- ...then hand-construct new open rows AND figure out which existing
-- open rows now need their valid_to closed -- this requires a second
-- statement, or careful UNION logic, that dbt's snapshot mechanism
-- already handles as one coherent operation.

final as (
    select
        customer_id,
        customer_id || '_' || updated_at as customer_id_version,
        plan_tier, email, updated_at as dbt_valid_from,
        cast(null as timestamp) as dbt_valid_to
    from changed
)

select * from final

-- Missing from this sketch entirely: actually closing out the PREVIOUS
-- open row's dbt_valid_to when a new version is inserted -- a MERGE
-- alone can't both close an old row AND insert a new one for the same
-- key in one statement the way dbt's snapshot materialization does.

That last comment is the crux of it: a real SCD Type 2 write is not one operation, it is two coupled operations that must happen together — close the previous open row's dbt_valid_to, and insert the new row's dbt_valid_from — and getting this coupling exactly right, for every edge case (a same-key row that changes twice in one batch, a row that reappears after a hard delete, an out-of-order arrival), is genuinely fiddly to hand-write correctly and easy to get subtly wrong in a way that does not show up until a point-in-time query returns a gap or an overlap months later.

The practical rule most teams land on: reach for dbt snapshot by default for any standard SCD Type 2 need, precisely because the coupled open/close mechanics, the standard column contract, and hard-delete handling are exactly the kind of undifferentiated, easy-to-get-subtly-wrong logic that a mature, widely-used built-in feature is worth trusting over a custom reimplementation. Hand-rolling the same pattern inside an incremental model is worth it only when a project's actual requirement falls genuinely outside what strategy, unique_key,check_cols, and invalidate_hard_deletes can express together — a real but uncommon situation in practice.

Where hand-rolling genuinely wins: an example

One real situation where teams do deliberately step outside dbt snapshot: a Type 3 slowly-changing pattern, where only the immediately previous value of a small number of columns needs to be visible, inline, on the current row itself — not a full growing history table. A support-ticketing analytics model that just needs "the customer's previous support tier, alongside their current one, on the same row" is a Type 3 need, not a Type 2 one, and forcing it throughdbt snapshot's full valid-from/valid-to history table is more machinery than the question actually requires.

{{ config(materialized='table') }}

select
    customer_id,
    support_tier as current_support_tier,
    lag(support_tier) over (
        partition by customer_id order by updated_at
    ) as previous_support_tier
from {{ ref('stg_support_tier_changes') }}
qualify row_number() over (partition by customer_id order by updated_at desc) = 1

-- One row per customer, with exactly one column of "what it used to be"
-- inline. No growing history table, no dbt_valid_from/dbt_valid_to
-- bookkeeping -- because the actual business question here never asked
-- for the FULL history, only the single most recent prior value.

The decision test that generalizes from this example: if the real requirement is "show me the complete history of every value this ever held, queryable at any point in time," reach fordbt snapshot — that is exactly the problem it is built to solve well. If the real requirement is narrower — "just the previous value, inline, no need to reconstruct arbitrary past states" — a plain model with a window function is simpler, cheaper to maintain, and does not carry the unbounded growth curve from Part 09 for a need that never actually required it.

When genuinely unsure which pattern a new requirement calls for, default to dbt snapshot— the cost of a growing history table you end up querying only for "current" and "previous" is modest, while the cost of a hand-rolled Type 3 column that later turns out to need real point-in-time history is a full, retroactively-impossible rebuild of history that was never actually captured.

Five Misconceptions About dbt Snapshots

What This Looks Like on Day One

5 Interview Questions — With Complete Answers

Five Mistakes That Corrupt a Snapshot's History

Snapshot Errors — And Exactly Why They Happen