Create a Delta Table with PK and FK constraints for a streaming source data

[Dharinip]

1. How to create a Delta Table with PK and FK constraints for a streaming source data?

2. When the streaming data in the silver layer gets updated, will the delta table also be updated?

My use case is:

We have a streaming data in the silver layer as SCD type 2. And we want to create a Delta Table with Primary key and foreign key constraints in the Gold Layer having SCD Type 1.

[Walter_C · Accepted solution]

1. You can use primary key and foreign key relationships on fields in Unity Catalog tables. Primary and foreign keys are informational only and are not enforced. Foreign keys must reference a primary key in another table.

You can declare primary keys and foreign keys as part of the table specification clause during table creation. This clause is not allowed during CTAS statements. You can also add constraints to existing tables.

CREATE TABLE T(pk1 INTEGER NOT NULL, pk2 INTEGER NOT NULL,
                CONSTRAINT t_pk PRIMARY KEY(pk1, pk2));
CREATE TABLE S(pk INTEGER NOT NULL PRIMARY KEY,
                fk1 INTEGER, fk2 INTEGER,
                CONSTRAINT s_t_fk FOREIGN KEY(fk1, fk2) REFERENCES T);

• Primary key and foreign key constraints are available in Databricks Runtime 11.3 LTS and above, and are fully GA in Databricks Runtime 15.2 and above.

• Primary key and foreign key constraints require Unity Catalog and Delta Lake.

2. When the streaming data in the silver layer gets updated, the Delta table will also be updated. Delta Lake supports streaming reads and writes, which means that new records are processed incrementally as new table versions commit to the source table. This ensures that the Delta table reflects the latest state of the streaming data.