Databricks Data Engineer Professional Question 25
Select 3You are working with a large Delta Lake table containing billions of records. The table experiences frequent queries that filter on both a 'region' column and a 'timestamp' column. You notice that query performance is degrading over time due to increasing data size. Which of the following optimizations should you apply to improve query performance in this scenario?
- A
Partition the table by the 'region' column.
- B
Apply ZORDER on the 'timestamp' column.
- C
Use a bloom filter index on the 'region' column.
- D
Reduce file sizes to be smaller than 10 MB.
- E
Apply ZORDER on both the 'region' and 'timestamp' columns.
Show answer and explanation
Correct answers: A, B, C
Explanation
In this scenario, partitioning the table by 'region' organizes the data for efficient region-based filtering, while ZORDER on the 'timestamp' column clusters data to optimize range queries on timestamp. Adding a bloom filter index on the 'region' column further enhances performance by skipping irrelevant files. These three approaches work together to improve query performance for the specified workload.
- A. Correct.
Partitioning by 'region' improves query performance by physically organizing the data into separate directories for each region, making region-based filters efficient.
- B. Correct.
ZORDER on the 'timestamp' column clusters data for faster filtering on the timestamp, which is beneficial for range queries on this column.
- C. Correct.
A bloom filter index on the 'region' column can speed up query execution by skipping data files that do not contain relevant regions.
- D. Incorrect.
Reducing file sizes below 10 MB is generally not recommended for Delta Lake, as it may lead to suboptimal performance due to an excessive number of small files.
- E. Incorrect.
Applying ZORDER on both columns ('region' and 'timestamp') is not possible in Delta Lake as ZORDER can only be applied on one set of columns at a time. Additionally, it would not complement partitioning effectively.