Question

A data engineer runs OPTIMIZE with ZORDER BY on a large Delta table to improve query performance. Most dashboard queries filter on the customer_id column, but a smaller set of queries filter on region. The engineer writes: OPTIMIZE sales ZORDER BY (region, customer_id). After running it, queries filtering only on customer_id show little improvement in data skipping. What is the most likely explanation?

Accepted Answer

Z-ordering effectiveness decreases for columns listed later in the ZORDER BY clause, so customer_id benefits less than region because it is listed second.

Answer

ZORDER BY can only accept a single column, so listing two columns causes the second column to be ignored entirely.

Answer

Z-ordering only improves performance for the partition columns of the table, not for the columns named in the ZORDER BY clause.

Answer

OPTIMIZE must be run separately for each column, so a single command with two columns produces no data skipping benefit for either.