A data analyst manages a 50 TB Delta table that is currently partitioned by ingestion_date. Analysts frequently filter on customer_region and product_category — two high-cardinality columns that are NOT the partition key — leading to very slow queries. She wants to improve filter performance without rewriting the table format from scratch. Which approach is most appropriate?
Answer & explanation
Correct answer: B
WHY B: ZORDER is fully compatible with existing Hive-style partitioned tables. Running OPTIMIZE table_name ZORDER BY (customer_region, product_category) improves multi-column filter performance via data co-location within each partition, without changing the partition scheme or rewriting the table. WHY NOT A: Adding high-cardinality columns like customer_region and product_category as Hive sub-partitions creates millions of tiny partitions, which severely degrades rather than improves performance. WHY NOT C: Liquid clustering is incompatible with existing Hive-style partitioned tables; enabling it requires removing the current partition first — effectively a full table rewrite — which the analyst explicitly wants to avoid. WHY NOT D: A materialized view pre-filtered on specific values duplicates storage and does not improve arbitrary filter queries on the base table. WHY NOT E: A full CTAS reload is unnecessary; OPTIMIZE ZORDER can be applied incrementally to the existing table without reloading data.