[dbt snapshot 1.9] dbt snapshot hard_deletes = new_record not working when deleting record from source table

[amaliavog]

Describe the bug

I would like to create a dbt snapshot with the 1.9 version and be able to handle deleted records from the source table.
I am using the check strategy and hard_deletes = new_record. When a record is deleted from the source table, the dbt snapshot is failing with
[DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE] error

Steps To Reproduce

1. create demo_model via dbt
   WITH create_table AS (
   SELECT
   1 AS step
   ),
   insert_data AS (
   SELECT
   1 AS id, 'Alice' AS name, current_timestamp() AS created_at
   UNION ALL
   SELECT
   2 AS id, 'Bob' AS name, current_timestamp() AS created_at
   UNION ALL
   SELECT
   3 AS id, 'Charlie' AS name, current_timestamp() AS created_at
   )

SELECT
id,
name,
created_at
FROM insert_data

2. select * from demo_model

3. create snapshot with dbt 1.9 with check strategy and hard_deletes = new_record
   snapshots:

• name: demo_snapshot
  description: "This is a demo snapshot"
  relation: ref('demo_model')
  docs:
  show: true
  config:
  unique_key: id
  strategy: check
  check_cols: ['id', 'name']
  hard_deletes: 'new_record'

4. snapshot created

5. delete record from demo_model
   DELETE FROM demo_model
   WHERE id = 3;

6. error when running dbt snapshot
   [DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE] Cannot perform Merge as multiple source rows matched and attempted to modify the same target row in the Delta table in possibly conflicting ways. By SQL semantics of Merge, when multiple source rows match on the same target row, the result may be ambiguous as it is unclear which source row should be used to update or delete the matching target row. You can preprocess the source table to eliminate the possibility of multiple matches. Please refer to https://docs.microsoft.com/azure/databricks/delta/merge#merge-error compiled code at /tmp/target/run/demo/snapshots/snapshots.yml

Expected behavior

Based on the documentation there should be 2 records for id=3, one of them having a dbt_valid_to value and dbt_is_deleted = False and another one with dbt_valid_to = null and dbt_is deleted = True

https://docs.getdbt.com/reference/resource-configs/hard-deletes

Screenshots and log output

Failed with error(s): [{'model_name': 'demo_snapshot', 'error': 'Database Error in snapshot demo_snapshot (snapshots/snapshots.yml)\n [DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE] Cannot perform Merge as multiple source rows matched and attempted to modify the same\n target row in the Delta table in possibly conflicting ways. By SQL semantics of Merge,\n when multiple source rows match on the same target row, the result may be ambiguous\n as it is unclear which source row should be used to update or delete the matching\n target row. You can preprocess the source table to eliminate the possibility of\n multiple matches. Please refer to\n https://docs.microsoft.com/azure/databricks/delta/merge#merge-error\n compiled code at /tmp/target/run/demo/snapshots/snapshots.yml'}]

System information

The output of dbt --version:

Core:
  - installed: 1.9.1
 Plugins:
  - databricks: 1.9.1 
  - spark:      1.9.0 

The operating system you're using: Ubuntu 22.04

The output of python --version: Python 3.11.9

Additional context

Add any other context about the problem here.