Databricks Data Engineer Professional Question 60
Single answerYou are working on a Delta Lake table containing a large volume of e-commerce transaction data. Each record includes columns such as transaction_id, customer_id, transaction_date, product_category, and region. The table is queried most often to generate daily sales reports for each region and product category. Which columns would be the most appropriate to use for partitioning the table to optimize query performance?
- A
transaction_dateandregion - B
customer_idandtransaction_id - C
product_categoryandtransaction_id - D
regionandproduct_category
Show answer and explanation
Correct answer: A
Explanation
Partitioning a table by the appropriate columns is critical for optimizing query performance. Since the table is queried most often for daily sales reports by region and product category, partitioning by transaction_date and region minimizes the amount of data scanned during these queries. Choosing high-cardinality columns like transaction_id or irrelevant columns for the primary query patterns would lead to poor performance and inefficiencies.
- A. Correct.
transaction_dateandregionare the correct partitioning columns because the queries primarily filter on these columns to generate daily sales reports for each region. Partitioning on these columns will reduce the amount of data scanned during queries. - B. Incorrect.
customer_idandtransaction_idare not suitable because these columns are highly cardinal. Partitioning on high-cardinality columns can lead to too many small files, which negatively impacts query performance. - C. Incorrect.
product_categoryandtransaction_idare not appropriate becausetransaction_idis unique for each record, leading to an excessive number of partitions. This would result in inefficient storage and query execution. - D. Incorrect.
regionandproduct_categoryare partially relevant but insufficient. While they are useful for certain queries, daily sales reports need filtering bytransaction_date. Omitting it from the partitioning strategy would prevent optimization for the primary use case.