skip to content

What is lost when a dbt snapshot table is dropped and rebuilt from source?

level: seniorimportance: should knowfreq 55%

answer

  1. dbt's rebuild-from-source promise has one exception
  2. the inputs that made it no longer exist
  3. after the rebuild, every row is open
  4. nothing errors, which is the problem
  5. treat that schema like production data

basics

~20 s

Every past version. A rebuilt dbt snapshot contains one open row per current source row, so all closed validity windows vanish permanently — unlike models, snapshots accumulate state that cannot be re-derived from a mutable source.

solid answer

~40 s

dbt's core promise is that any model can be rebuilt from source. Snapshots are the exception: their content is accumulated over many runs from a source that overwrites itself, so it is not derivable. Drop the table and the next `dbt snapshot` writes one open row per current source row — every closed version, every historical `dbt_valid_from`/`dbt_valid_to` window, is gone, and downstream point-in-time joins silently re-point to today's values with no error anywhere. Treat the snapshot schema as production data rather than a build artifact: back it up or rely on the warehouse's time travel and zero-copy clones, keep dev and CI runs from writing into the production snapshot schema, and keep the snapshot's select a plain `select *` from the source so logic changes never tempt anyone to rebuild it.

code

text · 5 lines
text
before                                     after rebuild
id tier    valid_from  valid_to            id tier  valid_from  valid_to
42 bronze  2023-01-04  2023-06-11          42 gold  2024-02-02  NULL
42 silver  2023-06-11  2024-02-02
42 gold    2024-02-02  NULL

go deeper

for a junior

Know that a snapshot table holds history the source no longer contains, so dropping it loses data permanently — unlike a model, which dbt can always rebuild.

for a middle

Explain what a rebuild produces: one open row per current source row, all closed windows gone, and no error raised anywhere in the build.

for a senior

Show the operational response — restricting write access to the snapshot schema, backups or clones, tests on row counts and open-row counts, and keeping the snapshot select trivial so it never needs rebuilding.

for a principal

Own the architectural question: whether business-critical history should depend on one stateful table surviving forever, or be sourced from an upstream change feed that keeps the warehouse layer rebuildable.

## Snapshots break dbt's rebuildability assumption Almost everything in a dbt project is a pure function of sources plus code. Delete every model, run `dbt build`, and you get the same warehouse back. That property is why dbt teams are relaxed about dropping and recreating things. Snapshots do not have it. A snapshot's content is the accumulation of what the source looked like at every past run, and the source overwrote those values long ago. The function that produced the snapshot table cannot be re-evaluated, because its inputs no longer exist. The snapshot **is** the only copy. ## What a rebuild actually produces Suppose `customers_snapshot` has tracked 400,000 customers for two years and holds 1.3 million version rows. Drop it and run the snapshot again: you get 400,000 rows, all open, all dated with the current `updated_at` (or the run time under the `check` strategy). Every closed window is gone. Nothing errors. `dbt build` is green. ## Why the damage is quiet That last point is what makes this a senior question. Downstream models keep compiling and keep returning rows. A mart that joins facts to the dimension version valid at the event date now finds only one version — today's — and so every historical fact is attributed to the customer's *current* tier, region or segment. Reports that were correct last week silently restate. Nobody gets an alert, because from the warehouse's point of view nothing failed. Secondary effects follow. Any downstream key derived from a version row changes. Tests that assert on historical counts start passing against a smaller table. And if the rebuild was accidental and unnoticed for a fortnight, the snapshot has meanwhile accumulated two weeks of *new* history on top of the truncated base, so restoring an older backup means reconciling the overlap by hand. ## Where accidental rebuilds come from - **Shared write access to the snapshot schema.** Historically, a snapshot's `target_schema` pinned it to one fixed schema regardless of who ran dbt, so a developer running `dbt snapshot` locally, or a CI job, wrote into the same production table as the scheduler. dbt 1.9 makes `target_schema` optional so snapshots can build into the environment's own schema like models do — but many projects still carry the old, pinned configuration. - **Treating the snapshot like a model during refactors.** "I changed the select, let me rebuild it" is fatal here in a way it never is for a model. - **Environment teardown scripts** that drop schemas by pattern and catch the snapshot schema. - **Warehouse restores or migrations** that do not recognise the snapshot schema as stateful data. ## How to protect it 1. **Treat the snapshot schema as production data.** It belongs in whatever backup, retention and disaster-recovery regime your source-of-truth data has. On warehouses with time travel and zero-copy cloning, take a periodic clone — it is cheap, and it is the difference between a bad afternoon and unrecoverable loss. 2. **Lock down write access.** Only the production runner should write the production snapshot schema. Developers and CI should snapshot into their own schemas, seeded from a clone when they need realistic data. 3. **Keep the snapshot select trivial.** `select * from {{ source(...) }}`, no joins, no filters, no renames. Then the snapshot code essentially never changes and nobody has a reason to rebuild it. Do the transformation in downstream models, where rebuilding is free. 4. **Add columns rather than reshaping.** A new column added to the select is only populated for versions captured from that point on; historical rows can only carry NULL, because the past value was never recorded. That is a limitation to plan around, not a reason to rebuild. 5. **Monitor the shape.** A test or alert on row count, and on `count(*) where dbt_valid_to is null`, catches a truncation the same day: after a rebuild every row is open, which is a distinctive signature. 6. **Document the irreversibility** in the snapshot's YAML description, so the next person who considers rebuilding it reads a warning first. ## The design lesson If accumulating history is business-critical, ask whether it should depend on one nightly job's table surviving forever. Sourcing history from an upstream event log or change feed makes the warehouse layer rebuildable again, which is a stronger position than any amount of care around a stateful snapshot table.

  • How would you detect that a dbt snapshot table was truncated and rebuilt, after the fact?
    Look for the signature: every row open, with dbt_valid_to NULL across the whole table, and a row count that collapsed to roughly the source's row count. Comparing min(dbt_valid_from) against when the snapshot was first deployed also gives it away. A scheduled test on row count and on the count of open rows catches it the same day rather than weeks later.
  • Why does keeping a dbt snapshot's select as a plain select * from the source reduce this risk?
    Because the snapshot code then almost never needs to change, and nobody is tempted to rebuild it to apply new logic. All filtering, joining and renaming happens in downstream models, which are pure functions of sources and can be dropped and rebuilt freely. The stateful artifact stays boring and untouched.
  • You add a column to a dbt snapshot's select. What do the historical rows show for it?
    NULL. dbt can start capturing that column from the next run onward, but the value it held during earlier versions was never recorded and cannot be reconstructed from a source that has since overwritten it. Plan for a discontinuity, communicate the date it starts from, and resist rebuilding in the hope of backfilling it.

saying these in an interview costs you the question

  • Says the snapshot can be rebuilt from source like a model
  • Assumes a rebuild fails loudly instead of silently restating
  • Lets dev and CI runs write the production snapshot schema
  • Rebuilds a snapshot to apply changed select logic
  • Thinks adding a column backfills historical versions

context

resolving links…