dbt snapshots and SCD type 2: what history misses

A dbt snapshot does not just back up a table. It turns a mutable dimension into an interval history, and a report that joins to it has to respect the interval or it silently produces a different, wrong number. dbt's snapshot documentation puts it plainly: "Analysts often need to look back in time at previous data states in their mutable tables... dbt provides a mechanism, snapshots, which records changes to a mutable table over time." That mechanism implements SCD type 2: keep every past version as its own row instead of overwriting it.

Two strategies drive a snapshot run. On the timestamp strategy, dbt compares the source row's own updated_at against what it recorded last run: "If the configured updated_at column for a row is more recent than the last time the snapshot ran, then dbt will invalidate the old record and record the new one." The check strategy instead compares a named list of columns and stamps the run's own execution time rather than the source's updated_at. This note traces the timestamp strategy throughout, because the hidden-state trap in the right-version section below depends on dbt_updated_at matching dbt_valid_from; a reader on the check strategy would see different literal timestamps in their own dbt_valid_from column, though the join logic would not change.

dbt writes a fixed set of meta-columns onto each snapshot row. The meta-column reference documents the defaults used here: dbt_valid_from ("the timestamp when this snapshot row became valid"), dbt_valid_to ("the timestamp when this row became invalidated... For current records, this is NULL by default"), and dbt_scd_id and dbt_updated_at, both used internally by dbt rather than read by a report. A rename does not retroactively touch a table that already has history: "To avoid any unintentional data modification, dbt will not automatically apply any column renames."

This note works from one small synthetic history: seven snapshot rows for five customers, modelling the output of a timestamp-strategy snapshot run (dbt_updated_at equals dbt_valid_from on every row, matching the mechanic above), and thirteen orders those customers placed. dbt_scd_id is shown here as a short readable id (s1a, s1b and so on); dbt itself generates an md5 hash, so treat the short id as a label, not as dbt's real format.

Three customers carry the core story. c1 upgrades from standard to premium on 2026-01-15, the exact date one of their own orders lands on. c2 never changes, a control. c3 changes twice between two runs: standard until 2026-01-06, when the recorded snapshot jumps straight to gold. Two further customers, c4 and c5, add a hard delete and a late arrival once both wrong joins are on the table.

This is a different failure from the one schema drift should fail loudly, not quietly covers: that note gates a source shape change before ingestion runs, while this note joins a dimension history correctly after ingestion. Replaying the same event twice is a separate failure again, covered by replay safety: a key changing twice between two runs is not the same thing as one event arriving twice, and the two should not be conflated. This note's fixture reuses the order shape of the flagship retail-orders pipeline — order_id, order_date, channel and amount_cents — with a customer_id column appended.

Two tempting joins both compile, both return a plausible-looking total and both are wrong, for different reasons.

Wrong version one: join to the current row

The simplest join filters customer_snapshots to the row with dbt_valid_to IS NULL for each customer_id and joins facts to that one row, regardless of the fact's own date. Some teams materialise this filter as a separate current-dimension view instead of writing it inline; that changes the SQL, not the numbers below.

-- Teaching sketch: wrong version 1. Join every order to the customer's-- current row (dbt_valid_to IS NULL), no matter when the order happened. A-- customer with no current row (a hard delete, with no replacement row) has-- no match at all, so an inner join silently drops every one of their orders.-- The application does not run this SQL.SELECT  s.segment,  CAST(COALESCE(SUM(o.amount_cents), 0) AS BIGINT) AS revenue_centsFROM orders AS oJOIN customer_snapshots AS s  ON o.customer_id = s.customer_id AND s.dbt_valid_to IS NULLWHERE ($1 = 'segment_history' AND o.customer_id IN ('c1', 'c2', 'c3'))   OR ($1 = 'with_hard_delete' AND o.customer_id IN ('c1', 'c2', 'c3', 'c4'))   OR ($1 = 'with_late_arrival' AND o.customer_id IN ('c1', 'c2', 'c3', 'c5'))GROUP BY s.segmentORDER BY s.segment;

Every order a customer ever placed is reported under their present segment. On the three-customer baseline the right join (below) gives standard 24000, premium 10000 and gold 9000, a total of 43000 cents. The current-row join redistributes the same 43000 cents across the wrong segments instead: standard drops to 10000, premium rises to 18000 and gold rises to 15000, because every one of c1's orders now counts as premium and every one of c3's now counts as gold, no matter when they actually happened. The total does not move; only where it lands does.

Wrong version two: join to every version

The second tempting join keeps every historical row and matches on customer_id alone, with no dbt_valid_from/dbt_valid_to bound at all.

-- Teaching sketch: wrong version 2. Join every order to every historical row-- for its customer, with no dbt_valid_from/dbt_valid_to bound. A customer-- with n historical rows fans every one of their orders out n times, and a-- segment-revenue aggregate sums the duplicates.-- The application does not run this SQL.SELECT  s.segment,  CAST(COALESCE(SUM(o.amount_cents), 0) AS BIGINT) AS revenue_centsFROM orders AS oJOIN customer_snapshots AS s  ON o.customer_id = s.customer_idWHERE ($1 = 'segment_history' AND o.customer_id IN ('c1', 'c2', 'c3'))   OR ($1 = 'with_hard_delete' AND o.customer_id IN ('c1', 'c2', 'c3', 'c4'))   OR ($1 = 'with_late_arrival' AND o.customer_id IN ('c1', 'c2', 'c3', 'c5'))GROUP BY s.segmentORDER BY s.segment;

A customer with n historical rows fans every one of their orders out n times. c1 and c3 each have two snapshot rows, so each of their orders is counted twice: once under the old segment and once under the new one. The baseline total inflates from 43000 to 76000 cents, a 33000-cent duplicate exactly equal to c1's and c3's own order totals (18000 and 15000). c2, with one snapshot row, does not duplicate at all.

The right join carries the interval as a range condition: match each order to the one snapshot row where dbt_valid_from <= order_date and order_date < dbt_valid_to, or dbt_valid_to IS NULL for the current row. An order that predates every snapshot row for its customer, or follows the last one with no replacement row, matches nothing; it is routed to an explicit unknown segment by policy, rather than silently dropped or silently matched to a segment it never held.

-- Teaching sketch: the right version. Join each order to the one snapshot-- row whose dbt_valid_from/dbt_valid_to interval covers the order's own-- date. An order that falls before every interval for its customer (a late-- arrival) or after the last one (a hard delete with no replacement row)-- matches nothing and is routed to 'unknown' by policy, not silently-- dropped or silently matched to an unrelated segment.-- The application does not run this SQL.SELECT  COALESCE(s.segment, 'unknown') AS segment,  CAST(COALESCE(SUM(o.amount_cents), 0) AS BIGINT) AS revenue_centsFROM orders AS oLEFT JOIN customer_snapshots AS s  ON o.customer_id = s.customer_id AND o.order_date >= s.dbt_valid_from AND (o.order_date < s.dbt_valid_to OR s.dbt_valid_to IS NULL)WHERE ($1 = 'segment_history' AND o.customer_id IN ('c1', 'c2', 'c3'))   OR ($1 = 'with_hard_delete' AND o.customer_id IN ('c1', 'c2', 'c3', 'c4'))   OR ($1 = 'with_late_arrival' AND o.customer_id IN ('c1', 'c2', 'c3', 'c5'))GROUP BY COALESCE(s.segment, 'unknown')ORDER BY COALESCE(s.segment, 'unknown');

On the three-customer baseline this gives the numbers quoted above: standard 24000, premium 10000, gold 9000, a total of 43000 cents that does not move under either wrong join; only the segment it lands on moves, by 14000 cents off standard, 8000 onto premium and 6000 onto gold.

This is where c3's hidden state matters. Their true segment held premium for three days that no snapshot row ever recorded, overwritten by gold before the next run captured anything in between. Their order o8, on 2026-01-04, falls inside that invisible window; the as-of join cannot recover a state a snapshot never wrote down, so it correctly reports o8 against the standard row that was the recorded truth at the time. dbt's own FAQ on snapshot frequency says only that snapshotting is a batch-based approach run on a schedule, intended to run between hourly and daily; it does not say what happens when the same key changes more than once between two runs. The claim that the middle state leaves no row at all follows from applying the documented timestamp-strategy mechanic to a same-key double change, rather than from a quoted dbt guarantee. It is corroborated by an operator's account of exactly this failure in a public GitHub discussion, cited in full below.

Two more customers extend the dataset with traps a snapshot table creates on its own, independent of which join reads it. c4 is hard-deleted: a run closes their last row's dbt_valid_to with no replacement row inserted. Their order before the deletion still matches the standard interval; their order after it matches no interval at all under the as-of join, so it is routed to unknown: standard rises to 27500 and unknown holds 7000, a total of 53500 cents. The current-row join instead finds no row at all with dbt_valid_to IS NULL for c4, so an inner join silently drops both their orders: the total stays at the baseline 43000, understating revenue by 10500 cents without raising an error. The every-version join never checks the interval, so it keeps matching c4's orders to their only-ever standard row even after the account closes: its standard also reaches 53500, for a total of 86500 cents, but by keeping a segment the customer no longer had rather than by routing anything to unknown — the same trap, two different wrong answers.

c5 is a late arrival: no snapshot row exists before 2026-01-22. Their order on 2026-01-10 predates it, so the as-of join routes it to unknown; their order on 2026-01-24 lands inside the new standard interval. standard reaches 28000 and unknown holds 6000. Both wrong joins fold the earlier order into standard anyway, since neither checks a lower bound. The current-row join gives standard 20000 with no unknown row at all, for a total of 53000 cents. The every-version join does the same without fanning out (c5 has one snapshot row): its standard reaches 53000, for a total of 86000 cents.

Step through the scrubber above in order and the running total only ever grows: by 2026-01-25 it has counted every order and reached 63500 cents, the sum of all thirteen. Compare any two methods against the same scenario below.

Three ways of modelling a changing dimension trade off differently. Type 1 (overwrite) is the simplest and smallest: no history, so every report restates the past with today's attributes. It is the right choice when no report needs to know what was true before the latest overwrite. Type 2 (versioned rows, what a dbt snapshot builds) keeps what was true at each captured point, so an as-of join gives a report that does not move after the fact; the cost is more rows, a join that must carry dbt_valid_from and dbt_valid_to, a hard-delete policy to choose and the cadence limit above. A periodic full copy of the dimension is a third option: easy to query by date without a join at all, but storage grows with every copy and it is still bound by the copy cadence, the same as a snapshot. Choose by which reports must not move after the fact, how often the source actually changes relative to the run schedule and how large the dimension is.

A hard-delete policy is itself a choice, not a default a reader can assume. This dataset models hard_deletes: invalidate, which keeps the oracle tractable, but dbt's own hard-delete reference names ignore as the actual default: "No action for deleted records." A project that never configured this keeps a deleted customer's dbt_valid_to at NULL forever, a different and more common failure than the one this note's trap models.

Meta-column names are not fixed either. From v1.9, snapshot_meta_column_names lets a team rename dbt_valid_from, dbt_valid_to and the rest to its own convention, and dbt_valid_to_current lets a team swap the current row's NULL for a sentinel date such as 9999-12-31 instead. Either change alters the literal values a join in this note's style has to compare against, not the join logic itself. Upgrading to v1.9 also moved snapshot definitions from a SQL block to an optional YAML file and made target_schema optional. A project on dbt Core 1.8 or earlier only has invalidate_hard_deletes: true or false, a hard-coded NULL for the current row and fixed meta-column names.

This teaching sketch does not prove that dbt executes this SQL, on any warehouse, for any strategy; the three SQL sketches above are teaching sketches, and the application does not run dbt. It does not model the check strategy, check_cols, a concurrent snapshot run or the naive-vs-aware timestamp corruption of dbt_valid_to that operators describe in a GitHub discussion on snapshot problems. One contributor there describes exactly the cadence-limit failure in production and built custom per-batch replay logic to recover the correct order; that account is named here as real operator evidence, not reproduced or measured. This note does not prove a cost or performance claim about snapshotting a large table, and it does not prove which hard-delete policy or run cadence a given team should choose.

The evidence is narrower: three join strategies against one seven-row synthetic history and thirteen synthetic orders, checked against an independent TypeScript oracle. A production SCD type 2 pipeline still needs a chosen strategy, a hard-delete policy, meta-column governance and a monitored run cadence. For the downstream write boundary this note does not cover, continue to re-running a backfill without doubling the total; read the bundled pipeline SQL or open the retail-orders studio or Canvas to inspect the wider project.