4.3.2. Lakehouse Table Optimization (OPTIMIZE, V-Order)

💡 First Principle: Delta Lake accumulates small files during streaming and incremental writes. File consolidation and V-Order optimization dramatically improve query performance—like defragmenting a hard drive.

Scenario: A Delta table received millions of small writes from streaming ingestion. Queries that once took 10 seconds now take 5 minutes because Spark must open thousands of small files.

OPTIMIZE Command

• Purpose: Consolidate small files into larger ones
• Benefit: Reduce file count, improve query performance
• Frequency: Schedule regularly for streaming tables

-- Consolidate small files
OPTIMIZE lakehouse.sales;

-- Optimize with Z-ordering on frequently filtered columns
OPTIMIZE lakehouse.sales ZORDER BY (region, date);

V-Order

• Concept: Microsoft's columnar format optimization for Parquet
• Benefit: Better compression and faster reads
• Implementation: A write-time optimization; files remain standard Parquet. Disabled by default in newly created workspaces (spark.sql.parquet.vorder.default = false)—enable it per session, per table (delta.parquet.vorder.enabled), per write, through a read-heavy resource profile, or with OPTIMIZE ... VORDER
• Why Power BI cares: V-Order's sorting and encoding suit the VertiPaq engine, so Direct Lake semantic models (and the SQL analytics endpoint) read V-Ordered files faster

-- Apply V-Order during optimization
OPTIMIZE lakehouse.sales VORDER;

Visual: File Optimization Impact

⚠️ Exam Trap: Never running OPTIMIZE on streaming tables guarantees performance degradation. Small file accumulation is inevitable with streaming—schedule OPTIMIZE regularly (hourly or daily depending on write volume).

VACUUM: reclaiming storage

• OPTIMIZE (like UPDATE, MERGE, and DELETE) writes new files; the files it replaces stay on storage, still referenced by older table versions—so storage temporarily grows after OPTIMIZE.
• VACUUM permanently deletes files that are no longer referenced by the Delta log and are older than the retention threshold—default 7 days (168 hours). It never compacts or rewrites files; files still inside the window survive, which is why storage may not drop right away.
• Retention is given in hours: VACUUM sales RETAIN 336 HOURS keeps 14 days of time travel. Values under 7 days are blocked by the safety check (spark.databricks.delta.retentionDurationCheck.enabled); RETAIN 0 HOURS wipes time-travel history and can break concurrent readers and writers still using older files.
• Maintenance order: confirm the small-file problem → OPTIMIZE (optionally ZORDER/VORDER) → VACUUM → measure and schedule.

Time travel and RESTORE work only while the older files still exist—inside the VACUUM retention window.

Z-order, auto compaction, and partitioning

• ZORDER BY (cols) colocates rows with similar values in the same files so filters on those columns skip whole files (data skipping). It is not an index, and it only describes the current layout: running OPTIMIZE ... ZORDER BY on different columns rewrites the layout for the new columns, and the old co-location is lost.
• Auto compaction (table property delta.autoOptimize.autoCompact, or spark.databricks.delta.autoCompact.enabled for the session) compacts right after writes that leave too many small files—Microsoft's recommended default for streaming and micro-batch tables. It doesn't Z-order, so still schedule OPTIMIZE ... ZORDER BY in a quiet window when you need clustering.
• Partition on a low-cardinality column that queries filter on (e.g., the month of OrderDate): queries prune partitions, and maintenance can target recent ones (OPTIMIZE sales WHERE OrderMonth >= '2025-06' ZORDER BY (ProductCategory)). Never partition on a high-cardinality column (IDs, timestamps)—it creates huge numbers of tiny files.