[Bug] hard_deletes='new_record' not working when strategy='check'

[paritoshg2010]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

In dbt-core 1.9, with dbt snapshot new feature to capture the new records for the deleted items, I have noticed that if the deleted records are restored, those are not reinserted in snapshot table.

snapshot config:

strategy='check',

hard_deletes= 'new_record'

As you see in the attached screenshot, the deleted record entry is the last entry in this table.

Expected Behavior

Once the records are restored, the deleted entry (is_deleted='True') in snapshot table should be invalidated and the new record will be created.

Steps To Reproduce

1. Run dbt snapshot on any table, to replicate all the records from monitored table.
2. Now delete one record from monitored table & run the dbt snapshot on that table. There will be new entry created for the deleted record with the previous one getting invalidated and the new one with dbt_is_deleted='True'.
3. Now again try to restore that delete record in the monitored table, and run the dbt snapshot on it. The new entry for the restored item is not getting create in snapshot table.

Relevant log output

Environment

- OS: macOS Sonoma ver 14.6.1
- Python: 3.11.8
- dbt-core: 1.9.1
- dbt-adapters: dbt-snowflake 1.9

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[graciegoheen]

Hi - thanks for opening. The test coverage we have is for using hard_deletes='new_record' with strategy='timestamp'. I'm wondering if this is specifically a bug for the strategy='check'.

[paritoshg2010]

Hi - thanks for opening. The test coverage we have is for using hard_deletes='new_record' with strategy='timestamp'. I'm wondering if this is specifically a bug for the strategy='check'.

Hey Grace, Thanks for replying. I did specifically faced this issue with the 'check' strategy. Wondering if this is resolved in v1.10 ?

[graciegoheen]

Not yet, but I will have one of our engineers take a look and hopefully we can get this resolved before the final 1.10 release!

[RB-Analytics]

Sorry to butt in but there's also a bug with the strategy = 'check' and the legacy invalidate_hard_deletes=True. Since dbt 1.9, the snapshot will no longer populate the field dbt_valid_to once it detects a deleted unique_key. It is now leaving them Null which means you get multiple valid values at a given point in time sadly. Has this been raised?

[jomach]

Same issue here with dbt core 1.9.4. I'm building the id with a surrogate key based on all fields. After restoring the value the value on the column dbt_is_deleted is still true but it should be false as the record is valid again.

snapshots:
  - name: t_strc_parent
    relation: ref('parent_child')
    config:
      unique_key: id
      strategy: check
      check_cols:
        - id
      hard_deletes: new_record