Why does a dbt snapshot miss changes that occur between two snapshot runs?

## Snapshots sample; they do not observe Every `dbt snapshot` run does the same thing: read the source as it exists right now, compare it to the open rows in the history table, close and insert where something differs. Between two runs, dbt is not watching. Whatever the source did in that interval leaves no trace beyond its final value. Suppose an order's `status` moves at 09:00 to `packed`, at 14:00 to `shipped`, and at 16:00 to `delivered`, and the snapshot runs nightly at 02:00. The next run sees `delivered` and writes exactly one new version. The history now claims the order went from `pending` straight to `delivered`, with `packed` and `shipped` never having existed. No later run repairs this: the source no longer holds those values, and the snapshot table only ever appends what it was shown. ## Both strategies have the same blind spot It is tempting to think the `timestamp` strategy avoids this because it reads a real modification time. It does not. dbt reads the row's *current* `updated_at` — a single scalar describing the most recent write. Three intermediate updates leave one timestamp behind. The `timestamp` strategy dates the recorded version accurately; it does not reveal the versions it never saw. The `check` strategy is identical in this respect and additionally dates the version with the run time. ## Snapshot cadence is a resolution decision Put plainly: **your schedule is your history's resolution.** A daily snapshot answers "what did this row look like on a given day" and nothing finer. An hourly snapshot answers hourly questions and multiplies row counts accordingly. This should be a deliberate choice driven by what the business asks: - Reporting end-of-day dimension state for a customer mart? Daily is fine and cheap. - Auditing how long orders sat in each fulfilment stage? Sampling is the wrong tool entirely — you need the transitions themselves. - Compliance questions about who held which entitlement at which moment? Sampling will not satisfy them, and saying so early is better than discovering it during an audit. Running snapshots more often is a legitimate lever, and a snapshot is usually cheap enough to schedule separately from your main `dbt build` — many teams run `dbt snapshot` on a tighter cadence than the rest of the project precisely for this reason. But there is a floor: no schedule catches changes that happen and reverse between runs, and a row that flips A → B → A is invisible at any interval that misses the excursion. ## When sampling is the wrong tool If missed transitions actually matter, the fix is upstream, not in dbt configuration. Either the source system emits an event or audit log recording each transition, or the ingestion layer captures changes from the database's transaction log; in both cases dbt's job shifts from snapshotting mutable state to modelling an already-complete history. A snapshot on top of a change feed is redundant. Be explicit about which world you are in, because "we snapshot nightly" quietly becomes "we have full history" in a stakeholder's head unless someone says otherwise. ## Practical guardrails - **Document the cadence next to the snapshot** in `schema.yml`, so consumers of `dbt_valid_from` know the windows are quantized to the schedule. - **Do not let the schedule drift silently.** A snapshot that stops running does not fail loudly — models still `ref()` it and still return rows, they are just increasingly stale, with every entity frozen in whatever state it held on the last successful run. A recency check on `max(dbt_updated_at)` catches this. - **Beware the catch-up illusion.** After an outage, the first successful run does not backfill the missed days; it writes one version dated now. If someone asks you to "backfill the snapshot", the honest answer is that source state for those days no longer exists. - **Watch for reverting values.** Fields that oscillate — a score, a risk band, an availability flag — are the ones sampling distorts most, and they are the best candidates for pushing upstream to an event-based source.