Hey all!
very simple question:
when you trigger a full refresh on an incremental model, is the unique key constraint of uniqueness and non-null still enforced? Only thing I found in the doc is full refresh treats your model like a regular table, so I wasn’t sure of the behavior.
Thanks!
dbt does not verify the uniqueness of the fields listed in the unique_key
config. If you want to verify that it’s actually unique, use a <Add data tests to your DAG | dbt Developer Hub test> or a <Model contracts | dbt Developer Hub model constraint> (but unique constraints are only enforced on Postgres currently)
The unique_key
config for an incremental model just tells dbt what fields to join on for a merge operation. https://docs.getdbt.com/docs/build/incremental-models#defining-a-unique-key-optional
Note: @Owen
originally posted this reply in Slack. It might not have transferred perfectly.
Okay thanks! So I can add a data_test that would verify for uniqueness, but I wouldn’t need one for not_null since that one is handled by the incremental model, correct?
I don’t know that the unique_key
config implies any runtime constraints (unique, non-null). As far as I know, you need to validate such things yourself. Although most database platforms support enforcing non-null constraints via dbt model contracts.
Note: @Owen
originally posted this reply in Slack. It might not have transferred perfectly.
For the incremental, it fails if the unique keys contains nulls. That part is written in the docs