Databricks Data Engineer Professional Question 42
Single answerA company is using Databricks to process large volumes of e-commerce transaction data. The dataset includes the following columns: transaction_id (unique identifier for each transaction), customer_id, transaction_date (date of the transaction), and region (geographical region of the customer). The data will be queried frequently for month-over-month sales trends within specific regions. Which partitioning strategy should you recommend for optimal query performance?
- A
Partition the dataset by
transaction_id. - B
Partition the dataset by
region. - C
Partition the dataset by
transaction_date. - D
Partition the dataset by
regionandtransaction_date.
Show answer and explanation
Correct answer: D
Explanation
When designing a partitioning strategy, it is important to align with the query patterns. Since the queries involve both region and transaction_date filters, partitioning by both columns ensures efficient partition pruning. This reduces the amount of data scanned for each query, improving performance. Partitioning by a highly granular column like transaction_id would create too many small partitions, while partitioning by just one of the relevant columns (region or transaction_date) would not fully optimize the queries.
- A. Incorrect.
Partitioning by
transaction_idis inefficient because it is a unique identifier, leading to too many small partitions that cannot be scanned efficiently. - B. Incorrect.
Partitioning by
regionalone can improve filtering performance for regional queries but fails to optimize queries that involve date-based trends. - C. Incorrect.
Partitioning by
transaction_datealone can optimize date-based queries but does not improve performance for region-based filters. - D. Correct.
Partitioning by both
regionandtransaction_dateis the most effective strategy as it allows efficient pruning of partitions when filtering by both region and date, which aligns with the query patterns described.