Databricks Data Engineer Professional Question 183
Select 3You are designing a Databricks pipeline to process customer transaction data. The source data contains a 'customer_id' field that references a separate customer lookup table containing details like name, email, and address. You need to decide whether to join the lookup table to the main transaction data or keep the data normalized by storing only the 'customer_id' reference in the transaction dataset. What are the trade-offs of keeping the data normalized in this scenario?
- A
Reduced storage requirements by avoiding data duplication
- B
Increased query performance for analytics workloads
- C
Greater flexibility for updating customer details in a single location
- D
Increased complexity in querying, as joins are required to retrieve customer details
- E
Reduced need for pipeline maintenance in case of schema changes in the lookup table
Show answer and explanation
Correct answers: A, C, D
Explanation
Keeping data normalized by referencing a lookup table reduces storage requirements and allows for easier updates to customer details in a single location. However, it introduces trade-offs such as increased query complexity and potentially reduced query performance due to the need for joins. This decision should be based on the specific requirements of the workload, such as whether query performance or data storage optimization is a higher priority.
- A. Correct.
Reduced storage requirements are a key benefit of normalization, as data duplication is eliminated by referencing the lookup table rather than embedding customer details in the transaction dataset.
- B. Incorrect.
Query performance may actually decrease for analytics workloads due to the need for joins between the transaction data and the lookup table, especially for large datasets.
- C. Correct.
Normalization offers flexibility, as updating customer details in the lookup table automatically reflects in all datasets referencing it, reducing the need for redundant updates.
- D. Correct.
Normalized data models require joins to retrieve related information, increasing query complexity.
- E. Incorrect.
Pipeline maintenance can be more challenging with normalized data models, as schema changes in the lookup table may require downstream pipelines to be updated to handle the new structure.