Launch Deal · 50% off 1st month ·

3.5 — Optimize a Lakehouse Table

DP-700 Domain 3 · Skill 3.5

What You Need to Know

Delta tables slow down as they collect many small files, deleted rows that are still stored and old file versions. Table maintenance keeps reads fast and storage lean. Each command fixes a different problem.

The maintenance toolbox

Command or feature What it does Use it when
OPTIMIZE (bin compaction) Rewrites many small Parquet files into fewer large ones (around 1 GB) Frequent small appends, streaming writes or many MERGEs have left thousands of small files
OPTIMIZE ... VORDER Compacts and applies V-Order (sorting, encoding and compression tuned for Fabric readers) The table is read heavily by Power BI Direct Lake or the SQL endpoint
OPTIMIZE ... ZORDER BY (col) Co-locates rows with similar values in a column so that file skipping works Queries often filter on a column that has many distinct values (CustomerID, DeviceID)
Liquid clustering (CLUSTER BY) Flexible clustering that's maintained incrementally, without fixed partitions A newer alternative to partitioning plus Z-order for tables whose query patterns change
VACUUM Deletes files that no current table version references and that are older than the retention period (7 days by default) You need to reclaim storage. Shorter retention limits time travel; Fabric blocks retention under 7 days unless you turn off the safety check
Partitioning (partitionBy("Year")) Splits the table into folders by a column with few distinct values Very large tables almost always filtered on that column. Don't partition small tables or high-cardinality columns
OPTIMIZE sales_fact VORDER;
OPTIMIZE telemetry ZORDER BY (DeviceId);
VACUUM sales_fact RETAIN 168 HOURS;   -- 7 days

V-Order specifics

  • V-Order adds about 15% write time. In return it can make reads much faster and give up to 50% better compression.
  • In new workspaces, V-Order is off by default for Spark writes (spark.sql.parquet.vorder.default = false, the writeHeavy resource profile). You can turn it on per session, per table (delta.parquet.vorder.enabled) or per write, or switch to the readHeavyForPBI profile.
  • Changing the table property affects future writes only. Run OPTIMIZE ... VORDER to rewrite existing files.

Preventing small files in the first place

  • Optimize write (spark.databricks.delta.optimizeWrite.enabled) combines output into larger files when it writes, which helps with partitioned tables and frequent appends.
  • Auto compaction compacts small files after each write.
  • Batch your writes. Appending a few rows every few seconds from a notebook creates thousands of tiny files.

How to run maintenance

  • Ad hoc: in Lakehouse explorer, use the table's Maintenance menu (Optimize with optional V-Order, Vacuum, and deletion-vector cleanup).
  • Scheduled: the pipeline Lakehouse Maintenance activity (preview), a scheduled notebook, or the lakehouse REST API. A typical example is a weekly VACUUM after the load.
  • Maintenance shows up in the Monitor hub as a TableMaintenance job.

Key Concept: OPTIMIZE combines small files. VORDER improves the layout for readers. ZORDER helps filtering. VACUUM removes files no longer referenced by the Delta log. Partition only by columns with few distinct values on large tables.

Exam Traps

  • Running VACUUM to fix small files (VACUUM deletes unreferenced files; OPTIMIZE compacts)
  • Running OPTIMIZE to free storage from old versions (that's VACUUM, after OPTIMIZE has replaced the files)
  • Partitioning by a column with very many distinct values, such as CustomerID or a timestamp
  • Assuming V-Order is on for Spark writes in a new workspace
  • Setting VACUUM retention to 0 hours while readers might still use older versions

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.