In a dbt snapshot, what happens to a row that disappears from the source?
answer
- absence is not the same as a signal
- an open window claims to be true today
- you have to opt in to noticing
- a partial extract looks exactly like a deletion
- soft deletes make the question go away
basics
~10 sBy default nothing: dbt never sees it, so its latest version keeps dbt_valid_to NULL and still looks current forever. Configuring hard-delete handling makes dbt close that version out at run time instead.
solid answer
~40 sA snapshot run only compares keys it finds in the source. A hard-deleted row is absent, so dbt leaves its open version untouched — and an open version means "currently true", so deleted entities linger in every downstream `where dbt_valid_to is null` query indefinitely. To fix that you opt in: older dbt versions use `invalidate_hard_deletes=true`, which closes the missing row's window at the run timestamp; dbt 1.9 replaces it with `hard_deletes`, taking `ignore`, `invalidate`, or `new_record` (which inserts a tombstone version flagged as deleted). The operational catch is that dbt cannot distinguish a real deletion from a source that returned fewer rows than usual — a filtered view, a partial extract, a permissions change — so one broken upstream load can close out half your dimension in a single run.
code
sql · 6 lines{{ config(
unique_key='id',
strategy='timestamp',
updated_at='updated_at',
invalidate_hard_deletes=true
) }}go deeper
Know that by default a row deleted from the source keeps its last snapshot version open, so it still looks current, and that closing it requires opting in through configuration.
Explain the config options and what each writes: leave the row alone, close its window at run time, or close it and insert an explicit deleted marker row.
Raise the failure mode unprompted — dbt cannot distinguish deletion from a partial source read — and describe the guards: freshness and row-count checks before the snapshot, atomic loads, and alerting on rows closed per run.
Push the problem upstream: negotiate soft deletes or a change feed in the source contract so deletion is an explicit fact rather than something the warehouse infers from missing rows.
## Absence is not a signal by default A snapshot run reads the source, matches each row against the open version in the history table, and closes or inserts as needed. Keys that are *not* in the source are simply never examined. dbt's default behaviour is therefore to leave a hard-deleted entity's last version open, with `dbt_valid_to` NULL. That is a semantic problem, not just an omission. NULL `dbt_valid_to` means "this is the current truth". So a customer deleted from the source two years ago still appears in every `where dbt_valid_to is null` query, in every current-state dimension built on the snapshot, and in every count of active entities. The history says the deletion never happened. ## Opting in to hard-delete handling dbt's older config is `invalidate_hard_deletes=true`. With it set, a run that finds an open snapshot row whose key is absent from the source writes the run timestamp into that row's `dbt_valid_to`. The version closes, no new version opens, and the entity correctly drops out of current-state queries while its history stays intact and queryable as of past dates. dbt 1.9 replaces that boolean with a three-valued `hard_deletes` config: - `ignore` — the historical default: absent keys are left alone. - `invalidate` — the behaviour of the old boolean: close the open window. - `new_record` — close the window *and* insert an explicit tombstone version carrying a deleted indicator column, so "was deleted at T" is a fact you can join to and count rather than an inference from a closed window with no successor. `new_record` is the more expressive option when downstream consumers need to reason about deletions directly; `invalidate` is enough when they only need current state to be correct. ## The dangerous failure mode Here is the part senior candidates are expected to raise unprompted: **dbt cannot tell a deletion from a bad read.** The signal is identical — the key was not in the result set. If the snapshot's source returns fewer rows than it should, `invalidate` will faithfully close out every missing entity as though the business deleted them. Real causes seen in practice: - an upstream extract that failed partway and landed a truncated table; - a permissions or row-level-security change that filters rows for the dbt service account; - a source defined over a view whose predicate changed; - an ingestion job that reloads by truncating first, with the snapshot running in the gap; - a partitioned source where the loader wrote only part of the range. The blast radius is large and recovery is awkward: dbt closed the windows correctly according to what it saw, and re-running after the source is fixed does not reopen them — it inserts *new* versions, so each affected entity now carries a spurious deletion-and-resurrection in its history. Cleaning that up means hand-editing the snapshot table, which is exactly the surgery you never want on your only copy of history. Mitigation is about not letting a bad read reach the snapshot: run source freshness and row-count checks *before* `dbt snapshot` in the schedule; never point a snapshot at a view whose filter can move; have the loader swap tables atomically rather than truncate-and-reload; and alert on the number of rows closed by a single run, since a normal day closes a handful and a broken day closes thousands. ## When you do not need this at all Many source systems soft-delete: they keep the row and set `is_deleted`, `deleted_at` or a status value. That is strictly better for snapshotting. The deletion is a value change like any other, the `timestamp` or `check` strategy picks it up naturally, and the tombstone is part of the data rather than an inference from absence. If you have any influence over the source, ask for soft deletes; if the source already soft-deletes, leave hard-delete handling off and let the flag do the work. ## Deciding Ask what a downstream consumer must be able to answer. If "is this entity active today?" must be right, you need at least `invalidate`. If "when did entities get deleted, and how many last month?" must be answerable, you need an explicit deleted marker — from the source if possible, from `new_record` otherwise. Whichever you choose, guard the source read, because with hard-delete handling enabled the snapshot is only as trustworthy as the completeness of the last query it ran.
- Why is enabling hard-delete invalidation on a dbt snapshot risky when the source load can fail partway?dbt sees only absence, and a truncated extract is indistinguishable from a mass deletion. One bad run can close out thousands of entities, and re-running after the fix does not reopen them — it inserts new versions, leaving a false deletion and resurrection in the history. Gate the snapshot behind freshness and row-count checks, and alert on how many rows a run closes.
- Why is a soft-deleted source easier to snapshot than one that hard-deletes?The row stays present with a flag such as is_deleted or deleted_at, so the deletion is an ordinary value change that the timestamp or check strategy captures with no special configuration. You also get the deletion timestamp from the business system rather than inferring it from when dbt happened to run, and a partial extract cannot masquerade as a deletion.
- What does the new_record option add over simply invalidating a deleted row in a dbt snapshot?It writes an explicit tombstone version carrying a deleted indicator, so downstream models can select and count deletions directly rather than inferring them from a closed window with no successor. That distinction matters when consumers need to report on deletion events, not merely exclude deleted entities from current state.
saying these in an interview costs you the question
- Assumes dbt closes deleted rows out automatically
- Says a deleted entity disappears from the snapshot table
- Enables invalidation without guarding the source read
- Thinks re-running after a bad load reopens closed windows
- Treats a truncated extract as distinguishable from deletions