SQL, data modeling, data quality, EDA, visualization · insight-1052
Schedule a snapshot of a mutable source at least as often as it changes, and handle deleted rows explicitly
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
An operational table holds a booking's current status and is overwritten in place, and a weekly job snapshots it to give "status history".
Action
Run the snapshot at a cadence at least as fast as the changes you must see, choose the strategy (an updated_at column if reliable, otherwise compare chosen columns), and configure what happens when a row vanishes (close it, or write a deleted record). Test that the unique key is truly unique.
Reason
A snapshot samples state at run time; it cannot recover intermediate versions, and without a rule a deleted row is indistinguishable from a skipped one.
Boundary
Source systems without change history, dbt snapshots and slowly changing dimension builds.
Symptom or risk
Changes between runs are never seen, so a booking that went confirmed, changed, cancelled in one week shows one transition, and rows deleted at the source simply disappear from later snapshots.
Exceptions
Sources that already expose change data capture or an append-only event log.
Trade-offs
Higher cadence costs compute and storage and multiplies versions for noisy columns.
Pitfalls
Using a check-all-columns strategy on a table with a volatile last_seen column, which versions every run.
Lessons and concepts
Claims and their sources
dbt snapshots implement type-2 slowly changing dimensions, capture changes only when the snapshot command runs, require a unique key, and offer timestamp and check strategies with hard_deletes options ignore, invalidate and new_record.