Optimize, ZORDER, and Vacuum in Databricks: What You Must Know

In the world of big data, performance is everything. Databricks, with its powerful Delta Lake engine, offers three key features—Optimize, ZORDER, and Vacuum—that can dramatically enhance query performance and manage storage efficiently. In this blog, we’ll break down each of these features from the ground up, progressing from basic understanding to advanced practices you can apply in your Databricks environment.

🔰 1. Understanding Delta Lake Tables

Before diving into optimization techniques, let’s establish what Delta Lake is:

Delta Lake is a storage layer on top of your data lake that brings ACID transactions, schema enforcement, and time travel to your data.
Data is stored as Parquet files, and every transaction is logged in a transaction log (_delta_log).

Now, let’s see how we keep this data fast and efficient.

⚡ 2. OPTIMIZE Command: Rewriting for Performance

🔹 What Is OPTIMIZE?

OPTIMIZE rewrites small files into larger ones, reducing file fragmentation. This process is often referred to as file compaction.

🔹 Why Is This Important?

When queries scan too many small files, Spark incurs high IO overhead and task scheduling latency. OPTIMIZE helps by coalescing data into fewer large files.

🔹 Basic Syntax

OPTIMIZE delta.`/mnt/data/sales`;

Or if using a managed Delta table:

OPTIMIZE sales_table;

🔹 When to Use

After frequent MERGE, INSERT, or UPDATE operations.
Periodically on large fact tables.
On streaming tables that accumulate micro-batches.

🔹 Best Practices

Schedule OPTIMIZE during low-usage hours.
Avoid running it too frequently (daily or weekly is often enough).
Monitor file size distribution before and after using:

DESCRIBE DETAIL sales_table;

🧭 3. ZORDER: Smart Data Clustering

🔹 What Is ZORDER?

ZORDER BY is used with OPTIMIZE to physically cluster data based on specified columns, improving data skipping during queries.

🔹 How It Works

ZORDER arranges data files to colocate rows with similar values of the Z-ordered columns, allowing Delta Lake to prune unnecessary files during reads.

🔹 Example Syntax

OPTIMIZE sales_table
ZORDER BY (customer_id, date);

🔹 When to Use ZORDER

On dimension columns commonly filtered in WHERE clauses.
For range scans like BETWEEN, >=, IN, etc.

🔹 ZORDER vs Partitioning

Feature	ZORDER	Partitioning
Type	Physical data co-location	Directory-level organization
Ideal for	Frequently filtered columns	High-cardinality grouping
Combine with	`OPTIMIZE`	`CREATE TABLE PARTITIONED BY`

Use both for best results. For example, partition by date and ZORDER by customer_id.

🧹 4. VACUUM: Cleaning Up Old Files

🔹 What Is VACUUM?

VACUUM deletes old versions of files (i.e., not referenced by Delta log) and frees up storage.

🔹 Why Is This Necessary?

Delta Lake retains transaction history for time travel, which leads to stale file accumulation over time.

🔹 Syntax

VACUUM sales_table RETAIN 168 HOURS;  -- Default is 7 days

🔹 Key Points

Minimum retention is 7 days (168 hours) unless you override with:

SET spark.databricks.delta.retentionDurationCheck.enabled = false;

You must ensure no readers need the historical data before vacuuming.

🔹 Best Practices

Schedule weekly or biweekly VACUUM jobs.
Monitor storage trends to determine appropriate retention duration.
Don’t vacuum too aggressively—retain at least 7 days if running time travel queries.

🧠 5. Advanced Tips & Real-World Use Cases

✅ 1. Automating with Jobs

Use Databricks workflows to schedule OPTIMIZE + ZORDER + VACUUM as part of your data hygiene pipeline.

Example flow:

Nightly Job:
   Step 1: OPTIMIZE table ZORDER BY (user_id)
   Step 2: VACUUM table RETAIN 168 HOURS

✅ 2. File Size Target Tuning

By default, files are optimized to ~1GB. You can control it:

SET spark.databricks.delta.optimize.maxFileSize = 134217728;  -- 128 MB

Useful for streaming workloads or small queries.

✅ 3. Using OPTIMIZE WHERE

To limit compaction to a subset of data:

OPTIMIZE sales_table
WHERE date > current_date() - interval 30 days
ZORDER BY (product_id);

This improves performance without touching old partitions, reducing cost.

✅ 4. Monitoring Tools

Use the following tools for observability:

DESCRIBE HISTORY table_name;
Cluster metrics for I/O and scan times
Unity Catalog’s audit logs to track performance over time

🏁 Conclusion

Delta Lake’s OPTIMIZE, ZORDER, and VACUUM commands are essential for ensuring high-performance, scalable data pipelines in Databricks. By understanding and correctly implementing these features:

You reduce cost by minimizing unnecessary reads.
You improve query speed with file compaction and clustering.
You manage storage effectively by cleaning up stale files.

As your data grows, these practices shift from optional tuning to absolute necessities. Automate them, monitor results, and you’ll ensure a future-proof, blazing-fast data lakehouse.

💬 Have Questions or a Use Case to Share?

Leave a comment below or reach out—we’d love to hear how you’re using OPTIMIZE, ZORDER, and VACUUM in your projects!

Optimize, ZORDER, and Vacuum in Databricks: What You Must Know