Databricks Data Engineer Professional Question 35
Select 3You are tasked with creating a Delta table in Databricks that will be heavily queried using Databricks SQL service. The table will store a large volume of daily transaction data, and queries will often filter by the 'transaction_date' column. How can you optimize the Delta table for efficient querying in this scenario?
- A
Partition the Delta table by the 'transaction_date' column.
- B
Use Z-Ordering on the 'transaction_date' column.
- C
Enable Delta Change Data Feed (CDF) for the table.
- D
Set 'optimizeWrite' to true when writing data to the Delta table.
- E
Use Auto Optimize to automatically compact small files.
Show answer and explanation
Correct answers: A, B, E
Explanation
To optimize a Delta table for Databricks SQL service, partitioning and Z-Ordering are key techniques for improving query performance, especially when filtering by specific columns like 'transaction_date'. Additionally, enabling Auto Optimize ensures that small files are compacted automatically, reducing the overhead of managing file sizes and improving query efficiency. While 'optimizeWrite' and Delta Change Data Feed have their own use cases, they are not specifically designed for optimizing query performance in this context.
- A. Correct.
Partitioning the Delta table by 'transaction_date' will allow queries filtering by this column to scan only the relevant partition, reducing query latency and improving performance.
- B. Correct.
Z-Ordering on the 'transaction_date' column will optimize the data layout for queries filtering by this column, further improving query performance.
- C. Incorrect.
Delta Change Data Feed (CDF) is not directly related to query optimization for Databricks SQL service. It is used for tracking changes to the Delta table.
- D. Incorrect.
While 'optimizeWrite' can help with efficient data ingestion by reducing the number of small files, it is not directly related to optimizing queries in Databricks SQL.
- E. Correct.
Auto Optimize ensures automatic compaction of small files, which enhances query performance by reducing the number of files scanned during query execution.