Analysts frequently filter a large Lakehouse table of digitised-object records by object category, but the table isn't partitioned by category because category values change over time and partitioning by it would create excessive small partitions. Query performance on category-filtered searches is still poor despite the table being regularly compacted. Which additional compaction option would most directly help category-filtered queries skip irrelevant data without partitioning the table by category?
Select an answer to reveal the explanation.
Short Explanation
Partitioning slices data into folders by value, but category isn't a good partition candidate here. Running OPTIMIZE with ZORDER on the category column groups similar values together inside the files themselves, so a filtered query can skip whole files that don't match. You get much of partitioning's skip-ahead benefit without the small-partition downside.
Full Explanation
OPTIMIZE with ZORDER BY co-locates rows that share similar values in the specified column within the same set of files during compaction, and Delta's file-level statistics (min/max value ranges per file) then let the query engine skip entire files that fall outside the filter's value range — data skipping — without needing the physical folder-per-value structure that true partitioning requires; this directly suits a column like category that's frequently filtered on but unsuitable for partitioning due to its cardinality and change pattern. Increasing the VACUUM retention period keeps more old file versions around for time travel; it has nothing to do with how efficiently a current query scans data and doesn't help with filtering at all. Dynamic data masking changes what values are displayed to certain users; it doesn't change which files are read or reduce the physical scan. Partitioning by ingestion date is not a universal fix — it only helps queries that filter on date, and it does nothing for a query filtering by category, which is the actual bottleneck described here; date-based partitioning would leave category-filtered queries just as unable to skip irrelevant data. A caveat: ZORDER benefits degrade as more unrelated columns are ZORDERed together or as the table receives heavy ongoing writes between compaction runs, so it works best on a small number of genuinely high-value filter columns, refreshed on a reasonable compaction schedule. A concrete check: compare the number of files read for a category-filtered query before and after running OPTIMIZE with ZORDER BY on that column.