Databricks Data Engineer Professional Question 126
Single answerA data engineering team is tasked with designing a Delta Lake schema for an e-commerce application. The application tracks users' orders and their corresponding payment details. The team decides to create two Delta tables: 'orders' and 'payments'. Each order can have multiple corresponding payments. Which approach should the team take to model the relationship between these tables in Delta Lake?
- A
Add a foreign key column in the 'payments' table that references the primary key of the 'orders' table.
- B
Merge the 'orders' and 'payments' tables into a single table to simplify the schema.
- C
Use a composite primary key in the 'payments' table that includes the order ID and payment ID.
- D
Create a join table to model the many-to-many relationship between 'orders' and 'payments'.
Show answer and explanation
Correct answer: A
Explanation
In Delta Lake, designing schemas that follow normalization principles ensures scalability and maintainability. Since each order can have multiple payments, the appropriate way to model this one-to-many relationship is to add a foreign key column in the 'payments' table that references the primary key of the 'orders' table. This approach avoids redundancy and ensures data integrity.
- A. Correct.
This is the correct approach because it models the one-to-many relationship between 'orders' and 'payments' by using a foreign key in the 'payments' table that references the 'orders' table.
- B. Incorrect.
Merging the tables into a single table is not recommended for this use case because it violates normalization principles and makes the schema harder to maintain.
- C. Incorrect.
While a composite primary key might be useful in some scenarios, it is not necessary here as only a foreign key is required to establish the relationship.
- D. Incorrect.
A join table is unnecessary because the relationship between 'orders' and 'payments' is not many-to-many; it is one-to-many.