Delta Lake table maintenance: OPTIMIZE, VACUUM and clustering

#Why tables slow down

Streaming and frequent small batch writes create thousands of small Parquet files. Every query has to open and list them all, and each UPDATE/MERGE leaves old file versions behind. Two jobs fix this: compaction (OPTIMIZE) and cleanup (VACUUM).

#Check table health

DESCRIBE DETAIL sales.orders;          -- numFiles, sizeInBytes, clusteringColumns
DESCRIBE HISTORY sales.orders LIMIT 20; -- who wrote what, operationMetrics

Divide sizeInBytes by numFiles. If the average file is well under about 100 MB on a large table, it needs compaction.

#OPTIMIZE: compact small files

OPTIMIZE sales.orders;
OPTIMIZE sales.orders WHERE order_date >= current_date() - INTERVAL 7 DAYS;  -- only recent partitions

For new writes, turn on automatic small-file handling:

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

#Z-ORDER vs liquid clustering

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

ALTER TABLE sales.orders CLUSTER BY (customer_id);   -- convert an existing unpartitioned table
OPTIMIZE sales.orders;                               -- clusters the data

Choose clustering columns from your most common filter columns, with no more than about 4.

#VACUUM: remove old files

VACUUM sales.orders DRY RUN;          -- list what would be deleted
VACUUM sales.orders;                  -- default retention: 7 days (168 hours)

Caution

Don't lower retention below 7 days unless you're sure nothing reads older versions. A short RETAIN breaks time travel and can make long-running readers or streaming jobs fail with missing files. Delta blocks it unless you disable spark.databricks.delta.retentionDurationCheck.enabled. Leave that check on.

Retention settings on the table:

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

#Schedule it

• Databricks with Unity Catalog managed tables: turn on predictive optimization and let the platform decide when to run OPTIMIZE and VACUUM.
• Fabric: use Maintenance on the Lakehouse table, or a notebook in a scheduled pipeline.
• Elsewhere: a nightly job that runs OPTIMIZE on recently changed tables and a weekly VACUUM.

#Measure the effect

Before and after maintenance, record numFiles, average file size, and the runtime of two or three representative queries. If those numbers don't improve, the clustering keys don't match how the table is queried.