Databricks Data Engineer Professional Question 27
Select 3You are working on optimizing a Delta Lake table that stores large-scale e-commerce transaction data. The table is frequently queried for transactions by product category and transaction date. The table currently has no optimizations applied. Which combination of techniques would you apply to improve query performance while considering storage and compute efficiency?
- A
Partition the table by transaction date
- B
Apply Z-Ordering on the product category column
- C
Enable Bloom filters on the product category column
- D
Reduce file sizes to less than 10 MB each
- E
Partition the table by product category and transaction date
Show answer and explanation
Correct answers: A, B, C
Explanation
To optimize the Delta Lake table for frequent queries on product category and transaction date, you should use a combination of partitioning, Z-Ordering, and Bloom filters. Partitioning by transaction date reduces data scans for date-based queries, Z-Ordering improves clustering on the product category column, and Bloom filters help efficiently skip irrelevant files during queries using equality filters. Reducing file sizes too much or over-partitioning can negatively impact performance and are not recommended.
- A. Correct.
Partitioning the table by transaction date is an effective way to optimize queries that filter on this column, as it reduces the amount of data scanned.
- B. Correct.
Z-Ordering on the product category column will cluster data related to this column together, improving the performance of queries that filter or sort by this column.
- C. Correct.
Enabling Bloom filters on the product category column enhances the ability to skip irrelevant files during queries, especially for equality conditions.
- D. Incorrect.
Reducing file sizes to less than 10 MB is not ideal for Delta Lake tables as it can lead to excessive metadata overhead and reduced query performance. Optimal file sizes are generally between 256 MB and 1 GB.
- E. Incorrect.
Partitioning by both product category and transaction date can lead to an excessive number of partitions, increasing complexity and potentially degrading performance for writes and metadata management.