dbt Error: Duplicate Row Detected During DML Action

Issue

A transformation for dbt Core is failing with the following error:

 Database Error in model <model_name> 100090 (42P18): Duplicate row detected during DML action

Environment

Transformations for dbt Core Connectors: Snowflake

Answer

If duplicate records are expected in the table, the error can be fixed by instructing dbt to use a two-step incremental approach. You can do this by setting the incremental_strategy configuration for your model to delete+insert, rather than the default merge strategy. See dbt's Merge Behavior documentation for more details.

Cause

From looking at the full error, it is clear the model that failed is an incremental model. Functionally, this means that the model is using some rules specified in the model to determine which rows have not yet been transformed in the table, and for Snowflake (by default), then using a merge function to add only those new rows to the table in the destination.

As per dbt's Merge behavior documentation, Snowflake's merge statement fails with a "nondeterministic merge" error if the unique_key specified in the model configuration is not actually unique. That is to say, the source of the error is a constraint on the Snowflake merge functionality.