Home / Data Analyst practice test / Analyzing Queries / Question

Databricks Data Analyst Associate · free practice question

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…

Exam-style question on Analyzing Queries from the free Data Analyst practice test, with the answer and a full explanation below.

Updated November 10, 2026

Analyzing Queries

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?

  1. AAdd a secondary partition by customer_region and product_category in addition to the existing ingestion_date partition, because Databricks supports multi-column hierarchical partitioning that handles high-cardinality columns as sub-partitions efficiently
  2. BRun OPTIMIZE table_name ZORDER BY (customer_region, product_category) to improve multi-column filter performance; ZORDER is compatible with the existing Hive-style ingestion_date partition and requires no table rewrite
  3. CEnable liquid clustering via ALTER TABLE table_name CLUSTER BY (customer_region, product_category), then run OPTIMIZE to incrementally recluster; liquid clustering enables data skipping without static-partition or ZORDER limitations
  4. DCreate a new materialized view pre-filtered on the most common customer_region and product_category combinations, so analysts can query the materialized view instead of the base table; this avoids the need to change the underlying table layout or run any OPTIMIZE operations
  5. EDelete and recreate the table using CTAS with customer_region and product_category as partition columns and ingest all 50 TB of data again, because liquid clustering and Z-ordering cannot be applied to existing large tables without a full reload operation
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.

Take the full Data Analyst practice test free →

Would you pass today?

This question is one of 45 in the free, timed Databricks Data Analyst Associate practice test. Take it and get a section-by-section score.

Start the free test →