Type 2 in Practice: dbt Snapshots

The last chapter ended with a taxonomy and a verdict: the SCD type you’ll reach for most is Type 2, because “show me the numbers as they were understood at the time” is the question a warehouse exists to answer. What it didn’t do is build one that survives contact with production. That’s this chapter — the configuration surface, the bookkeeping columns, what the table actually looks like after two runs, the joins that consume it, and the tests that keep it honest. And, because the tool has real limits, the cases where dbt’s built-in machinery isn’t enough and you have to write Type 2 by hand.

The machinery is the snapshot. The dbt series covered its mechanics in Remembering What Changed; here we’re using it for its actual purpose. A snapshot watches a relation over time and, on every dbt snapshot run, compares what the source says now against the latest version it recorded. Unchanged rows are left alone. Changed rows get their old version closed — an end-timestamp stamped on — and a new version inserted with an open window. That close-and-insert dance is precisely the Type 2 contract, and dbt performs it with a merge you never have to write.

One framing rule before the details, because it decides how you name things: the snapshot is not the dimension. It’s the history capture — an append-mostly table of versions that you should treat like a source of record. The dimension your facts join to is a model built on top of it. Keeping those layers separate is what lets you rename columns, mint surrogate keys, and add flags without touching the one table in your project you can never rebuild.

The two strategies, side by side

A snapshot needs to answer one question per row per run: did this row change since I last looked? dbt gives you two ways to answer it, and choosing between them is the first real design decision.

`timestamp`: trust the source’s clock

The timestamp strategy trusts an updated_at column. If the source’s updated_at for a given unique_key is newer than the one on the latest snapshotted version, the row changed; otherwise it didn’t. In dbt 1.9+ snapshots are configured in YAML — the old {% snapshot %} SQL block still runs but is legacy:

# snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: ref('stg_customers')
    config:
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      dbt_valid_to_current: "to_date('9999-12-31')"

This is the cheap, precise option (one column comparison per row, no value-by-value diffing), and it carries a bonus that matters more than the cost savings: the version windows inherit the source’s notion of when the change happened. The new version’s dbt_valid_from is set from updated_at, not from whenever your snapshot job happened to run. If the source stamps updated_at at the moment of the edit, your history is dated to the edit, even when the snapshot runs hours later.

The catch is the word “trust.” The strategy is exactly as good as the source’s discipline. An updated_at that a backend job touches on every write — including writes that change nothing — generates phantom versions. Worse is the reverse: an updated_at that doesn’t move when a bulk backfill or a DBA’s manual UPDATE changes the data means the change is invisible forever, because the snapshot never looks past that column. Before you commit to timestamp, interrogate the source: who sets this column, in how many code paths, and does anyone write to the table around it?

`check`: trust nothing, compare everything

The check strategy skips the clock and compares values. You name the columns that constitute a “change,” and each run diffs them against the latest version:

# snapshots/customers_snapshot.yml
snapshots:
  - name: customers_snapshot
    relation: ref('stg_customers')
    config:
      unique_key: customer_id
      strategy: check
      check_cols: [city, country, segment]
      dbt_valid_to_current: "to_date('9999-12-31')"

This is the strategy for sources with no reliable updated_at — which, in practice, is most sources you didn’t build yourself. It also gives you something timestamp can’t: selectivity. With check_cols you’re declaring which attributes are Type 2. A customer’s city change creates a version; an email correction — Type 1 by the last chapter’s logic — doesn’t, as long as email isn’t in the list. The strategy config doubles as your per-attribute SCD policy, written down where the next engineer will find it.

There’s a check_cols: all escape hatch that diffs every column. Use it reluctantly. On a wide table it means comparing every value of every row on every run — real compute on a 60-column dimension, and it turns any new column into a change-detection surface. That last part bites in a specific, nasty way: add a column to the source, and existing snapshot versions don’t have values for it. On the next run, every row looks changed (null then, populated now) and you mint a version for the entire dimension in one shot. A thousand-row dimension grows a thousand junk versions with identical business content. check_cols: all plus an evolving schema is a version-explosion machine; if you must diff broadly, list the columns explicitly so schema growth is a deliberate edit, not an ambush.

While we’re on nulls: the check comparison does handle null-to-value and value-to-null transitions as changes (a plain != wouldn’t: null comparisons come back null, not true, so dbt’s generated SQL adds the null-mismatch arms explicitly). A null staying null is, correctly, not a change. The residual sharp edge is semantic nulls: if your staging model coalesces nulls to 'Unknown' on some runs and not others, the snapshot will faithfully record that flapping as history. Snapshots downstream of unstable transformations record the instability. Snapshot a staging model, yes — it should rename and retype — but keep it boring: no business logic that might be refactored, because every refactor that changes an output value is a change as far as the snapshot can tell.

The honest summary: timestamp when the source maintains a trustworthy modified-time — cheaper, and better-dated history. check when it doesn’t, or when you want the change-column list to be your Type 2 policy. Many teams run check by default and promote individual snapshots to timestamp after auditing the source’s updated_at behavior, which is a defensible way around having to trust strangers’ code.

The meta-columns: what dbt writes on every version

Every snapshot table carries four bookkeeping columns, and the point-in-time join depends on reading them correctly.

dbt_valid_from — when this version became true. Under timestamp, it’s the source’s updated_at value; under check, it’s the snapshot run’s timestamp. This difference is load-bearing and we’ll come back to it.
dbt_valid_to — when this version stopped being true. Set to the successor’s dbt_valid_from when a new version closes this one. On the current version it’s null by default — or your sentinel, if you configure one.
dbt_scd_id — a hash of the unique key and the change timestamp: a synthetic identifier for this version of this row. dbt uses it internally as the merge key; you should use it as a uniqueness test target and otherwise leave it alone.
dbt_updated_at — the updated_at value (or run timestamp) that produced this version. Mostly a debugging aid.

The dbt_valid_to_current config in the examples above is a 1.9+ convenience worth adopting from day one: it stores a far-future sentinel like 9999-12-31 in the open row’s dbt_valid_to instead of null. The payoff is that every window predicate becomes a clean range test with no or dbt_valid_to is null special case: simpler SQL, and one less place to forget the null arm and silently drop every current customer from a report. One warning, straight from the docs: adding this config to an existing snapshot doesn’t migrate old rows. You’ll have nulls on rows created before the config and sentinels after, and your queries must tolerate both until you run a one-time update to backfill the sentinel. Adopt it before the first run and the problem never exists.

If the dbt_ prefixes offend your Kimball sensibilities, snapshot_meta_column_names lets you rename all four (dbt_valid_from → valid_from, and so on). I leave them alone in the snapshot and rename in the dimension model on top — the prefix usefully marks which layer you’re reading.

`hard_deletes`: when a row vanishes

Sources delete rows, and a snapshot has to decide what that means. The hard_deletes config (dbt 1.9+, replacing the older invalidate_hard_deletes flag) offers three answers:

ignore (default) — a vanished row’s last version stays open forever. Almost never what you want: the customer looks eternally current despite not existing.
invalidate — the last version gets closed with dbt_valid_to set to the run timestamp. The history reads “true until we noticed it was gone,” which is usually the right semantics.
new_record — closes the last version and inserts a tombstone version flagged with a dbt_is_deleted column. Reach for this when downstream logic needs to distinguish “deleted” from “we merely stopped hearing about it,” or when deleted entities can resurrect and you want that visible as history.

Pick invalidate unless you have a reason. The default of ignore is backward-compatibility politeness, not a recommendation.

Two runs, one move

Abstractions cheat; tables don’t. Here’s customer 2 across two snapshot runs under the timestamp strategy, with the sentinel configured.

Run 1 — April 1. The source row says Boston, updated_at from her signup edit in January:

customer_id	city	dbt_valid_from	dbt_valid_to	dbt_updated_at
2	Boston	2026-01-05 09:14:02	9999-12-31 00:00:00	2026-01-05 09:14:02

One row, window open. Note dbt_valid_from is January 5th — the source’s timestamp, not April 1st. The snapshot’s first sight of a row adopts the row’s own story about when it became true.

Run 2 — April 19. She moved; the source overwrote city to Denver on April 18 at 16:41. The run detects updated_at moved, closes Boston, inserts Denver:

customer_id	city	dbt_valid_from	dbt_valid_to	dbt_updated_at
2	Boston	2026-01-05 09:14:02	2026-04-18 16:41:37	2026-01-05 09:14:02
2	Denver	2026-04-18 16:41:37	9999-12-31 00:00:00	2026-04-18 16:41:37

Everything downstream hangs on the invariant visible in that second table: Boston’s dbt_valid_to equals Denver’s dbt_valid_from, exactly — no gap, no overlap, windows half-open (valid_from inclusive, valid_to exclusive). Every timestamp belongs to exactly one version. That invariant is what the point-in-time join assumes and what the tests at the end of this chapter exist to defend.

Had this been the check strategy, the table would differ in one meaningful way: the boundary would read 2026-04-19 06:00:13 (the run’s timestamp) instead of April 18 at 16:41. The history would be dated to observation, not to the event. Hold that thought; it becomes the first limitation.

Querying the history

Two joins matter, and they answer different questions.

The point-in-time join attaches each fact to the version that was true when the fact happened. With half-open windows and the sentinel, it’s a pair of range predicates:

select
    f.order_id,
    c.city,
    sum(f.extended_price) as revenue
from fct_order_items as f
join dim_customer_history as c
  on  f.customer_id = c.customer_id
  and f.order_date >= c.valid_from
  and f.order_date <  c.valid_to
group by 1, 2

January’s orders fall inside Boston’s window and report as Boston, forever, no matter how many times she moves. The >= / < pair against half-open windows means boundary timestamps land in exactly one version. Use between here instead and a fact stamped precisely at a boundary joins to two rows and double-counts.

One subtlety when facts carry dates and windows carry timestamps: a date compared to a timestamp coerces to midnight. An order dated April 18 — the move day — lands at 2026-04-18 00:00:00, which is before the 16:41 boundary, so it joins to Boston. That’s a defensible convention (the day belongs to whoever held it at midnight), but it should be a decision, not an accident. If you’d rather a change own its whole day, truncate the windows to dates in the dimension model and enforce one-version-per-day upstream.

The is_current fast path answers the other question — “what’s true now?” — without range predicates:

select c.segment, count(distinct f.customer_id) as customers
from fct_order_items as f
join dim_customer_history as c
  on f.customer_id = c.customer_id
where c.is_current
group by 1

This rolls all history up to today’s attributes — every order she ever placed reports as Denver. Sometimes that’s exactly what’s being asked (“current segmentation, all-time revenue”); it’s also precisely the amnesia Type 2 exists to prevent, applied on purpose. Both queries are legitimate. A report that doesn’t say which one it ran is not.

The dimension on top

Now the layer that makes the snapshot consumable. The history dimension renames the meta-columns, mints the keys, and derives is_current:

-- models/marts/dim_customer_history.sql
{{ config(materialized='table') }}

select
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'dbt_valid_from']) }} as customer_version_sk,
    {{ dbt_utils.generate_surrogate_key(['customer_id']) }}                   as customer_sk,
    customer_id,
    full_name,
    email,
    city,
    country,
    segment,
    dbt_valid_from                                       as valid_from,
    dbt_valid_to                                         as valid_to,
    dbt_valid_to = to_date('9999-12-31')                 as is_current
from {{ ref('customers_snapshot') }}

Two surrogate keys, deliberately. customer_version_sk — hashed from the natural key plus valid_from — identifies one version and is this table’s primary key. customer_sk, hashed from the natural key alone, is the durable key, identical across all of a customer’s versions, and identical to the customer_sk that fct_order_items computed for itself back in chapter 4. That’s not a coincidence to preserve; it’s the design.

Because here’s the tension nobody warns you about: the deterministic-hash trick that made fact loads so clean — hash the natural key on both sides, no lookup join — only resolves to a customer, not to a version of a customer. A Type 2 dimension has several rows per customer_sk, so the fact’s hashed key alone can’t name one. You have two coherent designs:

Durable key on the fact, windows at query time. The fact carries customer_sk and its own event date; every as-of query does the range join above. Loads stay simple and deterministic, and (this matters for the next chapter) a back-dated fix to the dimension’s windows is picked up by every query automatically, because nothing was baked in at load time. The cost is a range join in every historical query, which BI tools handle with varying grace.

Version key resolved at load time. Kimball’s orthodox answer: the fact build looks up customer_version_sk with the range join once, at load, and stores it. Queries become plain equi-joins — fast, BI-friendly. The costs are a lookup join on every incremental load and a subtle fragility: the stored key is a bet that the dimension’s windows were right at load time. When a late-arriving change rewrites a window, facts pointing into it are silently mispointed until you repair them.

If you store the version key, keep the durable key alongside it — repairs and current-view rollups need it. For the bookshop, the fact already carries customer_sk and order_date, so we take the first design and let queries do the windowing; a heavy BI workload would justify the second.

Consumers who never want history get a current view, and it costs one model:

-- models/marts/dim_customer.sql
{{ config(materialized='view') }}

select
    customer_sk,
    customer_id,
    full_name,
    email,
    city,
    country,
    segment
from {{ ref('dim_customer_history') }}
where is_current

One row per customer, today’s truth, keyed by the same customer_sk the facts already carry — a Type 1 face over a Type 2 spine. Analysts who want simplicity join this; analysts who want the truth over time join the history table. Both are reading the same captured changes.

Where snapshots stop being Type 2

dbt snapshots are excellent at what they do, and what they do is narrower than the full Type 2 requirement. Three structural limits, in ascending order of pain.

They record observation time, not business-effective time. A snapshot can only date a change to when it saw it (check) or to the source’s updated_at (timestamp). Neither is a business-effective date. When the pricing team decides on July 1st that a book’s list price changes on August 1st, the source row updates in July, and the snapshot opens the new version in July. There is nowhere in the mechanism to say “this version becomes true in the future,” or “this change was retroactively effective last month.” If your dimension’s changes carry effective dates as data, snapshots will systematically mis-date your history.

They only see changes that survive until a run. A snapshot’s history has the resolution of its schedule. If city goes Boston → Chicago → Denver between Monday’s run and Tuesday’s, the snapshot records Boston → Denver; Chicago never existed. Run cadence is a history-fidelity dial: daily runs mean daily-grain history, and no cadence catches everything. For most dimensions that’s fine — they’re called slowly changing for a reason. For audit-grade requirements it isn’t, and no amount of snapshot configuration fixes it, because the mechanism is polling, not change capture.

They cannot back-fill, and they cannot be rebuilt. The history starts the day you first run the snapshot; everything before is gone. If you deploy the snapshot in June, there is no history of May — even if the source could tell you about May. Equally, there’s no repair path: a bug that poisoned three weeks of versions can’t be fixed with --full-refresh, because rebuilding the table from today’s source yields exactly one version per row — today’s. The snapshot table is the only stateful, unrebuildable object in a dbt project. Treat it accordingly: it lives in one production schema, dev and CI environments don’t get to recreate it, and it’s in your backup story. Every other table in the project is disposable; this one is a source of record you happen to generate.

When you need to roll your own

All three limits share a root cause: the snapshot infers history by watching current state. The moment your source can hand you history as data — an effective_from column, an audit table, a CDC change stream — inference is the wrong tool, and hand-rolling Type 2 becomes both possible and better. Given a changelog like stg_customer_changes (one row per customer per change, carrying effective_from), the windows derive with one window function:

-- models/marts/dim_customer_history.sql  (changelog-derived variant)
{{ config(materialized='table') }}

select
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'effective_from']) }} as customer_version_sk,
    {{ dbt_utils.generate_surrogate_key(['customer_id']) }}                   as customer_sk,
    customer_id,
    city,
    country,
    segment,
    effective_from                                       as valid_from,
    coalesce(
        lead(effective_from) over (
            partition by customer_id
            order by effective_from
        ),
        to_date('9999-12-31')
    )                                                    as valid_to,
    lead(effective_from) over (
        partition by customer_id
        order by effective_from
    ) is null                                            as is_current
from {{ ref('stg_customer_changes') }}

Each version’s valid_to is simply the next version’s valid_from (lead() over the change sequence), with the sentinel closing the ranks. Look at what this buys against the three limits: valid_from is the business date, because it came from the data. Every change in the log appears, regardless of when any job ran. And the table is a plain deterministic model — rebuildable from scratch, back-fillable to wherever the log starts, testable in CI, safe to --full-refresh. A late-arriving, retroactive change is handled by rebuilding: the new changelog row slots into the sequence and lead() re-derives every window around it. That last property is the entire subject of the next chapter, so file it away.

At scale you’d wrap this in an incremental model — recompute windows only for customers with new changes, using the techniques from dbt’s incremental models — but the table form is the idea. The decision rule falls out cleanly: source gives you only current state → snapshot. Source gives you history as data → derive the windows yourself. Plenty of warehouses run both, per dimension, and that’s not inconsistency — it’s matching the tool to what each source can actually tell you.

The tests that actually protect it

A Type 2 dimension has failure modes that unique and not_null can’t see. The join contract is windows: for each customer, no overlaps, no gaps, exactly one current row. A duplicated window double-counts every fact that lands in it; a gap silently drops facts; two current rows corrupt every is_current rollup. The test that defends the contract is dbt_utils.mutually_exclusive_ranges:

# models/marts/_marts.yml
models:
  - name: dim_customer_history
    data_tests:
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: valid_from
          upper_bound_column: valid_to
          partition_by: customer_id
          gaps: not_allowed
    columns:
      - name: customer_version_sk
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - unique:
              config:
                where: "is_current"

mutually_exclusive_ranges sorts each customer’s windows and asserts that each one ends exactly where the next begins. gaps: not_allowed is the strict form you want for SCD, since a Type 2 dimension should tile time completely from first sight to the sentinel. Note it needs comparable, non-null bounds, which the 9999-12-31 sentinel guarantees; on a null-terminated snapshot you’d coalesce in the model first. The unique test with where: "is_current" enforces the one-current-row rule — a plain unique test on customer_id would be wrong, since history means multiple rows per customer.

Will these ever fire on a healthy snapshot? Rarely — dbt’s merge maintains the invariant. They fire when someone hand-patches the snapshot table (next chapter makes an honest case for doing exactly that), when a changelog-derived dimension ingests duplicate or contradictory change rows, or when two snapshot jobs run concurrently and race. Those are precisely the moments you want a red build instead of a subtly wrong report — quarters later, in front of an executive, which is where unprotected window corruption is traditionally discovered.

Final thoughts

dbt snapshots are the rare abstraction that gives you the full value of a hard pattern — close-and-insert versioning, windowed history, current flags — at close to zero implementation cost, and the temptation is to stop thinking once the YAML parses. Resist that on two fronts. First, the snapshot is infrastructure, not a dimension: keep it slim, feed it a boring staging model, guard it like the unrebuildable source of record it is, and do your modeling in the layer above. Second, know exactly which promise it makes — history of what we observed, dated to when we observed it — and stop expecting the other promise, history as the business meant it. When the source can tell you what the business meant, take the data and derive the windows yourself; a lead() over a changelog is less clever than a snapshot and strictly more truthful. The skill isn’t operating the tool. It’s knowing which of the two histories you’re capturing — and whether it’s the one your reports claim to show.

Next: Late-Arriving Data: The Warehouse After Hours