Issue

A DBT transformation is failing with the following error:

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

Environment

Transformations: DBT, 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. More about that can be found in this documentation.

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 described in this 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.