Databricks Machine Learning Professional Question 156
Single answerYou are working with a large dataset in Databricks that contains millions of rows of customer transactions. The dataset is queried frequently by analysts to retrieve transactions based on the region column. Performance of these queries has been slow. As a Databricks Certified Machine Learning Professional, how can you improve query performance when analysts filter data by the region column?
- A
Partition the dataset by the
regioncolumn. - B
Sort the dataset by the
regioncolumn. - C
Cache the dataset in memory using Delta Lake.
- D
Create a Delta Lake Z-Order index on the
regioncolumn.
Show answer and explanation
Correct answer: A
Explanation
Partitioning the dataset by the region column is the best approach because it physically organizes the data in a way that minimizes the amount of data scanned during queries. This is particularly beneficial when the query workload involves frequent filtering by the partition column, such as region in this scenario.
- A. Correct.
Partitioning the dataset by the
regioncolumn organizes the data into separate files or directories based on the column values, reducing the amount of data scanned during queries. This is the most efficient way to improve query performance for filters on theregioncolumn. - B. Incorrect.
Sorting the dataset by the
regioncolumn can improve query performance slightly, but it does not eliminate the need to scan the entire dataset. It is less effective than partitioning for this use case. - C. Incorrect.
Caching the dataset in memory can improve performance for repeated queries, but it does not specifically optimize queries that filter by the
regioncolumn. - D. Incorrect.
Creating a Delta Lake Z-Order index helps optimize multi-column queries or point-lookups, but it is not as efficient as partitioning when queries frequently filter by a single column like
region.