OPTIMIZE, Z-ORDER, clustering and VACUUM

Frequent incremental loads (every 5 minutes, via streaming) produce small files. Reading 10,000 files of 1 MB costs far more than reading 10 files of 1 GB: each file is an open, a read of the Parquet footer and an entry in the log.

OPTIMIZE: compact

OPTIMIZE silver.orders;
OPTIMIZE silver.orders WHERE order_date >= '2024-06-01';

It merges small files into larger ones. It is a transaction like any other: anyone reading during the compaction keeps seeing the previous version.

Data skipping and Z-ORDER

Delta stores the minimum and maximum of each column per file (by default, for the first 32 columns). A filter WHERE customer_id = 42 skips every file whose range does not contain 42. This only works well if similar values are in the same files.

OPTIMIZE silver.orders ZORDER BY (customer_id);

ZORDER BY rewrites the data, bringing rows with nearby values in the chosen columns closer together. Choose columns used in filters and with many distinct values (for a column with few values, partitioning already solves it).

Liquid clustering

For new tables, Databricks recommends *liquid clustering* instead of partitioning and Z-ORDER: the clustering keys can change without rewriting the table, and OPTIMIZE clusters incrementally.

CREATE TABLE silver.events (id STRING, customer_id BIGINT, ts TIMESTAMP)
CLUSTER BY (customer_id);

VACUUM: delete what no version uses

OPTIMIZE, UPDATE, DELETE and MERGE leave old files on disk. VACUUM deletes the ones that are no longer referenced by the current version and are older than the retention (default 7 days).

VACUUM silver.orders;                    -- default retention
VACUUM silver.orders RETAIN 168 HOURS;   -- the same, explicit
VACUUM silver.orders DRY RUN;            -- only lists what it would delete

• Shortening the retention below the default can delete a file that a long read or a stream still uses; Delta refuses unless a safety setting is deliberately turned off.
• After VACUUM, time travel to before the retention stops working.
• On tables managed by Unity Catalog, *predictive optimization* can run OPTIMIZE and VACUUM on its own.