Databricks Data Engineer Professional Question 170
Select 3You are designing a data pipeline in Databricks to process customer orders and their associated items. Due to the lack of foreign key constraints in Delta Lake, you want to ensure data integrity between the 'orders' table and the 'order_items' table. Which of the following approaches can help you avoid issues caused by the lack of enforced foreign key constraints?
- A
Implement a pipeline step that validates the existence of corresponding order IDs in the 'orders' table before inserting records into the 'order_items' table.
- B
Use ACID transactions provided by Delta Lake to guarantee referential integrity between the 'orders' and 'order_items' tables.
- C
Perform a join between the 'orders' and 'order_items' tables to identify and reject orphan records in the 'order_items' table before writing data.
- D
Enable Delta Lake's native foreign key constraint enforcement feature to ensure consistency between the 'orders' and 'order_items' tables.
- E
Log invalid 'order_items' records with missing order references to a separate audit table for further investigation.
Show answer and explanation
Correct answers: A, C, E
Explanation
Delta Lake does not support native foreign key constraints, so engineers must implement additional mechanisms to maintain referential integrity. Validating data relationships during pipeline execution, rejecting invalid records, or logging them for auditing purposes are common strategies to ensure consistency between related tables like 'orders' and 'order_items'.
- A. Correct.
This is correct. Validating the existence of order IDs in the 'orders' table before inserting into 'order_items' ensures that no orphan records are introduced.
- B. Incorrect.
This is incorrect. While Delta Lake provides ACID guarantees for transactions, it does not enforce referential integrity like traditional relational databases.
- C. Correct.
This is correct. Performing a join to identify orphan records allows you to proactively reject invalid data before it is written into the 'order_items' table.
- D. Incorrect.
This is incorrect. Delta Lake does not natively support foreign key constraint enforcement as of now, so this feature is not available.
- E. Correct.
This is correct. Logging invalid records to an audit table provides a mechanism to track and address data integrity issues without failing the entire pipeline.