Databricks Data Engineer Professional Question 186
Select 3You are designing a data pipeline in Databricks to process customer orders. The pipeline must include a lookup table for product details to minimize data redundancy. Which of the following are valid trade-offs of using a normalized data model with a lookup table in this scenario?
- A
Improved data consistency by storing product details in a single location
- B
Faster query performance when joining the customer orders with the product details
- C
Increased complexity in managing the pipeline due to the need for joins
- D
Reduced storage requirements by avoiding data duplication in the customer orders table
- E
Simpler schema design, making it easier to query without transformations
Show answer and explanation
Correct answers: A, C, D
Explanation
Using a normalized data model with lookup tables provides benefits like improved data consistency and reduced storage requirements, but it comes with trade-offs such as increased complexity due to the need for joins. While normalization reduces redundancy, it can also negatively impact query performance, particularly in scenarios where frequent joins are needed.
- A. Correct.
Storing product details in a single location improves data consistency because updates only need to be made in one place, reducing the risk of discrepancies.
- B. Incorrect.
Using joins in a normalized model can slow down query performance, especially with large datasets, making this option incorrect.
- C. Correct.
A normalized model increases complexity in the pipeline because joins are required to combine data from multiple tables, making data retrieval less straightforward.
- D. Correct.
Normalization reduces storage requirements by avoiding redundancy, as common data (e.g., product details) is stored in a separate table instead of being repeated.
- E. Incorrect.
Normalized data models typically result in more complex schema designs due to the need for relationships and joins, so this option is incorrect.