Delta Lake

Delta Table Maintenance in Microsoft Fabric: OPTIMIZE, VACUUM, V-Order and Small Files

Why Fabric Delta tables slow down over time and which maintenance actually helps: diagnosing small files, OPTIMIZE and auto compaction, VACUUM and retention, and when V-Order is worth it.

Par JaviPublié 18 min de lectureIntermédiaire

A Lakehouse table that was fast in its first month is slower in its sixth. The row count only doubled, but queries take five times longer, the SQL analytics endpoint feels sluggish, and the Direct Lake model on top of it has started to stutter. Someone suggests a nightly OPTIMIZE and VACUUM job for every table, and the problem seems to go away. Until the capacity starts filling up at night.

The usual cause is not the amount of data. It is the shape and number of files. A Delta table is Parquet files plus a log, and every write changes that file layout. Thousands of tiny files, fragmented partitions and accumulated history all make the same data more expensive to read and store.

Maintenance fixes that, but only the right maintenance, applied where the evidence says it helps:

  • Not every table needs OPTIMIZE.
  • Bigger files are not always better.
  • VACUUM does not make queries faster.
  • V-Order is not always beneficial.

This article is the production guide: why tables degrade, how to diagnose file health, what OPTIMIZE, auto compaction, VACUUM and V-Order each do, how they interact with MERGE, incremental loads and partitioning, and how to schedule maintenance without a cargo-cult nightly job. It assumes you know how Delta tables work; if not, start with Delta Tables in Microsoft Fabric for SQL Server Engineers.

Fabric behavior was checked against Microsoft Learn in October 2026. Several defaults differ between Spark runtimes, and some between workspaces of different ages. Those differences are called out where they matter.

Why Delta tables need maintenance

Delta’s files are immutable. Every write adds files, and every change replaces files. Over time, that produces four separate kinds of degradation:

  • Append-heavy workloads add new files on every run. Frequent small batches mean many small files.
  • MERGE, UPDATE and DELETE rewrite the files they touch, or mark rows in deletion vectors that readers must apply. Repeated rewrites leave a fragmented layout of uneven file sizes.
  • Repeated incremental loads combine both: small appends plus merges, every hour or every few minutes.
  • Partition fragmentation multiplies the effect: a small write spread across many partitions produces a small file in each.
  • Retained historical files pile up in storage. Files replaced by later commits stay in OneLake until they are cleaned up, so time travel can still read them.

These map to different concerns, and it pays to keep them apart:

Concern Symptom Relevant maintenance
Query performance Reads slower than the data volume justifies Compaction, data organization, sometimes V-Order
File-count health Many small files per table or partition Compaction (OPTIMIZE, auto compaction), better write patterns
Storage cleanup OneLake storage growing faster than the active table VACUUM
Retention and history How far back time travel and recovery must reach VACUUM retention settings

The small-file problem

A “small file” is one much smaller than the table’s target file size. A table of 2 GB stored as 20,000 files of 100 KB each holds the same data as one stored in a handful of large files, but it costs far more to read:

  • File discovery and metadata. Readers reconstruct the list of active files from the Delta log, and every file is an entry with its own statistics. More files mean more log to process and more metadata to plan with.
  • Task scheduling. Spark generally plans work per file or file split. Thousands of tiny files become thousands of tiny tasks, and scheduling overhead dominates the useful work.
  • Open and read overhead. Each file is a separate request to storage with its own fixed cost, no matter how few bytes it contains.
  • Inefficient scans. Small Parquet files compress worse and have small row groups, so column scans are less efficient. That matters especially for Direct Lake, which reads row groups into column segments.

There is no universal “good” file size. In Fabric Spark Runtime 2.0, adaptive target file size is on by default and picks a target per table, between 128 MB and 1 GB, based on the table’s size. In Runtime 1.3 it is opt-in, and OPTIMIZE targets 1 GB by default. The useful question is not “are my files 1 GB?” but “are most files far below the table’s target, and is the file count growing faster than the data?”

How small files are created

Small files are almost always a side effect of how data is written:

  • Many tiny pipeline loads. A copy every five minutes that lands a few hundred rows creates hundreds of files a day.
  • Frequent micro-batches from streaming or near-real-time ingestion.
  • Over-partitioning. Partitioning a modest table by a high-cardinality column, or by day when each day holds a few thousand rows, guarantees at least one small file per partition per write.
  • Repeated MERGE. Each merge rewrites the touched files and writes inserts as new files, so frequent merges leave many partially sized files.
  • Too many concurrent writers, each committing its own small set of files to the same table.
  • One file per tiny logical group, for example writing per customer or per source file in a loop.
  • Overly small Spark partitions. A DataFrame with 200 shuffle partitions of a few rows each writes 200 tiny files.

Many of these are fixed upstream more cheaply than downstream: fewer, larger write batches, sensible partitioning, and repartitioning or optimize write before writing. Optimize write is a Fabric Spark feature that shuffles data before writing to produce fewer, larger files; it is not enabled by default in current runtimes. Compaction cleans up what prevention misses.

Diagnose before maintaining

Look at the table before running anything. Three Fabric-supported tools cover most needs.

DESCRIBE DETAIL reports the table’s active file count and total size. Microsoft’s own guidance uses it to compute an average file size as a first indicator:

PySpark
def file_health(table_name):
    d = spark.sql(f"DESCRIBE DETAIL {table_name}").first()
    files, size = d["numFiles"], d["sizeInBytes"]
    avg_mb = size / files / 1024**2 if files else 0
    print(f"{table_name}: {files} files, {size / 1024**3:.2f} GB, avg {avg_mb:.1f} MB/file")

file_health("sales_orders")
PySpark7 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

DESCRIBE HISTORY shows what has been happening to the table: how often it is written, by which operations, and with what file counts. The PySpark API makes the metrics easy to select:

PySpark
from delta.tables import DeltaTable

(DeltaTable.forName(spark, "sales_orders")
    .history(20)
    .select("version", "timestamp", "operation", "operationMetrics")
    .show(truncate=False))
PySpark6 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

A history full of small WRITE and MERGE commits, each adding a few files, explains a fragmented table better than any single metric.

Files per partition. An average hides skew: one hot partition with thousands of files can look fine next to large compacted partitions. For a partitioned table, a best-effort count of active files per partition folder:

PySpark
from collections import Counter

files = spark.table("sales_orders").inputFiles()   # active files of the current version (best effort)
per_partition = Counter("/".join(p.split("/")[:-1]) for p in files)
for partition, count in per_partition.most_common(10):
    print(count, partition)
PySpark6 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

Avoid diagnosing by listing the table folder in OneLake directly. The folder also contains files that later commits have removed but that VACUUM has not yet deleted, so a raw listing overstates the active file count.

What to look for:

  • File count rising faster than table size.
  • Average and typical file sizes well below the table’s target.
  • A few partitions with many more files than the rest.
  • Frequent small writes or merges in recent history.
  • For Direct Lake: small or uneven row groups. Microsoft’s Delta Analyzer is the tool for that level of detail.

OPTIMIZE

OPTIMIZE is Delta’s compaction command. It groups small files into bins and rewrites each bin as fewer, larger files, then commits the new files and removes the old ones from the active table version.

%%sql
OPTIMIZE sales_orders;
Spark SQL2 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.
from delta.tables import DeltaTable
DeltaTable.forName(spark, "sales_orders").optimize().executeCompaction()

What it does:

  • Reduces the number of active files by rewriting small ones into larger ones, up to the target size.
  • Records what it did: the result and DESCRIBE HISTORY include metrics such as numFilesAdded, numFilesRemoved and the resulting file-size distribution.
  • Purges deletion vectors on files where more than 5% of records are marked deleted, so readers stop paying to apply them.
  • Applies clustering on liquid-clustered tables. Liquid clustering only organizes data when OPTIMIZE (or auto compaction) runs, not on ordinary writes.
  • Can co-locate data with ZORDER BY on selected columns, which improves file skipping for selective filters on those columns.

What it does not do:

  • It does not delete the old files. They stay in OneLake, unreferenced, until VACUUM removes them after the retention period. Compaction temporarily increases storage.
  • It does not fix a bad partitioning scheme. It compacts within partitions. A table partitioned into thousands of tiny partitions stays that way.
  • It does not make an already healthy table faster. Compacting files that are already near the target is pure write cost.

On a partitioned table, you can limit OPTIMIZE to specific partitions with a WHERE clause on partition columns, which is how large tables are usually maintained:

%%sql
-- Only valid when order_date is a partition column of the table
OPTIMIZE sales_orders WHERE order_date >= '2026-10-01';
Spark SQL3 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

Bin compaction

“Bin compaction” is the mechanism behind plain OPTIMIZE. Files below a minimum size are treated as candidates, grouped into bins whose combined size approaches the target, and each bin is rewritten. In Fabric Spark the default minimum and target for OPTIMIZE are both 1 GB (spark.databricks.delta.optimize.minFileSize and maxFileSize), and Runtime 2.0’s adaptive target file size adjusts the target per table.

Two Fabric Spark refinements reduce wasted work, and Microsoft recommends both:

  • Fast optimize (spark.microsoft.delta.optimize.fast.enabled) skips bins that are unlikely to produce a meaningfully better file. This makes repeated OPTIMIZE runs cheaper and closer to idempotent. It is not enabled by default.
  • File-level compaction targets (spark.microsoft.delta.optimize.fileLevelTarget.enabled) stop files that met an earlier target from being rewritten again when the target grows. Microsoft’s pages differ on its default: the compaction page says it is not enabled by default, while the cross-workload guidance lists it among the Runtime 2.0 defaults (and as opt-in in Runtime 1.3). Check the setting in your own session.

Auto compaction: compaction as part of writing

For Spark-written tables, Microsoft recommends auto compaction as the default compaction strategy rather than fixed OPTIMIZE schedules. After each write, it checks the affected partitions for too many small files and, only if needed, runs a compaction synchronously after the commit.

%%sql
ALTER TABLE sales_orders SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'true');
Spark SQL2 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

It can also be enabled for a session with spark.databricks.delta.autoCompact.enabled. By default it triggers when a partition holds at least 50 files below its minimum size, and compacts towards 128 MB files. The trade-off is write latency: the write that triggers compaction takes longer. Where write latency matters more than file health, keep writes lean and schedule OPTIMIZE separately, ideally on separate compute.

Auto compaction applies to Spark writers. Tables written by pipeline Copy activities or Dataflow Gen2 do not get Spark’s write-time compaction; inspect their layout and schedule maintenance for them.

V-Order

V-Order is a Fabric-specific, write-time optimization of the Parquet layout inside each file (sorting, encoding and compression) that can speed up reads, especially for Power BI Direct Lake. Files stay standard, open Parquet.

How it differs from compaction: compaction changes how many files there are and how big they are. V-Order changes how each file is laid out internally. They are independent. OPTIMIZE can apply V-Order to the files it rewrites, but plain compaction does not require it.

The facts that matter:

  • Read benefit: largest for read-heavy consumers such as dashboards and Direct Lake.
  • Write cost: writes take longer; Microsoft quotes around 15% on average.
  • Defaults: V-Order is disabled by default in newly created workspaces (spark.sql.parquet.vorder.default = false). Workspaces created before April 2025 may still have it enabled by default. Check your session rather than assuming.
  • Control: per session, per table (delta.parquet.vorder.enabled), per write, or during compaction with OPTIMIZE ... VORDER. Plain OPTIMIZE inherits the table property or session setting.
%%sql
SET spark.sql.parquet.vorder.default;                              -- check the session setting
ALTER TABLE sales_orders SET TBLPROPERTIES ('delta.parquet.vorder.enabled' = 'true');
OPTIMIZE sales_orders VORDER;                                      -- apply to rewritten files now
Spark SQL4 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

When it is worth considering: Microsoft’s cross-workload guidance is to enable V-Order for Spark-written tables whose primary consumer is Direct Lake, typically Gold tables. It recommends not enabling it solely for Spark or SQL analytics endpoint performance. For write-heavy Bronze and Silver tables, the write cost usually outweighs the benefit. If you turn it on, measure read and write times before and after.

VACUUM

VACUUM permanently deletes data files that are no longer referenced by the current table version and are older than the retention threshold. It is a storage and retention operation, not a performance tuning one. Microsoft’s documentation says so explicitly: VACUUM does not improve query performance by itself.

%%sql
VACUUM sales_orders DRY RUN;          -- list what would be deleted
VACUUM sales_orders;                  -- default retention: 7 days (168 hours)
VACUUM sales_orders RETAIN 336 HOURS; -- keep 14 days of history instead
Spark SQL4 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

The details that matter in Fabric:

  • Default retention is seven days. Retention below seven days triggers Delta’s safety check (spark.databricks.delta.retentionDurationCheck.enabled), and Lakehouse portal and API maintenance requests fail by default for shorter intervals.
  • Time travel and restore depend on it. Once files are vacuumed, versions that needed them can no longer be queried with time travel or restored.
  • Concurrent readers and writers can need older files. A long-running job reading an older snapshot can fail if VACUUM removes its files with a very short retention.
  • Files still referenced through deletion vectors are kept.
  • VACUUM does not delete log files in _delta_log; log cleanup follows checkpointing and log retention separately.
  • VACUUM LITE uses the Delta log instead of a full directory listing and is faster on large tables. It requires Runtime 2.0 or later and enough log history.
  • VACUUM runs in Spark: notebooks, Spark job definitions, and Lakehouse maintenance. It does not run in the SQL analytics endpoint.

The danger is aggressive cleanup. Setting retention to a few hours to “save storage” trades a small storage gain for the loss of time travel, restore and safety for concurrent jobs. Decide how much history you need for debugging, audits and recovery first, then set retention to match.

OPTIMIZE vs VACUUM vs V-Order

OPTIMIZE VACUUM V-Order
Purpose Compact small files; optionally organize data Delete unreferenced files past retention Optimize Parquet layout inside files for reads
Changes active data files? Yes: rewrites them into fewer, larger files No Yes, for files written or rewritten with it
Removes historical files? No: it creates more of them until vacuumed Yes No
Query performance impact Positive when files were fragmented; none when they were healthy None directly Positive for read-heavy consumers, especially Direct Lake
Storage impact Temporarily higher (old and new files) Lower Often better compression
Risk Compute cost and write amplification when overused Lost time travel, restore and reader safety when retention is too short Slower writes; little benefit for write-heavy tables
Typical frequency Evidence-based, or continuous through auto compaction Regular schedule matching retention needs A per-table decision, not a recurring job

Maintenance after MERGE

MERGE is the most common source of fragmented layouts in Silver and Gold tables. Each run rewrites the files it touches, writes inserted rows as new files, and with deletion vectors may mark rows instead of rewriting. Run it hourly against a large table and the file layout drifts away from the target size steadily.

A few principles:

  • Fix the MERGE first. Source deduplication, a match condition that prunes the target, and controlled file sizes reduce how many files each merge touches and creates. Delta Lake MERGE performance patterns covers this. Maintenance cleans up after the MERGE; it is not a substitute for designing it well.
  • Compact after large MERGE windows, not after every MERGE. A backfill, a reprocessing of several months, or a merge touching a large share of the table justifies an OPTIMIZE afterwards. A routine merge that touched a few files does not.
  • Prefer auto compaction or fast optimize for routine merges. They compact when fragmentation crosses a threshold instead of rewriting on every run.
  • Watch deletion vectors. Merges and deletes with deletion vectors are cheaper to write, but readers pay until files are compacted. Periodic OPTIMIZE purges deletion vectors on files that cross the 5% threshold.

Blindly running OPTIMIZE after every MERGE turns one rewrite into two on every run, often on the same files, and spends capacity for little gain.

Maintenance after incremental loads

Incremental loads trade one large write for many small ones. That is the right trade for freshness and cost, but it is exactly the pattern that creates small files:

  • Small daily or hourly windows append or merge a little data each run. Over weeks, that is thousands of small files.
  • Frequent writes to a Bronze table at near-real-time cadence create fragmentation fastest.
  • Compaction cadence should follow the write cadence: auto compaction for frequently written Spark tables, or a scheduled OPTIMIZE after the day’s last batch for tables written a few times a day.
  • Freshness vs file health. More frequent loads mean fresher data and more fragmentation. If the business needs 15-minute freshness, budget for compaction; if it needs daily data, do not load every five minutes.

One Fabric-specific restriction is worth knowing: Dataflow Gen2 Lakehouse destinations that use incremental refresh do not support OPTIMIZE or REORG TABLE. Check the destination’s maintenance limitations before choosing that pattern for a large table.

Partitioning and maintenance

Partitioning and file count are tightly linked:

  • Each partition has its own files. A write that touches 100 partitions writes at least 100 files.
  • Partition pruning lets filters on the partition column skip whole partitions. That helps only if each partition is large enough to hold properly sized files.
  • Compaction works within partitions. If a partition only holds 20 MB of data, OPTIMIZE can at best produce one 20 MB file for it. Over-partitioned tables stay full of small files no matter how often you compact.

Microsoft’s current guidance is to avoid partitioning by default for Lakehouse tables and use it only where a specific requirement justifies it, such as isolating concurrent writers working on disjoint partitions. For better file skipping on recurring filters, liquid clustering (on supported runtimes) or Z-Order on existing partitioned tables are usually better tools. Bad partitioning makes maintenance worse: more files to compact, more partitions to scan, and compaction that cannot reach a healthy size. The Delta Lake partitioning checklist covers when partitioning earns its place.

Large tables vs small tables

Maintenance effort should scale with the table:

  • Tiny tables (reference data, small dimensions, a few thousand rows) rarely need scheduled maintenance. They may be rewritten in full on every load anyway, which produces a fresh layout each time. An OPTIMIZE job for them is mostly scheduling overhead.
  • Medium tables written by Spark are well served by auto compaction plus a regular VACUUM.
  • Very large tables need targeted maintenance: compact the partitions or ranges that recent writes touched, not the whole table every time. On partitioned tables, OPTIMIZE ... WHERE scopes the work. On liquid-clustered tables, incremental OPTIMIZE only reorganizes files that need it. Reserve whole-table rewrites, such as OPTIMIZE FULL after changing clustering keys (Runtime 2.0), for deliberate one-off operations.

Table maintenance in the Fabric UI and pipelines

Fabric offers three ways to run maintenance without writing a notebook. As of October 2026:

  • Lakehouse explorer: the table’s Maintenance action opens a dialog that can run compaction (optionally with V-Order), VACUUM with a retention threshold, and a deletion-vector cleanup. Runs appear in Notifications and in the Monitoring hub as TableMaintenance jobs. This works for Delta tables only.
  • Pipelines: the Lakehouse Maintenance activity runs OPTIMIZE (with optional V-Order) and VACUUM as a pipeline step, so you can chain it after loads. It was in preview at the time of writing.
  • REST API: the Lakehouse API can trigger table maintenance jobs.

These are convenient for ad hoc work and simple schedules. For production, the same operations run from a notebook or Spark job definition are equally valid and easier to make conditional on file health. Do not let a preview UI feature be the only thing keeping a critical table healthy.

Scheduling maintenance

Run maintenance when evidence or write patterns say it is needed, not on a reflexive nightly timer:

  • After major batch windows: once the day’s loads finish, compact the tables they wrote.
  • On a periodic cadence for tables with steady write patterns, set from observed fragmentation rather than habit.
  • Based on observed file health: a scheduled notebook can check DESCRIBE DETAIL metrics and only compact tables or partitions past a threshold you define.
  • After heavy MERGE cycles: backfills, reprocessing, large corrections.
  • VACUUM on its own schedule, typically less often than compaction, aligned with your retention policy.
  • Away from critical reporting: maintenance competes for the same capacity as users and other jobs.

“Run OPTIMIZE on every table every night” is not a maintenance strategy. It compacts tables that do not need it, rewrites healthy files, and uses capacity at the same hour as every other nightly job.

Capacity implications

Maintenance is real work. OPTIMIZE reads and rewrites data, V-Order adds write cost, and even VACUUM has to identify and delete files. In Fabric, these Spark operations consume capacity as background operations, smoothed over 24 hours, alongside your pipelines and refreshes.

Practical consequences:

  • Compaction has a cost proportional to the data it rewrites. Rewriting healthy files is waste.
  • Maintenance competes with production. A fleet of maintenance jobs at 02:00 adds to the same background load as the nightly loads.
  • Scheduling and isolation matter. Stagger maintenance, scope it to what changed, and consider running heavy one-off rewrites when the capacity is quiet or on separate compute.

Microsoft Fabric Capacity Explained covers how to see what maintenance actually costs in the Capacity Metrics app.

A practical maintenance workflow

    1. Inspect file health
    2. Small-file problem?
    3. OPTIMIZEor auto compaction
    4. Monitor and repeat
    1. Storage growing?
    2. Retention allows?
    3. VACUUMwithin retention only
    4. Monitor and repeat
    1. Direct Lake primary consumer?
    2. Measure reads and writes
    3. Enable V-Order
Three separate maintenance decisions. Compaction, cleanup and V-Order are independent decisions. Each starts from evidence, and each ends in monitoring that decides when to act again.
  1. Inspect file health with DESCRIBE DETAIL, history and, for partitioned tables, files per partition.
  2. Small-file problem? If yes, run OPTIMIZE once for the backlog, then prevent recurrence with auto compaction or a scoped, evidence-based schedule. If no, do nothing.
  3. Storage growing and old files no longer needed? Run VACUUM, only within the retention your time-travel and recovery needs allow.
  4. V-Order is a separate, per-table decision: consider it when Direct Lake or other read-heavy consumers dominate, and measure before and after.
  5. Monitor and repeat based on evidence: file counts, history, query times and capacity use.

Before-and-after example

This example fragments a table on purpose, then compacts it. Run it in a sandbox Lakehouse with the lab’s sales_orders.csv in Files/raw/. It overwrites sales_orders.

PySpark
from pyspark.sql import functions as F, Window

# Keep this demo fragmented: no auto compaction in this session
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "false")

orders = (
    spark.read.option("header", True).csv("Files/raw/sales_orders.csv")
    .select("sales_order_id", "customer_id",
            F.to_timestamp("order_timestamp").alias("order_timestamp"),
            "status", F.col("grand_total").cast("decimal(18,2)").alias("grand_total"))
    .withColumn("batch", (F.row_number().over(Window.orderBy("sales_order_id")) - 1) % 50)
)

# 50 tiny writes, one file each: an exaggerated version of frequent small loads
for b in range(50):
    (orders.where(F.col("batch") == b).drop("batch")
        .coalesce(1)
        .write.format("delta")
        .mode("overwrite" if b == 0 else "append")
        .saveAsTable("sales_orders"))

file_health("sales_orders")                      # before
spark.sql("OPTIMIZE sales_orders").show(truncate=False)
file_health("sales_orders")                      # after
PySpark24 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

The expected results, derived from the code rather than measured:

Before OPTIMIZE After OPTIMIZE
Active files (DESCRIBE DETAIL numFiles) 50: one per write, because each batch is coalesced to one file 1: the whole table (250 rows) fits in a single compacted file
Table versions in DESCRIBE HISTORY 50 (one WRITE per batch) 51 (plus one OPTIMIZE)
Files in the table folder 50, plus any files left from an earlier version of sales_orders 51 or more: the 50 old files remain until VACUUM after the retention period

Your exact byte sizes will differ. Timings are deliberately not shown: on 250 rows they are dominated by fixed overheads and say nothing about production tables. The point is the mechanism. File count drops, the old files stay until VACUUM, and the history records exactly what happened. On a real table, compare query times and capacity use before and after rather than relying on file counts alone.

Common mistakes

  • Using VACUUM as a performance command. It cleans up storage. Queries do not get faster.
  • Running OPTIMIZE constantly. Compacting healthy files is pure write cost. Use auto compaction, fast optimize or evidence-based scheduling.
  • Optimizing tiny tables. Scheduling overhead for no measurable gain.
  • Over-partitioning, then wondering why compaction cannot fix the small files.
  • Enabling V-Order without measuring, especially on write-heavy tables where it mainly adds write time.
  • Deleting history too aggressively. Short VACUUM retention breaks time travel, restore and long-running readers.
  • Treating file size as the only metric. Data organization, deletion vectors, row groups for Direct Lake and the queries themselves matter too.
  • Ignoring capacity impact. Maintenance runs on the same capacity as production and is easy to over-schedule.
  • Forgetting non-Spark writers. Tables written by pipelines or Dataflow Gen2 need maintenance scheduled for them.

Production checklist

  • File count inspected with DESCRIBE DETAIL and history, per partition where relevant.
  • Partitioning reviewed: partitions large enough for healthy files, or no partitioning.
  • OPTIMIZE justified by evidence, or replaced with auto compaction for Spark-written tables.
  • VACUUM retention understood and set deliberately, never below seven days without a documented reason.
  • Time-travel requirements known for debugging, audits and recovery.
  • V-Order decision documented per table, knowing the workspace default.
  • Maintenance scheduled away from peaks and scoped to what changed.
  • Capacity impact monitored in the Capacity Metrics app.
  • MERGE patterns reviewed: deduplicated sources, pruning match conditions, no compaction after every routine merge.
  • Non-Spark writers covered: tables loaded by pipelines or Dataflow Gen2 have maintenance planned.

What you learned

  • Delta tables slow down mostly because of file layout (many small files, fragmentation, deletion vectors), not just data volume.
  • Diagnose first: DESCRIBE DETAIL, DESCRIBE HISTORY and files per partition tell you whether maintenance will help.
  • OPTIMIZE compacts files; auto compaction is Microsoft’s recommended default for Spark-written tables. Neither deletes old files.
  • VACUUM deletes unreferenced files after retention. It saves storage, bounds time travel and does not speed up queries.
  • V-Order is an optional, per-table read optimization, off by default in new workspaces and most valuable for Direct Lake consumers.
  • Partitioning, MERGE patterns and write cadence decide how much maintenance a table needs. Fix them first, then maintain on evidence.

For the broader performance picture in notebooks, see How to Make Microsoft Fabric Notebooks Faster. For tracking jobs and runs, see Monitoring Microsoft Fabric Pipelines and Notebooks. For layer-by-layer context, see Medallion Architecture Explained, Bronze, Silver, Gold in Microsoft Fabric and, for the Warehouse’s automatic maintenance, Microsoft Fabric Lakehouse vs Warehouse.

Étiquettes

  • Delta Lake
  • Microsoft Fabric
  • Maintenance des tables
  • Taille des fichiers
  • Performance
  • Partitionnement