Databricks Data Engineer Professional Question 182
Select 3You are tasked with designing a data model in Databricks for a customer-facing application. The model needs to store information about customers, orders, and products. To ensure better performance for certain queries, you decide to implement lookup tables. Which of the following are valid trade-offs of using a normalized data model with lookup tables in this scenario?
- A
Improved query performance for analytical workloads requiring joins
- B
Reduced data duplication, leading to lower storage costs
- C
Simplified querying logic due to fewer joins in normalized models
- D
Potential increase in query complexity and execution time for transactional workloads
- E
Easier maintenance of data consistency through updates in lookup tables
Show answer and explanation
Correct answers: B, D, E
Explanation
Normalized data models are commonly used in scenarios where reducing data redundancy and maintaining data consistency are priorities. By using lookup tables, you can centralize updates and reduce storage costs, but you may encounter increased query complexity and slower performance, particularly for transactional workloads. These trade-offs must be carefully considered when designing a data model in Databricks.
- A. Incorrect.
While normalized data models can optimize storage and ensure data consistency, they do not necessarily improve query performance for analytical workloads. In fact, frequent joins required in normalized models can slow down analytical queries.
- B. Correct.
Normalized models reduce data duplication since related data is stored in separate tables, leading to lower storage costs.
- C. Incorrect.
Normalized models typically require multiple joins to retrieve complete datasets, which can complicate querying logic rather than simplifying it.
- D. Correct.
For transactional workloads, normalized models with lookup tables can increase query complexity and execution time due to the need for frequent joins.
- E. Correct.
Lookup tables in normalized models help centralize data updates, making it easier to maintain data consistency across the model.