topic Re: Foreign key constraint in a dlt pipeline in Data Engineering

Foreign key constraint in a dlt pipeline

Mario_D — Fri, 13 Sep 2024 11:08:03 GMT

As primary/foreign key constraints are now supported/available in Databricks, how are foreign key constraints handled in a dlt pipeline, i.e if a foreign key constraint is violated, is the record logged as a data quality issue and still added to the target table?

Re: Foreign key constraint in a dlt pipeline

szymon_dybczak — Fri, 13 Sep 2024 11:47:32 GMT

Hi @Mario_D ,

I don't think primary/foreign constraint are supported by DLT. At least I can't find anything in documentation. But you obtain same result using DLT expectations:

Manage data quality with Delta Live Tables - Azure Databricks | Microsoft Learn

Re: Foreign key constraint in a dlt pipeline

RCo — Mon, 16 Sep 2024 16:52:32 GMT

Hi @Mario_D!

While primary & foreign key constraints are generally available in Databricks Runtime 15.2 and above, they are strictly informational only.

This means that a primary key will not prevent duplicates from being added to a table and a foreign key does not require the referenced value to exist in another table.

https://docs.databricks.com/en/tables/constraints.html#declare-primary-key-and-foreign-key-relationships