Databricks Data Engineer Professional Question 28
Select 3You are tasked with optimizing a Delta Lake table containing billions of records for a data analytics workload. The table contains user activity logs partitioned by the 'date' column. Analysts frequently query the table using filters on the 'user_id' column. What combination of Delta Lake optimizations should you apply to improve query performance in this scenario?
- A
Partition the table by the 'user_id' column.
- B
Partition the table by the 'date' column and apply ZORDER on the 'user_id' column.
- C
Use a Bloom filter index on the 'user_id' column.
- D
Optimize the table to reduce file sizes to around 100 MB.
- E
Use ZORDER on both the 'date' and 'user_id' columns.
Show answer and explanation
Correct answers: B, C, D
Explanation
To optimize the Delta Lake table for this scenario, partitioning by 'date' allows efficient pruning for date-based queries, while ZORDER on 'user_id' ensures better locality for filtering by 'user_id'. Additionally, a Bloom filter index on 'user_id' further enhances query performance by minimizing unnecessary file scans. Optimizing file sizes to around 100 MB is a best practice to ensure efficient query execution. Partitioning by 'user_id' or applying ZORDER on multiple columns would not be optimal due to high cardinality and reduced efficiency.
- A. Incorrect.
Partitioning by 'user_id' would create an excessive number of small files due to the high cardinality of 'user_id', leading to poor performance.
- B. Correct.
Partitioning by 'date' enables efficient pruning for date-based queries, and ZORDER on 'user_id' ensures that data is co-located for faster filtering on 'user_id'.
- C. Correct.
Bloom filter indexing is effective for high-cardinality columns like 'user_id' as it optimizes point lookups and reduces unnecessary file scans.
- D. Correct.
Optimizing file sizes to around 100 MB ensures efficient file reads and reduces query latency, which is critical for large datasets.
- E. Incorrect.
Applying ZORDER on both 'date' and 'user_id' is not recommended because ZORDER works best when applied to a single column or a small subset of columns based on query patterns.