dbt Snapshots: SCD-2 Without Writing a Single MERGE Statement

dbt snapshots are the declarative feature that quietly killed the hand-rolled SCD-2 MERGE statement — and the single piece of dbt infrastructure most senior analytics engineers inherit half-configured on the day someone asks "what did this customer's plan look like on the 14th of last month?" A scd type 2 dbt history table is the answer to every point-in-time question in a warehouse: what was the customer's tier when they placed that order, what was the discount rate at fulfilment time, what did the product catalog say the price was three revisions ago. Before dbt shipped snapshots, you built that history by hand with a MERGE plus a NOT MATCHED clause plus a WHEN MATCHED THEN UPDATE plus a WHEN MATCHED THEN INSERT — and you got it wrong the first three times because SCD-2 is easy to describe and hard to write correctly.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "when do you reach for the check strategy versus the timestamp strategy?" or "what does invalidate_hard_deletes actually do?" or "walk me through the four dbt_valid_* columns and how you'd query the state of a row on a specific date." It covers the pre-dbt SCD-2 pain and why snapshots collapse it to a nine-line snapshot config, the two snapshot strategy primitives — check strategy for tables without an updated_at and timestamp strategy for the common case — the anatomy of the resulting snapshot table with the four dbt-managed meta-columns, the hard-delete story via the dbt snapshot invalidate hard deletes toggle, and the production patterns senior teams ship — separate target_schema, unique_key on a natural key, snapshot cadence per source velocity, and the audit-safe migration from a hand-rolled dbt slowly changing dimension MERGE to a declarative dbt scd2 snapshot. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the modelling axis with the optimization practice library →.

---

On this page

• Why dbt snapshots replaced the hand-rolled SCD-2 MERGE
• The two snapshot strategies — timestamp + check
• Snapshot table anatomy — the four dbt_valid_* columns
• Hard deletes + invalidate_hard_deletes
• Production patterns — sources, target_schema, dbt Cloud
• Cheat sheet — dbt snapshot recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why dbt snapshots replaced the hand-rolled SCD-2 MERGE

The pre-dbt SCD-2 world was hand-rolled MERGE — declarative snapshots collapsed it to a 9-line config

The one-sentence invariant: a dbt snapshot is a declarative SCD-2 primitive that turns a SELECT * FROM source statement plus a strategy, a unique_key, and an updated_at (or check_cols) column into a warehouse-managed history table with four automatically-populated meta-columns and no MERGE statement anywhere in your project. Before dbt shipped snapshots, every analytics team wrote the same SCD-2 MERGE by hand — six or seven times — and got it wrong the first three because SCD-2 is a surprisingly deep well of edge cases (open-row semantics, close-old-row-then-insert-new-row atomicity, natural-key clashes, hard-delete handling, and the "same second, different value" ordering problem). Snapshots collapse all of that into a Jinja block that dbt compiles into a MERGE behind the scenes — the MERGE you never touch, never review, never regress on.

The four axes interviewers actually probe.

• Strategy. timestamp or check — each one detects change differently. Timestamp trusts an updated_at column; check hashes a list of check_cols. Knowing when each one wins is the first senior signal.
• Unique key. Every snapshot needs a natural key that identifies the entity across time — customer_id, product_sku, tenant_id. Get this wrong and you either miss updates (natural key too broad) or create duplicate rows (natural key too narrow).
• Updated_at. For the timestamp strategy, an updated_at column that monotonically increases when the source row changes. The gotcha: source clocks vs warehouse clocks, second-precision vs millisecond-precision, and the "two updates in the same second" edge case.
• Hard deletes. By default, dbt snapshots ignore source deletes — the row stays open forever. invalidate_hard_deletes: true closes the row when it disappears from source. The trade-off is a full-source scan on every snapshot run.

Why hand-rolled MERGE was the wrong abstraction.

• The atomicity trap. SCD-2 requires you to (a) close the currently-open row (UPDATE ... SET valid_to = now() WHERE valid_to IS NULL AND key = ...) and (b) insert the new row (INSERT ... VALUES (..., now(), NULL)) — atomically. A hand-rolled two-statement version has a race window where a concurrent read sees zero open rows.
• The "same second" edge case. If the update happens at 12:00:00.500 and the snapshot runs at 12:00:00.501, the old row closes at 501ms and the new row opens at 501ms — but a naive query for "state at 12:00:00.500" returns the new row, not the old one. Getting the boundary semantics right requires strict-less-than on one side.
• The retro-update problem. Source systems sometimes back-date corrections. If a row's updated_at moves backwards, a naive MERGE inserts a new SCD-2 row with a valid_from before the previous close date — the history becomes non-monotonic. dbt's snapshot logic detects this.
• The hard-delete question. SCD-2 has three valid answers for "what happens when a row disappears from source?" — leave it open forever (default), close it at the snapshot run time (invalidate_hard_deletes), or delete the history entirely (never). Getting this wrong means either lost audit trail or misleading "still active" rows.
• The developer-time cost. A hand-rolled SCD-2 MERGE is ~40 lines of SQL per table. A dbt snapshot is 9 lines of Jinja. Multiply by 20 dimensions in a mature warehouse and the productivity delta is a full-time headcount.

What a dbt snapshot actually is.

• Input side. A snapshot block in a .sql file under the project's snapshots/ directory. The block contains a config() call declaring the strategy, unique_key, updated_at (or check_cols), and target_schema — plus a Jinja SELECT from a source or a staging model.
• Compilation. On dbt snapshot, dbt compiles the block to a warehouse-specific MERGE-plus-INSERT statement that (a) closes rows whose payload changed, (b) inserts a new open row for each changed key, (c) inserts open rows for brand-new keys, (d) optionally closes rows for keys that vanished from source.
• Output side. A physical table (default in the project's default schema; commonly relocated to a snapshots schema) with the source columns plus four dbt-managed meta-columns: dbt_scd_id, dbt_updated_at, dbt_valid_from, dbt_valid_to.
• Idempotence. Running the snapshot twice in a row against unchanged source is a no-op. This is the property that makes snapshots safe to re-run on any cadence.

What interviewers listen for.

• Do you say "declarative SCD-2 replaces hand-rolled MERGE" in the first sentence when asked what a snapshot is? — senior signal.
• Do you mention the four dbt-managed meta-columns without prompting? — senior signal.
• Do you push back on "just add valid_from and valid_to columns to the source table" with the retro-update and atomicity arguments? — required answer.
• Do you describe snapshots as a contract with the warehouse (declarative, idempotent, audit-safe) rather than as "a way to save history"? — senior signal.

Worked example — the pain of the hand-rolled SCD-2 MERGE

Detailed explanation. Walk an interviewer through the ~40-line MERGE that a pre-dbt team would ship for a customers SCD-2 history table. Then compare it to the 9-line dbt snapshot that replaces it. The delta motivates the entire snapshot feature.

• The source. A raw.customers table with columns id, email, plan, updated_at.
• The goal. A warehouse.customers_history table with columns id, email, plan, valid_from, valid_to.
• The invariant. For each id, exactly one row has valid_to IS NULL (the current row); all other rows for that id have a non-null valid_to.

Question. Write the SCD-2 MERGE statement by hand for the customers table, then rewrite it as a dbt snapshot. Compare line count and the number of edge cases each one handles.

Input.

Table	Column	Type	Notes
raw.customers	id	INT	natural key
raw.customers	email	TEXT	payload
raw.customers	plan	TEXT	payload
raw.customers	updated_at	TIMESTAMP	monotonic on row change
warehouse.customers_history	id	INT	natural key
warehouse.customers_history	email	TEXT	payload
warehouse.customers_history	plan	TEXT	payload
warehouse.customers_history	valid_from	TIMESTAMP	open time
warehouse.customers_history	valid_to	TIMESTAMP	close time (NULL = open)

Code.

-- The hand-rolled SCD-2 MERGE — the version dbt snapshots replaced
-- Step 1: close any currently-open row whose payload changed
UPDATE warehouse.customers_history AS h
SET    valid_to = s.updated_at
FROM   raw.customers AS s
WHERE  h.id       = s.id
  AND  h.valid_to IS NULL
  AND  (h.email  <> s.email OR h.plan <> s.plan)
  AND  s.updated_at > h.valid_from;

-- Step 2: insert a new open row for each changed key
INSERT INTO warehouse.customers_history (id, email, plan, valid_from, valid_to)
SELECT s.id, s.email, s.plan, s.updated_at, NULL
FROM   raw.customers AS s
LEFT   JOIN warehouse.customers_history AS h
       ON h.id = s.id AND h.valid_to IS NULL
WHERE  h.id IS NULL
   OR  (h.email <> s.email OR h.plan <> s.plan);

-- Step 3: brand-new keys (no history yet)
INSERT INTO warehouse.customers_history (id, email, plan, valid_from, valid_to)
SELECT s.id, s.email, s.plan, s.updated_at, NULL
FROM   raw.customers AS s
WHERE  NOT EXISTS (
  SELECT 1 FROM warehouse.customers_history AS h WHERE h.id = s.id
);

-- The dbt snapshot equivalent — 9 lines of Jinja, no MERGE anywhere
-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at'
    ) }}
    SELECT id, email, plan, updated_at FROM {{ source('raw', 'customers') }}
{% endsnapshot %}

Step-by-step explanation.

1. The hand-rolled MERGE ships three separate DML statements — close the open row, insert the new open row, insert brand-new keys. The three statements must run inside a single transaction; a partial failure leaves the history in an inconsistent state.
2. The UPDATE ... FROM in step 1 has a subtle bug: s.updated_at > h.valid_from guards against retro-updates that would produce a valid_to < valid_from. Miss that predicate and back-dated source corrections create malformed history rows.
3. The LEFT JOIN in step 2 finds keys that either have no open row (brand-new) or have an open row with a different payload (changed). The OR inside the WHERE handles both cases in one INSERT. Miss the LEFT JOIN and you either double-insert or miss inserts.
4. The dbt snapshot version delegates all of this to the dbt macro. The strategy='timestamp' tells dbt to compare updated_at values; the unique_key='id' identifies the natural key; the target_schema='snapshots' places the physical table on its own schema. Nine lines total; every edge case handled.
5. The compiled output of the snapshot is a MERGE (Snowflake, BigQuery, Databricks) or a paired UPDATE+INSERT wrapped in a transaction (Postgres, Redshift). The dbt-generated MERGE handles the same three cases as the hand-rolled version plus the "same-second update" edge case and the atomicity guarantee.

Output.

Approach	Line count	Edge cases handled	Idempotent	Audit-safe
Hand-rolled MERGE	~40	3 (partial)	requires txn wrapping	fragile
dbt snapshot	9	5+ (all)	yes (by construction)	yes

Rule of thumb. Never write an SCD-2 MERGE by hand in a dbt project. If the source has an updated_at, use a timestamp snapshot; if it does not, use a check snapshot. The 40-line hand-rolled version is a maintenance liability from the moment it ships.

Worked example — retro-update edge case that a naive MERGE misses

Detailed explanation. A CRM system supports "back-dated corrections" — a support agent can set the updated_at of a customer row to a timestamp in the past to correct a data-entry mistake. A naive SCD-2 MERGE that only checks "did the payload change?" will insert a new open row with a valid_from earlier than the currently-open row's valid_from, producing a non-monotonic history. Show how dbt's snapshot logic detects and rejects the retro-update, and how the hand-rolled MERGE has to be augmented to match.

• The setup. Row A has updated_at = 2026-06-01; it's the currently-open row.
• The retro-update. A support agent sets updated_at = 2026-05-15 (before A's valid_from).
• The bug. A naive MERGE creates row B with valid_from = 2026-05-15 and closes A at valid_to = 2026-05-15. Now A has valid_to < valid_from.

Question. Show the retro-update scenario with concrete data, demonstrate the bug in a naive MERGE, and show how the dbt snapshot handles it.

Input.

Time (real)	Event	Source row updated_at
2026-06-01	Customer signs up on Basic plan	2026-06-01
2026-06-15	Customer upgrades to Pro	2026-06-15
2026-06-22	Support agent back-dates a correction	2026-05-15

Code.

-- Before the retro-update — history is correct
SELECT id, plan, valid_from, valid_to
FROM   warehouse.customers_history
WHERE  id = 42
ORDER  BY valid_from;
-- id | plan  | valid_from | valid_to
-- 42 | Basic | 2026-06-01 | 2026-06-15
-- 42 | Pro   | 2026-06-15 | NULL     (open)

-- The retro-update — source now shows plan=Enterprise, updated_at=2026-05-15
-- A naive MERGE (missing the s.updated_at > h.valid_from predicate):
UPDATE warehouse.customers_history AS h
SET    valid_to = s.updated_at         -- valid_to = 2026-05-15
FROM   raw.customers AS s
WHERE  h.id = s.id AND h.valid_to IS NULL AND h.plan <> s.plan;
-- Now the Pro row has valid_from=2026-06-15 and valid_to=2026-05-15 — INVERTED

# dbt snapshot config — no retro-update code required
# snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at'
    ) }}
    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'customers') }}
    WHERE  updated_at > (
        SELECT COALESCE(MAX(dbt_updated_at), '1970-01-01')
        FROM   {{ this }}
    )
{% endsnapshot %}

Step-by-step explanation.

1. Before the retro-update, the history table has two rows for id = 42: a closed Basic row and an open Pro row. The invariant is valid_from < valid_to for closed rows and valid_to IS NULL for the open row.
2. The naive MERGE compares payloads (h.plan <> s.plan) and uses s.updated_at as the new close time. Because the retro-update's updated_at = 2026-05-15 is before the open row's valid_from = 2026-06-15, the close time is set to a value earlier than the open time — the history is corrupt.
3. dbt's snapshot logic guards against this by comparing updated_at against dbt_updated_at on the currently-open row. If the source updated_at is not strictly greater, the change is not recorded — dbt treats retro-updates as no-ops rather than corruption.
4. The optional WHERE updated_at > (SELECT MAX(dbt_updated_at) FROM {{ this }}) filter is a defensive layer many teams add. It filters out any source row whose updated_at is not monotonically greater than the snapshot's high-water mark. Retro-updates are silently dropped at the SELECT stage.
5. The result is an audit-safe history: retro-updates are logged separately in a monitoring table, but the SCD-2 history remains monotonic. The hand-rolled version needs 5–10 extra lines to match; the dbt version needs zero.

Output.

Approach	Retro-update outcome	History integrity
Naive hand-rolled MERGE	valid_to < valid_from	corrupt
Hand-rolled with s.updated_at > h.valid_from guard	retro-update recorded correctly if monotonic; dropped otherwise	correct if written right
dbt snapshot (timestamp)	retro-update ignored	audit-safe by construction

Rule of thumb. Retro-updates are the most common cause of "malformed SCD-2 history" in hand-rolled implementations. Prefer the dbt snapshot, whose macro contains the guard by default.

Worked example — the "same-second update" boundary problem

Detailed explanation. Two updates to the same source row arrive in the same warehouse second — the updated_at values are identical to the second's precision. A naive MERGE either loses the second update (thinks nothing changed) or closes and opens the same row at the identical timestamp, producing a zero-length history row. Walk through the boundary math and show how dbt's snapshot handles the sub-second case.

• The scenario. Update 1 at 2026-06-22 12:00:00.400; Update 2 at 2026-06-22 12:00:00.700; snapshot runs at 12:00:01.
• The bug at second-precision. Both updates have updated_at = 12:00:00 (truncated); the MERGE cannot distinguish them.
• The dbt behaviour. dbt uses millisecond precision (or the source's native precision); the snapshot detects both changes if the source column is high-resolution.

Question. Show the "same-second" scenario with both a low-precision and a high-precision updated_at column. Explain how dbt's strategy='timestamp' handles each case.

Input.

Real time	Update	updated_at at second precision	updated_at at millisecond precision
12:00:00.400	plan → Pro	2026-06-22 12:00:00	2026-06-22 12:00:00.400
12:00:00.700	plan → Enterprise	2026-06-22 12:00:00	2026-06-22 12:00:00.700
12:00:01.000	snapshot runs	—	—

Code.

-- Case A — source column stored at second precision
-- The snapshot sees only the FINAL state at 12:00:00
-- Update 1 (plan=Pro) is lost; only the last-writer-wins state is recorded.
CREATE TABLE raw.customers (
  id         INT,
  plan       TEXT,
  updated_at TIMESTAMP(0)   -- second precision
);

-- Case B — source column stored at millisecond precision
-- The snapshot sees two distinct updated_at values; both changes captured.
CREATE TABLE raw.customers (
  id         INT,
  plan       TEXT,
  updated_at TIMESTAMP(3)   -- millisecond precision
);

# dbt snapshot — timestamp strategy, high-precision column recommended
# snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at'
    ) }}
    SELECT id, plan, updated_at
    FROM   {{ source('raw', 'customers') }}
{% endsnapshot %}

Step-by-step explanation.

1. In case A (second precision), the source column truncates both updates to 12:00:00. When the snapshot runs at 12:00:01 and compares the source's updated_at to the snapshot's dbt_updated_at on the currently-open row, only one transition is detected — the intermediate state (Pro) is lost.
2. The bug is not a dbt bug; it's a schema bug. Any SCD-2 mechanism (dbt, hand-rolled, or otherwise) is limited by the precision of the source's updated_at column. If your source loses intermediate states, so does your history.
3. In case B (millisecond precision), the source column stores both updates as distinct values. When the snapshot runs, dbt sees two updated_at values greater than the currently-open row's dbt_updated_at. The snapshot behaviour is warehouse-specific: some warehouses produce two history rows (one per input row); most produce one (the last value wins per snapshot run, because the snapshot is a batch operation).
4. The recommended pattern for "capture every intermediate state" is not a snapshot at all — it's a CDC (change data capture) stream from the source (Debezium, Fivetran CDC, Airbyte CDC) plus a downstream dbt model that unpivots the stream into an SCD-2 shape. Snapshots are a batch primitive; CDC is a streaming primitive.
5. For most analytics workloads, second-precision updated_at is sufficient because intra-second updates are noise. Confirm the source precision before designing the snapshot; align the snapshot cadence to be higher-resolution than the noise you care about.

Output.

Case	Source precision	Snapshot cadence	Intermediate states captured?
A	second	1 hour	last-write-wins per snapshot; intra-second updates lost
B	millisecond	1 hour	last-write-wins per snapshot; sub-second updates within the run are collapsed
C (CDC)	any	streaming	every update captured

Rule of thumb. dbt snapshots are a batch tool. If you need every intermediate state, layer a CDC stream upstream of the snapshot; otherwise accept that snapshot cadence + source updated_at precision jointly determine the temporal resolution of your history.

Senior interview question on why dbt snapshots exist

A senior interviewer often opens with: "You inherit an analytics warehouse where every dimension has a hand-rolled SCD-2 MERGE. Walk me through why you'd migrate them to dbt snapshots, what the migration looks like, and what edge cases you'd worry about in the switch-over."

Solution Using declarative dbt snapshots with a phased audit-safe migration

-- Step 1 — pick one dimension, keep both pipelines running in parallel for 1 week
-- Existing hand-rolled table stays: warehouse.customers_history
-- New dbt snapshot writes to:       snapshots.customers_snapshot

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=true
    ) }}
    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'customers') }}
{% endsnapshot %}

-- Step 2 — backfill from the existing history into the snapshot table
-- This is the audit-safe move: we do NOT run the snapshot cold against source
-- (which would collapse existing history into a single row per key).
-- Instead we hydrate the snapshot table with the existing history rows.

INSERT INTO snapshots.customers_snapshot
  (id, email, plan, updated_at,
   dbt_scd_id, dbt_updated_at, dbt_valid_from, dbt_valid_to)
SELECT
  h.id, h.email, h.plan, h.valid_from AS updated_at,
  MD5(CONCAT(h.id, '-', h.valid_from)) AS dbt_scd_id,
  h.valid_from AS dbt_updated_at,
  h.valid_from AS dbt_valid_from,
  h.valid_to   AS dbt_valid_to
FROM warehouse.customers_history AS h;

-- Step 3 — run BOTH pipelines every day for a week; diff the resulting tables
-- to prove the snapshot produces identical rows to the hand-rolled MERGE.

WITH new AS (SELECT id, plan, dbt_valid_from AS valid_from, dbt_valid_to AS valid_to FROM snapshots.customers_snapshot),
     old AS (SELECT id, plan, valid_from, valid_to FROM warehouse.customers_history)
SELECT 'in_new_not_old' AS delta, id, plan, valid_from, valid_to FROM new EXCEPT SELECT 'in_new_not_old', id, plan, valid_from, valid_to FROM old
UNION ALL
SELECT 'in_old_not_new', id, plan, valid_from, valid_to FROM old EXCEPT SELECT 'in_old_not_new', id, plan, valid_from, valid_to FROM new;

-- Step 4 — after one week of zero diff, cut over: point downstream models at snapshots.customers_snapshot
-- Delete the hand-rolled MERGE and warehouse.customers_history in step 5 after another week of soak.

Step-by-step trace.

Step	Before	After
Existing history	warehouse.customers_history (hand-rolled MERGE)	preserved during migration
New snapshot table	—	snapshots.customers_snapshot (dbt-managed)
Backfill	—	hand-rolled rows inserted into snapshot table with correct meta-columns
Parallel run window	0 days	7 days both pipelines running
Diff query	—	passes with zero rows for 7 consecutive days
Cutover	downstream reads warehouse.customers_history	downstream reads snapshots.customers_snapshot
Decommission	hand-rolled MERGE still exists	deleted after +7 days soak

After the rollout, the SCD-2 pipeline is declarative, idempotent, and audit-safe. The hand-rolled MERGE is gone; the 40-line SQL is replaced with 9 lines of Jinja. Future dimensions ship with a snapshot on day one instead of a MERGE.

Output:

Surface	Before	After
SQL line count per SCD-2 dimension	~40	9
MERGE statements in the project	many	0
Retro-update handling	manual guard	built-in
Hard-delete handling	manual	invalidate_hard_deletes toggle
Downstream contract	table shape	table shape (identical)

Why this works — concept by concept:

• Declarative SCD-2 — the snapshot config is a contract with the warehouse: "give me a history table for this source with this strategy and this unique_key." The MERGE is generated, not written. The generated MERGE is audited by dbt-labs, not by every team that writes their own.
• Parallel-run migration — running both pipelines side-by-side for a week is the audit-safe migration pattern. The diff query is the correctness proof; the soak period catches edge cases the diff missed on day one.
• Backfill via history hydration — you do not run the snapshot cold against source (that would collapse history into a single row per key). You hydrate the snapshot table with the existing history and let dbt take over from there.
• invalidate_hard_deletes toggle — the migration is a good time to add hard-delete tracking that the hand-rolled MERGE didn't have. Toggle it on during the parallel run, watch how deletes are recorded, and confirm downstream models handle the closed-row semantics.
• Cost — one senior-engineer-week per dimension for the migration, amortised across future retirements of hand-rolled MERGEs. The ongoing per-run cost is identical (both compile to a MERGE); the developer-time cost is O(1) per snapshot vs O(N) for the hand-rolled version.

SQL
Topic — sql
SQL SCD-2 modelling and MERGE problems

Practice →

ETL Topic — etl ETL problems on slowly-changing dimensions

Practice →

---

2. The two snapshot strategies — timestamp + check

Two strategies, one contract — timestamp trusts an updated_at column, check hashes a set of payload columns

The mental model in one line: timestamp strategy uses an updated_at column to detect change (fast, simple, correct if the source's clock is trustworthy), and check strategy hashes a list of check_cols on every snapshot run to detect change (slower, works without an updated_at, sensitive to which columns you include). Every other snapshot decision — unique_key, target_schema, invalidate_hard_deletes — is orthogonal to the strategy choice. Picking the right strategy is 60% of the snapshot interview.

Timestamp strategy — the simple case.

• Detection. Compare source updated_at against snapshot's dbt_updated_at. If source is greater, the row changed.
• Config. strategy='timestamp', updated_at='updated_at' (or whatever the source column is called).
• When it wins. Any source table with a reliable updated_at — application databases with an ORM that auto-updates the column, CDC-produced tables, event streams with a processed_at.
• The cost. One column comparison per row. The snapshot query is essentially SELECT * FROM source WHERE updated_at > MAX(dbt_updated_at) — a fast scan on an indexed column.
• The trap. Sources with an unreliable updated_at — the column exists but isn't reliably updated on every row change. Interviewer signal: mention this trap without prompting.

Check strategy — the fallback.

• Detection. Compute a hash of the check_cols on both source and snapshot's currently-open row. If the hashes differ, the row changed.
• Config. strategy='check', check_cols=['col_a', 'col_b'] (or check_cols='all' for every non-meta column).
• When it wins. Sources with no updated_at column at all, sources with an unreliable updated_at, and sources where you specifically want to track changes to a subset of columns (e.g. "only track plan changes, not email changes").
• The cost. A hash computation per row per snapshot run. On a 100M-row source, this is 10-100× slower than the timestamp strategy.
• The trap. check_cols='all' catches noise columns you didn't mean to track (e.g. a last_login_at column that changes every minute), producing spurious history rows. Prefer an explicit list.

Strategy comparison — the interviewer's table.

• Detection cost. timestamp: O(1) per row; check: O(hashed columns) per row.
• Correctness dependency. timestamp: depends on source updated_at; check: depends on the choice of check_cols.
• Retro-update handling. timestamp: built-in guard; check: not applicable (hash detects any change).
• Sub-second precision. timestamp: bounded by source column precision; check: not applicable (change is detected per snapshot run, not per source update).
• Column subset tracking. timestamp: whole row; check: exactly the check_cols set.

The three most common configuration mistakes.

• check_cols='all' with a noisy column. A last_seen_at or updated_by column drifts every hour; the check strategy creates a new history row every hour. Fix: enumerate check_cols explicitly, excluding noise.
• strategy='timestamp' with a stale updated_at. The source app forgot to update the column when the row was patched by a manual UPDATE; the snapshot misses the change. Fix: verify the source's update path — every mutation must touch updated_at.
• Different unique_key from what the source uses. The unique_key must uniquely identify the entity across all history. Composite keys need quoting: unique_key="tenant_id || '-' || record_id".

Common interview probes on strategy.

• "Your source has no updated_at. What strategy do you pick?" — check strategy with explicit check_cols.
• "Why is check_cols='all' risky?" — noise columns produce spurious history.
• "How does dbt handle retro-updates under strategy='timestamp'?" — built-in guard: source updated_at must strictly exceed dbt_updated_at.
• "The source app doesn't reliably update updated_at. Now what?" — check strategy, or add a trigger to the source to bump updated_at on every mutation.

Worked example — timestamp strategy on a user profile table

Detailed explanation. A raw.users table has columns id, email, name, plan, updated_at. The application ORM (Rails, Django, Sequelize) auto-updates updated_at on every row mutation. This is the textbook case for the timestamp strategy: nine lines of Jinja, one updated_at column, correct on day one.

• The source. Application-owned users table with reliable updated_at.
• The goal. Track plan, email, and name history per user.
• The cadence. Snapshot runs every hour.

Question. Write the dbt snapshot for raw.users and demonstrate what the snapshot table looks like after 3 hours of updates.

Input.

Table	Column	Type
raw.users	id	INT
raw.users	email	TEXT
raw.users	name	TEXT
raw.users	plan	TEXT
raw.users	updated_at	TIMESTAMP

Code.

# snapshots/users_snapshot.sql
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at'
    ) }}

    SELECT id, email, name, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

-- Simulated sequence of source updates + snapshot runs
-- t = 09:00 — initial state: user 42 on Basic plan
INSERT INTO raw.users (id, email, name, plan, updated_at)
VALUES (42, '[e-mail]', 'Ada', 'Basic', '2026-06-22 09:00:00');

-- t = 09:30 — snapshot runs
-- inserts one open row for user 42 (Basic, valid_from=09:00, valid_to=NULL)

-- t = 10:15 — user upgrades to Pro
UPDATE raw.users SET plan='Pro', updated_at='2026-06-22 10:15:00' WHERE id=42;

-- t = 10:30 — snapshot runs
-- closes Basic row (valid_to=10:15), inserts open Pro row (valid_from=10:15, valid_to=NULL)

-- t = 11:20 — user upgrades to Enterprise
UPDATE raw.users SET plan='Enterprise', updated_at='2026-06-22 11:20:00' WHERE id=42;

-- t = 11:30 — snapshot runs
-- closes Pro row (valid_to=11:20), inserts open Enterprise row (valid_from=11:20, valid_to=NULL)

Step-by-step explanation.

1. At 09:30, dbt runs the snapshot for the first time. No rows exist in snapshots.users_snapshot; dbt inserts one row per source row, each with dbt_valid_from = updated_at, dbt_valid_to = NULL, dbt_updated_at = updated_at, and dbt_scd_id = MD5(id || updated_at).
2. At 10:30, dbt runs the snapshot again. The source row for user 42 now has updated_at = 10:15:00; the currently-open snapshot row for user 42 has dbt_updated_at = 09:00:00. The comparison detects the change.
3. dbt closes the currently-open Basic row by setting dbt_valid_to = 10:15:00 (the source's new updated_at). It then inserts a new open row with dbt_valid_from = 10:15:00, dbt_valid_to = NULL.
4. At 11:30, the same logic runs again for the Pro → Enterprise transition. The Pro row's dbt_valid_to is set to 11:20:00; a new open Enterprise row is inserted.
5. After three runs, the snapshot table has three rows for user 42 — one closed Basic (09:00 → 10:15), one closed Pro (10:15 → 11:20), one open Enterprise (11:20 → NULL). Every plan transition is captured with the exact timestamp of the source event.

Output.

id	plan	dbt_valid_from	dbt_valid_to
42	Basic	2026-06-22 09:00:00	2026-06-22 10:15:00
42	Pro	2026-06-22 10:15:00	2026-06-22 11:20:00
42	Enterprise	2026-06-22 11:20:00	NULL

Rule of thumb. If the source has a reliable updated_at, always use the timestamp strategy. It is faster, simpler, and produces exact-timestamp history rather than snapshot-run-aligned history.

Worked example — check strategy on a product catalog with no updated_at

Detailed explanation. A raw.products table has columns sku, name, price, category. The upstream vendor drops a CSV every night; the CSV is loaded straight into raw.products with a full overwrite. There is no updated_at column. The vendor doesn't provide one; the upstream ETL doesn't add one. This is the textbook case for the check strategy — hash the payload columns and detect change by hash mismatch.

• The source. Vendor-supplied product catalog with no updated_at.
• The goal. Track price and category changes; email changes to name are noise and should be ignored.
• The cadence. Snapshot runs every night after the CSV load.

Question. Write the dbt snapshot for raw.products using the check strategy. Explain why check_cols=['price', 'category'] beats check_cols='all'.

Input.

Table	Column	Type	Track changes?
raw.products	sku	TEXT	natural key (no)
raw.products	name	TEXT	noise (no)
raw.products	price	NUMERIC	yes
raw.products	category	TEXT	yes

Code.

# snapshots/products_snapshot.sql
{% snapshot products_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='sku',
        strategy='check',
        check_cols=['price', 'category']
    ) }}

    SELECT sku, name, price, category
    FROM   {{ source('raw', 'products') }}
{% endsnapshot %}

-- Under the hood, dbt runs (compiled Snowflake DDL, simplified):
MERGE INTO snapshots.products_snapshot AS h
USING (
  SELECT
    sku, name, price, category,
    MD5(COALESCE(price::text,'') || '|' || COALESCE(category,'')) AS row_hash
  FROM   raw.products
) AS s
ON h.sku = s.sku AND h.dbt_valid_to IS NULL
WHEN MATCHED AND h.row_hash <> s.row_hash THEN
  UPDATE SET dbt_valid_to = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (sku, name, price, category, dbt_scd_id, dbt_updated_at,
          dbt_valid_from, dbt_valid_to)
  VALUES (s.sku, s.name, s.price, s.category,
          MD5(s.sku || s.row_hash), CURRENT_TIMESTAMP(),
          CURRENT_TIMESTAMP(), NULL);

Step-by-step explanation.

1. The check_cols=['price', 'category'] config tells dbt to hash only these two columns on every snapshot run. The name column is included in the SELECT but not in the hash — its changes are silently ignored (which is what we want: name changes are cosmetic noise).
2. On the first snapshot run, dbt inserts one open row per source SKU. Each row's dbt_valid_from = CURRENT_TIMESTAMP() (the snapshot run time, not a source timestamp — because we don't have one).
3. On subsequent runs, dbt joins source rows to the currently-open snapshot rows by SKU, compares hashes, and closes rows whose hash changed. New open rows are inserted for the changed SKUs.
4. Because we don't have a source updated_at, the timeline granularity is snapshot cadence — a price change at 3 PM won't be recorded until the next snapshot run at midnight. This is the fundamental trade-off of the check strategy: no source clock means no source-time resolution.
5. If we'd used check_cols='all', the name column would be in the hash. Every night the vendor's CSV has some cosmetic name-normalisation drift, so every product's name would flap slightly, producing a spurious history row every night. check_cols=['price', 'category'] is the intent-preserving choice.

Output.

Config	History quality
check_cols='all'	spurious rows on cosmetic drift
check_cols=['price', 'category']	rows only on price or category change
check_cols=['price']	rows only on price change (misses category)

Rule of thumb. Never use check_cols='all' on a source with cosmetic columns you don't care about. Enumerate the columns you do want to track. The check-cols choice is a business decision, not a technical one.

Worked example — hybrid case where updated_at exists but is unreliable

Detailed explanation. A legacy application writes to raw.orders with an updated_at column, but manual DBA scripts periodically update the table via direct UPDATE ... WHERE ... statements that don't touch updated_at. The column is sometimes correct and sometimes stale. Neither pure strategy works: timestamp misses the manual updates, check ignores the correct ones. The senior answer is a check strategy on the payload columns, ignoring the unreliable updated_at.

• The source. raw.orders with id, status, total, updated_at.
• The problem. Manual scripts update status without touching updated_at.
• The senior answer. Ignore updated_at; use check strategy on status and total.

Question. Given the unreliable updated_at, choose the correct snapshot strategy and defend the choice. What is the trade-off vs the timestamp strategy?

Input.

Column	Reliability
id	trustworthy (primary key)
status	trustworthy payload
total	trustworthy payload
updated_at	untrustworthy (manual scripts skip it)

Code.

# snapshots/orders_snapshot.sql — check strategy despite the column existing
{% snapshot orders_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='check',
        check_cols=['status', 'total']
    ) }}

    SELECT id, status, total, updated_at
    FROM   {{ source('raw', 'orders') }}
{% endsnapshot %}

-- Contrast — the WRONG choice if you trust updated_at:
-- {% snapshot orders_snapshot %}
--     {{ config(strategy='timestamp', updated_at='updated_at', ...) }}
-- {% endsnapshot %}
--
-- Impact: DBA-script updates that bypass updated_at are invisible to the snapshot.
-- The history table shows the row as unchanged for months while the actual
-- status has cycled through 3 states.

Step-by-step explanation.

1. The trap: the updated_at column exists, so a naive engineer picks strategy='timestamp'. It works for the 90% of updates that go through the application (which touches updated_at), and silently misses the 10% that come from DBA scripts.
2. The tell in an audit: dbt_valid_from values on the snapshot table are always at 8 AM (the snapshot cadence), except for one column of history changes that appear to happen when the app runs — but the DBA-script transitions are missing entirely.
3. The senior answer: switch to strategy='check' on the payload columns. The check strategy re-hashes the payload on every snapshot run, so any change — application, DBA script, or otherwise — is detected.
4. The cost: check strategy hashes every row on every run. On a 100M-row orders table, this is a full-table scan per snapshot run. Compare to timestamp, which reads WHERE updated_at > MAX(dbt_updated_at) and skips 99% of rows.
5. Mitigation for the cost: partition the source by created_at and snapshot only recent partitions, or snapshot at a coarser cadence, or (long-term) fix the DBA scripts to touch updated_at. The check strategy is the correctness-first answer; the operational cost is a secondary optimisation.

Output.

Strategy	Correctness	Runtime cost	Recommendation
timestamp (naive)	misses DBA-script updates	cheap	wrong
check (correct)	catches every change	expensive full scan	right
Fix source updated_at + timestamp	catches every change	cheap	best long-term

Rule of thumb. If the source's updated_at is not the only path that mutates the table, use the check strategy. Trust in the timestamp is what makes the timestamp strategy fast; if that trust is misplaced, correctness beats runtime cost.

Senior interview question on picking a snapshot strategy

A senior interviewer might ask: "You have three source tables — a Rails-managed users with a reliable updated_at, a vendor-supplied products CSV with no updated_at, and a legacy orders table where the updated_at is sometimes bypassed by DBA scripts. Design the three snapshots and defend each strategy choice."

Solution Using strategy-per-source with explicit check_cols where needed

# snapshots/users_snapshot.sql — reliable updated_at → timestamp strategy
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=true
    ) }}
    SELECT id, email, name, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

# snapshots/products_snapshot.sql — no updated_at → check strategy, explicit cols
{% snapshot products_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='sku',
        strategy='check',
        check_cols=['price', 'category']
    ) }}
    SELECT sku, name, price, category
    FROM   {{ source('raw', 'products') }}
{% endsnapshot %}

# snapshots/orders_snapshot.sql — untrustworthy updated_at → check strategy
{% snapshot orders_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='check',
        check_cols=['status', 'total']
    ) }}
    SELECT id, status, total, updated_at
    FROM   {{ source('raw', 'orders') }}
{% endsnapshot %}

Step-by-step trace.

Source	updated_at reliable?	Strategy	check_cols	Rationale
users	yes (Rails ORM)	timestamp	—	fast, sub-second precision, retro-update guard
products	no column at all	check	['price', 'category']	hash payload; ignore cosmetic name drift
orders	sometimes bypassed	check	['status', 'total']	correctness first; accept full-scan cost

After the rollout, each source has the strategy that matches its reality. The users snapshot runs in seconds against the last hour of updated_at deltas; the products snapshot runs after the nightly CSV load and hashes the whole catalog; the orders snapshot runs on a coarser cadence but catches DBA-script transitions.

Output:

Snapshot	Runtime	Coverage	Strategy note
users_snapshot	3s (delta scan on updated_at)	100% of app updates	timestamp
products_snapshot	45s (full catalog hash)	100% of price/category changes	check (2 cols)
orders_snapshot	5min (full-scan hash)	100% of app + DBA updates	check (2 cols)

Why this works — concept by concept:

• Strategy per source — a dbt project can and should use different snapshot strategies for different sources. The strategy is a per-snapshot config, not a project-wide default. Pick the strategy that matches the source's semantics.
• Explicit check_cols — check_cols='all' is a foot-gun on any source with cosmetic columns. Enumerate the columns whose changes you want to record; ignore the rest. The choice is a business decision (which transitions matter?), not a technical one.
• Reliable-updated_at short-circuit — the timestamp strategy is 10-100× faster than check because it filters the source scan by updated_at > MAX(dbt_updated_at). Use it whenever the source column is trustworthy.
• Retro-update guard on timestamp — dbt's timestamp strategy silently ignores source rows whose updated_at is not strictly greater than the currently-open row's dbt_updated_at. Retro-updates don't corrupt history.
• Cost — timestamp is O(delta) per run; check is O(source rows) per run. On a 1B-row table, that's the difference between a 10-second run and a 30-minute run. Choose strategy on correctness first; optimise cost afterwards.

SQL
Topic — sql
SQL change-detection and hash comparison problems

Practice →

ETL Topic — etl ETL problems on timestamp vs hash-based CDC

Practice →

---

3. Snapshot table anatomy — the four dbt_valid_* columns

Every dbt snapshot ships four dbt-managed meta-columns — dbt_scd_id, dbt_updated_at, dbt_valid_from, dbt_valid_to

The mental model in one line: every dbt-managed snapshot table has your source columns plus exactly four dbt-managed meta-columns — dbt_scd_id (surrogate primary key), dbt_updated_at (source or run clock), dbt_valid_from (open time), dbt_valid_to (close time or NULL for open rows) — and every point-in-time query is one BETWEEN predicate over the last two. Once you internalise the four columns and their semantics, every downstream query pattern (current state, historical state, transition analysis) follows.

The four columns dbt appends.

• dbt_scd_id. A surrogate primary key — hash of unique_key || dbt_valid_from. Guarantees uniqueness across all history rows; useful as a stable identifier for downstream fact-table joins that need row-version-level fidelity.
• dbt_updated_at. For timestamp strategy: the source's updated_at at the moment of the snapshot. For check strategy: the snapshot run time. Represents "the last time dbt saw this row change."
• dbt_valid_from. The moment this version became current. For timestamp strategy: equals the source updated_at at insert. For check strategy: equals the snapshot run time at insert.
• dbt_valid_to. The moment this version stopped being current. NULL if the version is still current; a non-NULL timestamp if the version has been superseded.

The two row states.

• Open row. dbt_valid_to IS NULL. This is the currently-active version of the row. Every unique_key has exactly one open row (unless you use invalidate_hard_deletes, in which case it has zero after being closed).
• Closed row. dbt_valid_to IS NOT NULL. A historical version. The row was current from dbt_valid_from to dbt_valid_to; then a newer version took over.

The interval semantics — [from, to) or (from, to]?

• dbt's convention. [dbt_valid_from, dbt_valid_to) — half-open on the right. A row is valid at time T if dbt_valid_from <= T AND (T < dbt_valid_to OR dbt_valid_to IS NULL).
• The BETWEEN pattern. Downstream queries typically use T BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, '9999-12-31') — which is inclusive on both sides. This differs from dbt's strict half-open by an infinitesimal at the boundary; for most business queries the difference is invisible.
• The strict pattern. For millisecond-precision analytics, prefer dbt_valid_from <= T AND (dbt_valid_to IS NULL OR T < dbt_valid_to). This matches dbt's convention exactly.

Querying the snapshot — the three canonical patterns.

• Current state. WHERE dbt_valid_to IS NULL — get exactly one row per unique_key.
• State at time T. WHERE '2026-06-15' BETWEEN dbt_valid_from AND COALESCE(dbt_valid_to, '9999-12-31') — get exactly one row per unique_key.
• All transitions between T1 and T2. WHERE dbt_valid_from >= T1 AND dbt_valid_from < T2 — get one row per transition.

What you should NEVER do to a snapshot table.

• Never UPDATE by hand. The dbt_valid_from and dbt_valid_to invariants are maintained by dbt. Manual UPDATE breaks the invariant and downstream point-in-time queries silently return wrong answers.
• Never DELETE by hand. Rows are immutable. If you need to expunge (GDPR, PII correction), do it at the source and re-snapshot with invalidate_hard_deletes.
• Never rename dbt_valid_from / dbt_valid_to. dbt's snapshot macro depends on these exact column names.
• Never add your own columns to the snapshot table. Downstream models can add columns; the snapshot table itself must stay dbt-managed. If you need a derived column, add it in a downstream model, not on the snapshot.

Common interview probes on the anatomy.

• "Write the SQL for 'what was user 42's plan on 2026-05-15?'" — the BETWEEN pattern with dbt_valid_from and dbt_valid_to.
• "How does dbt guarantee dbt_scd_id is unique?" — hash of unique_key + dbt_valid_from.
• "What if the source updated_at is NULL?" — some warehouses treat NULL comparisons as false; dbt's macro handles this but the source design should avoid nullable updated_at.
• "How do you filter to the current row?" — WHERE dbt_valid_to IS NULL.

Worked example — querying the state of a customer at a past date

Detailed explanation. A senior BI engineer needs to answer "what plan was customer 42 on when they placed order 987 (at 2026-05-20 14:22)?" The snapshot table has all the history; the query is a single BETWEEN predicate against the two dbt_valid_* columns. Walk through the query, the expected result, and the join pattern for enriching a fact table with point-in-time dimension state.

• The question. State of customer 42 at 2026-05-20 14:22.
• The query. BETWEEN on dbt_valid_from and dbt_valid_to.
• The invariant. Exactly one snapshot row matches per customer at any single point in time.

Question. Write the query that returns customer 42's plan at 2026-05-20 14:22. Extend it to enrich a fact table (orders) with the dimension state as of the order's created_at.

Input.

id	plan	dbt_valid_from	dbt_valid_to
42	Basic	2026-04-01 09:00	2026-05-10 08:30
42	Pro	2026-05-10 08:30	2026-06-01 12:00
42	Enterprise	2026-06-01 12:00	NULL

Code.

-- Single point-in-time query
SELECT id, plan, dbt_valid_from, dbt_valid_to
FROM   snapshots.customers_snapshot
WHERE  id = 42
  AND  '2026-05-20 14:22:00'
       BETWEEN dbt_valid_from
       AND COALESCE(dbt_valid_to, '9999-12-31');

-- Point-in-time enrichment of a fact table (orders → customer state at order time)
SELECT
  o.order_id,
  o.customer_id,
  o.created_at,
  c.plan AS plan_at_order_time,
  c.email AS email_at_order_time
FROM   warehouse.orders            AS o
LEFT JOIN snapshots.customers_snapshot AS c
  ON  c.id = o.customer_id
  AND o.created_at BETWEEN c.dbt_valid_from
                       AND COALESCE(c.dbt_valid_to, '9999-12-31');

Step-by-step explanation.

1. The single-point query uses BETWEEN to match the customer's active version at 2026-05-20 14:22. Only the middle row (Pro, valid_from=2026-05-10, valid_to=2026-06-01) satisfies the predicate. Exactly one row per unique_key matches at any single point in time.
2. The COALESCE(dbt_valid_to, '9999-12-31') handles the currently-open row. Without the COALESCE, BETWEEN against a NULL right bound would return unknown (SQL three-valued logic) and the row would be excluded — a common bug.
3. The fact-enrichment join uses the same BETWEEN pattern inside the ON clause. For each order, exactly one customer snapshot row matches (the one valid at o.created_at). The join gives us the customer's plan as it was when the order was placed — not their current plan, which is Enterprise.
4. The join is a range join. Most modern warehouses (Snowflake, BigQuery, Databricks) optimise range joins with special operators; on Postgres, add an index on (customer_id, dbt_valid_from) for the seek + range scan.
5. This pattern is the canonical use of an SCD-2 dimension: enriching facts with point-in-time dimension state so that historical reports are stable. Rebuild the same report a year later and you get the same numbers, because the dimension state at fact time is immutable.

Output.

Query	order_id	customer_id	plan_at_order_time
Order at 2026-04-15	900	42	Basic
Order at 2026-05-20	987	42	Pro
Order at 2026-06-10	999	42	Enterprise

Rule of thumb. Every fact-to-dimension join in a warehouse with SCD-2 dimensions uses the BETWEEN pattern with dbt_valid_from and COALESCE(dbt_valid_to, '9999-12-31'). Bookmark this pattern; it appears in every point-in-time enrichment.

Worked example — building a current-state model on top of a snapshot

Detailed explanation. The snapshot table holds history. Downstream analytics often needs a "current state" view — one row per unique_key with the current values. Instead of every query filtering by dbt_valid_to IS NULL, build a downstream model that materialises the current state. Walk through the dbt model, the incremental strategy, and why the naive "SELECT * WHERE dbt_valid_to IS NULL" model is fine at most scales.

• The pattern. One dbt model per snapshot, materialised as a view or table.
• The query. SELECT ... FROM snapshot WHERE dbt_valid_to IS NULL.
• The trade-off. View is always fresh; table is faster at query time but requires refresh.

Question. Write the current-state model on top of customers_snapshot. Show both the view and the table variants.

Input.

Snapshot column	Downstream column
id	customer_id
email	email
name	name
plan	plan
dbt_valid_from	current_since
dbt_valid_to	(filtered to NULL — not surfaced)

Code.

# models/staging/stg_customers.sql — the view variant (always fresh, cheap at write time)
{{ config(materialized='view') }}

SELECT
  id            AS customer_id,
  email,
  name,
  plan,
  dbt_valid_from AS current_since
FROM   {{ ref('customers_snapshot') }}
WHERE  dbt_valid_to IS NULL

# models/staging/stg_customers.sql — the table variant (faster at query time; requires periodic refresh)
{{ config(materialized='table') }}

SELECT
  id            AS customer_id,
  email,
  name,
  plan,
  dbt_valid_from AS current_since
FROM   {{ ref('customers_snapshot') }}
WHERE  dbt_valid_to IS NULL

Step-by-step explanation.

1. Both variants share the same SQL: filter to currently-open snapshot rows and rename id to customer_id (a stylistic choice for downstream clarity). The only difference is materialization.
2. The view variant is always fresh — every query against stg_customers executes the underlying SELECT against the snapshot. If the snapshot updates between two consecutive queries, the view reflects it immediately.
3. The table variant materialises the current state as a physical table. Downstream queries read the table, not the snapshot. This is faster at query time (no BETWEEN filter, no NULL check) but requires dbt build to refresh — usually right after the snapshot runs.
4. The dependency order in dbt is enforced by ref('customers_snapshot') — dbt runs the snapshot before the model. The snapshot writes; the model reads. The order is a DAG edge.
5. At most scales, the view variant is the right choice: the snapshot table is already small (one row per open key), so the view is essentially free. Escalate to the table variant only if the snapshot has 100M+ open rows and query performance matters more than freshness.

Output.

Materialization	Query cost	Refresh cost	Freshness
view	one SELECT per query	0 (no refresh)	always live
table	table scan (cheaper)	one INSERT per snapshot run	as fresh as last dbt build

Rule of thumb. Default to a view on top of the snapshot for the current-state model. Escalate to a table only when query performance measurably matters — most snapshots are small enough that the view is free.

Worked example — using dbt_scd_id as a fact-table foreign key

Detailed explanation. A senior data modeller wants a fact table's foreign key to reference not just the customer_id but the specific version of the customer at fact time. The dbt_scd_id surrogate key (hash of unique_key + valid_from) uniquely identifies each row version. Using dbt_scd_id as the fact's foreign key means the fact is bound to the exact dimension version — the referential integrity is at the version level, not the natural-key level.

• The pattern. Fact stores customer_dbt_scd_id (not just customer_id).
• The lookup. At fact-build time, look up the dbt_scd_id of the customer snapshot row valid at fact time.
• The result. Fact joins to dimension by dbt_scd_id — a simple equi-join, no range predicate.

Question. Write the SQL that populates fact_orders with customer_dbt_scd_id. Show how the downstream analytics join becomes a plain equi-join.

Input.

Table	Column
stg.orders	order_id, customer_id, created_at, total
snapshots.customers_snapshot	id, plan, dbt_scd_id, dbt_valid_from, dbt_valid_to

Code.

-- Build fact_orders with the point-in-time surrogate key baked in
{{ config(materialized='incremental') }}

SELECT
  o.order_id,
  o.customer_id,
  o.created_at,
  o.total,
  c.dbt_scd_id AS customer_dbt_scd_id     -- version-level FK
FROM   {{ ref('stg_orders') }} AS o
LEFT JOIN {{ ref('customers_snapshot') }} AS c
  ON  c.id = o.customer_id
  AND o.created_at BETWEEN c.dbt_valid_from
                       AND COALESCE(c.dbt_valid_to, '9999-12-31')

{% if is_incremental() %}
WHERE o.created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

-- Downstream analytics — plain equi-join, no BETWEEN needed
SELECT
  o.order_id,
  o.total,
  c.plan
FROM   warehouse.fact_orders            AS o
JOIN   snapshots.customers_snapshot     AS c
  ON   c.dbt_scd_id = o.customer_dbt_scd_id;

Step-by-step explanation.

1. The fact build resolves the point-in-time dimension version once, at fact-write time. The dbt_scd_id of the customer row valid at o.created_at is stored on the fact. Every future join uses this surrogate key.
2. The BETWEEN join in the build cost is paid once per fact row. If the fact is incremental (only new orders processed per run), the cost is O(new orders × avg matching dimension versions), typically 1.
3. Downstream analytics uses a plain equi-join (c.dbt_scd_id = o.customer_dbt_scd_id). No BETWEEN. No COALESCE. No range predicate. The query planner picks the fastest join algorithm; on most warehouses this is a hash join with a small hash table (the snapshot).
4. The pattern is called pre-resolved SCD-2 join. It trades a slightly heavier fact-build (one extra range join) for a simpler downstream query (one plain join). At scale, the downstream simplification saves seconds per query.
5. The alternative — every downstream query doing the BETWEEN join itself — is correct but slower and easier to get wrong. Junior engineers frequently miss the COALESCE on dbt_valid_to and produce incorrect results. Pre-resolving the FK avoids the error class entirely.

Output.

Approach	Fact build cost	Downstream query cost	Correctness risk
Store customer_id only	cheap	BETWEEN join everywhere	high (miss COALESCE)
Store customer_dbt_scd_id	one range join at build	equi-join everywhere	low

Rule of thumb. For fact tables that reference SCD-2 dimensions, pre-resolve the dimension version at fact-build time and store the surrogate dbt_scd_id as the FK. Downstream queries become plain equi-joins and the error surface shrinks.

Senior interview question on snapshot anatomy

A senior interviewer might ask: "Walk me through the four dbt_valid_* columns on a snapshot table. For each one, tell me what dbt uses it for internally, how downstream queries reference it, and a mistake you've seen a junior engineer make."

Solution Using the four meta-columns as a query contract

-- Column reference — annotated with dbt's usage and the downstream query pattern
-- Column: dbt_scd_id
--   dbt use: unique surrogate key; hash of unique_key || dbt_valid_from
--   Query use: fact-table FK when pre-resolving SCD-2 joins
--   Mistake:   junior engineer treats it as changing between runs (it's stable per version)

-- Column: dbt_updated_at
--   dbt use: the timestamp dbt uses to compare source vs snapshot for the timestamp strategy
--   Query use: rarely surfaced downstream; internal
--   Mistake:   junior engineer joins on dbt_updated_at instead of dbt_valid_from (subtly different)

-- Column: dbt_valid_from
--   dbt use: the moment this row version became current
--   Query use: lower bound in BETWEEN for point-in-time queries
--   Mistake:   junior engineer uses > instead of >= (misses the boundary)

-- Column: dbt_valid_to
--   dbt use: the moment this row version stopped being current; NULL = still current
--   Query use: upper bound; must COALESCE(dbt_valid_to, '9999-12-31') for open rows
--   Mistake:   junior engineer forgets COALESCE; open row excluded from BETWEEN result

-- Full point-in-time query template — use this as the canonical form
SELECT s.*
FROM   {{ ref('some_snapshot') }} AS s
WHERE  :as_of_ts BETWEEN s.dbt_valid_from
                     AND COALESCE(s.dbt_valid_to, '9999-12-31');

Step-by-step trace.

Column	dbt-side role	Query-side role	Junior mistake
dbt_scd_id	surrogate PK; hash(unique_key		valid_from)
dbt_updated_at	source-side change detection watermark	rarely surfaced	join on it instead of valid_from
dbt_valid_from	open time	BETWEEN lower bound	> instead of >=
dbt_valid_to	close time; NULL if open	BETWEEN upper bound	forget COALESCE

After the walk-through, an interviewer knows you understand why each column exists, not just that it does. The junior-mistake column is the senior signal — it demonstrates you've mentored someone through the pattern.

Output:

Concept	Query fragment
Point-in-time	T BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31')
Current state	WHERE dbt_valid_to IS NULL
All transitions in window	WHERE dbt_valid_from BETWEEN T1 AND T2
Fact FK	f.customer_dbt_scd_id = s.dbt_scd_id

Why this works — concept by concept:

• Four columns, one contract — the snapshot table is a contract with downstream models. The four meta-columns are the schema you promise; downstream queries know what to expect. Never rename them; never drop them.
• BETWEEN as the primary predicate — the point-in-time query is a BETWEEN over dbt_valid_from and COALESCE(dbt_valid_to, '9999-12-31'). This is the canonical form; every senior data modeller has it memorised.
• COALESCE on the upper bound — the single most common junior mistake is forgetting the COALESCE. Open rows have dbt_valid_to = NULL; without COALESCE, BETWEEN against NULL is unknown, and the open row falls out of the result. Ship linter rules to catch this.
• dbt_scd_id as pre-resolved FK — storing dbt_scd_id on the fact table lets downstream queries use equi-joins instead of range joins. One BETWEEN at build time; N equi-joins at query time.
• Cost — the four columns are essentially free at storage (16 bytes overhead per row). The runtime cost is dominated by the BETWEEN join at fact-build time; downstream equi-joins are O(1) per row.

SQL
Topic — sql
SQL point-in-time and range-join problems

Practice →

Optimization Topic — optimization Optimization problems on SCD-2 join patterns

Practice →

---

4. Hard deletes + invalidate_hard_deletes

Deleted source rows stay open by default — invalidate_hard_deletes: true closes them

The mental model in one line: by default, dbt snapshots ignore source deletes — a row that disappears from source stays with dbt_valid_to = NULL forever, which lies about the entity's current state; setting invalidate_hard_deletes: true tells dbt to detect missing keys and close the row with dbt_valid_to = CURRENT_TIMESTAMP() on the next snapshot run. The trade-off is a full-source scan on every snapshot (to find missing keys) versus an incorrect open-row indicator. Every senior interviewer asks about this toggle; getting it right is a required answer.

The default behaviour — snapshots ignore deletes.

• What dbt does. On each snapshot run, dbt compares source rows to snapshot's currently-open rows for the source's unique_keys. It does not check for keys that used to exist and no longer do.
• The result. A user deleted from raw.users still has an open row in snapshots.users_snapshot with dbt_valid_to = NULL. Downstream queries treat the user as still active.
• When this is correct. Sources that don't do hard deletes — the source uses soft deletes (a deleted_at column), tombstones, or archival tables. In these cases, "vanished from source" is a bug you want to catch, not a normal state.
• When this is wrong. Sources that regularly hard-delete rows — GDPR compliance flows, PII expiry, or upstream systems that just DELETE instead of tombstoning. Ignoring the deletes leaves stale "active" rows in the history.

The invalidate_hard_deletes toggle.

• The setting. invalidate_hard_deletes: true in the snapshot config.
• What dbt does. On each snapshot run, dbt joins source to snapshot's currently-open rows and identifies unique_keys that exist in the snapshot but not in the source. For each missing key, dbt sets dbt_valid_to = CURRENT_TIMESTAMP() (or the snapshot run time). The row is closed.
• The cost. Full source scan on every run, plus a join to find the missing keys. On very large sources this can be expensive.
• The reward. Downstream queries filtering by dbt_valid_to IS NULL correctly reflect "still exists in source." Deleted rows are historical.

The three delete-handling patterns you'll see in production.

• Soft deletes upstream + snapshot ignores. The source has a deleted_at column; the snapshot tracks it as a payload column. dbt_valid_to IS NULL means "still in source"; deleted_at IS NOT NULL means "soft-deleted in source." Every downstream query filters by both.
• Hard deletes upstream + invalidate_hard_deletes: true. The source deletes the row; the snapshot closes it on the next run. dbt_valid_to IS NULL means "still in source"; a non-NULL dbt_valid_to on the last row means "deleted from source."
• Hard deletes upstream + snapshot ignores. The dangerous mode. The source deletes the row; the snapshot leaves it open forever. Downstream queries think the entity still exists.

Interpretation of a closed row after invalidate_hard_deletes.

• Non-NULL dbt_valid_to on the last row for a key = the row was deleted at that time. This is the correct interpretation only if invalidate_hard_deletes: true is set. Without it, a non-NULL dbt_valid_to on the last row cannot happen (unless the row was updated, in which case a new open row exists).
• NULL dbt_valid_to on the last row for a key = the row is still in source. True in both modes.
• The pattern for "when was this deleted?" — SELECT MAX(dbt_valid_to) FROM snapshot WHERE unique_key = 42 AND NOT EXISTS (open row for same key).

Common interview probes on hard deletes.

• "What does invalidate_hard_deletes do?" — closes rows that disappear from source.
• "What's the default behaviour?" — rows stay open forever; deleted keys are silent bugs.
• "What's the cost?" — full source scan per run to find missing keys.
• "How does downstream code detect a deleted vs a still-active row?" — filter by dbt_valid_to IS NULL.

Worked example — a SaaS user table with hard-delete GDPR flow

Detailed explanation. A SaaS platform hard-deletes users when they request account deletion (GDPR right-to-erasure). The users table in the operational database goes from N rows to N-1 rows when a user requests deletion. Without invalidate_hard_deletes, the snapshot table still shows the deleted user as active — every downstream metric (MAU, ARR, active user count) is wrong. Turn the toggle on and the snapshot correctly closes the deleted row.

• The source. raw.users with id, email, plan, updated_at.
• The delete flow. GDPR request → DELETE FROM raw.users WHERE id = ....
• The bug. Snapshot leaves the row open. SELECT COUNT(*) FROM snapshots.users_snapshot WHERE dbt_valid_to IS NULL overstates the user count.

Question. Add invalidate_hard_deletes: true to the snapshot config. Show the before/after behaviour on a synthetic delete.

Input.

Time	Event	raw.users rows
t0	user 42 signs up	42 present
t1	user 42 requests GDPR delete	42 absent
t2	snapshot runs	—

Code.

# snapshots/users_snapshot.sql — with invalidate_hard_deletes toggled on
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=true       # ← the key change
    ) }}

    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

-- Simulation — BEFORE the toggle
-- Snapshot table AFTER GDPR delete + snapshot run:
-- id | plan  | dbt_valid_from     | dbt_valid_to  ← STILL NULL
-- 42 | Basic | 2026-06-01 09:00   | NULL

-- Downstream metric (WRONG):
SELECT COUNT(*) FROM snapshots.users_snapshot WHERE dbt_valid_to IS NULL;
-- returns N (overstates by 1)

-- Simulation — AFTER the toggle
-- id | plan  | dbt_valid_from     | dbt_valid_to
-- 42 | Basic | 2026-06-01 09:00   | 2026-06-22 08:00  ← now closed

-- Downstream metric (CORRECT):
SELECT COUNT(*) FROM snapshots.users_snapshot WHERE dbt_valid_to IS NULL;
-- returns N-1 (correct)

-- Bonus — audit query for deleted users
SELECT id, dbt_valid_to AS deleted_at
FROM   snapshots.users_snapshot
WHERE  dbt_valid_to IS NOT NULL
  AND  NOT EXISTS (
    SELECT 1 FROM snapshots.users_snapshot s2
    WHERE  s2.id = snapshots.users_snapshot.id
      AND  s2.dbt_valid_to IS NULL
  );

Step-by-step explanation.

1. Without the toggle, dbt's snapshot logic does not look for "keys that were in the snapshot but are not in the source." It only processes keys present in the source. A vanished key is invisible.
2. With the toggle, dbt adds a second pass: LEFT JOIN snapshot open rows against source unique_keys; any snapshot open row with no source match is closed by setting dbt_valid_to = CURRENT_TIMESTAMP().
3. The audit query for "users deleted from source" finds unique_keys where the most recent snapshot row has a non-NULL dbt_valid_to and no open row exists. This is the definitive "deleted" indicator.
4. The trade-off: invalidate_hard_deletes: true forces a full-source scan on every snapshot run (to enumerate all source unique_keys and check which snapshot keys are missing). On a 500M-row source, this can add minutes per run.
5. For hot sources with high delete rates, some teams choose a partial approach: run the toggle-off snapshot at high cadence and the toggle-on snapshot at a lower cadence. The result is prompt detection of updates and eventually-consistent detection of deletes.

Output.

Toggle	Deleted rows correctly closed	Runtime cost	Downstream MAU accuracy
off (default)	no	cheap	overstated
on (invalidate_hard_deletes: true)	yes	full-source scan per run	correct

Rule of thumb. For any source that hard-deletes, set invalidate_hard_deletes: true. If you're not sure whether the source hard-deletes, ask the source team or diff yesterday's row count vs today's; a net decrease is a hard delete.

Worked example — soft-delete pattern (deleted_at column) instead of the toggle

Detailed explanation. Some teams intentionally avoid hard deletes at the source: they use a deleted_at column instead. The row stays in the table forever; the column marks the delete time. In this world, the snapshot's invalidate_hard_deletes toggle is irrelevant — the row never vanishes from source, so nothing needs closing. The delete is tracked as a payload column change. Walk through the pattern and compare downstream queries.

• The source. raw.users with id, email, plan, updated_at, deleted_at (nullable).
• The delete flow. GDPR request → UPDATE raw.users SET deleted_at = now() WHERE id = ....
• The snapshot behaviour. Timestamp strategy detects the updated_at bump (the app updates updated_at on the DELETE-flag flip); the snapshot inserts a new row with the non-NULL deleted_at.

Question. Write the snapshot config for the soft-delete pattern. Show the downstream query that filters to "currently active" users.

Input.

Column	Type
id	INT
email	TEXT
plan	TEXT
updated_at	TIMESTAMP
deleted_at	TIMESTAMP NULL

Code.

# snapshots/users_snapshot.sql — soft-delete pattern; NO invalidate_hard_deletes needed
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at'
        # note: no invalidate_hard_deletes — rows never vanish from source
    ) }}

    SELECT id, email, plan, updated_at, deleted_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

-- Downstream — "currently active" users must filter by BOTH dbt_valid_to IS NULL AND deleted_at IS NULL
SELECT id, email, plan
FROM   snapshots.users_snapshot
WHERE  dbt_valid_to IS NULL          -- current version
  AND  deleted_at   IS NULL;          -- and not soft-deleted

-- Downstream — audit "users deleted in the last 30 days"
SELECT id, deleted_at
FROM   snapshots.users_snapshot
WHERE  dbt_valid_to IS NULL          -- current version
  AND  deleted_at   IS NOT NULL       -- deleted
  AND  deleted_at >= CURRENT_DATE - INTERVAL '30 days';

Step-by-step explanation.

1. The soft-delete pattern keeps the row in source. The deleted_at column is a normal payload column that transitions from NULL to a timestamp when the delete happens. The snapshot's timestamp strategy detects the transition via the updated_at bump.
2. On the delete event, dbt inserts a new SCD-2 row for the user with deleted_at = <the delete time>. The old row (with deleted_at IS NULL) is closed by dbt_valid_to. The transition is fully preserved.
3. Downstream queries must filter by both dbt_valid_to IS NULL (get the current version) and deleted_at IS NULL (not soft-deleted). Missing either predicate produces wrong answers.
4. The soft-delete pattern is philosophically the strongest choice: it preserves referential integrity (foreign keys pointing at deleted users still work), it's easy to reverse (UPDATE ... SET deleted_at = NULL), and it plays nicely with SCD-2 without any special dbt config.
5. The trade-off is source-side complexity: every source query must filter by deleted_at IS NULL, or you accidentally surface deleted users. Row-level security policies at the source database enforce this; the pattern is standard in many SaaS platforms.

Output.

Pattern	Source-side complexity	Snapshot-side complexity	Downstream complexity
Hard delete + invalidate_hard_deletes	low	full-scan per run	filter by dbt_valid_to IS NULL
Soft delete + deleted_at column	high (filter everywhere)	none special	filter by two columns

Rule of thumb. If you control the source, prefer soft deletes with a deleted_at column — the SCD-2 story is cleaner. If you don't control the source and it hard-deletes, use invalidate_hard_deletes: true.

Worked example — reappearing keys (delete + re-insert)

Detailed explanation. A subtle case: a source hard-deletes a row and then later re-inserts a row with the same natural key (a user closes their account, then re-signs up with the same email six months later). With invalidate_hard_deletes: true, the snapshot correctly closes the old row on delete and opens a new row on re-insert. Walk through the two snapshot runs and the resulting history.

• The scenario. User 42 deleted at t1; user 42 re-created at t3.
• The snapshots. Snapshot at t2 (after delete) and t4 (after re-insert).
• The history. Two closed rows and one open row for id = 42.

Question. Show the snapshot table state after each of the four events. Explain how downstream queries distinguish the two versions of user 42.

Input.

Time	Event	raw.users row for id=42
t0 (2026-04-01)	user 42 signs up	present, plan=Basic
t1 (2026-05-01)	user 42 hard-deleted	absent
t2 (2026-05-15)	snapshot runs	—
t3 (2026-11-01)	user 42 re-signs up	present, plan=Pro
t4 (2026-11-15)	snapshot runs	—

Code.

# snapshots/users_snapshot.sql — invalidate_hard_deletes on
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=true
    ) }}

    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

-- State after t2 (post-delete snapshot):
-- id | plan  | dbt_valid_from      | dbt_valid_to
-- 42 | Basic | 2026-04-01 09:00    | 2026-05-15 08:00  ← closed by invalidate_hard_deletes

-- State after t4 (post-re-insert snapshot):
-- id | plan  | dbt_valid_from      | dbt_valid_to
-- 42 | Basic | 2026-04-01 09:00    | 2026-05-15 08:00  ← closed old row
-- 42 | Pro   | 2026-11-01 12:00    | NULL              ← new open row

-- Downstream query — "when was user 42 active?"
SELECT id, dbt_valid_from, dbt_valid_to,
       CASE WHEN dbt_valid_to IS NULL THEN 'currently active'
            ELSE 'was active in the window' END AS status
FROM   snapshots.users_snapshot
WHERE  id = 42
ORDER  BY dbt_valid_from;

Step-by-step explanation.

1. At t2 (the post-delete snapshot), dbt joins source to snapshot. User 42 exists in the snapshot as an open row but does not exist in source. invalidate_hard_deletes closes the row: dbt_valid_to = 2026-05-15 08:00 (the snapshot run time).
2. Between t2 and t4, the snapshot table has zero open rows for id=42. Any point-in-time query for id=42 at a date in this window returns no row — matching the source's "user 42 doesn't exist" state.
3. At t3, a new user with id=42 (or a re-created row that happens to have id=42) appears in source. At t4, the snapshot runs. The new source row's key doesn't match any currently-open snapshot row for id=42 (there is none — the old one was closed at t2). dbt inserts a new open row with dbt_valid_from = 2026-11-01 12:00.
4. The final state: two rows for id=42. The old closed Basic row (active from 2026-04-01 to 2026-05-15), and the new open Pro row (active from 2026-11-01 onwards). The gap between 2026-05-15 and 2026-11-01 is correctly represented — no row exists.
5. Downstream point-in-time queries for id=42 return: Basic for dates in [t0, t2), no row for dates in [t2, t3), Pro for dates in [t3, t4] and onwards. The BETWEEN semantics still work; the gap is a natural consequence of "no row valid at that time."

Output.

Snapshot row	Interpretation
Basic 2026-04-01 → 2026-05-15	account existed
(gap 2026-05-15 → 2026-11-01)	account deleted
Pro 2026-11-01 → NULL	re-signed up

Rule of thumb. invalidate_hard_deletes: true handles reappearing keys correctly: the old row closes on delete; the new row opens on re-insert. The gap in the timeline is real information — don't try to "fix" it.

Senior interview question on hard-delete semantics

A senior interviewer might ask: "You're building an SCD-2 dimension for a users source that both updates rows and hard-deletes them (GDPR). Walk me through the snapshot config, the cost of invalidate_hard_deletes, and how you'd audit for stale open rows if the toggle were off."

Solution Using invalidate_hard_deletes plus a monitoring model

# snapshots/users_snapshot.sql — full config with invalidate_hard_deletes
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=true
    ) }}

    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

-- Auditing model — surface any drift between snapshot open rows and source keys
-- models/monitoring/mon_users_snapshot_drift.sql
{{ config(materialized='view') }}

WITH snapshot_open AS (
  SELECT id FROM {{ ref('users_snapshot') }} WHERE dbt_valid_to IS NULL
),
source_keys AS (
  SELECT id FROM {{ source('raw', 'users') }}
)
SELECT
  'in_snapshot_not_in_source' AS delta,
  s.id
FROM   snapshot_open AS s
LEFT   JOIN source_keys AS r ON r.id = s.id
WHERE  r.id IS NULL

UNION ALL

SELECT
  'in_source_not_in_snapshot' AS delta,
  r.id
FROM   source_keys AS r
LEFT   JOIN snapshot_open AS s ON s.id = r.id
WHERE  s.id IS NULL;

-- Alert on any row: drift indicates snapshot is stale (missed a run, or misconfigured).

Step-by-step trace.

Event	Toggle off	Toggle on
User 42 present in source	open row for 42	open row for 42
User 42 hard-deleted	open row for 42 (STALE)	row closed at run time
Audit mon_users_snapshot_drift	flags 42 as in_snapshot_not_in_source	zero rows for 42
Downstream MAU count	overstates	correct

After the rollout, the snapshot table reflects both updates and deletes; the monitoring view catches any residual drift. The team has a two-layer defence: invalidate_hard_deletes at the snapshot; the audit view at the model layer.

Output:

Metric	Before toggle	After toggle
Open rows in snapshot for id=42 (post-delete)	1 (stale)	0 (closed)
MAU accuracy	overstated	correct
Snapshot runtime	cheap (delta scan)	+30% (full source scan for missing keys)
Drift audit rows	non-zero	zero

Why this works — concept by concept:

• Toggle on for hard-delete sources — the toggle is a per-snapshot choice. Sources with hard deletes get invalidate_hard_deletes: true; sources with soft deletes leave it off. Never set it globally without thinking.
• Full-source scan cost — the toggle adds a LEFT JOIN of snapshot open rows against source unique_keys on every run. Cost scales with source size; on a 1B-row source, this can add minutes. Reconcile against your SLA.
• Reappearing-key correctness — dbt's snapshot logic correctly handles delete + re-insert: the old row is closed on delete, a new row is opened on re-insert, and the timeline has a real gap. Don't try to bridge the gap.
• Monitoring model — a monitoring view surfaces drift between snapshot open rows and source keys. Any non-zero result means the snapshot is stale (missed a run or misconfigured); page the data-platform team.
• Cost — the toggle adds O(source rows) to each snapshot run. The monitoring view is O(source rows) per query. Together they cost ~50% more than a snapshot with the toggle off; the correctness of downstream MAU / ARR / churn metrics is worth the cost.

SQL
Topic — sql
SQL delete-tracking and drift-audit problems

Practice →

ETL Topic — etl ETL problems on soft-delete and hard-delete SCD-2

Practice →

---

5. Production patterns — sources, target_schema, dbt Cloud

The five production patterns senior teams ship — separate schema, source vs staging, cadence per source, dbt Cloud jobs, migration audit

The mental model in one line: a production dbt snapshot config in 2026 lives on its own target_schema='snapshots' sitting next to raw and mart schemas, reads from the raw source() (never a downstream model), runs on a cadence matched to the source's velocity, is scheduled via dbt Cloud or an Airflow-orchestrated dbt Core job, and — for migrations from a hand-rolled MERGE — ships behind a week of parallel-run diffs before cutover. None of these patterns is exotic; missing any one is a production incident waiting to happen.

Pattern 1 — separate target_schema for snapshots.

• Config. target_schema='snapshots' in every snapshot config.
• Why. Snapshots are audit artefacts. Isolating them from downstream marts (analytics, marts) means (a) permissions can be tighter (few writers, many read-only auditors), (b) storage can be tiered separately, (c) accidental table renames or drops in mart schemas don't touch history.
• The alternative. Snapshots in the default schema — mingled with dbt models. Every warehouse audit becomes a hunt through the analytics schema for the SCD-2 history. Anti-pattern.
• Naming convention. Table name matches the snapshot block name — customers_snapshot, products_snapshot. Suffix aids scanning schema listings for "what's a snapshot?"

Pattern 2 — snapshot the raw source, not the staging model.

• Rule. Snapshot the raw source(), not the ref('stg_customers').
• Why. The raw source is stable; the staging model is a transformation you may change. If you snapshot the staging model and later add a filter or a column derivation, you either lose historical rows or invalidate the entire history.
• The exception. If the raw source has PII you legally cannot store in the snapshot's audit schema, snapshot a redacted staging model — but pin the model's shape as a contract and never change it.
• The failure mode. Team snapshots ref('stg_customers') which does WHERE deleted_at IS NULL. Six months later a new engineer removes the filter to "capture soft-deleted users." The snapshot's history now contains rows that were previously invisible; downstream point-in-time joins silently break.

Pattern 3 — snapshot cadence per source velocity.

• High-velocity sources (many updates per hour). Snapshot every 5-15 minutes. Cost: many quick runs. Reward: high temporal resolution.
• Medium-velocity sources (updates per day). Snapshot hourly. Balances resolution vs cost.
• Low-velocity sources (updates per week). Snapshot daily or after each source load. Cheap; still meets resolution needs.
• The wrong pattern. One-size-fits-all "run every snapshot every hour." Cheap sources are over-serviced; expensive sources are still expensive. Sizing per-source is a 5-minute exercise per source.

Pattern 4 — dbt Cloud or Airflow orchestration.

• dbt Cloud. First-party job scheduler; dbt snapshot runs as a job with cron schedule; UI shows recent runs and any failures; integrates with Slack/email alerts. Zero-glue for teams already on dbt Cloud.
• Airflow / Prefect / Dagster with dbt Core. More flexibility (custom retries, upstream dependency waiting, cross-source dependencies), more glue. The dbt run --select tag:snapshots selector runs only snapshot models on the schedule.
• The failure to instrument. Running snapshots via cron on a build server with no alerting. When the job fails, no one notices for a week; the snapshot is stale; downstream point-in-time queries return values that lag by that week. Alerting is non-negotiable.

Pattern 5 — MERGE → snapshot migration.

• Parallel run. New dbt snapshot writes to a new table; the old hand-rolled MERGE keeps writing to its existing table. Both run on the same schedule for at least a week.
• Diff query. Daily diff between the two tables; the diff must show zero rows for the parallel run to be a success.
• Cutover. After a week of clean diffs, point downstream models at the new snapshot. Delete the old MERGE code and the old history table after a soak of another week.
• The rollback. During the parallel run, the old MERGE is your rollback. Never delete it prematurely.

Common interview probes on production patterns.

• "Where should snapshots live in the warehouse schema?" — dedicated snapshots schema.
• "Do you snapshot the raw source or the staging model?" — raw source, with contract-pinned exceptions.
• "How often do you run snapshots?" — cadence matches source velocity.
• "How do you migrate a hand-rolled SCD-2 MERGE to a dbt snapshot?" — parallel run + diff + cutover + soak.

Worked example — production snapshot config for a Rails-managed users table

Detailed explanation. A production-grade snapshot config for a Rails-managed users table pulls together all five patterns: target_schema='snapshots', snapshot the raw source(), hourly cadence, dbt Cloud job, and a monitoring view. Walk through the full config and the job scheduler.

• The source. raw.users — Rails-managed with reliable updated_at.
• The cadence. Hourly (moderate velocity; roughly 10k updates per day).
• The orchestration. dbt Cloud job on hourly cron.

Question. Produce the complete production config — snapshot definition, model schema, dbt Cloud job spec, and monitoring view.

Input.

Component	Value
Source	raw.users
Warehouse	Snowflake
Cadence	hourly
Orchestrator	dbt Cloud
Alerting	Slack channel #data-alerts

Code.

# snapshots/users_snapshot.sql — production-grade
{% snapshot users_snapshot %}
    {{ config(
        target_schema='snapshots',
        unique_key='id',
        strategy='timestamp',
        updated_at='updated_at',
        invalidate_hard_deletes=true
    ) }}

    SELECT id, email, name, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

# models/sources.yml — declare the raw source with freshness SLA
version: 2

sources:
  - name: raw
    database: prod_raw
    schema:   public
    tables:
      - name: users
        description: Rails users table; SCD-2 tracked via snapshots.users_snapshot
        freshness:
          warn_after:  { count: 2,  period: hour }
          error_after: { count: 6,  period: hour }
        loaded_at_field: updated_at

# dbt_project.yml — pin the snapshots' behaviour
snapshots:
  my_project:
    +target_schema: snapshots
    +tags: ['snapshots']

# dbt Cloud job spec (illustrative — configured in the UI)
name: hourly-snapshots
schedule: "5 * * * *"                # 5 minutes past every hour
commands:
  - "dbt source freshness --select source:raw.users"
  - "dbt snapshot --select tag:snapshots"
  - "dbt run --select tag:snapshots+ tag:daily+"
on_failure:
  - notify: slack:#data-alerts

-- Monitoring view — flags stale snapshots
-- models/monitoring/mon_snapshot_freshness.sql
{{ config(materialized='view') }}

SELECT
  'users_snapshot' AS snapshot_name,
  MAX(dbt_updated_at) AS last_change_captured,
  EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(dbt_updated_at))) AS staleness_seconds
FROM   {{ ref('users_snapshot') }};

Step-by-step explanation.

1. The snapshot config sits in snapshots/users_snapshot.sql. The target_schema='snapshots' places the physical table in a dedicated schema; the unique_key='id' identifies the natural key; the strategy='timestamp' uses the source's updated_at; invalidate_hard_deletes=true handles GDPR deletes.
2. The models/sources.yml declares raw.users with a freshness SLA. If the source's updated_at hasn't moved forward in 6 hours, dbt source freshness fails, and the dbt Cloud job pages the on-call. The freshness check catches upstream load failures before they cascade into snapshot staleness.
3. The dbt_project.yml block pins snapshots in the target_schema and tags them for scheduling. Every snapshot in the project inherits both settings without repeating them in the block.
4. The dbt Cloud job runs at 5 minutes past every hour: first dbt source freshness (catch upstream failures early), then dbt snapshot (execute the SCD-2 update), then any downstream models tagged daily that depend on the fresh snapshot. Failures Slack the data-alerts channel.
5. The monitoring view exposes staleness_seconds — how long since the snapshot last captured a change. A dashboard panel + alert on staleness_seconds > 7200 catches the case where the snapshot job "succeeded" but there were no source changes to capture (which is also worth investigating).

Output.

Component	Purpose
snapshots/users_snapshot.sql	SCD-2 logic
sources.yml (freshness)	catch upstream load failures
dbt_project.yml	schema + tag defaults
dbt Cloud job	orchestration + alerting
monitoring view	staleness dashboard

Rule of thumb. A production snapshot is not just the snapshots/ file — it's the source freshness contract + the schema/tag defaults + the orchestrator job + the monitoring view. Ship all five together or the snapshot is a hidden fragility.

Worked example — migrating a hand-rolled MERGE with parallel run and diff

Detailed explanation. A senior data engineer inherits a hand-rolled SCD-2 pipeline for customers and needs to migrate to a dbt snapshot with zero downtime and no lost history. The audit-safe path is (1) hydrate the new snapshot table with existing history, (2) run both pipelines in parallel, (3) diff daily, (4) cut over after clean diffs, (5) delete the old pipeline after soak. Walk the interviewer through the six days.

• Day 0. Snapshot config written; new snapshot table hydrated from existing history.
• Days 1–7. Both pipelines run daily; diff query compared.
• Day 8. Downstream models point at new snapshot; old pipeline still runs (rollback).
• Day 15. Old pipeline deleted.

Question. Produce the diff query that runs daily during the parallel window. What thresholds trigger rollback vs cutover?

Input.

Table	Owner
warehouse.customers_history	legacy hand-rolled MERGE
snapshots.customers_snapshot	new dbt snapshot

Code.

-- Daily diff — must return zero rows for cutover to be safe
-- models/monitoring/mon_customers_history_diff.sql
{{ config(materialized='view') }}

WITH new AS (
  SELECT id, plan, dbt_valid_from AS valid_from, dbt_valid_to AS valid_to
  FROM   {{ ref('customers_snapshot') }}
),
old AS (
  SELECT id, plan, valid_from, valid_to
  FROM   {{ source('warehouse', 'customers_history') }}
)
SELECT 'in_new_not_old' AS delta, id, plan, valid_from, valid_to
FROM   new
EXCEPT
SELECT 'in_new_not_old', id, plan, valid_from, valid_to
FROM   old

UNION ALL

SELECT 'in_old_not_new', id, plan, valid_from, valid_to
FROM   old
EXCEPT
SELECT 'in_old_not_new', id, plan, valid_from, valid_to
FROM   new;

-- Cutover decision matrix (documented in runbook):
--   delta rows > 0 for >2 consecutive days → HALT cutover; investigate
--   delta rows == 0 for 7 consecutive days → PROCEED to cutover
--   delta rows > 100 → rollback; there is a real bug

-- Cutover — point downstream models at the new snapshot
-- Before:
SELECT ... FROM warehouse.customers_history WHERE ...
-- After:
SELECT ... FROM {{ ref('customers_snapshot') }} WHERE ...

Step-by-step explanation.

1. The diff query uses EXCEPT to find rows present in one table but not the other. The first EXCEPT finds rows in the new snapshot missing from the old (either the snapshot captured something the MERGE missed, or the two disagree). The second finds rows in the old missing from the new.
2. A cutover is safe only when both deltas are zero for at least a week. The one-week soak absorbs the tail of edge cases (timezone boundaries, month-end batch runs, holiday-week volume spikes) that a single day's diff might miss.
3. If the delta shows a small number of rows (~10 per day), investigate. Common causes: retro-updates handled differently by the two pipelines, second-precision vs millisecond-precision updated_at, invalidate_hard_deletes disagreement. Each cause has a known fix; document them in the runbook.
4. If the delta shows a large number of rows (>100 per day), roll back — there is a real bug in the new snapshot or the hydration. Don't cut over; fix first.
5. After cutover (day 8), the old pipeline still runs. It's the emergency rollback: if downstream analytics discovers a regression on day 10, point back at the old table. On day 15 (after another week of soak), delete the old pipeline for good.

Output.

Day	Both pipelines run?	Diff rows	Action
1	yes	0	continue soak
2	yes	0	continue soak
3	yes	0	continue soak
...	...	...	...
7	yes	0	approve cutover
8	yes (old as rollback)	—	downstream now on new snapshot
15	new only	—	delete old pipeline

Rule of thumb. Every migration from a hand-rolled MERGE to a dbt snapshot ships with a week of parallel run + a diff + a soak. Skip any of the three and you ship a rollback event, not a migration.

Worked example — dbt Cloud vs dbt Core with Airflow for snapshot scheduling

Detailed explanation. Two production teams pick different orchestrators. Team A uses dbt Cloud and configures the snapshot job in the UI. Team B uses Airflow with dbt Core; the snapshot is a task in an Airflow DAG. Show both configurations and the trade-offs.

• Team A — dbt Cloud. Job scheduled in the UI; runs dbt snapshot; failures Slack an alert channel; run history visible in the UI.
• Team B — Airflow + dbt Core. DAG has a BashOperator running dbt snapshot --profiles-dir /etc/dbt; failures trigger Airflow's alert path.

Question. Show both configurations for the same snapshot and explain when to pick each.

Input.

Team	Orchestrator	dbt runtime
A	dbt Cloud	dbt Cloud
B	Airflow	dbt Core (on a scheduler-side worker)

Code.

# Team A — dbt Cloud job (configured in UI; YAML for illustration)
name: users-snapshot-hourly
schedule: "5 * * * *"
commands:
  - "dbt snapshot --select users_snapshot"
notify_on_failure:
  - slack: "#data-alerts"
environment:
  target: prod

# Team B — Airflow DAG (dags/users_snapshot.py)
from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'analytics-eng',
    'retries': 2,
    'retry_delay': timedelta(minutes=5),
    'on_failure_callback': slack_alert,   # user-defined
}

with DAG(
    dag_id='users_snapshot',
    schedule_interval='5 * * * *',
    start_date=datetime(2026, 1, 1),
    catchup=False,
    default_args=default_args,
) as dag:

    snapshot = BashOperator(
        task_id='dbt_snapshot',
        bash_command=(
            'cd /opt/dbt_project && '
            'dbt snapshot --select users_snapshot '
            '--profiles-dir /etc/dbt --target prod'
        ),
    )

Step-by-step explanation.

1. Team A's dbt Cloud config is one job in the UI. dbt Cloud handles the cron schedule, the run environment (a hosted dbt runtime), and the alerting. Everything lives inside dbt Cloud; the operational burden is near zero.
2. Team B's Airflow DAG defines the same schedule (5 past every hour), but runs dbt snapshot as a BashOperator on an Airflow worker. The DAG has explicit retries (2, with 5-minute backoff) and a Slack callback on failure. More YAML/Python; more flexibility.
3. The trade-off is clear: dbt Cloud minimises glue at the cost of vendor lock-in and per-run pricing. Airflow maximises flexibility at the cost of running your own scheduler infrastructure (or paying MWAA / Cloud Composer for a hosted one).
4. Teams already on Airflow for other pipelines almost always add dbt as a task rather than adopting dbt Cloud — the marginal cost of one extra DAG is trivial. Teams starting fresh often pick dbt Cloud because the time-to-first-scheduled-snapshot is measured in minutes.
5. Both configurations produce the same result: the dbt snapshot command runs at the desired cadence, and the snapshot table stays fresh. The choice of orchestrator is operational, not architectural.

Output.

Team	Time to first schedule	Flexibility	Operational cost
A (dbt Cloud)	minutes	limited to dbt Cloud features	vendor bill
B (Airflow)	hours (write DAG, deploy)	full	run Airflow yourself

Rule of thumb. Pick the orchestrator that already runs the rest of your data platform. Introducing a second orchestrator just for dbt snapshots is a common mistake — the operational overhead outweighs any per-snapshot convenience.

Senior interview question on production snapshot patterns

A senior interviewer might ask: "You inherit a project with three snapshots — all in the default schema, all scheduled via cron on a build server, none with source-freshness checks. Walk me through the first-week hardening you'd do."

Solution Using a five-day production hardening plan

First-week hardening plan — dbt snapshots
==========================================

Day 1 — Move to dedicated target_schema
  - Add {{ config(target_schema='snapshots') }} to every snapshot
  - Grant read on 'snapshots' schema to analysts + BI
  - Grant write on 'snapshots' schema only to the dbt Cloud service role

Day 2 — Verify source vs staging model
  - Audit every snapshot: does it read from source() or ref()?
  - Rewrite any that read from ref() to read from source() — or pin the ref as a contract

Day 3 — Add source freshness SLAs
  - models/sources.yml — add freshness block per source
  - dbt source freshness runs first in the job; catches upstream load failures

Day 4 — Move orchestration to dbt Cloud or Airflow
  - Retire cron on the build server
  - dbt Cloud job or Airflow DAG scheduled per source velocity
  - Slack alerts on failure

Day 5 — Add monitoring model + freshness dashboard
  - models/monitoring/mon_snapshot_freshness.sql
  - Dashboard panel: staleness_seconds per snapshot
  - Alert: staleness_seconds > 2x cadence

Step-by-step trace.

Day	Activity	Output
1	Move to snapshots schema	permissions tightened, audit-safe layout
2	Source() vs ref() audit	contract pinned; no accidental filter drift
3	Source freshness SLAs	upstream failures caught before snapshot staleness
4	dbt Cloud / Airflow orchestration	cron retired; alerting live
5	Monitoring model + dashboard	on-call sees staleness at a glance

After day 5, the deployment has all five production patterns in place. The snapshots are on their own schema, read from the raw source, have freshness SLAs, run on a real scheduler with alerts, and expose staleness metrics on a dashboard. The team can sleep at night; the on-call has a paved runbook.

Output:

Pattern	Before	After
Dedicated schema	no	yes (snapshots)
Source() vs ref()	mixed	source() everywhere
Freshness SLAs	none	per-source blocks in sources.yml
Real orchestrator	cron	dbt Cloud / Airflow
Monitoring	none	dashboard + alert

Why this works — concept by concept:

• Five patterns, one production posture — dedicated schema + source() reads + freshness SLAs + real orchestrator + monitoring. Missing any one is a future incident.
• Contract with the raw source — the snapshot's contract is with the raw table's shape, not the staging model's shape. If you ever need to redact PII in the staging layer, do it downstream of the snapshot — never upstream.
• Source freshness as the leading indicator — the snapshot can only be as fresh as the source. If the upstream loader stalls, the snapshot silently produces old data. Freshness SLAs make the stall visible before the snapshot becomes wrong.
• Orchestrator = alerting — cron is not an orchestrator. Real orchestrators (dbt Cloud, Airflow, Prefect, Dagster) come with run history, retries, and alerting out of the box. Cron on a build server is a hidden fragility.
• Cost — five days of senior-engineer time for the hardening; the avoided cost of one 4-hour on-call incident pays for the entire week. O(1) per quarter for re-tuning source freshness thresholds.

SQL
Topic — sql
SQL production SCD-2 and audit-schema problems

Practice →

ETL
Topic — etl
ETL problems on dbt snapshot orchestration and freshness

Practice →

---

Cheat sheet — dbt snapshot recipes

• Timestamp-strategy 8-line snapshot. The default recipe for any source with a reliable updated_at:

{% snapshot users_snapshot %}
    {{ config(target_schema='snapshots', unique_key='id',
              strategy='timestamp', updated_at='updated_at') }}
    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

• Check-strategy skeleton. Use when the source has no updated_at or you specifically want to track a subset of columns:

{% snapshot products_snapshot %}
    {{ config(target_schema='snapshots', unique_key='sku',
              strategy='check', check_cols=['price', 'category']) }}
    SELECT sku, name, price, category
    FROM   {{ source('raw', 'products') }}
{% endsnapshot %}

• Point-in-time query template. The canonical form for "what was the value at time T?":

SELECT *
FROM   {{ ref('users_snapshot') }}
WHERE  '2026-05-15 12:00:00'
       BETWEEN dbt_valid_from
       AND     COALESCE(dbt_valid_to, '9999-12-31');

• Current-state view. One row per unique_key, currently active:

{{ config(materialized='view') }}
SELECT id AS customer_id, email, plan
FROM   {{ ref('users_snapshot') }}
WHERE  dbt_valid_to IS NULL;

• invalidate_hard_deletes safety toggle. For sources that hard-delete rows (GDPR, PII expiry):

{% snapshot users_snapshot %}
    {{ config(target_schema='snapshots', unique_key='id',
              strategy='timestamp', updated_at='updated_at',
              invalidate_hard_deletes=true) }}
    SELECT id, email, plan, updated_at
    FROM   {{ source('raw', 'users') }}
{% endsnapshot %}

• MERGE → snapshot migration recipe. Parallel run + diff + cutover + soak. Hydrate the new snapshot from existing history; run both pipelines for a week; diff query returns zero for 7 consecutive days; cutover; leave the old pipeline as rollback for another week; then delete.
• Composite unique_key. For sources without a single-column natural key, concatenate:

{{ config(unique_key="tenant_id || '-' || record_id") }}

• Sizing the snapshot cadence. High-velocity sources → every 5–15 minutes; medium → hourly; low → daily. Never a global default; sizing is per-source.
• Source freshness contract. In sources.yml, add freshness per source with warn_after and error_after thresholds. Run dbt source freshness first in the snapshot job; upstream stalls fail fast.
• Monitoring model. A view over each snapshot that exposes staleness_seconds = EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(dbt_updated_at))). Dashboard + alert on staleness_seconds > 2 × cadence.
• Fact-table pre-resolved FK. Store customer_dbt_scd_id on the fact when it's built; downstream joins are equi-joins, not range joins. One BETWEEN at build time; N cheap joins at query time.
• Never UPDATE or DELETE the snapshot table by hand. The four dbt_valid_* columns are dbt-managed. Manual mutation breaks the SCD-2 invariant. GDPR corrections happen at source, then re-snapshot.
• Target schema convention. Always target_schema='snapshots'. Never mix snapshots with mart tables. Permission the schema tightly: few writers (the dbt Cloud service role), many read-only auditors.
• Snapshot the source(), not the ref(). The snapshot's contract is with the raw table shape. If you must snapshot a staging model, pin the model's shape as a contract and never change the columns.
• Two-strategy warehouse pattern. Timestamp for the 80% of sources with a reliable updated_at; check for the 20% without. Pick per source; document the choice in a comment on the snapshot block.

Frequently asked questions

What is a dbt snapshot and how does it differ from a normal dbt model?

A dbt snapshot is a declarative SCD-2 primitive: you write a SELECT from a source, declare a strategy, a unique_key, and an updated_at (or check_cols), and dbt maintains a warehouse-managed history table with four automatically-populated meta-columns (dbt_scd_id, dbt_updated_at, dbt_valid_from, dbt_valid_to). A normal dbt model materialises a query into a table or view — it can be a full-refresh table, an incremental table, or a view — but every run either rebuilds the table or appends rows; none of the model materializations preserves history the way a snapshot does. The snapshot's contract is that the resulting table is append-only (with an in-place update to close superseded rows) and that every unique_key has exactly one open row at any time. That contract is what turns a source SELECT into an audit-safe timeline. Every senior dbt project uses snapshots for dimensions and models for facts and marts.

Timestamp vs check strategy — which do I pick?

Default to the timestamp strategy whenever the source has a reliable updated_at column — it's an order of magnitude faster (delta scan vs full-table hash) and produces exact-timestamp history rather than snapshot-run-aligned history. Switch to the check strategy in three cases: (a) the source has no updated_at column at all (vendor-supplied CSV, log-derived table), (b) the source has an updated_at but you can't trust it (manual DBA scripts bypass it, some ORMs skip it on partial updates), or (c) you specifically want to track changes to a subset of columns and ignore the rest (e.g. price + category, ignoring a noisy last_seen_at). With the check strategy, always enumerate check_cols explicitly — check_cols='all' picks up cosmetic-noise columns and produces spurious history rows on every run. If in doubt, prefer timestamp and verify the source's updated_at reliability; if verification fails, migrate to check.

Where should snapshots live in my dbt project?

Physically, put every snapshot in a dedicated snapshots schema — set {{ config(target_schema='snapshots') }} on every snapshot block, or set the default project-wide in dbt_project.yml. Isolating snapshots from marts means (a) permissions can be tighter (few writers, many read-only auditors), (b) storage tiers can be set separately (snapshots often merit slower/cheaper storage than hot marts), and (c) an accidental schema drop on analytics doesn't wipe your history. In the dbt project source tree, snapshot files live under snapshots/ (not models/) — dbt's project layout separates the two so that dbt snapshot and dbt run are independent commands. Downstream models ref() the snapshot exactly like a normal model; the schema-separation is a runtime concern, not a DAG concern.

Do dbt snapshots track deletes from the source?

By default, no — a row that disappears from source stays with dbt_valid_to = NULL forever, which lies about the entity's current state. Set invalidate_hard_deletes: true in the snapshot config to make dbt scan for missing keys on every run and close them by setting dbt_valid_to = CURRENT_TIMESTAMP(). The cost is a full-source scan per run; the reward is correct MAU / ARR / churn metrics downstream. The alternative pattern is to use soft deletes at the source — a nullable deleted_at column that the app sets on delete — and treat that column as a normal snapshot payload column. In the soft-delete world, invalidate_hard_deletes is unnecessary because rows never vanish; downstream queries filter by both dbt_valid_to IS NULL (current version) and deleted_at IS NULL (not soft-deleted). Both patterns are valid; pick based on whether you control the source's delete semantics.

Can I snapshot a dbt model or only a raw source?

Technically yes — dbt lets you write SELECT ... FROM {{ ref('stg_customers') }} inside a snapshot block. Operationally you almost always shouldn't. The snapshot's contract is with the shape of the input. If you snapshot a staging model, you couple the snapshot's history to that model's transformations; if you later add a filter or change a column derivation, you either lose historical rows or invalidate the entire history. The safe pattern is snapshot the raw source() — the raw table is stable, and your staging transformations run downstream of the snapshot, not upstream. The narrow exception: PII you legally cannot store in the snapshot's schema. Snapshot a redacted staging model, but pin its shape as a contract (schema tests, dbt-checkpoint config, a comment declaring "do not modify") and never change the columns.

How often should I run dbt snapshots?

Run snapshots at a cadence that matches the source's velocity, not a project-wide global. High-velocity sources (many updates per hour) merit every 5–15 minutes; medium-velocity sources (updates per day) merit hourly; low-velocity sources (updates per week) merit daily or after each source load. The one-size-fits-all "every hour" pattern over-services cheap sources and under-serves expensive ones; sizing per source takes 5 minutes per snapshot and saves warehouse compute at every subsequent run. Cadence also determines the temporal resolution of your history: an hourly snapshot cannot distinguish two updates that happen within the same hour (the last-write-wins in a batch). If you need sub-hour resolution, run more frequently — or (for genuine per-update fidelity) layer a CDC stream upstream of the snapshot and treat the snapshot as an aggregation layer, not the source of truth.

Practice on PipeCode

• Drill the SQL practice library → for the SCD-2 modelling, point-in-time query, and MERGE-vs-snapshot problems senior interviewers love.
• Rehearse on the ETL practice library → for the slowly-changing-dimension pipelines and hard-delete audit patterns that show up in warehouse design rounds.
• Sharpen the modelling axis with the optimization practice library → for the snapshot-cadence, fact-table-FK, and hash-strategy trade-off problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the snapshot + strategy intuition against real graded inputs.

Lock in dbt snapshot muscle memory

Docs explain configs. PipeCode drills explain the decision — when to pick the check strategy, when invalidate_hard_deletes matters, when the point-in-time BETWEEN pattern breaks without a COALESCE. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →