In a dbt snapshot, when do you choose the check strategy over timestamp?

## Two ways to answer "did this row change?" Every snapshot run has to decide, for each `unique_key`, whether the source row still matches the open version in the history table. dbt offers two mechanisms, chosen with the `strategy` config. ## The timestamp strategy ```sql {{ config( unique_key='id', strategy='timestamp', updated_at='updated_at' ) }} ``` dbt compares the source row's `updated_at` against the `dbt_updated_at` of the open snapshot row. If it is later, the open row is closed at that timestamp and a new version is inserted with `dbt_valid_from` set to the source `updated_at`. This is the preferred strategy for three reasons: 1. **The history carries business time.** The window boundaries say when the value actually changed in the source system, not when your scheduler happened to fire. 2. **It is cheap.** One column comparison, no wide value diffing. 3. **It is stable under column additions.** Adding a column to the select does not by itself create versions. The catch is total dependence on the source. If the application updates `status` with a trigger that does not maintain `updated_at`, or a bulk backfill script writes rows directly, the change is silently invisible — the snapshot will never record it, and no later run can recover it. A NULL `updated_at` is also a problem: dbt has nothing to place that row on the timeline with. Before choosing `timestamp`, verify with the source owners that the column is maintained on **every** write path, and consider a test that fails if `updated_at` is NULL or in the future. ## The check strategy ```sql {{ config( unique_key='id', strategy='check', check_cols=['status', 'is_cancelled', 'assigned_to'] ) }} ``` dbt compares the listed columns value by value against the open snapshot row. Any difference closes the old version and inserts a new one. Two consequences follow directly: - **Timestamps come from the run, not the data.** `dbt_valid_from` on the new version is the snapshot execution time. If the source changed at 09:12 and the snapshot ran at 02:00 the next morning, the history says the change happened at 02:00. For most reporting that is acceptable; for anything that must reconcile against source-system timestamps, it is not. - **Only listed columns are watched.** A change to a column outside `check_cols` produces no new version, and the current row simply keeps showing the *old* value of that column until something in `check_cols` moves. This surprises people: an untracked column is not merely un-versioned, its updates are invisible until an unrelated tracked change flushes them through. `check_cols='all'` watches every column in the select. It sounds safe and is usually a mistake on wide tables: a volatile technical column — a re-scored field, a last-seen timestamp, a row hash from the loader — will spawn a new version on nearly every run, and the history table explodes while telling you nothing. Comparing many columns also costs more per run. Pick the columns whose history the business will actually query. ## Choosing between them Start from the source contract, not from convenience: - A reliable, always-maintained modification timestamp → `timestamp`. - A timestamp that exists but is only maintained on some write paths → `check` on the columns you care about, because a partially-maintained timestamp is worse than none: it looks trustworthy and silently drops changes. - No timestamp at all → `check`, with an explicit, short column list. A useful sanity check when someone claims the timestamp is reliable: compare a `check`-style column comparison for one day's data against what the `timestamp` strategy would have caught, and see whether the counts agree. ## Changing strategy later Switching strategy on a live snapshot does not restate what is already stored — old versions keep the boundaries they were written with, and you get a discontinuity at the switch point. Document it, or accept that the mixed semantics live in the table forever. Adding a column to `check_cols` behaves similarly: the new column starts being watched from the next run, and its earlier changes were never captured and cannot be recovered.