Databricks Data Engineer Professional Question 36
Select 2You are tasked with creating a Delta table in Databricks to support a team of data analysts using Databricks SQL. The table will be queried frequently for aggregate analytics, and performance is critical. Which of the following steps should you take to optimize the Delta table for Databricks SQL workloads?
- A
Enable Z-Ordering on columns that are commonly used in filtering or grouping operations.
- B
Optimize the table by running the OPTIMIZE command with a specific column for Z-Ordering.
- C
Select a partitioning strategy based on high-cardinality columns to ensure better parallelism.
- D
Manually compact small files in the Delta table to improve query performance.
- E
Enable Delta Change Data Feed to track changes in the table for SQL queries.
Show answer and explanation
Correct answers: A, B
Explanation
To optimize Delta tables for Databricks SQL workloads, enabling Z-Ordering and using the OPTIMIZE command are the most effective strategies. Z-Ordering organizes data to improve query performance by reducing scan time, while the OPTIMIZE command compacts files for better performance. Other options, such as inappropriate partitioning or enabling Change Data Feed, do not directly contribute to SQL workload optimization.
- A. Correct.
Z-Ordering improves data skipping and query performance by organizing data based on the columns most commonly used in filtering or grouping. This optimization is highly beneficial for Databricks SQL workloads.
- B. Correct.
The OPTIMIZE command, combined with Z-Ordering, can compact files and improve data locality, which significantly boosts performance for aggregate analytics and SQL queries.
- C. Incorrect.
Partitioning based on high-cardinality columns is generally not recommended because it can lead to small partitions, which degrade performance. Instead, partitioning should be based on low-cardinality columns.
- D. Incorrect.
Manually compacting small files is not necessary because Databricks automatically handles file compaction through the OPTIMIZE command for Delta tables. Manual compaction is not the best practice in this scenario.
- E. Incorrect.
Delta Change Data Feed is used for tracking changes in a Delta table, which is helpful for incremental data processing but does not directly optimize performance for Databricks SQL queries.