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

### Is this a new bug in dbt-core?

- [x] I believe this is a new bug in dbt-core
- [x] 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

```shell

```

### Environment

```markdown
- 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_

Start by investigating dbt's snapshot check strategy for the BigQuery adapter and compare the reported a→b→a sequence with the expected validity intervals. Reproduce the nightly-run behavior if possible, then inspect the snapshot merge or update path involved. Done means the earlier matching fingerprint retains its closing dbt_valid_to value while the latest row remains open, with regression coverage for this sequence.

Written by the indexing model from the issue text.