[Bug] Using snapshots with check strategy sometimes creates duplicate rows

[amuweee]

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 use dbt's snapshot functionality with the check strategy to create snapshots from some source tables to track changes. The reason for the check strategy is that we only need to track one column out of many, and won't want a new row each time other non-tracked columns are updated.

When an existing row with the check column is updated from null to any non-null value, two rows identical rows are inserted instead of just one. These two rows have the same values in snapshot metadata fields as well (dbt_scd_id, dbt_updated_at, etc.)

Expected Behavior

Snapshot should only insert one row on source change.

Steps To Reproduce

with a snapshot model config like:

{% snapshot cards %}

    {{
        config(
            unique_key='id',
            strategy='check',
            check_cols=['maximum_spend_amount']
        )
    }}

    select
        id
        , maximum_spend_amount
        , created_at
        , updated_at
    from {{ source('source_db', 'cards') }}

{% endsnapshot %}

run dbt snapshot when with row that has null for maximum_spend_amount column.
update same row's maximum_spend_amount with a non-null value, run dbt snapshot
the same row is created twice in the snapshot.

Relevant log output

Environment

- OS: Debian GNU/Linux 12 (bookworm)
- Python: 3.12
- dbt: 1.9.1

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[ataft]

We just ran into this issue as well. We've been running our snapshots successfully for a long time in 1.7.11 and this started happening a couple days after upgrading to 1.9.2.