Databricks Data Engineer Professional Question 172
Select 3You are designing a data engineering solution in Databricks to manage customer orders and their associated items. Since Databricks tables do not enforce foreign key constraints, how can you ensure data integrity between the 'orders' table and the 'order_items' table?
- A
Implement delta constraints to enforce that the 'order_id' in 'order_items' matches an existing 'order_id' in 'orders'.
- B
Use a join query to periodically validate that all 'order_id' values in 'order_items' exist in 'orders'.
- C
Design the ingestion pipelines to validate 'order_id' relationships before writing data to the tables.
- D
Leverage the ZORDER command on both tables to ensure consistency of 'order_id' values.
- E
Use Delta Live Tables to implement data quality checks that validate 'order_id' relationships automatically.
Show answer and explanation
Correct answers: B, C, E
Explanation
Since Databricks Delta tables do not enforce foreign key constraints, you must implement alternative mechanisms to ensure data integrity. Periodic validation using join queries, upfront validation in ingestion pipelines, and implementing automated data quality checks with Delta Live Tables are all effective methods to avoid issues caused by the lack of foreign key constraints. ZORDER only optimizes performance and does not address data integrity, and Delta constraints cannot enforce foreign key relationships.
- A. Incorrect.
Delta constraints are currently limited to NOT NULL and CHECK constraints, and they do not enforce foreign key relationships.
- B. Correct.
Periodically validating 'order_id' relationships using join queries helps ensure data integrity even in the absence of built-in foreign key constraints.
- C. Correct.
Validating 'order_id' relationships during data ingestion ensures that invalid data never reaches the tables, maintaining data integrity.
- D. Incorrect.
The ZORDER command optimizes query performance but does not enforce or validate relationships between tables.
- E. Correct.
Delta Live Tables can include data quality rules, such as expectation checks, to validate foreign key relationships programmatically.