DP-750 Certification Practice Question #44

Question

A Unity Catalog Delta table is frequently queried with predicates on a **high-cardinality** `transaction_id` column, and analysts complain that these point-lookup queries scan too many files. The data engineer is **not** using partitioning or liquid clustering on the table.

**Proposed solution:** Run `OPTIMIZE sales ZORDER BY (transaction_id)` so that related values are colocated, file-level min/max statistics tighten, and the engine can skip more files for queries that filter on `transaction_id`.

Does this solution meet the goal of improving query performance for filters on the high-cardinality column?

Accepted Answer

Although Databricks now recommends liquid clustering over Z-ordering for new tables, the proposed `OPTIMIZE ... ZORDER BY` is technically valid and does meet the stated goal. Microsoft's documentation explicitly states that Z-order works for high-cardinality fields — naming the customer ID in a transactions/orders table as an example — because it colocates related values and tightens file-level min/max statistics, increasing the percentage of files the engine can skip for filters on that column. Since the table is neither partitioned nor liquid-clustered, there is no incompatibility (you can't Z-order a partition column, and Z-order and liquid clustering are mutually exclusive), so the solution improves the targeted query pattern.

More DP-750 practice questions