Databricks Data Engineer Professional Question 125
Single answerYou are tasked with designing a data model in Databricks for a retail company. The company has historical sales data, product catalog information, and customer demographics stored in separate tables. The business team has requested a single view that combines these datasets to analyze sales trends by customer segments and product categories. Additionally, they want the model to support efficient querying for aggregations, such as total sales by region and product category. Which data modeling approach would be most appropriate for this requirement?
- A
Create a star schema where a fact table contains sales data and dimension tables contain product and customer information.
- B
Store all the data in a single flat table by joining the sales, product, and customer tables during data ingestion.
- C
Use a snowflake schema by normalizing the dimensions into multiple related tables to reduce redundancy.
- D
Replicate the data into multiple Delta tables for each specific query use case to optimize performance.
Show answer and explanation
Correct answer: A
Explanation
The star schema is the most appropriate data modeling approach for this scenario. It centralizes transactional data in a fact table and organizes descriptive data in dimension tables, optimizing the model for analytical queries and aggregations. The other options either compromise performance, increase maintenance complexity, or fail to align with the business requirements.
- A. Correct.
This is the correct approach. A star schema organizes the data into a central fact table for sales and related dimension tables for product and customer data. It supports efficient querying and aggregations, which aligns well with the business requirements.
- B. Incorrect.
While a single flat table might seem simpler, it can lead to storage inefficiencies, difficulties in maintaining the data model, and slower query performance for large datasets.
- C. Incorrect.
A snowflake schema could reduce redundancy, but it would increase query complexity and may not provide the performance benefits needed for frequent aggregations and analyses.
- D. Incorrect.
Replicating the data into multiple tables for specific queries would lead to data duplication, increased maintenance overhead, and lack of a centralized model for analysis.