Skip to main content

Duplicate Row Detected During Dml Action Row Values

Error​

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

The results screen indicates a duplicate row error detected during a DML action for the node DIM_OHOHP0000_CRASH. The error message provides the specific row values that caused the duplication issue. This helps users identify and resolve data integrity problems in their data processing workflows.

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.

The source table contains two rows with the IDs 1 and 2 for Ross and Monica Geller. The target table for the merge, table2, contains rows with the IDs 1 and 2 for Jack Geller and Rachel Green. The merge operation aims to update or insert rows from the source table into the target table based on matching IDs.

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.