Databricks Data Engineer Professional Question 122
Single answerYou are designing a data model for a Databricks Lakehouse solution. The data includes sales transactions from multiple regions, which will be queried frequently for aggregated metrics (e.g., total sales per region and per product category). The data should also support efficient point-in-time queries, such as retrieving the state of sales at a specific date. Which of the following approaches would best optimize for both analytical and point-in-time query performance?
- A
Implement a star schema with one fact table for sales transactions and multiple dimension tables for regions, products, and time.
- B
Store all sales data in a single wide table with denormalized fields for regions, products, and dates.
- C
Use a Delta Lake table for sales transactions and implement Change Data Capture (CDC) to track historical changes.
- D
Partition the sales data by product category and store it as Parquet files in an object store.
Show answer and explanation
Correct answer: A
Explanation
A star schema is a widely used design for data warehouses and supports both analytical queries and point-in-time lookups effectively. The fact table in a star schema stores quantitative data (e.g., sales transactions), while dimension tables store descriptive attributes (e.g., regions, products, time). This enables efficient querying for aggregated metrics and historical data snapshots. Other approaches, such as denormalized tables or simple file partitioning, may address specific use cases but lack the scalability and flexibility provided by a star schema.
- A. Correct.
This is the correct approach because a star schema balances query performance for aggregations and point-in-time lookups. The fact table enables efficient storage and query performance, while the dimension tables provide rich metadata for analytical queries.
- B. Incorrect.
A single wide table with denormalized fields can lead to storage inefficiencies and slower query performance due to the lack of separation between dimensions and facts. While simple, this approach is not optimal for scalable analytical workloads.
- C. Incorrect.
Using a Delta Lake table and CDC is useful for change tracking and incremental data updates, but it does not directly optimize for analytical queries or point-in-time queries in the same way a well-designed star schema does.
- D. Incorrect.
Partitioning by product category may improve query performance for some analytical queries but does not address the need for point-in-time queries or the advantages of using a relational schema for aggregated metrics.