Databricks Data Engineer Professional Question 33
Select 4You are tasked with creating a Delta table on Databricks that will be used for ad-hoc querying and reporting by the Databricks SQL service. The table will store large volumes of sales data and must be optimized for query performance. Which of the following actions should you take to ensure the Delta table is optimized for Databricks SQL?
- A
Partition the Delta table by a frequently filtered column, such as 'region'.
- B
Enable Z-Ordering on columns that are commonly used in filter conditions, such as 'product_id'.
- C
Use the OPTIMIZE command to compact small files into larger ones.
- D
Disable Delta caching to reduce memory overhead for the Databricks SQL service.
- E
Set the Delta table property 'delta.autoOptimize.autoCompact' to 'true' to allow automatic file compaction.
Show answer and explanation
Correct answers: A, B, C, E
Explanation
To optimize Delta tables for the Databricks SQL service, you should partition the table by frequently filtered columns, use Z-Ordering for commonly queried fields, compact files using the OPTIMIZE command, and enable automatic compaction to handle small files efficiently. Delta caching should not be disabled, as it can significantly enhance query performance by reducing data retrieval times.
- A. Correct.
Partitioning the Delta table by a frequently filtered column, such as 'region', helps reduce the amount of data scanned during queries, improving performance.
- B. Correct.
Z-Ordering reorganizes data within partitions to optimize for queries with specific filtering conditions, such as 'product_id', which enhances query performance.
- C. Correct.
Using the OPTIMIZE command compacts small files into larger ones, which reduces the number of file reads and improves query performance for large datasets.
- D. Incorrect.
Disabling Delta caching is not recommended because caching improves query performance by storing frequently accessed data in memory.
- E. Correct.
Setting the Delta table property 'delta.autoOptimize.autoCompact' to 'true' enables automatic file compaction, which helps maintain optimal performance for write-heavy workloads.