Databricks Data Engineer Professional Question 174
Select 3You are designing a Delta Lake-based data warehouse in Databricks. Your architecture includes a fact table that references multiple dimension tables. Since Delta Lake does not enforce foreign key constraints, how can you ensure data integrity and avoid issues caused by the lack of these constraints?
- A
Implement ETL pipelines that validate references between fact and dimension tables during data ingestion.
- B
Set up Delta Lake's built-in foreign key constraint enforcement to validate relationships.
- C
Use Delta Live Tables to define data quality rules that check for orphaned records in the fact table.
- D
Leverage Unity Catalog to enforce primary and foreign key relationships between tables.
- E
Periodically run validation queries to identify and repair inconsistencies between the fact and dimension tables.
Show answer and explanation
Correct answers: A, C, E
Explanation
Since Delta Lake does not enforce foreign key constraints, data engineers must implement custom solutions to ensure data integrity. Validating references during ingestion, using Delta Live Tables to enforce data quality rules, and running periodic validation queries are effective strategies to avoid issues caused by the lack of foreign key constraints.
- A. Correct.
Correct. Validating references during data ingestion ensures that the fact table contains valid foreign keys that match the primary keys in dimension tables.
- B. Incorrect.
Incorrect. Delta Lake does not natively support foreign key constraints. This is not a valid option.
- C. Correct.
Correct. Delta Live Tables allows you to define data quality constraints, which can be used to identify orphaned records and ensure data integrity.
- D. Incorrect.
Incorrect. Unity Catalog does not provide functionality for enforcing primary or foreign key constraints in Delta Lake tables.
- E. Correct.
Correct. Running periodic validation queries is a common practice to detect and repair any inconsistencies between tables in the absence of enforced foreign key constraints.