Microsoft Fabric Delta Optimization: Liquid Clustering & V-Order

A decision framework for data engineers, analytics engineers, and Fabric architects who need results, not marketing.

Why This Matters

Most Delta optimization problems in Fabric come from applying the right lever to the wrong workload. A team enables V-Order on a Bronze ingestion table and wonders why writes got slower. Another team runs Z-Order on a Liquid-Clustered table and hits an error. A third team enables everything, everywhere, and silently burns capacity units.

Microsoft Learn documents each lever. This article covers which lever belongs on which table, and why.

The Philosophy Behind Delta Optimization

Every optimization in this article follows from three principles. Once they click, the individual settings stop feeling arbitrary.

Principle 1: Writes and reads are not symmetric

A Delta table in an enterprise Lakehouse is typically written a handful of times and read thousands of times. Every optimization shifts cost between those two sides, so the deciding number is the table's read-to-write ratio. A Bronze table with one daily full scan does not deserve the treatment of a Gold dimension feeding a Direct Lake model queried thousands of times an hour.

Principle 2: The consumer determines the layout

Spark, the SQL analytics endpoint, the Warehouse, Direct Lake semantic models, KQL databases, and external tools all read the same Parquet files, each in its own way. V-Order pays off for Direct Lake and gives Spark and the SQL analytics endpoint too little to justify its write cost. Liquid Clustering helps Spark and SQL file skipping but cannot coexist with partitioning. Deletion vectors speed up deletes and add a small read overhead.

So start from one question: "who is the primary consumer of this table?" In medallion terms, Bronze is read by Spark, Silver by a mix, and Gold by analytical engines.

Principle 3: Defaults are the most important feature

Runtime 2.0 turns on adaptive file sizing, deletion vectors, and Low Shuffle Merge without you asking, and they absorb most write-time tuning. What remains is picking profiles, placing V-Order, switching on auto compaction, scheduling VACUUM, and not over-tuning.

The rule that follows from all three

When in doubt, trust the defaults, change one lever at a time, and measure. If you find yourself reaching for a sixth or seventh lever before the first five are correctly applied, the problem is almost certainly elsewhere: schema design, partitioning strategy, or pipeline architecture.

Lever 1: Resource Profiles

A resource profile applies a tested set of Spark and Delta settings (V-Order, Optimize Write, target file size) from a single property. Fabric ships three predefined profiles plus custom.

The workspace Spark settings page also has an Optimize for your use case wizard: you pick a medallion layer or task type, a data volume, and a CU ceiling, and it recommends a Spark pool and environment configuration. Under the hood, the profile is still the value of spark.fabric.resourceProfile.

The three profiles

Values from Microsoft's profile configuration table. Only readHeavyForPBI turns V-Order on.

New Fabric workspaces default to writeHeavy. This is the right default for ingestion but the wrong default for a Gold workspace. Many teams never change it, and their Direct Lake queries pay the price.

How to set it

At environment level (recommended for consistency across a workspace):

spark.fabric.resourceProfile = readHeavyForPBI

At runtime, for a specific notebook:

spark.conf.set("spark.fabric.resourceProfile", "readHeavyForSpark")

Runtime configuration takes precedence over environment configuration.

Medallion-to-profile mapping

Lever 2: V-Order

V-Order is a write-time Parquet optimization. Its value depends entirely on who reads the table.

Cost and benefit

Microsoft puts the write cost at "often around 15% on average", with read gains that vary by workload. The cross-workload guidance recommends it for one consumer: Direct Lake. It says not to enable V-Order solely for Spark or SQL analytics endpoint performance.

Decision rule

How to enable it

Session-level, for all writes in the current notebook. This one wins over table properties, even ones set to false:

spark.conf.set("spark.sql.parquet.vorder.default", "true")

Table-level property, persistent across writers:

ALTER TABLE gold.fact_sales
SET TBLPROPERTIES ('delta.parquet.vorder.enabled' = 'true');

Write-level option, per operation:

df.write.format("delta") \
    .option("parquet.vorder.enabled", "true") \
    .saveAsTable("gold.fact_sales")

Changing the property only affects future writes. To V-Order the files already there, run OPTIMIZE gold.fact_sales VORDER.

Row groups matter for Direct Lake too

Direct Lake generally performs best with row groups of 1 million to 16 million rows. Small or uneven row groups increase transcoding work. spark.sql.parquet.native.writer.maxRowGroupRowCount caps rows per row group when the native execution engine writes the files; its default of 0 sets no cap. Set it only after Delta Analyzer shows row groups are the problem.

Lever 3: OPTIMIZE, Z-Order, and Liquid Clustering

These three tools shape physical file layout. Each solves a different problem.

OPTIMIZE

OPTIMIZE silver.fact_sales;

Runtime 2.0 picks a target file size per table with adaptive target file size: 128 MB for tables under 10 GB, scaling to 1 GB for tables past 10 TB. You no longer need to pick a size yourself.

Two settings keep OPTIMIZE from rewriting data it already compacted. File-level compaction targets skip files that met an earlier target, and are on by default in Runtime 2.0. Fast optimize (spark.microsoft.delta.optimize.fast.enabled) skips bins unlikely to produce a healthy file, and is opt-in. On Runtime 1.3, adaptive sizing and file-level targets are opt-in too.

Z-Order

OPTIMIZE silver.fact_sales ZORDER BY (customer_id, sale_date);

Microsoft now advises against partitioning by default, which makes Z-Order a tool for existing partitioned tables whose queries filter selectively on the same columns.

Liquid Clustering

CREATE TABLE silver.dim_customer (
    customer_id INT,
    region STRING,
    signup_date DATE
)
CLUSTER BY (region, signup_date);

-- Existing unpartitioned table
ALTER TABLE silver.orders CLUSTER BY (order_date, region);
OPTIMIZE silver.orders;

For file skipping, Microsoft recommends Liquid Clustering over both partitioning and Z-Order. Clustering columns live in table metadata, so you change them with another ALTER TABLE ... CLUSTER BY. The new layout applies on the next OPTIMIZE; run OPTIMIZE ... FULL once after a key change if you want every existing file reclustered.

Regular writes do not cluster data. OPTIMIZE does. In Runtime 2.0 that is cheap: incremental clustering rewrites only unclustered, small, or deletion-vector-heavy files, so frequent OPTIMIZE and auto compaction are safe on clustered tables. Check the result with the Scala clusteringQuality() method, also new in 2.0.

Lever 4: Defaults and Auto Compaction

Runtime 2.0 turns these on for you:

• Low Shuffle Merge keeps unmodified rows out of the expensive shuffle during MERGE. On in every runtime.
• Adaptive target file size, file-level compaction targets, and incremental Liquid Clustering, covered under Lever 3.
• Deletion vectors let DELETE, UPDATE, and MERGE mark rows as removed instead of rewriting whole Parquet files. Reads pay a minor cost, and OPTIMIZE purges any file where more than 5% of rows are marked.

On Runtime 1.3, everything in that list except Low Shuffle Merge is opt-in.

Optimize Write depends on your profile rather than the runtime: writeHeavy applies it only to partitioned tables, and both read-heavy profiles turn it on. It helps most for partitioned tables, frequent small inserts, and MERGE, UPDATE, or DELETE.

Auto compaction is the one you switch on. After each write it checks for too many small files and, only when needed, runs a synchronous OPTIMIZE. Microsoft recommends it as the default maintenance strategy for Spark-written tables.

# Every new table in this session
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "true")

-- One existing table
ALTER TABLE silver.fact_sales
SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'true');

Because it runs inside the write, tables with strict write-latency targets should use scheduled OPTIMIZE instead. For a table that already has a small-file backlog, run OPTIMIZE once, then turn auto compaction on.

Lever 5: VACUUM

VACUUM removes old files that the Delta log no longer references. The default retention period is seven days.

-- See what would be deleted first
VACUUM gold.fact_sales DRY RUN;

-- Use the default 7-day retention
VACUUM gold.fact_sales;

-- Or specify hours explicitly
VACUUM gold.fact_sales RETAIN 168 HOURS;

-- Runtime 2.0: read the Delta log instead of listing every file
VACUUM gold.fact_sales LITE;

Two practical notes. First, retention below seven days trips a safety check, removes time travel to versions outside the window, and can disrupt concurrent readers or writers. Second, schedule VACUUM in a maintenance notebook separate from your ELT pipelines, after OPTIMIZE, and not inline with ingestion. Mixing VACUUM with active writes is a common source of "file not found" errors in downstream consumers.

Workload Profiles: What Good Looks Like

Small to mid workload (under 10 TB, under 50 tables)

Profile: writeHeavy for Bronze, readHeavyForPBI for Gold.

Maintenance: V-Order on Gold only. Auto compaction on for Spark-written tables, with one OPTIMIZE to clear any existing backlog. VACUUM weekly with seven-day retention. Skip Z-Order unless queries consistently filter on two or more columns.

Example: A SaaS analytics workspace with about 30 tables feeding a Direct Lake model, running exactly this setup with no manual Spark tuning.

Enterprise workload (100+ TB, 500+ tables)

Profile: Per-layer profile assignment, documented in a platform standard.

Maintenance: V-Order on every Gold table that Direct Lake reads, with row groups checked in Delta Analyzer. Liquid Clustering for new Silver and Gold tables on common filter columns. Z-Order retained for partitioned legacy tables. Auto compaction by default; scheduled OPTIMIZE only where write latency is tight. VACUUM automated via scheduled notebooks, split by hot versus cold tables. Statistics refresh on dimension tables after each load.

Example: A metadata-driven ingestion framework with 750 tables across Bronze, Silver, and Gold. Each layer runs in its own workspace with a locked resource profile. Maintenance is orchestrated through a separate operations pipeline that respects capacity smoothing windows.

High-mutation workload (frequent MERGE and DELETE)

Profile: writeHeavy, tuned for merge throughput.

Maintenance: Rely on Low Shuffle Merge and deletion vectors (on by default in Runtime 2.0). Keep auto compaction on; if deletes pile up without creating small files, schedule OPTIMIZE to purge them. Deletion vectors hide rows without removing them, so GDPR-style deletes need REORG TABLE ... APPLY (PURGE) and then VACUUM to physically remove the data. Keep VACUUM retention short only if time travel is not required.

Persona: What to Do Next

Moving from Runtime 1.3 to 2.0

Runtime 2.0 (Spark 4.1, Delta Lake 4.2, Java 21, Python 3.13) is generally available, and Microsoft recommends it for production. It is scheduled to become the default for new workspaces and environments in late September 2026. Existing workspaces switch under Workspace settings > Data Engineering/Science > Spark settings > Environment, or per Environment item.

For Delta optimization, the upgrade is mostly good news: the defaults in Lever 4 switch on, Liquid Clustering becomes incremental, and OPTIMIZE FULL, clusteringQuality(), and VACUUM LITE become available. Four things to check first:

1. Delta 4.x features stay Spark-only. Collations, variant type, and coordinated commits work only in Spark notebooks and job definitions. Type widening is the exception: the SQL analytics endpoint and Direct Lake now read it. Do not enable the others on tables other engines read.
2. Deletion vectors raise the table protocol to reader 3 and writer 7. Fabric's Spark, SQL analytics endpoint, and Direct Lake all handle it. Python notebooks using delta-rs or Polars cannot read those tables; use PySpark, or DuckDB's delta_scan for read-only access.
3. Protocol upgrades are one-way. Once a table's protocol moves up, older readers and writers may lose access, and the change cannot be undone.
4. Libraries need retesting. Fabric migrates your library list, but JARs often break across the Scala, Java, and Spark jump, and the Python version changes from 3.11 to 3.13.

Practical recommendation: Test on one non-production Environment item first, and confirm every downstream reader still works before switching the workspace default.

Closing Notes

Delta optimization in Fabric is less about knowing every feature and more about making a handful of correct decisions consistently. Run on Runtime 2.0. Set the right resource profile per layer. Enable V-Order where Direct Lake reads the table. Let the defaults carry most of the load, and turn on auto compaction. Use Liquid Clustering for new tables, Z-Order for partitioned legacy tables. Schedule VACUUM separately from ELT.

That is the entire framework. Everything else is tuning at the margins.

If you find exceptions to this framework in your own workloads, I would like to hear them. The fastest way to strengthen a practitioner framework is for practitioners to challenge it.