OPTIMIZE, Z-ORDER, Liquid Clustering and VACUUM

Small files

Streaming and frequent small batches create many small files, which makes every read slow.

OPTIMIZE silver.events;                       -- compact into larger files
OPTIMIZE silver.events WHERE event_date >= current_date() - 7;

Z-ORDER: co-locate related values

OPTIMIZE silver.events ZORDER BY (customer_id);

Z-ordering arranges data so that rows with similar values of the chosen columns share files. File-level min/max statistics then let queries that filter on customer_id skip most files.

Liquid clustering

On recent Databricks runtimes, liquid clustering replaces partitioning plus Z-ORDER for new tables:

CREATE TABLE silver.events (…) CLUSTER BY (event_date, customer_id);

Clustering keys can be changed later without rewriting the table layout by hand.

VACUUM: remove old files

Delta keeps old file versions for time travel. VACUUM deletes files no longer referenced by any version within the retention period:

VACUUM silver.events RETAIN 168 HOURS;   -- 7 days, the default threshold