Databricks ZORDER and OPTIMIZE


   Databricks ZORDER and OPTIMIZE

In Databricks, OPTIMIZE and ZORDER are two commands used together to improve the performance of Delta Lake tables. They work by physically reorganizing the data files to make data access more efficient.


The OPTIMIZE command consolidates small files into larger ones and collects statistics on the data. This reduces the number of files that need to be read during queries, leading to faster query performance. It can also be used to compact files after deleting or updating data to reclaim space.


ZORDER is an extension to the OPTIMIZE command. It first sorts the data based on one or more columns and then performs the file compaction. This sorting ensures that related data is colocated in the same files, improving data skipping and further boosting query performance.

When to Use Them

  • OPTIMIZE: Use this command regularly to maintain optimal file sizes and keep up with data changes. It’s especially useful after significant data ingestion, updates, or deletions.
  • ZORDER: Use this command when you have high-cardinality columns (columns with many distinct values) that are frequently used in query filters (WHERE clauses). Z-ordering these columns can drastically reduce the amount of data that needs to be scanned during queries.


OPTIMIZE my_delta_table
ZORDER BY column1, column2
This command will optimize the my_delta_table Delta table, z-ordering it by column1 and column2.

Important Considerations

  • Multiple Columns: You can specify multiple columns for ZORDER BY, but the effectiveness of colocation decreases with each additional column.
  • Overhead: Z-ordering incurs additional overhead due to sorting, so use it judiciously.
  • Data Skipping: Z-ordering enhances data skipping, a feature that allows Delta Lake to skip reading irrelevant files based on query predicates.

Databricks Training Demo Day 1 Video:

You can find more information about Databricks Training in this Dtabricks Docs Link



Unogeeks is the No.1 IT Training Institute for Databricks Training. Anyone Disagree? Please drop in a comment

You can check out our other latest blogs on Databricks Training here – Databricks Blogs

Please check out our Best In Class Databricks Training Details here – Databricks Training

 Follow & Connect with us:


For Training inquiries:

Call/Whatsapp: +91 73960 33555

Mail us at:

Our Website ➜

Follow us:





Leave a Reply

Your email address will not be published. Required fields are marked *