Duplicate Row Detected During Dml Action Row Values

Error

“Duplicate row detected during DML action Row Values:[]”

The error received is actually a Snowflake error. This error results from when there is more than one row that matches the set merge key attempting to update/replace the same record in the target table.
The merge does not know with what record to replace the destination record with since it is only able to determine the uniqueness of the record on the primary key.
This can usually be resolved be re-evaluating the set of table columns that make up the business/merge keys to include another unique identifier.

This error is returned from Snowflake. It happens when there is more than one row that matches the set merge key attempting to update or replace the same record in the target table. The merge does not know with what record to replace the destination record with since it is only able to determine the uniqueness of the record on the primary key.

This can usually be resolved be re-evaluating the set of table columns that make up the business or merge keys to include another unique identifier.

Table one contains Ross and Monica using the LAST_NAME as the business or merge key. You will get an error if you try to merge into Table two since there are two records that share the same business key attempting to merge into Table two that contains one record with that same business key.

Solution

You can do one of two solutions.

1. You can fix this by using the ID column as our business key or selecting a composite business key as first_name and last_name in the merge.
2. Set a session variable in the pre-sql.
   1. alter session set ERROR_ON_NONDETERMINISTIC_MERGE = true;
   2. This solution may result in data loss since it will pick records at random to include or exclude in the merges.