dbt Snapshot Interview Questions: Change Detection, Hard Deletes, and History Gaps

dbt snapshot interview questions are ultimately questions about evidence: which states did the pipeline observe, how did it recognize a change, and what can the resulting history prove? A snapshot can preserve versions of a mutable row. It cannot reconstruct a state that disappeared before any run read it.

This guide follows four customers across three actual snapshot runs. Use it alongside PracHub Data Engineer interview questions to practice explaining the output before proposing a configuration change.

Evidence boundary: dbt documentation supplies official configuration behavior. Our results were executed on September 9, 2026 with dbt Core 1.11.8, dbt-duckdb 1.10.1, and DuckDB 1.5.2, using an isolated local database and UTC sessions. The fixture and interview prompts are original preparation material, not candidate reports or employer question-bank claims. Results for this adapter are not guarantees for every warehouse.

What does a snapshot preserve?

Official behavior: dbt snapshots retain versions of mutable source records. The first run establishes the initial snapshot; later runs identify changes, close prior versions, and insert new versions. The default open-ended dbt_valid_to is NULL. See the dbt snapshot guide.

Our source has four columns: customer_id, status, email, and updated_at. Customer ID is the stable entity key. Status is the business attribute we want to analyze; email is deliberately outside the check strategy's tracked column list.

T0, T1, and T2 identify source timestamps. Snapshot execution times are recorded separately. In the verified run they were 19:59:21.375530, 20:29:21.375530, and 20:59:29.456992 UTC on September 9. Between runs 1 and 2, A briefly became paused, then active; both source updates occurred before the second snapshot.

That fixture separates three problems: an overwritten intermediate state, an unreliable update timestamp, and a deleted row. They need different explanations. Changing one configuration cannot solve all three.

Which configuration did we execute?

We created four independent, initially empty snapshot tables. Three used timestamp detection with different deletion policies; the fourth used status-value comparison and explicit deletion records. This YAML shows the two new_record variants:

snapshots:
  - name: ts_new
    relation: source('raw', 'customers')
    config:
      schema: history
      unique_key: customer_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: new_record

  - name: check_new
    relation: source('raw', 'customers')
    config:
      schema: history
      unique_key: customer_id
      strategy: check
      check_cols: [status]
      hard_deletes: new_record

The other timestamp tables were named ts_ignore and ts_invalidate, with their corresponding hard_deletes values. The raw source was declared separately, and uniqueness/not-null tests on customer ID passed before each run. With our profile's base schema main and the standard schema naming behavior, these tables were created in main_history.

Version boundary: YAML snapshot configuration and the newer deletion configuration are documented for dbt 1.9 and later. The official hard-deletes reference lists PostgreSQL, BigQuery, Snowflake, and Redshift adapters. Our DuckDB results are additional measured evidence for the exact versions above; verify your own adapter instead of assuming compatibility from the Core version alone.

To reproduce the experiment, initialize a separate project/database, declare the four-column source, create these four snapshots, and apply the source states in the table before each dbt snapshot. Use real timestamp values in increasing order and set the database session timezone explicitly. Do not run a destructive fixture against a production snapshot schema.

Why did timestamp miss B's suspension?

Official distinction: the strategy configuration selects timestamp-based or column-comparison change detection. The timestamp strategy requires an updated_at field representing the source row's update time.

In run 2, B's status changed from active to suspended while updated_at stayed T0. All three timestamp snapshots retained only B's active version. The check snapshot recorded suspended because status itself differed.

When B's timestamp advanced to T2 before run 3, the timestamp snapshots finally recorded suspended, with the new interval beginning at T2. They did not retroactively recover when the suspension originally happened. A later successful run fixed the latest observed status, but not the missing historical boundary.

An interview answer should distinguish this from scheduling. Running the same timestamp comparison more frequently would still miss a status update whose timestamp never advances. Investigate the source update contract: does every relevant change update the field, at sufficient precision, without moving backward?

Avoid describing timestamp as comparing against a single global watermark. In our installed implementation, the change expression compares the matched snapshot version's dbt_valid_from with the incoming row's updated_at. The entity key matters; one customer's timestamp does not define every customer's history.

Does check capture every change?

No. The check_cols reference specifies which columns are compared. Our check_cols: [status] detected B's suspension but ignored D's email-only change. D retained one check-history row through run 3, including the email value captured in that version.

The timestamp snapshots gave D two rows because its update timestamp advanced, even though status stayed active. Both rows legitimately say active. If the analytical requirement is status transitions only, counting every timestamp snapshot row as a status change would overstate transitions.

Choosing check_cols: all expands what counts as a change. It can also track operational noise, such as a refreshed ingestion timestamp. Choose columns from the historical reporting contract, then test a relevant change and an irrelevant change separately.

A's paused state is absent from every snapshot table. Check compared the states available at the run boundaries: trial, then active, then premium. It never received paused as an input. A value-comparison strategy cannot infer an intermediate value simply because a later value differs.

What do the hard-delete options change?

Customer C disappears before run 2 and returns before run 3. The observed timestamp results were:

The new_record output adds dbt_is_deleted. In this adapter's output it is a string, with values 'True' and 'False'. The deleted row retained C's last business status, active. Do not assume dbt changes your business status column to the word deleted; inspect the metadata flag.

For C in ts_new, the actual intervals were:

All times are UTC on the same date. The deletion timestamp came from the snapshot's observation, not a source deletion event: the missing source row had no new timestamp to supply. It establishes when this run recognized absence, not the exact instant the customer was deleted upstream.

Explicit deletion history still cannot capture a delete-and-restore cycle that occurs entirely between runs. Also confirm that apparent absence is real: an incomplete upstream load or a changed source filter can make a customer disappear from the snapshot input without an actual business deletion.

How do timestamp and check assign validity times?

For A, ts_new produced trial from T0 to T1, active from T1 to T2, and premium from T2 onward. Those boundaries use the source timestamps supplied in the fixture.

Our check snapshot deliberately omitted updated_at. Its A boundaries instead used the snapshot execution timestamps: trial from 20:59:26.081165 to 20:59:28.967673; active until 20:59:31.899599; then premium with NULL end. These are observed-state boundaries. They should not be presented as the original business change times.

Adding updated_at to a check configuration changes the timestamp source without changing the fact that check_cols detects changes. That can be useful, but an unreliable timestamp can then create misleading validity boundaries even when detection succeeds. Explain detection and dating as separate decisions.

Our first local execution exposed a timezone mismatch: UTC-naive source values were combined with local-time snapshot values, producing an end before its start for C. We discarded that run as validation evidence, configured UTC consistently, reran all three rounds, and checked every closed interval. This was a fixture configuration error, not a claim of a general dbt defect.

The lesson is practical: inspect timestamp types, session timezone, and precision before trusting a historical join. Matching row counts do not prove that time intervals are valid.

What checks distinguish correct history from plausible history?

The verified table counts were:

Counts alone are insufficient. We also asserted that paused never appeared, B's suspension was detected by check before timestamp, D had two timestamp versions but one check version, C had three new_record versions after restoration, and no closed interval ended before it began.

Official key behavior: unique_key identifies the input entity; dbt does not automatically prove that the configured key is unique. Test the input, then check version identity and open-row counts in the output. A snapshot intentionally contains multiple historical rows per customer, so a uniqueness test on customer ID over the entire snapshot would be inappropriate.

For this NULL-ended, string-flag fixture, current nondeleted records can be selected with:

select customer_id, status
from main_history.ts_new
where dbt_valid_to is null
  and dbt_is_deleted = 'False';

After run 3 this returns A premium, B suspended, C active, and D active. If you configure dbt_valid_to_current, adapt the predicate: a future-date sentinel is different from NULL, and existing rows are not automatically rewritten by that configuration change.

Can a rerun rebuild the missing history?

Not from the current mutable source alone. Once paused has been overwritten, rerunning the snapshot cannot recover it. Retained CDC events, audit logs, or historical extracts may provide additional evidence; their completeness and time semantics must be assessed separately.

Snapshots and incremental models solve different problems. An incremental model describes how a transformation processes updates efficiently; a snapshot specifically preserves detected versions. Compare the dbt incremental-model interview guide, then use the data-modeling guide to reason about historical joins.

Treat existing snapshot history as data that may be irreplaceable. Switching deletion modes is not an automatic migration of old tables, and dropping a snapshot is not a backfill strategy. Preserve the old history, define the desired semantics, and validate a planned migration before changing production.

Practice explaining the evidence boundary

These verified PracHub prompts extend the reasoning into ingestion, CDC, downstream contracts, and recovery. They are related practice rather than claims of exact dbt interview questions.

Continue with Data Engineer interview practice. For each proposed history table, state one fact it preserves and one event it could have missed.

Sources and Further Reading

• dbt: snapshots and metadata timestamps
• dbt: snapshot strategy
• dbt: updated_at
• dbt: check_cols
• dbt: hard_deletes and migration considerations
• dbt: unique_key
• dbt: dbt_valid_to_current
• DuckDB: dbt-duckdb adapter repository