Answer: Z-Ordering, dynamic file pruning (DFP)

## Explanation To minimize execution time for queries against non-partitioned tables and joins on non-partitioned columns in Delta Lake on Azure Databricks, the optimal approaches are: ### **B: Z-Ordering** Z-Ordering is a data layout optimization technique that co-locates related data in the same set of files by sorting data across multiple columns using a space-filling curve. This is particularly beneficial for: - **Non-partitioned tables**: Since there are no partition boundaries to leverage, Z-Ordering creates data locality that allows the query engine to skip irrelevant files during scans. - **Joins on non-partitioned columns**: When joining on columns that aren't used for partitioning, Z-Ordering those columns ensures that matching records are physically close together, reducing the amount of data that needs to be shuffled and processed during join operations. ### **D: Dynamic File Pruning (DFP)** Dynamic File Pruning is a query optimization technique that eliminates unnecessary file scans at runtime by: - **Non-partitioned table queries**: DFP can skip entire data files that don't contain relevant data based on filter predicates, even without traditional partitioning. - **Joins on non-partitioned columns**: During join operations, DFP uses information from one side of the join to eliminate irrelevant files from the other side, significantly reducing I/O and processing overhead. ### Why other options are less suitable: **A: The clone command** - This creates a copy of a Delta table but doesn't inherently optimize query performance. While it can be useful for testing or creating backups, it doesn't address the core performance challenges with non-partitioned data structures. **C: Apache Spark caching** - While caching can improve performance for repeated queries on the same dataset, it has limitations: - Memory-intensive and may not scale for large datasets - Doesn't optimize the initial query execution - Less effective for ad-hoc queries where data isn't repeatedly accessed - Doesn't address the fundamental data layout issues with non-partitioned tables The combination of Z-Ordering and Dynamic File Pruning provides complementary benefits: Z-Ordering optimizes the physical data layout, while DFP leverages that optimized layout at query runtime to minimize data scanning and processing.

Author: LeetQuiz Editorial Team