Databricks Machine Learning Professional Question 159
Single answerYou are working with a large dataset of customer transactions in a Delta table stored in a Databricks lakehouse. The table has columns for customer_id, transaction_date, transaction_amount, and store_location. Data scientists frequently query this table to analyze the transaction history of specific customers. These queries often filter on the customer_id column. How can you optimize the performance of these queries?
- A
Partition the Delta table by the
customer_idcolumn. - B
Partition the Delta table by the
transaction_datecolumn. - C
Enable Delta Lake Z-Ordering on the
customer_idcolumn. - D
Use caching to store the entire table in memory.
Show answer and explanation
Correct answer: A
Explanation
When optimizing query performance in Delta tables, partitioning is an effective strategy for columns that are frequently filtered in queries. Since the filtering is done on customer_id in this scenario, partitioning by customer_id is the best choice. Partitioning physically organizes the data in the storage layer, reducing the amount of data scanned during queries and improving performance.
- A. Correct.
Correct: Partitioning by the
customer_idcolumn will physically group data by this column, significantly speeding up queries that filter bycustomer_id. - B. Incorrect.
Incorrect: Partitioning by
transaction_datemight benefit date-based queries but will not improve performance forcustomer_id-based filtering. - C. Incorrect.
Incorrect: Z-Ordering optimizes data locality for queries but does not provide the same performance boost as partitioning for specific column-based filtering.
- D. Incorrect.
Incorrect: While caching can improve performance for repeated queries, it does not address the core issue of optimizing query performance for large datasets filtered by a specific column.