Optimizing Delta Table Performance in a Fabric Lakehouse: V-Order, OPTIMIZE, VACUUM, and Z-Order for Faster Queries and Smaller Storage

Introduction

You've built the lakehouse. Data is flowing in through pipelines, notebooks are transforming records from bronze to silver to gold, and your Power BI reports are connecting via the SQL analytics endpoint. Then someone runs a query against a 500-million-row fact table and it takes 47 seconds. A product manager notices the lakehouse is consuming 800 GB of storage for what should be a 200 GB dataset. Your Direct Lake semantic model is falling back to DirectQuery mode because framing is failing. These are not edge cases — they are the completely normal consequences of skipping Delta table optimization, and they bite every team eventually.

Delta Lake is not a magic black box that performs well automatically. Under the hood, it's a collection of Parquet files governed by a transaction log, and the shape of those files — their size, their count, their internal sort order, and their encoding — determines almost everything about query performance and storage cost. Microsoft Fabric adds a layer on top of standard Delta Lake called V-Order, a Parquet write optimization that dramatically changes the economics of read performance. But V-Order is only one piece. You also need to understand file compaction with OPTIMIZE, data skipping with Z-Order, and storage reclamation with VACUUM. Together, these four mechanisms form a complete performance management system for Delta tables in a Fabric Lakehouse.

By the end of this lesson, you will be able to diagnose performance problems in Delta tables using the transaction log and file statistics, apply V-Order writes correctly in Spark and SQL, compact fragmented files with OPTIMIZE and tune its behavior, implement multi-dimensional data skipping with Z-Order clustering, and safely reclaim storage with VACUUM without breaking time travel or downstream consumers. You'll also understand how each of these optimizations interacts with Direct Lake mode in Power BI and when to apply them in a production pipeline.

What you'll learn:

• How Delta Lake's file layout affects query performance and why small files are so destructive at scale
• What V-Order is, how it works at the Parquet encoding level, and when Fabric applies it automatically versus when you need to intervene
• How to use OPTIMIZE to compact files and when to schedule it in your ingestion pipeline
• How Z-Order clustering creates multi-dimensional data skipping statistics that Spark can exploit
• How VACUUM works, what it deletes, and how to configure retention safely for production environments
• How all four optimizations interact with Direct Lake mode and the SQL analytics endpoint

---

Prerequisites

Before working through this lesson, you should be comfortable with:

• Creating and querying Delta tables in a Fabric Lakehouse — if you need a foundation, start with Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint
• Writing PySpark in Fabric notebooks — see Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables if you need a refresher
• Basic understanding of how OneLake stores Delta tables as Parquet files — OneLake Explained: One Copy of Data, Delta Tables, and Shortcuts covers this well

You'll also benefit from having a lakehouse with at least a few Delta tables loaded with real data — a fact table with tens of millions of rows will make the before/after comparisons in this lesson more meaningful.

---

The Small File Problem and Why It Destroys Query Performance

To understand why OPTIMIZE exists, you need to understand what happens to a Delta table during continuous ingestion. Every time Spark writes data — whether from a streaming job, a Copy activity in a pipeline, or a notebook — it produces one Parquet file per Spark partition per write operation. If you're ingesting hourly batches of 50,000 rows and your Spark job has 200 partitions, each hourly load creates up to 200 tiny Parquet files. After a month, that one fact table has accumulated roughly 144,000 files, many of them containing only a few hundred rows.

This is the small file problem, and it compounds in two distinct ways. First, there's the metadata cost: when Spark plans a query against a Delta table, it reads the Delta transaction log to discover all active files, then reads the footer metadata of each Parquet file to determine whether it can be skipped. With 144,000 files, that metadata scan alone can take seconds before a single row of actual data is read. Second, there's the I/O cost: even if statistics allow Spark to skip many files, the remaining files are still tiny, meaning you get poor vectorized read throughput from the Parquet reader and poor compression ratios because compression works better on larger blocks.

The Delta transaction log lives in the _delta_log directory of each table. You can inspect it directly in a notebook to see what's happening:

from delta.tables import DeltaTable

# Point at your lakehouse table
dt = DeltaTable.forName(spark, "sales_fact")

# Get file count and size statistics
detail = dt.detail().collect()[0]
print(f"Table name: {detail['name']}")
print(f"Number of files: {detail['numFiles']}")
print(f"Size in bytes: {detail['sizeInBytes']}")
print(f"Avg file size: {detail['sizeInBytes'] / detail['numFiles'] / 1024 / 1024:.2f} MB")

If your average file size comes back as 2–5 MB on a table that should be well-optimized, you have a small file problem. The target for compacted Parquet files in a Fabric Lakehouse is typically 128–256 MB per file, though 512 MB files are reasonable for very large tables with selective filter queries.

You can also inspect the history of the table to see how many write operations have accumulated:

dt.history(20).select("version", "timestamp", "operation", "operationMetrics").show(truncate=False)

This will show you the last 20 operations — appends, merges, schema changes — along with metrics like how many files were added and removed in each version. If you see dozens of small append operations with numOutputFiles in the hundreds, you're looking at a fragmented table that needs compaction.

---

V-Order: Fabric's Parquet Write Optimization

V-Order is a Microsoft-developed write-time optimization for Parquet files. It's applied during the Parquet encoding step, which means it happens before the data hits storage and doesn't require any additional post-processing step. Understanding what V-Order actually does — not just that it "makes things faster" — is essential for knowing when you need it and when you can skip it.

What V-Order Does at the Encoding Level

Standard Parquet writing encodes column data in row groups (the large vertical partitions of a Parquet file, typically 128 MB each) and pages (smaller sub-divisions within a row group, typically 1 MB). V-Order applies additional sorting and encoding optimizations within each row group:

1. Vertical encoding sort: Rows within a row group are sorted by a heuristically chosen key (usually high-cardinality columns) before encoding. This isn't user-visible as a sort order for queries, but it improves run-length encoding (RLE) compression significantly because similar values end up adjacent to each other.

2. Dictionary encoding optimization: V-Order aggressively uses dictionary encoding for string columns and applies compression in a way that maximizes the effectiveness of the CPU's dictionary cache. This is why V-Order files tend to be smaller than standard Parquet files — not dramatically smaller, but meaningfully so (typically 10–30% smaller on analytical workloads).

3. Page-level statistics: V-Order writes tighter min/max statistics at the Parquet page level, which allows the Parquet reader to skip pages within a row group, not just entire files. This is significant for analytical queries that filter on high-cardinality columns.

The net result is that V-Order files are read significantly faster by the Fabric Parquet reader (the Microsoft-optimized reader used in both Spark and the SQL analytics endpoint). Microsoft's benchmarks show 2–5× faster query times on V-Order files compared to standard Parquet, and this tracks with real-world observations on analytical workloads.

When Fabric Applies V-Order Automatically

In a Fabric Lakehouse, V-Order is enabled by default for all Spark write operations. You can verify this in a notebook:

# Check the current V-Order setting
print(spark.conf.get("spark.sql.parquet.vorder.enabled"))
# Should return "true" for Fabric Spark sessions

This means that when you write a DataFrame to a Delta table using standard PySpark syntax, V-Order is applied automatically:

# V-Order is applied here automatically in Fabric Spark
df.write.format("delta").mode("append").saveAsTable("gold.sales_fact")

OPTIMIZE also applies V-Order when it rewrites files, which is one of the reasons OPTIMIZE can actually make your table smaller even when you already have large files — it re-encodes existing non-V-Order Parquet files with V-Order compression.

When V-Order Is Not Applied

There are several situations where you won't get V-Order writes:

• External tools writing to OneLake: If you're using an external Spark cluster, Azure Databricks, or a non-Fabric pipeline to write data to a lakehouse Delta table, V-Order will not be applied unless that external tool has the V-Order library configured.
• Data arriving via shortcuts: Tables accessed via OneLake shortcuts to ADLS Gen2 or other sources are not re-encoded with V-Order — the files are read as-is. See Using OneLake Shortcuts to Query Data in ADLS Gen2 and Amazon S3 Without Copying It for important context on this.
• Streaming writes with V-Order disabled: Some configurations explicitly disable V-Order for streaming writes to reduce write latency at the expense of read performance.

If you have a table written by an external tool, you can bring it into V-Order by running OPTIMIZE on it in a Fabric Spark notebook — OPTIMIZE will rewrite all files with V-Order encoding enabled.

Disabling V-Order When It Hurts You

V-Order adds CPU overhead at write time. For streaming workloads where write latency matters — say, you're writing sensor data to a Delta table every few seconds — the additional encoding cost can cause write latency to spike. In these cases, you can disable V-Order for a specific write:

# Disable V-Order for a specific write operation
spark.conf.set("spark.sql.parquet.vorder.enabled", "false")

streaming_df.write.format("delta").mode("append").saveAsTable("bronze.sensor_events")

# Re-enable for subsequent operations
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")

The trade-off is explicit: you get lower write latency but higher query latency. The right approach for streaming tables is usually to disable V-Order on writes and then run OPTIMIZE periodically to compact and re-encode the accumulated files.

---

OPTIMIZE: File Compaction Done Right

OPTIMIZE is the command that compacts many small Parquet files into fewer, larger ones. It's conceptually simple but has a lot of nuance in how it decides which files to compact, how large the output files should be, and how it interacts with partitioning.

Basic OPTIMIZE Syntax

You can run OPTIMIZE from either a SQL cell or using the DeltaTable API in PySpark:

-- In a SQL cell or via spark.sql()
OPTIMIZE sales_fact;

# Using the DeltaTable API
from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "gold.sales_fact")
dt.optimize().executeCompaction()

OPTIMIZE reads all active Parquet files in the table (or partition, if you specify one), combines their contents into fewer, larger files, and writes those new files back to the Delta table. The old files are then marked as removed in the transaction log but kept on disk until VACUUM cleans them up. This is an important point: OPTIMIZE does not shrink your storage immediately. The old files continue to exist until VACUUM runs.

Target File Size

By default, OPTIMIZE targets 128 MB output files. You can override this at the session or table level:

# Set target file size to 256 MB for this session
spark.conf.set("spark.microsoft.delta.optimizeWrite.targetFileSize", 268435456)  # 256 MB in bytes

Or as a table property:

ALTER TABLE gold.sales_fact 
SET TBLPROPERTIES ('delta.targetFileSize' = '268435456');

Larger target file sizes (256–512 MB) are beneficial when:

• Your queries use selective filters that rely on data skipping — fewer, larger files mean fewer files to open but each file contributes more rows per data skip
• You have very wide tables with many columns — larger files pack better
• Your queries are heavily sequential (full scans) — 512 MB files mean fewer S3/ADLS round trips

Smaller target file sizes (64–128 MB) are better when:

• Queries are highly selective and you expect data skipping to eliminate most files
• You're doing frequent merges (MERGE INTO) and small files mean fewer rows to rewrite per merge conflict
• Your table has high partition cardinality and you want each partition to remain manageable

Selective OPTIMIZE with Partition Predicates

On a large partitioned table, running OPTIMIZE on the entire table every night is expensive — it will try to compact every partition even if most partitions haven't changed. Use a WHERE clause to target only recently modified partitions:

-- Only optimize partitions for the current month
OPTIMIZE gold.sales_fact WHERE order_date >= '2024-11-01';

# PySpark equivalent with partition predicate
dt.optimize().where("order_date >= '2024-11-01'").executeCompaction()

This pattern works naturally with a medallion architecture where your gold layer receives daily incremental loads. After each daily load, OPTIMIZE only the partition that was just written. You might also run a full OPTIMIZE weekly or monthly to catch any accumulated fragmentation across older partitions.

For Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers, a practical pipeline pattern is:

1. Load daily incremental data into the gold table (appending or merging)
2. Immediately run OPTIMIZE with a WHERE clause targeting today's partition
3. Skip VACUUM in the daily run (weekly cadence is fine for VACUUM)
4. Run Z-Order as part of the OPTIMIZE call (discussed in the next section)

Auto-Optimize and Optimized Write

Fabric Spark also supports optimizeWrite, which is a write-time optimization that adjusts the number of output files before writing, rather than compacting afterwards. It works by coalescing shuffle partitions to target the configured file size:

ALTER TABLE gold.sales_fact 
SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

Or at session level:

spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")

Optimized Write is particularly valuable for streaming workloads and frequent small batch loads — it reduces the number of small files created in the first place, which means OPTIMIZE has less work to do later. The trade-off is slightly higher write latency because Spark needs to do an additional shuffle to coalesce partitions.

---

Z-Order: Multi-Dimensional Data Skipping

Z-Order is where things get genuinely sophisticated. It's a technique for organizing data within Parquet files so that rows with similar values on multiple dimensions end up in the same files — enabling Spark's query planner to skip entire files when a query filters on those dimensions.

Understanding Data Skipping Without Z-Order

Delta Lake maintains per-file statistics in the transaction log: for each active Parquet file, it records the minimum and maximum value of each column (up to 32 columns by default). When you run a query with a WHERE clause, Spark's Delta reader checks these min/max statistics and skips any file where the filter condition could not possibly match.

For example, if you have a transaction_date column and each file contains data from a specific day (because the table is partitioned by date), the min/max statistics will tightly bound each file to a single day, and a query for transaction_date = '2024-11-15' will skip all files except those in that day's partition.

But what about columns that aren't the partition key? Say your sales_fact table is partitioned by order_date, but your most common query pattern filters by both order_date AND customer_region. Without Z-Order, the data within each date partition is written in whatever order the source produced it — rows from all customer regions interleaved randomly. Every file in the partition will have customer_region statistics that span all regions, so no files can be skipped based on the customer_region filter. Spark has to read every file in the partition to answer the query.

How Z-Order Solves This

Z-Order uses a space-filling curve (the Z-order curve, also called Morton code) to map multi-dimensional data into a single-dimensional sort order that preserves locality across all dimensions simultaneously. In practice, this means: rows with similar values on all the Z-Order columns end up adjacent to each other in the output files.

For a table Z-Ordered on (customer_region, product_category), files will tend to contain:

• File 1: Northeast region, Electronics and Books
• File 2: Northeast region, Clothing and Home
• File 3: Southwest region, Electronics and Books
• ... and so on

Now, a query filtering for customer_region = 'Northeast' AND product_category = 'Electronics' can skip Files 2 and 3 entirely based on the min/max statistics that Delta maintains.

The key insight is that this is approximate, not exact. Z-Order doesn't perfectly cluster data — it's a best-effort approximation that degrades as you add more columns to the Z-Order definition. Two or three columns is typically the sweet spot; beyond four or five columns, the clustering benefit diminishes significantly.

Running OPTIMIZE with Z-Order

Z-Order is applied as part of OPTIMIZE — you can't Z-Order without also compacting files:

-- Compact files AND apply Z-Order clustering
OPTIMIZE gold.sales_fact
ZORDER BY (customer_region, product_category);

# PySpark equivalent
dt.optimize().executeZOrderBy(["customer_region", "product_category"])

With a partition predicate:

OPTIMIZE gold.sales_fact
WHERE order_date >= '2024-11-01'
ZORDER BY (customer_region, product_category);

The OPTIMIZE command will sort all data in scope by the Z-Order key, then write it into the target file size. This means the first run of OPTIMIZE with Z-Order on a large table will be expensive — it needs to read and rewrite the entire table (or partition). Subsequent runs are incremental: Delta tracks which files were already Z-Ordered and only rewrites files that have been added since the last OPTIMIZE run.

Choosing Z-Order Columns

The choice of Z-Order columns is a performance design decision that should be driven by your actual query patterns. Here's the framework:

Good Z-Order candidates:

• High-cardinality columns that appear frequently in WHERE clauses (e.g., customer_id, product_sku, store_id)
• Columns that appear in JOIN conditions (especially when the table is the probe side)
• Columns used in GROUP BY that are also filtered (e.g., customer_segment in a query that also filters by segment)

Poor Z-Order candidates:

• Your partition column — it's already providing file-level clustering, Z-Ordering on it is redundant
• Boolean or very low-cardinality columns — min/max statistics already handle these effectively without Z-Order
• Columns with significant NULL proportions — NULLs don't contribute to clustering effectiveness
• Columns that appear in aggregations but never in WHERE clauses

The trade-off with multiple columns: Z-Order on two columns typically gives you 60–80% of the clustering effectiveness of Z-Order on one column for each of those individual columns. As you add a third column, you're diluting the clustering effect across three dimensions. By the time you get to four or five columns, the effectiveness per column has dropped significantly and you might be better served by a different strategy (like creating separate materialized tables or using Liquid Clustering, discussed below).

Verifying Z-Order Effectiveness

After running OPTIMIZE with Z-Order, you can verify the effectiveness by examining the file statistics in the transaction log:

# Read the Delta log to inspect file-level statistics
import json

log_path = "abfss://your-workspace@onelake.dfs.fabric.microsoft.com/your-lakehouse.Lakehouse/Tables/gold/sales_fact/_delta_log/"

# Read the most recent checkpoint or log file
# For simplicity, use the DeltaTable API
dt = DeltaTable.forName(spark, "gold.sales_fact")
files_df = spark.read.format("delta").load(
    "abfss://your-workspace@onelake.dfs.fabric.microsoft.com/your-lakehouse.Lakehouse/Tables/gold/sales_fact/"
)
print(f"Total files after OPTIMIZE: {dt.detail().collect()[0]['numFiles']}")

The more practical test is simply to run a representative query before and after OPTIMIZE + Z-Order and compare the job metrics. In the Spark UI (accessible from the monitoring hub while the job runs), look at the "number of files pruned" metric in the Scan task — after Z-Order, this should increase significantly for queries that filter on your Z-Order columns.

---

Liquid Clustering: The Next Evolution Beyond Z-Order

It's worth knowing that Delta Lake and Fabric are evolving toward a newer clustering approach called Liquid Clustering, which addresses some of Z-Order's limitations. Liquid Clustering is an incremental, adaptive clustering approach that:

• Doesn't require you to rewrite the entire table when cluster columns change
• Handles evolving data distributions better (Z-Order degrades as new data arrives with different distributions)
• Can be defined as a table property and applied automatically on writes

As of late 2024, Liquid Clustering is available in Fabric Spark as a preview feature. The syntax is:

-- Define clustering when creating the table
CREATE TABLE gold.sales_fact (
    order_id BIGINT,
    order_date DATE,
    customer_id BIGINT,
    product_sku STRING,
    revenue DECIMAL(18,2)
) USING DELTA
CLUSTER BY (customer_id, product_sku);

-- Or add clustering to an existing table
ALTER TABLE gold.sales_fact
CLUSTER BY (customer_id, product_sku);

-- Then run OPTIMIZE to apply the clustering (no ZORDER BY needed)
OPTIMIZE gold.sales_fact;

You cannot combine Liquid Clustering with Z-Order — they're mutually exclusive clustering strategies. If you define CLUSTER BY on a table, OPTIMIZE will use Liquid Clustering automatically. For new tables in Fabric where you expect the data distribution or query patterns to evolve, Liquid Clustering is worth considering over Z-Order. For established tables with stable query patterns, Z-Order is still perfectly valid and better understood.

---

VACUUM: Reclaiming Storage Without Breaking Time Travel

Every OPTIMIZE run, every UPDATE, every DELETE, every MERGE — all of these operations mark old Parquet files as removed in the Delta transaction log but leave the actual files on disk. This is intentional: it supports time travel (querying a previous version of the table) and atomic operations (if a write fails partway through, the old files are still intact).

Over time, these "tombstoned" files can accumulate significantly. It's not unusual to see a Delta table where the live data is 150 GB but the total storage consumed (including all tombstoned versions) is 450 GB. VACUUM is the command that deletes these orphaned files and reclaims the storage.

VACUUM Retention Period

VACUUM has a configurable retention period — the minimum age of a file before it's eligible for deletion. The default is 7 days (168 hours). This means:

• Files written more than 7 days ago that have been superseded will be deleted
• Files written within the last 7 days that have been superseded will be kept (time travel back to any point in the last 7 days remains available)

-- Check what VACUUM would delete without actually deleting it
VACUUM gold.sales_fact DRY RUN;

-- Run VACUUM with the default 7-day retention
VACUUM gold.sales_fact;

-- Run VACUUM with a custom retention period (e.g., 30 days)
VACUUM gold.sales_fact RETAIN 720 HOURS;

The retention period choice has real operational consequences:

Retention	Storage Cost	Time Travel Available	Risk
168 hours (7 days, default)	Moderate	7 days back	Low
720 hours (30 days)	High	30 days back	Very low
0 hours	Minimal	None	High
24 hours	Low	1 day back	Moderate

Overriding the Safety Check (and When Not To)

Delta Lake includes a safety check that throws an error if you try to VACUUM with a retention period below 7 days. You can override it:

spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")

This configuration key works in Fabric Spark as well. The scenario where you legitimately need this is development/test environments where you want to aggressively reclaim storage after testing OPTIMIZE runs. In production, leave the safety check enabled. The 7-day minimum exists for a reason.

VACUUM and Direct Lake Mode

Direct Lake mode in Power BI works by directly reading the Delta table's Parquet files through OneLake, bypassing the import/export cycle. This means the semantic model is reading specific Parquet files referenced by a particular Delta table version. When you run VACUUM, you need to ensure those files haven't been made obsolete during an ongoing query.

The practical guidance is: Direct Lake mode re-frames (re-reads the file list from the Delta log) on each semantic model refresh. As long as you run VACUUM after the semantic model has refreshed and you don't vacuum files from the current or recent versions, you're safe. The default 7-day retention gives you substantial headroom. For more on how Direct Lake interacts with your Delta table file layout, see Connecting a Power BI Semantic Model to a Fabric Lakehouse in Direct Lake Mode: Creating, Refreshing, and Optimizing Delta Tables for Reporting.

---

How These Optimizations Interact with the SQL Analytics Endpoint

The SQL analytics endpoint lets you query lakehouse Delta tables using T-SQL without a warehouse. As described in Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse, this endpoint is served by a serverless SQL engine that reads the same Delta files as Spark.

The good news: V-Order, OPTIMIZE, and Z-Order all benefit T-SQL queries through the SQL analytics endpoint, not just Spark queries. The SQL engine is Microsoft's Parquet reader, which is the same one that benefits from V-Order encoding. File compaction reduces metadata overhead for the SQL engine's query planner. Z-Order statistics are respected by the SQL engine's pushdown predicates.

The nuance: the SQL analytics endpoint uses column-level statistics differently than Spark. Spark exploits Z-Order statistics through the Delta metadata reader; the SQL analytics endpoint uses a slightly different path that still benefits from tighter file-level statistics but may not exploit multi-column Z-Order as aggressively as Spark. In practice, you'll still see meaningful improvement in SQL analytics endpoint query times after OPTIMIZE + Z-Order, but Spark direct reads will typically see the largest gains.

---

Hands-On Exercise: Optimizing a Fragmented Sales Fact Table

Let's work through a complete optimization scenario. We'll simulate a fragmented table, measure the problem, apply all four optimizations, and measure the improvement.

Step 1: Create and Fragment a Test Table

Open a new Spark notebook in your Fabric lakehouse and run the following to create a realistic sales fact table with intentional fragmentation:

from pyspark.sql import functions as F
from pyspark.sql.types import *
import random

# Generate a realistic sales dataset
spark.conf.set("spark.sql.shuffle.partitions", "200")

# Create base data - 10 million rows
orders = spark.range(0, 10_000_000).select(
    F.col("id").alias("order_id"),
    (F.rand() * 1000).cast("long").alias("customer_id"),
    F.date_add(F.lit("2024-01-01"), (F.rand() * 365).cast("int")).alias("order_date"),
    F.when(F.rand() < 0.25, "Northeast")
     .when(F.rand() < 0.5, "Southeast") 
     .when(F.rand() < 0.75, "Midwest")
     .otherwise("West").alias("customer_region"),
    F.when(F.rand() < 0.3, "Electronics")
     .when(F.rand() < 0.6, "Clothing")
     .when(F.rand() < 0.8, "Books")
     .otherwise("Home").alias("product_category"),
    (F.rand() * 500 + 10).cast("decimal(10,2)").alias("revenue"),
    (F.rand() * 5 + 1).cast("int").alias("quantity")
)

# Write in 50 small batches to simulate fragmentation
# This is what happens with incremental loads over time
for i in range(50):
    batch = orders.filter(F.col("order_id") % 50 == i)
    batch.write.format("delta").mode("append").saveAsTable("sales_fact_test")

print("Fragmented table created.")

Step 2: Measure the Baseline

from delta.tables import DeltaTable
import time

dt = DeltaTable.forName(spark, "sales_fact_test")
detail = dt.detail().collect()[0]

print(f"File count: {detail['numFiles']}")
print(f"Total size: {detail['sizeInBytes'] / 1024 / 1024:.1f} MB")
print(f"Avg file size: {detail['sizeInBytes'] / detail['numFiles'] / 1024 / 1024:.2f} MB")

# Baseline query - filter by region and category
start = time.time()
result = spark.sql("""
    SELECT customer_region, product_category, 
           SUM(revenue) as total_revenue, COUNT(*) as order_count
    FROM sales_fact_test
    WHERE customer_region = 'Northeast' 
      AND product_category = 'Electronics'
      AND order_date BETWEEN '2024-06-01' AND '2024-08-31'
    GROUP BY customer_region, product_category
""").collect()
baseline_time = time.time() - start
print(f"\nBaseline query time: {baseline_time:.2f} seconds")

Step 3: Apply OPTIMIZE with Z-Order

# Run OPTIMIZE with Z-Order on our analysis columns
print("Running OPTIMIZE with Z-Order...")
start = time.time()

spark.sql("""
    OPTIMIZE sales_fact_test
    ZORDER BY (customer_region, product_category)
""")

optimize_time = time.time() - start
print(f"OPTIMIZE completed in {optimize_time:.1f} seconds")

# Check new file stats
detail = dt.detail().collect()[0]
print(f"\nAfter OPTIMIZE:")
print(f"File count: {detail['numFiles']}")
print(f"Total size: {detail['sizeInBytes'] / 1024 / 1024:.1f} MB (including old files)")
print(f"Avg file size: {detail['sizeInBytes'] / detail['numFiles'] / 1024 / 1024:.2f} MB")

Step 4: Measure Query Improvement

# Same query after optimization
start = time.time()
result = spark.sql("""
    SELECT customer_region, product_category, 
           SUM(revenue) as total_revenue, COUNT(*) as order_count
    FROM sales_fact_test
    WHERE customer_region = 'Northeast' 
      AND product_category = 'Electronics'
      AND order_date BETWEEN '2024-06-01' AND '2024-08-31'
    GROUP BY customer_region, product_category
""").collect()
optimized_time = time.time() - start

print(f"Baseline query time: {baseline_time:.2f} seconds")
print(f"Optimized query time: {optimized_time:.2f} seconds")
print(f"Improvement: {baseline_time / optimized_time:.1f}x faster")

Step 5: Run VACUUM to Reclaim Storage

# First, do a dry run to see what would be deleted
print("VACUUM dry run:")
spark.sql("VACUUM sales_fact_test DRY RUN").show(truncate=False)

# Then run the actual VACUUM (default 7-day retention)
print("\nRunning VACUUM...")
spark.sql("VACUUM sales_fact_test")

# Check actual storage after VACUUM
detail = dt.detail().collect()[0]
print(f"\nAfter VACUUM:")
print(f"File count: {detail['numFiles']}")
print(f"Actual storage: {detail['sizeInBytes'] / 1024 / 1024:.1f} MB")

Step 6: Automate the Optimization Pattern

Here's a reusable function for production use that combines all the optimizations:

def optimize_lakehouse_table(
    table_name: str,
    zorder_columns: list = None,
    partition_predicate: str = None,
    run_vacuum: bool = True,
    vacuum_hours: int = 168
):
    """
    Comprehensive Delta table optimization for Fabric Lakehouse.
    
    Args:
        table_name: Fully qualified table name (e.g., 'gold.sales_fact')
        zorder_columns: Columns to Z-Order by (optional, 2-3 recommended)
        partition_predicate: WHERE clause to scope OPTIMIZE (e.g., "order_date >= '2024-11-01'")
        run_vacuum: Whether to run VACUUM after OPTIMIZE
        vacuum_hours: Retention period for VACUUM in hours (default 168 = 7 days)
    """
    import time
    
    dt = DeltaTable.forName(spark, table_name)
    
    # Get pre-optimization stats
    pre_detail = dt.detail().collect()[0]
    print(f"[{table_name}] Pre-OPTIMIZE: {pre_detail['numFiles']} files, "
          f"{pre_detail['sizeInBytes']/1024/1024:.1f} MB")
    
    # Build and run OPTIMIZE command
    optimize_sql = f"OPTIMIZE {table_name}"
    if partition_predicate:
        optimize_sql += f" WHERE {partition_predicate}"
    if zorder_columns:
        optimize_sql += f" ZORDER BY ({', '.join(zorder_columns)})"
    
    start = time.time()
    spark.sql(optimize_sql)
    optimize_duration = time.time() - start
    print(f"[{table_name}] OPTIMIZE completed in {optimize_duration:.1f}s")
    
    # Run VACUUM if requested
    if run_vacuum:
        spark.sql(f"VACUUM {table_name} RETAIN {vacuum_hours} HOURS")
        print(f"[{table_name}] VACUUM completed (retained {vacuum_hours} hours)")
    
    # Get post-optimization stats
    post_detail = dt.detail().collect()[0]
    print(f"[{table_name}] Post-optimization: {post_detail['numFiles']} files, "
          f"{post_detail['sizeInBytes']/1024/1024:.1f} MB")
    
    return {
        "table": table_name,
        "pre_files": pre_detail['numFiles'],
        "post_files": post_detail['numFiles'],
        "pre_size_mb": pre_detail['sizeInBytes'] / 1024 / 1024,
        "post_size_mb": post_detail['sizeInBytes'] / 1024 / 1024,
        "optimize_duration_s": optimize_duration
    }

# Example usage
result = optimize_lakehouse_table(
    table_name="sales_fact_test",
    zorder_columns=["customer_region", "product_category"],
    partition_predicate="order_date >= '2024-11-01'",
    run_vacuum=False  # Run VACUUM separately on a weekly schedule
)
print(result)

---

Common Mistakes & Troubleshooting

Mistake 1: Running OPTIMIZE on Every Write in a Pipeline

Some teams add OPTIMIZE immediately after every incremental load in their pipeline. This seems thorough but is actually counterproductive. OPTIMIZE rewrites files, which costs Spark CU time and adds to your Fabric capacity consumption. If you run it after every 5-minute micro-batch, you're spending enormous resources on a rewrite cycle that produces diminishing returns.

Better approach: Use optimizeWrite at the session level to reduce small file creation during writes, then run OPTIMIZE on a scheduled cadence (daily for high-frequency tables, after each significant batch for lower-frequency tables). Consider using the Scheduling and Automating Fabric Data Pipeline Runs with Activity-Level Retries, Alerts, and Email Notifications capabilities to schedule optimization as a separate, periodic pipeline step.

Mistake 2: Z-Ordering on the Partition Column

If your table is partitioned by order_date, then Z-Ordering by order_date is entirely redundant — the partition already ensures that all files in a given partition have the same order_date value, so the min/max statistics are perfectly tight without Z-Order. Worse, including the partition column in Z-Order wastes one of your "effective Z-Order slots" without providing any benefit.

Fix: Never include partition columns in your ZORDER BY clause. Z-Order is for filtering within partitions.

Mistake 3: Too Many Z-Order Columns

Running OPTIMIZE ... ZORDER BY (customer_id, product_sku, store_id, order_channel, customer_segment) with five columns will produce files that are mediocrely clustered on all five dimensions but not especially well clustered on any single dimension. The clustering effectiveness degrades roughly as O(1/n) for n columns.

Fix: Identify your top 2–3 filter columns by query frequency. If you genuinely need to optimize for 5+ filter dimensions, look at Liquid Clustering or consider whether pre-aggregated materialized tables for common query patterns are more appropriate.

Mistake 4: VACUUM Deleting Files Needed by Active Readers

If you have a streaming Spark job reading your Delta table using readStream, or a Direct Lake semantic model with an active query in flight, running VACUUM can potentially remove files that those consumers hold references to.

Fix: For streaming readers, ensure Delta's ignoreChanges or ignoreDeletes options are configured appropriately, and schedule VACUUM during low-activity windows. For Direct Lake models, coordinate VACUUM with the semantic model refresh schedule.

Mistake 5: Forgetting That OPTIMIZE Counts as a Table Version

Every OPTIMIZE run creates a new entry in the Delta transaction log. This matters for downstream systems that use readChanges on the Delta log or that use DESCRIBE HISTORY to track data lineage. Don't be alarmed if you see your table version jump dramatically after an OPTIMIZE run on a fragmented table — that's expected behavior. Each OPTIMIZE is atomic; it either completes fully or rolls back cleanly.

Troubleshooting: OPTIMIZE Runs But File Count Doesn't Decrease Much

If OPTIMIZE reports completing but your file count barely changes, check:

1. Are the files already at or above the target file size? OPTIMIZE won't split large files and won't compact files that are already at the target size. Run dt.detail() to check average file size.

2. Is the table heavily partitioned with small data per partition? OPTIMIZE operates within partition boundaries, so if each partition has only a few files, there may be nothing to compact.

3. Is there a table property overriding the target file size? Check with DESCRIBE DETAIL your_table and look at the properties field.

Troubleshooting: V-Order Not Applied After External Write

Run OPTIMIZE on the table after the external write to re-encode files with V-Order. You can confirm V-Order is being applied by checking the Parquet file metadata:

# Check if V-Order is enabled in the session
print(spark.conf.get("spark.sql.parquet.vorder.enabled"))

# After OPTIMIZE, new files will be V-Order encoded
# You can verify by reading a file's Parquet metadata
import pyarrow.parquet as pq
# The metadata won't directly expose V-Order as a label,
# but file sizes will be smaller and read performance will be faster

---

Summary & Next Steps

Delta table optimization in a Fabric Lakehouse isn't a one-time task — it's an ongoing operational practice that needs to be built into your data pipeline design from the start. Here's what you now understand:

V-Order is Fabric's Parquet write optimization that produces smaller, faster-to-read files. It's on by default in Fabric Spark sessions, but you need to be conscious of it for external writes and streaming workloads. OPTIMIZE applies V-Order retroactively when it rewrites files.

OPTIMIZE compacts many small Parquet files into fewer, larger ones. Run it on a scheduled cadence — not after every write. Use partition predicates to scope it to recently changed data. Target 128–256 MB output files for most analytical workloads.

Z-Order organizes data within files so that rows with similar values on multiple dimensions are co-located, enabling Delta's data skipping to eliminate files based on multi-column filter predicates. Two to three columns is the sweet spot. Never Z-Order on partition columns.

VACUUM deletes the tombstoned Parquet files that OPTIMIZE and DML operations leave behind. The default 7-day retention is the right choice for production — it balances storage cost against time travel capability and safety for active readers.

Together, these four mechanisms can reduce query times by 3–10× and storage costs by 30–60% on tables that arrive from continuous incremental ingestion.

Where to go next:

• If you're connecting Power BI in Direct Lake mode, the file layout optimizations you've learned here directly affect framing performance and fallback behavior — see Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery to understand how Direct Lake consumes your optimized Delta files.
• If you're loading data incrementally via pipelines, optimizing the write pattern (using optimizeWrite, proper partition design) reduces the work OPTIMIZE needs to do — Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities covers the ingestion side of this equation.
• To understand how your optimization jobs are consuming Fabric capacity, familiarize yourself with the monitoring tools available at Monitoring Fabric Capacity Usage and Pipeline Activity with the Monitoring Hub.