Which of the following is a key LIMITATION of Z-order (OPTIMIZE ... ZORDER BY) compared to liquid clustering?
Show answer & explanation
Correct answer: A
WHY A is correct: The Databricks documentation explicitly states: 'Z-order works in tandem with the OPTIMIZE command. You cannot combine files across partition boundaries, and so Z-order clustering can only occur within a partition. For unpartitioned tables, files can be combined across the entire table.' This is a fundamental constraint: on a partitioned table, OPTIMIZE ZORDER BY runs independently per partition, meaning that files from different partitions are never co-sorted together. This limits the effectiveness of ZORDER on multi-partition queries. Liquid clustering, by contrast, organizes data across all files in the table regardless of any partition structure, and does not have a partition-boundary limitation.
WHY NOT D: This is exactly backwards. Z-ordering actually works BETTER on unpartitioned tables because files can be combined across the entire table. On partitioned tables, ZORDER is constrained to within-partition boundaries. Z-ordering can be applied to both partitioned and unpartitioned tables.
WHY NOT C: Z-ordering is a long-standing Delta Lake feature available in all supported Databricks Runtime versions — it does not require Runtime 15.2. Runtime 15.2 is the Databricks Runtime version at which liquid clustering GA support for Delta Lake tables was introduced, not ZORDER. This swaps the version requirement of the two features.
WHY NOT B: Z-ordering requires that statistics be collected on the columns used in ZORDER BY (documentation: 'Z-ordering on columns that do not have statistics collected on them would be ineffective'). However, statistics collection can be configured at any time using ALTER TABLE SET TBLPROPERTIES with dataSkippingStatsColumns or dataSkippingNumIndexedCols. Statistics columns are not frozen at table creation time.