A DBT transformation is failing with the following error:
Database Error in model <model_name> 100090 (42P18): Duplicate row detected during DML action
Transformations: DBT, Snowflake
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.
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