dbt / Intermediate: history and controlled workflows / Lesson 9

Snapshots and observed history

Track changing branch attributes while distinguishing source-change time, snapshot observation and complete business history.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain timestamp and check snapshot strategies.
Define stable keys, deletion policy and interval-query meaning.

A periodic snapshot cannot recover every unseen change

A branch’s service_level can change while the source table retains only its current value. A dbt snapshot can preserve observed versions using a supported slowly changing dimension pattern. It is not a substitute for a complete source event log: two changes between snapshot runs can be invisible if only the final state remains. Define what historical questions the observation cadence can answer. Keep the stable branch key separate from its mutable name. Reusing an identifier for a different real branch would corrupt historical meaning even if snapshot SQL and metadata remain technically valid.

Use the selected generation’s snapshot configuration

The YAML snapshot form is available in the named v1.12 baseline and was introduced for v1.9-era configuration. It references a previously defined staging relation and uses updated_at as the source change indicator. The timestamp strategy needs that field to advance reliably when tracked data changes; a payload change with an unchanged timestamp can be missed. The alternative check strategy compares configured columns and has different timestamp considerations. Neither strategy validates source-key uniqueness automatically. The example performs no snapshot command, relation creation or history mutation.

yaml Illustrative technical example; logical results are reasoned, not engine-tested.

snapshots:
  - name: snap_branch_service
    relation: ref('stg_branches')
    config:
      unique_key: branch_id
      strategy: timestamp
      updated_at: updated_at

Illustrative YAML snapshot definition. The staging relation, reliable timestamp and adapter support must be established independently.

Validity metadata follows the strategy’s documented clock

For the timestamp strategy, source updated_at values populate the relevant validity and change metadata according to documented behavior. A snapshot run at10:30 observing source updated_at10:00 does not make10:30 the source change time. The local timeline shows a later update at11:00 observed at11:30. Under the stated model, the first version ends at11:00 and the next begins there. These are synthetic intervals, not captured dbt rows. The source’s updated_at can still differ from when a business policy legally took effect, so do not label technical validity as universal business effective time.

text Illustrative technical example; logical results are reasoned, not engine-tested.

source version A:updated_at10:00, observed10:30
source version B:updated_at11:00, observed11:30
local timestamp-strategy model:A valid[10:00,11:00), B current from11:00

A local interval interpretation under the stated timestamp assumptions. No engine snapshot or clock measurement is performed.

Missing source rows need an explicit deletion policy

Current snapshot versions commonly use NULL dbt_valid_to unless a configured current-value sentinel changes that convention. Hard-delete handling is a separate opt-in configuration with version and adapter support boundaries; ignore, invalidate and new-record approaches mean different histories. A temporary incomplete source extract must not be mistaken for a real branch deletion. Define source completeness before interpreting absence. Interval joins should respect the chosen end-boundary convention so one loan does not match two branch versions at a shared timestamp. Test current, historical, missing and deleted cases with synthetic data.

Protect snapshot history as a data asset

Snapshot state is accumulated history, so a code change alone cannot recreate versions the source no longer contains. The selected documentation distinguishes snapshot behavior from incremental full refresh: do not assume a model refresh flag is a supported history reset. Plan backup, migration, metadata changes and historical reconciliation under the target’s capabilities. Verify downstream consumers if meta-column names or current-value conventions change. This course runs no snapshot, backup or warehouse operation. Local validation checks only the interval model and configuration intent, not dbt’s SCD implementation, transaction behavior or completeness of a real source.

Common errors and fixes

A changed payload is missed by timestamp strategy

Why it happens: The configured updated_at did not advance with the change.
How to fix it: Repair the source change indicator or evaluate a justified check strategy.
Prevent it next time: Test unchanged timestamps with changed attributes.

An incomplete extract is recorded as mass deletion

Why it happens: Source absence was interpreted before completeness was established.
How to fix it: Validate extraction scope and the approved delete policy.
Prevent it next time: Guard snapshot inputs with source delivery checks.

Snapshot validity is labelled exact business effective time

Why it happens: Technical change metadata was confused with domain policy history.
How to fix it: Document the source clock and add domain effective dates when required.
Prevent it next time: Label observed, changed and effective times separately.

Check your understanding

1. A change timestamp is10:00 and the snapshot observes it at10:30. Which time does the timestamp model use for the source version start?

Show worked solution

Under the stated timestamp strategy, source updated_at10:00 supplies the relevant start metadata. Observation happened later. This does not prove the real-world policy became effective at10:00; that requires a separate domain clock and source guarantee.

2. Can a daily snapshot recover two source changes that occurred and reverted between runs?