Databricks Data Engineer Professional Question 55
Select 2You are designing a data storage solution in Databricks for a retail company that processes daily sales data. The data is stored in Delta tables and queried frequently for reporting and analysis. The table is expected to grow significantly, with billions of rows over time. Sales queries often filter by region, store_id, and date. Which column(s) should you use for partitioning to optimize query performance and storage efficiency?
- A
Partition by
regionandstore_id - B
Partition by
date - C
Partition by
region,store_id, anddate - D
Partition by
store_idonly - E
Avoid partitioning and rely on indexing only
Show answer and explanation
Correct answers: B, C
Explanation
Partitioning is a critical strategy for optimizing query performance and storage in Databricks. In this scenario, date is a commonly queried column, making it a strong candidate for partitioning. Additionally, combining date with other frequently queried columns like region and store_id can further enhance performance for multi-filter queries. However, care must be taken to ensure that the number of partitions does not become unmanageable. Over-partitioning can lead to small files and high metadata overhead, while under-partitioning can result in inefficient queries.
- A. Incorrect.
Partitioning by
regionandstore_idmay result in too many small partitions, especially if the number of regions and stores is very large. This can lead to inefficient query performance and high metadata management overhead. - B. Correct.
Partitioning by
dateis an effective strategy becausedateis commonly used in queries. It ensures that data is organized chronologically, reducing the amount of data scanned during date-based queries. - C. Correct.
Partitioning by
region,store_id, anddateis a good option for optimizing queries that filter by all three columns. However, this strategy can lead to a large number of small partitions if the cardinality ofregionandstore_idis high, which needs to be carefully managed. - D. Incorrect.
Partitioning by
store_idonly is not ideal because it does not address the common query patterns that filter bydate. This could lead to scanning unnecessary partitions during queries. - E. Incorrect.
Avoiding partitioning and relying on indexing only is not recommended for large datasets, as it can result in slower queries due to the lack of data pruning and an inefficient storage format.