Databricks Data Engineer Professional Question 26
Select 3A retail company is using Delta Lake to store sales transaction data. The dataset includes columns for transaction_date, store_id, and product_id. The company frequently queries the data to find all transactions for specific dates and products. However, the query performance has been slow due to the large dataset size. As a Data Engineer, how would you optimize the Delta Lake table to improve query performance?
- A
Partition the table by the
transaction_datecolumn - B
Apply Z-Order optimization on the
product_idcolumn - C
Enable bloom filters on the
store_idcolumn - D
Reduce the file size below the default threshold
- E
Partition the table by both
transaction_dateandproduct_idcolumns
Show answer and explanation
Correct answers: A, B, C
Explanation
Optimizing query performance in Delta Lake often requires a combination of partitioning, Z-Order optimization, and bloom filters. Partitioning by transaction_date helps prune large portions of data, Z-Order optimization clusters data for better skipping, and bloom filters accelerate selective queries. However, over-partitioning or reducing file sizes beyond the default threshold can negatively impact performance.
- A. Correct.
Partitioning by
transaction_dateis effective because it aligns with one of the main query predicates, allowing queries to prune irrelevant partitions and reduce the data scanned. - B. Correct.
Z-Order optimization on
product_idis effective for improving query performance since it clusters data based on the column frequently queried, enabling more efficient data skipping. - C. Correct.
Enabling bloom filters on
store_idcan improve point lookups or selective queries on this column by quickly identifying files that may contain matching records. - D. Incorrect.
Reducing file size below the default threshold is not recommended as it can increase the number of files, leading to query inefficiencies and higher metadata overhead.
- E. Incorrect.
Partitioning by both
transaction_dateandproduct_idis generally not recommended because excessive partitioning can lead to small files and performance degradation, depending on the data distribution.