dbt snapshot hard deletes not detected, snapshot config broken

TL;DR

A timestamp strategy snapshot can only see rows that still exist, so a row physically deleted from the source simply never appears again and its snapshot record stays current forever. To catch hard deletes, use the check strategy, or keep the timestamp strategy and set invalidateharddeletes to true, and make sure the snapshot runs often enough and the source query does not filter the missing rows out of sight. Then backfill the records that were already stranded as current.

dbt snapshot hard deletes not detected, snapshot config broken

Use this when

• Rows deleted in the source still show as current in the snapshot, with no end date set.
• The snapshot uses the timestamp strategy and the source hard deletes.
• A reconciliation against the source shows keys that exist only in the snapshot.

Not for this skill when

• The source marks deletes with an is_deleted style flag. That is an update the timestamp strategy already sees.
• The snapshot is meant to keep deleted rows current for legal hold reasons. That is a design choice, not a bug.

Steps

1. Confirm the delete style: delete a known test row or inspect source behaviour, and check whether it vanishes or returns flagged. Note which style the source uses. Verify: hard delete behaviour is proven, not assumed
2. Read the snapshot config: strategy, uniquekey, and whether invalidatehard_deletes is set. Note what the current config can and cannot detect. Verify: the config gap is named
3. Switch to the check strategy on meaningful columns, or set invalidateharddeletes to true on the timestamp strategy, understanding that dbt must then compare keys against the full source set. Note the chosen approach and its cost. Verify: the snapshot is configured to notice absence
4. Make sure the snapshot source query returns the full current key set with no where clause that hides rows, and that the snapshot runs on a schedule shorter than your reporting tolerance. Note the query and cadence. Verify: absence is actually observable at run time
5. Run the snapshot, then reconcile: keys current in the snapshot but absent from the source should now have an end date set. Handle the historically stranded rows with the first corrected run and note the count it closes. Verify: previously stranded records are closed and new deletes are caught

Variant phrasings

dbt snapshot invalidateharddeletes not working

The config flag phrasing.

dbt check strategy snapshot deleted rows still current

The check strategy version of the symptom.

Compatibility: dbt snapshots in Core 1.x and dbt Cloud. invalidateharddeletes applies to the timestamp strategy; the check strategy detects deletes by comparing row hashes and key sets.

Why it happens

Snapshots work by looking at what the source shows them. With the timestamp strategy, dbt selects rows changed since the last run; a deleted row produces no changed row, so there is nothing to trigger closing its snapshot record. Detecting an absence requires a different kind of pass, comparing the full set of keys, which is exactly what the check strategy and the invalidateharddeletes option add, at the cost of scanning the whole source. Teams skip that cost by default and discover the gap months later through reconciliation.

Edge cases / pitfalls

• A source query with a status filter can make live rows look deleted. When a row falls out of the filter, the snapshot will close it, correctly per its inputs.
• Full key comparison on a very large source is expensive. Partition or window the snapshot cadence deliberately rather than reverting to invisible deletes.
• The first corrected run closes every stranded record with the same end timestamp. Tell downstream users that date is a detection date, not the real delete date.
• If the source can restore deleted rows, a closed record followed by the same key reappearing creates a new record. Downstream models should expect that.

Provenance

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

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

Thread query: dbt snapshot hard deletes not detected, snapshot config broken

TL;DR: A timestamp strategy snapshot can only see rows that still exist, so a row physically deleted from the source simply never appears again and its snapshot record stays current forever. To catch hard deletes, use the check strategy, or keep the timestamp strategy and set invalidateharddeletes to true, and make sure the snapshot runs often enough and the source query does not filter the missing rows out of sight. Then backfill the records that were already stranded as current.