Databricks Data Engineer Professional Question 29
Select 4A data engineering team is working on optimizing a Delta Lake table that stores large-scale e-commerce transaction data. The table is queried frequently by the customer_id and purchase_date columns for performance-critical dashboards. Currently, the table's performance is suboptimal due to large file sizes and inefficient query execution. Which of the following actions should the team take to improve performance?
- A
Partition the Delta Lake table by the
purchase_datecolumn. - B
Apply Z-Order clustering on the
customer_idcolumn. - C
Enable Bloom filters on the
customer_idcolumn. - D
Reduce the file sizes by compacting small files through Delta Lake's OPTIMIZE command.
- E
Partition the table by both
customer_idandpurchase_datecolumns.
Show answer and explanation
Correct answers: A, B, C, D
Explanation
To optimize a Delta Lake table for frequent queries on customer_id and purchase_date, the team should use a combination of partitioning, Z-Ordering, Bloom filters, and file compaction. Partitioning by purchase_date ensures efficient pruning for date-based queries. Z-Ordering improves data clustering for efficient data skipping on customer_id. Bloom filters further enhance performance for equality queries on customer_id. Finally, compacting small files reduces overhead and improves query execution. Partitioning by both customer_id and purchase_date is avoided due to the risk of creating too many small files.
- A. Correct.
Partitioning by
purchase_dateimproves performance for queries filtering by this column, as it reduces the amount of data scanned during query execution. - B. Correct.
Z-Ordering the table by
customer_idoptimizes data skipping for queries filtering oncustomer_id, further improving query performance. - C. Correct.
Bloom filters are effective for improving the performance of point queries and equality checks, particularly on high-cardinality columns like
customer_id. - D. Correct.
Compacting small files with Delta Lake's OPTIMIZE command reduces overhead for file management and improves query performance, especially in scenarios with many small files from frequent writes.
- E. Incorrect.
Partitioning by both
customer_idandpurchase_dateis not recommended because it may lead to an excessive number of small files due to high cardinality incustomer_id, which can negatively impact performance.