Databricks Data Engineer Professional Question 181
Select 3A data engineering team is designing a normalized data model for a retail application in Databricks. The team wants to store product category information in a lookup table and use it to reference the main transactions table. What are the trade-offs of using a lookup table in this scenario?
- A
Improved data consistency since product category information is maintained in a single location.
- B
Increased query performance for transactional queries due to reduced table joins.
- C
Reduced data redundancy by avoiding repeated category information in the transactions table.
- D
Potential performance overhead during query execution due to the need for joins between the transactions and lookup tables.
- E
Simplified schema design, as all data is kept in a single table rather than separate normalized tables.
Show answer and explanation
Correct answers: A, C, D
Explanation
Using lookup tables in normalized data models offers benefits such as improved data consistency and reduced data redundancy. However, it comes with potential trade-offs, such as performance overhead due to joins and increased schema complexity. While these trade-offs need to be carefully evaluated based on the use case, normalized models are often preferred for ensuring data integrity and efficient storage.
- A. Correct.
Maintaining product category information in a single lookup table ensures data consistency, as any changes to category details are reflected wherever they are referenced.
- B. Incorrect.
This statement is incorrect. Using lookup tables introduces joins, which can potentially decrease query performance for transactional queries.
- C. Correct.
By storing product category information in a lookup table, the model avoids duplicating the same information in every row of the transactions table, thereby reducing data redundancy.
- D. Correct.
Using a lookup table requires joins during query execution, which can result in a performance overhead depending on the size of the tables and the complexity of the joins.
- E. Incorrect.
This statement is incorrect. Using a normalized schema with lookup tables typically increases the complexity of the schema design compared to a denormalized schema where all data is stored in a single table.