You can verify if Z-Ordering has been applied and assess its effectiveness through a combination of methods:

1. Check Z-Order Configuration:

• SQL: Use DESCRIBE DETAIL table_name to see if a zOrderBy clause exists in the table’s properties.

• Databricks UI: In the Data tab, select your Delta table. Under the “Details” section, you should see “Z-Order By” if it’s configured.

2. Assess Z-Order Effectiveness (Data Skipping):

• EXPLAIN: Run EXPLAIN SELECT ... for your query. The query plan should indicate “data skipping” if Z-Order is effectively used to filter data. Look for phrases like:

  • “PushedFilters: [… zOrderBy columns …]:” This shows that Z-Order is used to filter files based on your query predicates.
  • “FileScan parquet [… zOrderBy columns …]:” This indicates the files scanned are filtered due to Z-Order.

• ANALYZE TABLE: Use ANALYZE TABLE table_name COMPUTE STATISTICS FOR ALL COLUMNS to ensure statistics are up-to-date. Data skipping relies on these statistics to determine which files to skip.

• Monitoring: Observe the query’s execution time and the amount of data scanned. Significant reductions compared to queries without Z-Order suggest effective data skipping.

Important Considerations:

• Column Choice: Z-Ordering is most effective on high-cardinality columns frequently used in filtering predicates.
• Statistics: Up-to-date column-level statistics are crucial for data skipping to work effectively.
• Incremental: Z-Ordering is incremental. Consider re-ordering if new data is added or query patterns change significantly.