Databricks Data Engineer Professional Question 169
Select 2You are designing a data engineering solution in Databricks for a retail company. Their data pipeline ingests transactional data into a Delta table called 'sales' and product information into another Delta table called 'products'. Since Delta Lake does not support foreign key constraints, what steps should you take to ensure data consistency between 'sales' and 'products'?
- A
Implement a batch job to validate that all product IDs in the 'sales' table exist in the 'products' table.
- B
Use Delta Lake's built-in foreign key constraint feature to enforce consistency.
- C
Add a NOT NULL constraint on the product ID column in the 'sales' table to prevent null values.
- D
Implement a streaming job to continuously check that all product IDs in the 'sales' table exist in the 'products' table.
- E
Create a Delta Live Table pipeline to enforce referential integrity between the two tables.
Show answer and explanation
Correct answers: A, D
Explanation
Since Delta Lake does not support foreign key constraints, data engineers must implement custom solutions to ensure data consistency. Batch jobs and streaming jobs are effective approaches to validate referential integrity between tables. Adding a NOT NULL constraint or using Delta Live Table pipelines alone does not guarantee that a referenced ID exists in another table, nor does Delta Lake provide built-in support for foreign keys.
- A. Correct.
Correct. A batch job can periodically validate the referential integrity by ensuring all product IDs in the 'sales' table exist in the 'products' table.
- B. Incorrect.
Incorrect. Delta Lake does not currently support foreign key constraints, so this is not a valid approach.
- C. Incorrect.
Incorrect. Adding a NOT NULL constraint ensures that the product ID column cannot contain null values, but it does not guarantee that the product ID exists in the 'products' table.
- D. Correct.
Correct. A streaming job can continuously validate referential integrity in real-time, ensuring data consistency as new records are ingested.
- E. Incorrect.
Incorrect. Delta Live Table pipelines are designed for building and managing data pipelines but do not enforce referential integrity automatically.