Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 36 ยท Delta Lake

OPTIMIZE, VACUUM, Clustering and Schema Evolution

Keep Delta tables fast and tidy: compact small files with OPTIMIZE, clean up with VACUUM, cluster data with Z-ORDER or liquid clustering, and evolve schemas safely.

A freshly written Delta table is usually healthy. After months of streaming inserts, small updates and deletes it is not: thousands of tiny files, old unused files filling storage and a schema that has to keep changing. Delta gives you maintenance commands for this. Learning them is the difference between a pipeline that runs fine for a week and one that stays fast for years.

The small-files problem

Every write creates files. A stream that commits every few seconds creates many files of a few kilobytes. Each file costs an open, a metadata read and a task, so a table with a million tiny files is slow even if it holds little data. The target is files of roughly 128 MB to 1 GB.

OPTIMIZE rewrites many small files into fewer large ones (compaction). It runs in its own transaction, so readers are not disturbed, and the old files are removed from the log (not yet deleted from disk).

SQL
OPTIMIZE main.sales.orders;
OPTIMIZE main.sales.orders WHERE order_date >= '2024-05-01';   -- only recent data

In Unity Catalog managed tables, predictive optimization can run OPTIMIZE and VACUUM for you automatically when enabled; check your workspace settings before scheduling your own.

Z-ORDER and liquid clustering

Queries that filter on a column are fast when matching rows are stored together, so Delta can skip whole files using their min/max statistics. Two tools arrange data this way:

SQL
-- classic: rewrite files sorted along a space-filling curve on several columns
OPTIMIZE main.sales.orders ZORDER BY (customer_id);

-- modern: declare clustering once; OPTIMIZE then maintains it incrementally
CREATE TABLE main.sales.events (event_id BIGINT, user_id BIGINT, ts TIMESTAMP)
CLUSTER BY (user_id, ts);

ALTER TABLE main.sales.orders CLUSTER BY (customer_id);   -- change it later

Liquid clustering replaces both Hive-style partitioning and Z-ORDER for most new tables: you can change clustering keys without rewriting everything, and it avoids too many partitions. Choose columns you filter and join on often. Classic partitioning is still fine for large tables where you always filter on a low-cardinality column such as a date, but over-partitioning (a column with thousands of values) creates tiny files.

VACUUM: delete unreferenced files

Compaction, updates and deletes leave old files that the current table no longer uses. They stay on disk for time travel and for readers still using older snapshots. VACUUM deletes those files once they are older than the retention period (default 7 days).

SQL
VACUUM main.sales.orders;                   -- default retention
VACUUM main.sales.orders DRY RUN;           -- list what would be deleted

Run DRY RUN first if unsure. Do not set the retention below the length of your longest-running query or stream, or you may delete files they still read. A weekly OPTIMIZE plus VACUUM is a common baseline for busy tables.

Schema enforcement and evolution

By default Delta enforces the schema: appending a DataFrame whose columns do not match fails rather than silently corrupting the table. That is a feature. When the change is intentional, you evolve the schema.

Python
# add new columns found in the DataFrame (read-only Databricks code)
(new_df.write
   .mode("append")
   .option("mergeSchema", "true")
   .saveAsTable("main.sales.orders"))
SQL
ALTER TABLE main.sales.orders ADD COLUMNS (coupon STRING COMMENT 'promo code used');
ALTER TABLE main.sales.orders ALTER COLUMN amount COMMENT 'order total in EUR';
ALTER TABLE main.sales.orders RENAME COLUMN coupon TO promo_code;   -- needs column mapping
ALTER TABLE main.sales.orders DROP COLUMN promo_code;               -- needs column mapping
ALTER TABLE main.sales.orders SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name');

Rules of thumb:

  • Adding a column is cheap and safe; old rows get NULL.
  • Renaming and dropping need column mapping so Delta tracks columns by id instead of by name; it raises the table protocol version.
  • Changing a type to a wider one (int to long) is supported in some cases; otherwise rewrite the table with overwriteSchema.
  • For MERGE, automatic evolution is enabled with spark.databricks.delta.schema.autoMerge.enabled; enable it deliberately.

Stand-in: compaction

The stand-in compacts small "files" of rows into files of a target size, and prints how the count of files falls. It follows the idea of OPTIMIZE, with simplified sizes.

Python Stand-in: OPTIMIZE compacts small files

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second stand-in demonstrates data skipping. Each file stores min and max of a column; a filter skips every file whose range cannot match. Sorting the data first (the effect of Z-ORDER or clustering) makes far more files skippable.

Python Stand-in: data skipping with and without clustering

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A maintenance plan

  1. Let predictive optimization manage managed tables if available.
  2. Otherwise schedule OPTIMIZE (daily or weekly) and VACUUM (weekly).
  3. Choose liquid clustering keys from your common filters.
  4. Review table health with DESCRIBE DETAIL (number of files, average size).
  5. Evolve schemas through reviewed changes, not accidental mergeSchema everywhere.

Common mistakes

  • Running VACUUM with a retention shorter than your longest query or stream.
  • Partitioning by a high-cardinality column such as user_id.
  • Turning on automatic schema merging globally and letting a typo create a new column.
  • Running OPTIMIZE on a tiny table where it brings no benefit.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does OPTIMIZE do?

    OPTIMIZE rewrites small files into fewer large files. VACUUM is the command that deletes unreferenced files.
  2. 2. Why does clustering data by a column speed up filters on it?

    Min/max statistics per file allow data skipping, which works best when similar values are stored together.
  3. 3. What is the default VACUUM retention period?

    Files unreferenced for more than 7 days are eligible for deletion by default.
  4. 4. What happens when you append a DataFrame with an extra column without mergeSchema?

    Schema enforcement rejects mismatched writes; use mergeSchema or ALTER TABLE for intentional changes.

Summary

  • OPTIMIZE compacts small files; VACUUM removes unreferenced files after the retention period.
  • Liquid clustering (or Z-ORDER) groups related rows so queries can skip files.
  • Delta enforces schemas; evolve them on purpose with ALTER TABLE or mergeSchema.
  • Schedule maintenance, or let predictive optimization handle managed tables.

Next: Notebooks and Magic Commands