Cross-workload table maintenance and optimization in Microsoft Fabric

Delta tables in Microsoft Fabric can serve Spark, SQL analytics endpoint, Power BI Direct Lake, Warehouse, and other Fabric experiences from data stored in OneLake. Optimal cross-workload performance depends on two factors:

• The workload that creates and maintains the table.
• The engines that consume the table.

Lakehouse tables are commonly managed by Spark, Fabric pipeline Copy activity, or Dataflow Gen2. Spark is the most common writer and provides the broadest layout and maintenance controls. Warehouse and database mirroring manage their physical layouts automatically. Mirrored catalogs retain the layout managed in the source system. Consumer requirements are generally compatible, but Power BI Direct Lake has additional storage requirements for optimal performance.

Use one shared table whenever its requirements are compatible. For the exceptions that justify another table, see When to create another table.

Understand layout ownership

Start by identifying which workload owns the physical table layout. The controls in the following table are the key controls relevant to cross-workload table layout and maintenance, not an exhaustive list of each engine's capabilities.

Cross-workload guidance

The following table summarizes the recommended approach by producer and consumer.

Optimize Lakehouse tables

Lakehouse Delta tables require an explicit maintenance strategy regardless of whether Spark, Pipeline Copy activity, or Dataflow Gen2 writes them. Spark is the primary example in this section because it provides the broadest layout and maintenance controls in Fabric.

Use the Spark runtime defaults

When Spark writes the table, use Fabric Spark runtime 2.0 or later defaults:

• Keep adaptive target file size enabled. It automatically selects a target for each table from 128 MB to 1 GB.
• Keep file-level compaction targets enabled to avoid rewriting files that met an earlier adaptive target.
• Keep deletion vectors enabled.
• Don't impose an arbitrary maximum row count per file. Row width varies, so a row limit can create excessive small files for narrow tables.

In Fabric Spark runtime 1.3, adaptive target file size, file-level compaction targets, and deletion vectors are available as opt-in settings.

When Pipeline Copy activity or Dataflow Gen2 writes the table, inspect the resulting file layout and schedule maintenance separately. Don't assume that these writers apply Spark runtime defaults.

• Fabric pipelines can orchestrate a Lakehouse maintenance activity after writes.

Prevent and compact small files

For Spark-written tables, prefer auto compaction. This feature evaluates table fragmentation after writes and runs compaction only when needed. It eliminates the need for a separate table-health check before maintenance runs.

Use the following guidance for exceptions and complementary features:

OPTIMIZE compacts files and automatically purges a file's deletion vectors when more than 5% of its records are referenced by deletion vectors. Use REORG TABLE ... APPLY (PURGE) only when you must physically purge records below that threshold or meet a specific compliance requirement.

Run VACUUM on a separate schedule to remove unreferenced files after the retention period. VACUUM reclaims storage but doesn't improve the active file layout.

Organize data for file skipping

Use liquid clustering when recurring filter or processing patterns benefit from improved file skipping. Liquid clustered tables require OPTIMIZEor auto compaction to organize newly written data.

Avoid partitioning by default. Use it when a specific requirement justifies the operational tradeoffs, such as isolating concurrent writers that update, delete, or merge data across disjoint partitions. For more information, see When to use partitioning.

For existing partitioned tables, consider Z-Order when selective predicates commonly filter on the same columns within a partition.

Optimize Warehouse-managed tables

Fabric Data Warehouse manages the physical Delta table layout regardless of the ingestion method.

Use the strategic controls that Warehouse exposes to tune the data layout:

• Apply data clustering to large tables when queries repeatedly use selective predicates on the same columns.
• Keep V-Order enabled for read-oriented and mixed workloads. V-Order is enabled by default.
• Consider disabling V-Order for write-intensive warehouse workloads.

For complete Warehouse guidance, see Performance guidelines in Fabric Data Warehouse.

Optimize mirrored data

Your ability to improve the physical layout depends on whether Fabric replicates the data or references source files:

• Database mirroring: Fabric replicates source data into Delta tables in OneLake and manages the V-Ordered file layout and maintenance. You can't directly configure target file size, deletion-vector cleanup, liquid clustering, partitioning, or V-Order on the mirrored destination.
• Mirrored catalogs: Fabric synchronizes metadata and uses OneLake shortcuts to reference source data in place. Fabric doesn't rewrite or maintain these files. Improve the physical layout and cleanup in the source system when its supported features allow it. Those changes are visible through the shortcuts without creating another copy in Fabric.

For database-mirrored data:

• Use selective predicates and avoid unnecessary columns in Spark and SQL queries.
• Design Power BI semantic models and DAX measures for efficient Direct Lake consumption.

For mirrored catalogs:

• Use the source platform's supported table-maintenance and layout features.
• Evaluate the source file and row-group distribution for the Fabric consumers that query the shortcuts.
• For Direct Lake, evaluate creating an additional dimensionally modeled, V-Ordered serving layer when the source layout can't meet performance requirements.

For mirroring concepts, types, and supported sources, see What is Mirroring in Fabric? and How metadata mirroring works.

Apply consumer-specific optimization

Spark and SQL analytics endpoint perform well on the same adaptive lakehouse layout. Use adaptive target file size, prevent excessive small files, and apply liquid clustering when measured predicates benefit from improved file skipping. Don't enable V-Order solely for Spark or SQL analytics endpoint performance. For engine-specific details, see SQL analytics endpoint performance considerations.

Power BI Direct Lake

Direct Lake uses the same underlying Delta tables but adds recommendations related to transcoding and incremental framing:

• File and row-group layout: Avoid small row groups and uneven row group distribution, this creates more VertiPaq column segments and increases transcoding overhead.
• V-Order: Follow the producer-specific recommendation in the cross-workload guidance. For Spark-written tables primarily consumed through Direct Lake, enable V-Order or use the readHeavyForPBI resource profile.
• Update patterns: Prefer append-friendly update patterns where possible to preserve existing Parquet files and support incremental framing.

For Spark-written tables, spark.sql.parquet.native.writer.maxRowGroupRowCount sets the maximum rows per row group when the native execution engine writes the Parquet files. The default value is 0, which doesn't impose a maximum. If analysis shows that row-group sizing is affecting Direct Lake performance, set a tested limit before writing or rewriting the table. For example:

spark.conf.set("spark.sql.parquet.native.writer.maxRowGroupRowCount", 8_000_000)

Don't set the limit solely to reach a specific row count. Row width, compression, file distribution, and capacity parallelism also affect performance. Use Delta Analyzer to evaluate the resulting layout.

For detailed guidance on framing, transcoding, row groups, update patterns, and Delta Analyzer, see Understand Direct Lake query performance.

Apply the guidance to medallion layers

Bronze, Silver, and Gold describe data purpose and refinement. They don't determine whether layout is user-managed or system-managed, and they don't require separate copies for each consumer.

Resolve layout and maintenance issues

Use producer-aware remediation. Apply Spark maintenance commands to lakehouse tables when the destination mode supports those operations. Treat the signals as indicators rather than universal thresholds, and validate them against the table's write pattern and consumer performance.

For mirrored data, follow the producer-specific remediation in Optimize mirrored data. Database mirroring is system-managed; for mirrored catalogs, apply supported maintenance in the source platform.

For lakehouse tables, Spark-supported inspection options include:

• Run DESCRIBE DETAIL to inspect file count, total size, and the evaluated delta.targetFileSize.adaptive property.
• Run DESCRIBE HISTORY to review write patterns and maintenance history.
• Use Delta Analyzer when you need detailed Direct Lake row group and update-pattern analysis.

Inspect average file size

Use DESCRIBE DETAIL to calculate the average file size as an initial indicator of the table layout:

details = spark.sql("DESCRIBE DETAIL schema_name.table_name").first()

table_size_gb = details["sizeInBytes"] / (1024**3)
num_files = details["numFiles"]
avg_file_size_mb = (
    details["sizeInBytes"] / num_files / (1024**2)
    if num_files
    else 0
)

print(f"Table size: {table_size_gb:.2f} GB")
print(f"Number of files: {num_files}")
print(f"Average file size: {avg_file_size_mb:.2f} MB")

An average can hide skew between partitions or recent and previously compacted files. If the average indicates a possible layout issue, inspect the individual Parquet files or use Delta Analyzer to evaluate the distribution before changing maintenance settings.

When to create another table

Don't create another physical table solely because multiple Fabric engines consume the data.

Create another table when it has an independent purpose, such as:

• A transformation or aggregation that changes the data's grain or business meaning.
• Different security, retention, or data-quality requirements.
• A latency or refresh requirement that the shared table can't meet.
• A consumer-specific layout whose measured benefit outweighs its storage, processing, lineage, and governance costs.

Related content

• Tune the size of Delta table data files
• Compacting Delta tables
• Deletion vectors for Delta tables
• Apply liquid clustering on Delta tables
• Partitioning for Delta tables
• Optimize Delta Lake tables with V-Order
• SQL analytics endpoint performance considerations
• Understand Direct Lake query performance
• Performance guidelines in Fabric Data Warehouse
• Data clustering in Fabric Data Warehouse
• What is Mirroring in Fabric?