incremental model + unique constraint still allows duplicates

Help
1

In my dbt I have something like this

{{ config
(
materialized=‘incremental’,
unique_key=[‘yearmonth’,‘domain_id’,‘pzn’]

)
}}

I understand that incremental will search in already existing data and do the update by unique key.
That is fine. But what is happening is that dbt is disregarding unique constraint when I have multiple rows with same yearmonth/doman_id/pzn (unique constraint). dbt loads those multiple rows without causing an error. Isn’t that weird?

2

Check if the duplication is in the source data you are trying to merge.
Incremental will use that unique_key (depending on merge strategy; you didn’t mention your target warehouse), but it’s goal is to prevent inserting source data that already exists in the target.

You may need to de-dupe the data you are trying to insert before you insert it.

Note: @Renee originally posted this reply in Slack. It might not have transferred perfectly.

1 Like
3

Thanks for the quick answer. Yes, there are duplicates in the source data, so I was expecting that putting unique key in dbt will also raise an error during the insert. So, actually that is not intention of unique key, it’s to update already existing records?

4

Correct. I’ve run into the same issue myself; I forget until the test on unique fails…

Note: @Renee originally posted this reply in Slack. It might not have transferred perfectly.

1 Like
5

Thanks for the answer! :heart: