agent changed the snapshot strategy mid-stream: history split in two

TL;DR

Snapshots assume one strategy for the life of the table. Switching from check to timestamp (or changing the check columns or updated_at source) mid-stream makes old and new records follow different change rules, producing gaps and double-open records. Pick the strategy the business needs, rebuild the snapshot history deliberately if source history allows, or start a new snapshot table and keep the old one as the archive of record.

agent changed the snapshot strategy mid-stream: history split in two

Use this when

• a snapshot table shows split or overlapping validity ranges after its strategy was changed
• dbtvalidto and dbtvalidfrom patterns change character partway through the table
• the snapshot config diff shows a strategy, checkcols, or updatedat change

Not for this skill when

• the snapshot is new with no meaningful history, just rebuild it with the right strategy
• history looks odd because the source itself restated rows, that is correct snapshot behavior
• you changed only target schema or grants, strategy history is unaffected

Steps

1. Confirm the change: diff the snapshot config in version control and identify the date the strategy changed; sample the snapshot table before and after that date for overlapping or zero-length validity ranges. Verify: the strategy change date and the history anomalies are both documented.

2. Decide the end state with the data owner: keep one strategy going forward. If the source retains full history with reliable updated_at values, a timestamp rebuild can reconstruct history; if not, freeze the old snapshot table as archive and start a new snapshot under a new name. Verify: a rebuild versus archive-and-restart decision is recorded.

3. Execute the choice: for a rebuild, invalidate or drop the snapshot table and rerun dbt snapshot in dependency order; for archive-and-restart, rename configs so the new snapshot writes a new target table and update downstream refs. Verify: the snapshot going forward follows one strategy with consistent validity ranges.

4. Validate history: check for overlapping ranges per unique key, gaps where source rows existed, and counts of currently open records versus the source. Verify: validation queries return no overlaps and open records match the source.

Variant phrasings

only check_cols changed

Adding or removing a check column is a strategy change in effect: previously ignored changes start closing records (or vice versa). Treat it with the same rebuild decision.

updated_at source changed

Switching the timestamp column (for example from load time to business update time) re-dates history boundaries. Expect apparent splits even with the same nominal strategy.

Compatibility: dbt snapshots (SCD Type 2) with check and timestamp strategies on all supporting adapters. Snapshot tables carry dbtvalidfrom, dbtvalidto, and dbtupdatedat system columns.

Why it happens

A snapshot table is an accumulation of decisions made by one consistent change-detection rule. Each strategy defines change differently (column diffs versus a timestamp), so rows closed under one rule and opened under another cannot form a coherent timeline: the same source change may be invisible to one rule and obvious to the other, splitting history at the switch point.

Edge cases / pitfalls

• Never edit snapshot system columns by hand to patch overlaps without a rebuild decision; hand edits hide the rule break and confuse future runs.
• Downstream point-in-time queries built on the split history return wrong as-of results for the overlap window; warn consumers explicitly.
• Record the strategy and its start date in the snapshot description so the next agent sees the constraint before editing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ciq7xABJ0P9qiewM4Y2rTw