[Bug] dbt snapshot check strategy does not correctly handle reverting to previous fingerprint

[jamesjjq-altway]

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

We have a snapshot with configs like

    {{
        config(
            target_schema=snapshot_dataset(),
            unique_key='id',
            strategy='check',
            check_cols=["atg_fingerprint"],
            hard_deletes='invalidate'
        )
    }}

where fingerprint is basically a hash of all the columns.

We are seeing issues when a fingerprint changes from a to b then back to a, both records in the snapshot table with fingerprint a have dbt_valid_to set to null.
This seems like a bug for a totally legitimate use case.

The data i am seeing is something like this:

atg_fingerprint                     id        dbt_scd_id                           dbt_updated_at                    dbt_valid_from                      dbt_valid_to                
22bf5053f030f0c6ee9ba93019205455    7887019   8e597072823df9b980bf5e43753588a6     2025-05-19 06:27:56.646731 UTC    2025-05-19 06:27:56.646731 UTC.     null
367915a9b8bca75880c3cdecd0ecf4d4    7887019   ffffe2862642772b56760772c9d42cad     2025-05-20 06:29:08.708283 UTC    2025-05-20 06:29:08.708283 UTC      2025-05-21 06:29:58.550541 UTC 
22bf5053f030f0c6ee9ba93019205455    7887019   189052a00bafda6835b625d552959a6e     2025-05-21 06:29:58.550541 UTC    2025-05-21 06:29:58.550541 UTC      null

Expected Behavior

The previous record's dbt_valid_to should not have been updated to null

atg_fingerprint                     id        dbt_scd_id                           dbt_updated_at                    dbt_valid_from                      dbt_valid_to                
22bf5053f030f0c6ee9ba93019205455    7887019   8e597072823df9b980bf5e43753588a6     2025-05-19 06:27:56.646731 UTC    2025-05-19 06:27:56.646731 UTC.     2025-05-20 06:29:08.708283 UTC 
367915a9b8bca75880c3cdecd0ecf4d4    7887019   ffffe2862642772b56760772c9d42cad     2025-05-20 06:29:08.708283 UTC    2025-05-20 06:29:08.708283 UTC      2025-05-21 06:29:58.550541 UTC 
22bf5053f030f0c6ee9ba93019205455    7887019   189052a00bafda6835b625d552959a6e     2025-05-21 06:29:58.550541 UTC    2025-05-21 06:29:58.550541 UTC      null

Steps To Reproduce

this happened in our nightly run.

Relevant log output

Environment

- OS: composer-2.10.2-airflow-2.10.2
- Python: 3.11.8
- dbt: 1.9.2

Which database adapter are you using with dbt?

bigquery

Additional Context

No response