Delta Lake Table Maintenance on Databricks: OPTIMIZE, Z-ORDER vs Liquid Clustering, and VACUUM

Most Delta tables don't get slow because of one bad query. They get slow because nobody looks after them. Streaming jobs and frequent small batch writes leave thousands of tiny files behind, deletes and updates leave old files sitting in storage, and the data layout drifts away from how people actually filter it.

This post covers the three maintenance tools you'll use on Azure Databricks, which also come up a lot in data engineering interviews: OPTIMIZE (compaction), data layout with Z-ORDER or liquid clustering, and VACUUM. For each one I'll explain what it does, show code, and point out the trap that catches people.

1. First, measure: is this table actually unhealthy?

Don't run maintenance blindly. DESCRIBE DETAIL tells you how many files a table has and how big it is:

detail = spark.sql("DESCRIBE DETAIL main.sales.orders").select(
    "numFiles", "sizeInBytes", "partitionColumns", "clusteringColumns"
).first()

avg_mb = detail.sizeInBytes / max(detail.numFiles, 1) / 1024 / 1024
print(f"files={detail.numFiles}, avg file size={avg_mb:.1f} MB")

If a table of a few hundred GB has tens of thousands of files averaging a few MB each, you have a small-file problem. Every file means extra listing, opening, and footer-reading work for each query, and Spark ends up scheduling far more tasks than it needs.

DESCRIBE HISTORY is the other quick check. It shows which operations write to the table and how often:

(spark.sql("DESCRIBE HISTORY main.sales.orders")
     .select("version", "timestamp", "operation", "operationMetrics")
     .show(10, truncate=False))

A wall of STREAMING UPDATE or WRITE entries every minute, each adding a handful of files, is the classic cause.

2. OPTIMIZE: compacting small files

OPTIMIZE rewrites small files into larger ones. It's a transactional operation, so readers keep seeing a consistent snapshot while it runs.

# SQL
spark.sql("OPTIMIZE main.sales.orders")

# Only compact recent partitions on a partitioned table
spark.sql("OPTIMIZE main.sales.orders WHERE order_date >= '2026-10-01'")

The same thing with the Python DeltaTable API:

from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "main.sales.orders")
metrics = dt.optimize().executeCompaction()
metrics.select("metrics.numFilesAdded", "metrics.numFilesRemoved").show(truncate=False)

Things worth knowing:

• The WHERE clause only works on partition columns for a partitioned table. It's how you avoid recompacting years of cold data every night.
• OPTIMIZE is idempotent. Running it again on an already-compacted table does very little work, so scheduling it daily is cheap once the table is in good shape.
• Prevent the problem at write time too. Optimized writes and auto compaction reduce how many small files get created in the first place:

ALTER TABLE main.sales.orders SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

On Unity Catalog managed tables, predictive optimization can run OPTIMIZE and VACUUM for you. It's worth checking whether it's enabled before you build your own maintenance jobs.

3. Data layout: Z-ORDER vs liquid clustering

Compaction fixes file count. Layout fixes which rows live together, and that's what makes data skipping work. Delta stores min/max statistics per file; if rows for customer_id = 42 are spread across every file, no file can be skipped.

Z-ORDER (the older approach)

OPTIMIZE main.sales.orders ZORDER BY (customer_id, product_id);

Z-ORDER co-locates related values across multiple columns. Its weaknesses:

• It isn't incremental. Re-running it rewrites a lot of data, which gets expensive on big tables.
• The columns aren't stored with the table. If someone forgets the ZORDER BY clause in the next job, the layout quietly degrades.
• It stacks on top of partitioning, so you still have to choose partition columns up front, and get them right.

Liquid clustering (the current recommendation)

Liquid clustering replaces both partitioning and Z-ORDER for new tables. You declare clustering keys on the table itself:

CREATE TABLE main.sales.orders (
  order_id     BIGINT,
  customer_id  BIGINT,
  product_id   BIGINT,
  order_date   DATE,
  amount       DECIMAL(12, 2)
)
CLUSTER BY (customer_id, order_date);

Or from PySpark:

(df.write
   .format("delta")
   .clusterBy("customer_id", "order_date")
   .saveAsTable("main.sales.orders"))

Then a plain OPTIMIZE clusters the data, incrementally, touching only data that isn't clustered yet:

OPTIMIZE main.sales.orders;

Why interviewers like this topic:

• You can change the keys without rewriting the table. ALTER TABLE main.sales.orders CLUSTER BY (product_id) changes the keys used for future clustering. Existing data is left alone until you run OPTIMIZE ... FULL (on recent runtimes) to recluster everything.
• No high-cardinality partition explosion. Clustering on customer_id is fine; partitioning on it would create millions of directories.
• It can't be combined with partitioning or Z-ORDER on the same table. That's a common gotcha when migrating.
• Keep the key count small. Databricks allows up to four clustering columns, and fewer keys usually work better. Pick the columns that actually appear in filters.

On recent runtimes with predictive optimization you can use CLUSTER BY AUTO and let Databricks pick keys from your query history.

How to choose keys: look at the WHERE clauses and join keys in your heaviest queries. Columns used in equality and range filters are good candidates. A column nobody filters on is wasted effort, no matter how "important" it seems.

4. VACUUM: reclaiming storage without breaking time travel

Delta never deletes data files during an update, delete, merge, or OPTIMIZE. It marks them as removed in the transaction log, and they stay in storage. That's what makes time travel work, and it's also why storage costs creep up.

VACUUM physically deletes files that are no longer referenced by any table version within the retention window (7 days by default).

-- See what would be deleted first
VACUUM main.sales.orders DRY RUN;

-- Delete files older than the default retention (7 days)
VACUUM main.sales.orders;

-- Explicit retention in hours
VACUUM main.sales.orders RETAIN 168 HOURS;

from delta.tables import DeltaTable

DeltaTable.forName(spark, "main.sales.orders").vacuum(168)  # hours

The traps:

1. VACUUM limits time travel. After vacuuming with a 7-day retention, SELECT * FROM t VERSION AS OF <older version> fails if that version needs deleted files. If the business needs 30 days of time travel, set the retention to match:

   ALTER TABLE main.sales.orders SET TBLPROPERTIES (
     'delta.deletedFileRetentionDuration' = 'interval 30 days',
     'delta.logRetentionDuration'         = 'interval 30 days'
   );

1. Don't disable the safety check casually. Setting spark.databricks.delta.retentionDurationCheck.enabled = false to run RETAIN 0 HOURS can delete files that a long-running query or a lagging streaming job still needs. That's how people corrupt readers in production.

2. VACUUM doesn't shrink the transaction log. Log cleanup is controlled separately by delta.logRetentionDuration (30 days by default) and happens automatically at checkpoints.

3. Deletion vectors change the picture slightly. With deletion vectors enabled, a DELETE or UPDATE can mark rows as deleted without rewriting the file. The old rows physically stay until the file is rewritten. If you need them gone for compliance, run REORG TABLE main.sales.orders APPLY (PURGE) and then VACUUM.

5. A simple maintenance job

If you aren't using predictive optimization, a nightly job like this covers most tables:

from delta.tables import DeltaTable

TABLES = ["main.sales.orders", "main.sales.order_items"]

for name in TABLES:
    dt = DeltaTable.forName(spark, name)

    before = spark.sql(f"DESCRIBE DETAIL {name}").first().numFiles
    dt.optimize().executeCompaction()   # also clusters liquid-clustered tables
    after = spark.sql(f"DESCRIBE DETAIL {name}").first().numFiles

    dt.vacuum()                          # uses the table's retention setting
    print(f"{name}: files {before} -> {after}")

Run it on a job cluster, outside peak hours, and stagger it away from heavy writers to avoid concurrent-write conflicts.

Interview-ready summary

• Small files: diagnose with DESCRIBE DETAIL, fix with OPTIMIZE, prevent with optimized writes and auto compaction.
• Layout: prefer liquid clustering for new tables; Z-ORDER is the legacy option and isn't incremental.
• VACUUM: deletes unreferenced files older than the retention window (7 days by default), and that window is also your time-travel limit.
• Safety: never drop the retention check to clean up faster unless you're certain no reader or stream needs the old files.

If you're preparing for Databricks interviews, I put together a free 10-question sample with worked answers on topics like these: free sample. The full interview pack has more if you find it useful.