Debugging "duplicate row detected" errors in runs

[jeremyyeo]

Might be good to have a post discussing all the ways duplicates can be introduced. The examples below show that this is almost always due to a duplicate occurring in the source table.

Error message

Duplicate row detected during DML action

A subsequent run of an incremental model with duplicates in the source data.

Let's assume we have an incremental model like the following:

-- models/my_incremental.sql
{{ config(materialized = 'incremental', unique_key = 'user_id') }}

select 'alice' as user_id, 1 as status

A dbt run --full-refresh results in the following log:

00:43:59  1 of 1 START incremental model dbt_jyeo.my_incremental.......................... [RUN]
00:44:03  1 of 1 OK created incremental model dbt_jyeo.my_incremental..................... [SUCCESS 1 in 3.91s]

Now let's change our simulated¹ data source and make it so that duplicates were introduced:

-- models/my_incremental.sql
{{ config(materialized = 'incremental', unique_key = 'user_id') }}

select 'alice' as user_id, 2 as status
union all
select 'alice' as user_id, 3 as status

A dbt run (incremental) results in the following log:

00:46:52  1 of 1 START incremental model dbt_jyeo.my_incremental.......................... [RUN]
00:46:57  1 of 1 ERROR creating incremental model dbt_jyeo.my_incremental................. [ERROR in 5.52s]
00:46:57  
00:46:57  Finished running 1 incremental model in 11.29s.
00:46:57  
00:46:57  Completed with 1 error and 0 warnings:
00:46:57  
00:46:57  Database Error in model my_incremental (models/my_incremental.sql)
00:46:57    100090 (42P18): Duplicate row detected during DML action
00:46:57    Row Values: ["alice", 1]
00:46:57    compiled SQL at target/run/my_dbt_project/models/my_incremental.sql

If we look close at the debug logs:

00:46:52.140180 [debug] [Thread-1  ]: On model.my_dbt_project.my_incremental: /* {"app": "dbt", "dbt_version": "1.0.4", "profile_name": "snowflake", "target_name": "dev", "node_id": "model.my_dbt_project.my_incremental"} */
      create or replace temporary table development.dbt_jyeo.my_incremental__dbt_tmp  as
      (
select 'alice' as user_id, 2 as status
union all
select 'alice' as user_id, 3 as status
      );

00:46:56.267873 [debug] [Thread-1  ]: On model.my_dbt_project.my_incremental: merge into development.dbt_jyeo.my_incremental as DBT_INTERNAL_DEST
        using development.dbt_jyeo.my_incremental__dbt_tmp as DBT_INTERNAL_SOURCE
        on 
            DBT_INTERNAL_SOURCE.user_id = DBT_INTERNAL_DEST.user_id
    when matched then update set
        "USER_ID" = DBT_INTERNAL_SOURCE."USER_ID","STATUS" = DBT_INTERNAL_SOURCE."STATUS"
    when not matched then insert
        ("USER_ID", "STATUS")
    values
        ("USER_ID", "STATUS")
;

There were > 1 row in our source table with the same user_id (which we've set to be our unique_key) so we don't actually know which source (2 as status or 3 as status) should we have used to update the row that currently exists in our destination table.

Note that if you were to dbt run --full-refresh with the incremental that had dupes shown above, no errors would be thrown because we would essentially be recreating the table from scratch (and not using the merge statement which is used on subsequent runs dbt run (sans --full-refresh). But the very next dbt run will reintroduce our Duplicate row detected error.

Footnotes

1. We're simulating all the source data here but the duplicates could easily come from some other actual model that is being selected from in your my_incremental model. ↩

[jeremyyeo]

Error message

UPDATE/MERGE must match at most one source row for each target row

Snapshot source unique_key is not actually unique

Add a source table that we will use in our snapshot:

-- models/my_source.sql
{{ config(materialized = 'table') }}
select 1 as user_id, 'active' as user_state
union all
select 2 as user_id, 'active' as user_state

And our snapshot itself:

-- snapshots/snappy.sql
{% snapshot snappy %}
{{
    config(
      target_database = 'cse-sandbox-319708',
      target_schema = 'snapshots',
      unique_key = 'user_id',
      strategy = 'check',
      check_cols = ['user_state']
    )
}}
select * from {{ ref('my_source') }}
{% endsnapshot %}

Do a dbt run && dbt snapshot. You should find that there are no issues here.

Now, lets change our source to introduce a duplicate:

-- models/my_source.sql
{{ config(materialized = 'table') }}
select 1 as user_id, 'inactive' as user_state
union all
select 1 as user_id, 'unknown' as user_state

Do a dbt run && dbt snapshot. You will then find the following error in your logs:

02:51:02  Running with dbt=1.0.4
02:51:03  Found 1 model, 0 tests, 1 snapshot, 0 analyses, 188 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
02:51:03  
02:51:13  Concurrency: 1 threads (target='dev')
02:51:13  
02:51:13  1 of 1 START snapshot snapshots.snappy.......................................... [RUN]
02:51:34  1 of 1 ERROR snapshotting snapshots.snappy...................................... [ERROR in 21.07s]
02:51:34  
02:51:34  Finished running 1 snapshot in 30.79s.
02:51:34  
02:51:34  Completed with 1 error and 0 warnings:
02:51:34  
02:51:34  Database Error in snapshot snappy (snapshots/snappy.sql)
02:51:34    UPDATE/MERGE must match at most one source row for each target row
02:51:34    compiled SQL at target/run/bigquery/snapshots/snappy.sql
02:51:34  
02:51:34  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1