Databricks Data Engineer Professional Question 173
Select 3You are designing a data pipeline in Databricks to handle transactional data for an e-commerce platform. The platform has separate tables for 'Orders' and 'Customers', but Databricks does not enforce foreign key constraints between these tables. How can you ensure data integrity and avoid issues caused by missing foreign key constraints when implementing these tables in Databricks?
- A
Apply a JOIN operation to validate that all customer IDs in the 'Orders' table exist in the 'Customers' table during data ingestion.
- B
Create a Delta Lake constraint that enforces a foreign key relationship between the 'Orders' and 'Customers' tables.
- C
Implement data validation logic in the pipeline to check for orphaned records in the 'Orders' table before writing to the target table.
- D
Use Unity Catalog to directly enforce foreign key constraints between the 'Orders' and 'Customers' tables.
- E
Maintain referential integrity by ensuring that upstream systems only produce valid 'Orders' data linked to existing customers.
Show answer and explanation
Correct answers: A, C, E
Explanation
Since Databricks does not enforce foreign key constraints, it is critical to implement alternative methods to ensure referential integrity between related tables. Validating data using JOINs during ingestion, adding pipeline-level checks for orphaned records, and ensuring that upstream systems provide clean data are all effective strategies. Delta Lake and Unity Catalog currently do not natively enforce foreign key constraints, so relying on these tools for this purpose is not viable.
- A. Correct.
Correct: Validating customer IDs in the 'Orders' table using a JOIN operation ensures that only valid records are ingested, preventing orphaned orders.
- B. Incorrect.
Incorrect: Delta Lake does not currently support direct enforcement of foreign key constraints.
- C. Correct.
Correct: Adding validation logic in the pipeline to detect orphaned records ensures referential integrity before writing data to the target tables.
- D. Incorrect.
Incorrect: Unity Catalog provides advanced data governance capabilities, but it does not enforce foreign key constraints.
- E. Correct.
Correct: Ensuring that upstream systems produce valid data eliminates the risk of orphaned records before they reach the Databricks pipeline.