Optimizing Performance in Fabric: Lakehouse, Spark, Warehouse and Queries

The most common Lakehouse performance problem is thousands of small Parquet files under one Delta table. Every streaming micro-batch, frequent trickle insert, or highly parallel write creates new files; the more files a table has, the more file-open operations, metadata reads, and network round trips every query pays before it scans a single row. A table with a few large, well-organized files almost always outperforms the same data spread across thousands of tiny ones.

The primary fix is bin compaction with the OPTIMIZE command, which rewrites many small files into fewer, larger ones. In Fabric you can run it from the Lakehouse UI (the table's maintenance menu) or from a notebook:

OPTIMIZE lakehouse_name.sales_orders;

V-Order is a Fabric-specific write-time optimization applied to Parquet files: it sorts, groups, and compresses data so the Power BI and SQL engines — especially Direct Lake semantic models — read it dramatically faster. It is transparent to any Parquet reader and can be applied during OPTIMIZE with VORDER or enabled as a Spark write option. The trade-off is slightly slower writes for much faster reads, which is the right trade for read-heavy analytics tables.

VACUUM completes the maintenance story: after compaction, the old small files remain on disk to support Delta time travel. VACUUM deletes files no longer referenced by the table that are older than the retention threshold. Shortening retention aggressively saves storage but breaks time travel to older versions — a classic exam trade-off.

Beyond compaction, how you physically arrange rows determines how much data each query can skip. Three techniques matter, and the exam expects you to know when each fits.

Partitioning splits the table into folders by a column value (commonly a date). Queries that filter on the partition column read only matching folders — this is partition elimination. Partition on low-cardinality columns that appear in most filters; partitioning on a high-cardinality column (like a customer ID with millions of values) recreates the small-file problem you were trying to escape.

Z-Order co-locates related values across files within the data by clustering on one or more columns during OPTIMIZE (ZORDER BY). It enables data skipping on high-cardinality columns that are poor partition keys, because file-level min/max statistics become far more selective.

Liquid clustering is the newer Delta approach that replaces both for many workloads: you declare clustering columns on the table, and the engine incrementally maintains the layout without rigid folder boundaries. You can change clustering columns later without rewriting the whole table — flexibility partitioning cannot offer.

Lakehouse table optimization at a glance

Spark performance in Fabric hinges on how work is split across executors and how much data moves between them. Start with partition tuning: too few partitions leaves cores idle; too many buries the job in scheduling overhead and produces small output files. Repartition wide transformations sensibly, and use coalesce to reduce partitions before writing so output files are reasonably sized.

Broadcast joins remove the most expensive operation Spark has — the shuffle. When one side of a join is small (a dimension or lookup table), broadcasting it to every executor lets each node join locally with no shuffle at all. Spark broadcasts automatically below a size threshold, and you can force it with a broadcast hint when the optimizer misjudges.

Data skew is the scenario the exam loves: one join key (a dominant customer, a null-heavy column) holds most of the rows, so one task runs for an hour while the rest finish in seconds. Fixes include salting the hot key, filtering or handling nulls separately, and relying on adaptive query execution, which can split oversized shuffle partitions at runtime.

Caching (df.cache() or persist) pays off when a DataFrame is reused across multiple actions — an iterative aggregation, or a base table feeding several outputs. Caching something read once just wastes memory.

Finally, two platform-level levers: the native execution engine runs Spark operators as vectorized native code over Parquet and Delta, often accelerating queries substantially with no code changes — enable it in the environment or session settings. And pool sizing: starter pools give fast session startup with default sizing, while custom pools let you set node family, size, and autoscale range to match the workload instead of over-provisioning.

Warehouse query speed rests on giving the distributed query optimizer accurate information and asking it for less data. Statistics are the foundation: the optimizer uses them to estimate row counts and pick join strategies. Fabric Warehouse creates and updates statistics automatically, but after large loads you can update them manually (UPDATE STATISTICS) so plans reflect the new data distribution. Stale statistics leading to a bad plan is a standard exam scenario.

Avoid SELECT *. The Warehouse stores data in columnar Parquet, so reading only the columns you name skips whole column segments. SELECT * forces every column off storage, inflates memory grants, and defeats the columnar format's main advantage. Project only what the query needs — the single cheapest optimization available.

Result-set caching returns the stored result of a previously executed query when the same query arrives again and the underlying data has not changed — repeat dashboard queries come back almost instantly without consuming compute for a rescan. It complements, not replaces, tuning the first execution.

Appropriate data types matter more in a distributed columnar engine than in a small OLTP database: use the narrowest type that fits (a date as date, not a string; integer keys instead of GUID strings where possible), and keep varchar lengths realistic. Narrow types compress better, join faster, and shrink data movement.

That last point generalizes: minimize data movement. Filter early, aggregate before joining where valid, and design joins so large intermediate results are not shuffled between nodes. Every byte a distributed query does not move is latency you do not pay.

Pipeline optimization is mostly about doing more work in parallel and moving data along the shortest path. The ForEach activity is the first lever: by default you choose sequential or parallel execution, and the batch count setting controls how many iterations run concurrently. Loading fifty tables sequentially when they could load in parallel is the canonical slow-pipeline scenario — switch off sequential mode and set a batch count that balances speed against overwhelming the source system.

The Copy activity has its own throughput controls. Increasing the degree of copy parallelism and the intelligent throughput optimization setting raises how aggressively the copy reads and writes. Partitioned reads from the source (splitting a large source table into ranges copied concurrently) can transform a multi-hour copy into minutes. Also prefer copying only what changed — an incremental load driven by a watermark column beats a full reload every run.

Staging helps when the source and destination cannot stream efficiently point-to-point — for example, some sources load into a warehouse far faster via an intermediate staged copy that enables bulk ingestion on the destination side. Enable staging in the Copy activity when the destination's bulk path requires it.

Finally, question the design itself: many small activities each carrying scheduling overhead may be slower than one notebook doing the same transformations in Spark, and pushing a transformation down to the source query (filtering at the source rather than after the copy) is often the biggest win of all.

Real-time components have their own levers. In an Eventhouse (KQL database), the caching policy decides how much recent data lives on fast local SSD storage versus cheaper cold storage: queries over the hot cache are fast, so set the hot window to cover the period your dashboards and queries actually interrogate — caching a year when queries only look at the last week wastes capacity, while a hot window shorter than your query range forces slow cold reads. The retention policy is separate: it controls how long data is kept at all before deletion. Keep the two distinct — retention governs existence, caching governs speed.

Materialized views precompute an aggregation (or a latest-record-per-key deduplication) over a source table and keep it incrementally updated. A dashboard that repeatedly aggregates billions of raw events should query a materialized view instead — the summarization cost is paid once at ingestion time, not on every query.

Update policies transform data at ingestion: when events land in a raw source table, the policy runs a KQL function and writes the shaped result into a target table. Parsing, filtering, and enrichment happen once, so downstream queries hit clean, query-ready tables. Table partitioning policies can also help specific patterns, such as reorganizing extents by a key that queries filter on heavily.

For Eventstreams, keep the event path lean: filter and route events inside the stream rather than landing everything and cleaning up later, use appropriate destinations for the workload, and avoid unnecessary processing stages that add latency between source and destination.

For reporting, the storage mode of the semantic model often dominates every other tuning choice. You have three options. Import copies data into the model's in-memory cache — fastest queries, but data is only as fresh as the last refresh, and large models consume memory and refresh time. DirectQuery leaves data at the source and translates every visual interaction into a source query — always current, but every click pays source latency. Direct Lake is Fabric's answer: the model reads Delta/Parquet files in OneLake directly into memory on demand, giving near-Import query speed without a scheduled copy refresh, because there is no import step to run.

Decision cue: if the data already lives in a Lakehouse or Warehouse as Delta tables in OneLake and you want both large-scale data and fresh results without refresh windows, choose Direct Lake. Choose Import when the data is small or heavily transformed inside the model, or when you rely on modeling features Direct Lake does not support — in those cases the model can fall back to DirectQuery behavior, and if fallback happens constantly you have lost the benefit. Choose DirectQuery mainly when data cannot be materialized into OneLake.

Direct Lake performance loops back to everything in this lesson: it reads Parquet, so V-Ordered, compacted tables are exactly what makes it fast. A Direct Lake model over thousands of un-compacted small files will disappoint — table maintenance and model performance are the same problem.

Put the pieces together with the scenario DP-700 most likes to paint. A Power BI dashboard uses a Direct Lake semantic model over a Lakehouse table fed by a streaming ingestion job every few minutes. Users report visuals taking many seconds to load, and it is getting worse each week. Inspecting the table's files reveals tens of thousands of small Parquet files.

The diagnosis: frequent micro-batch writes have fragmented the table, and Direct Lake — which reads the Parquet files directly — is paying per-file overhead on every column load. The fix is a maintenance routine, in this order:

• Run OPTIMIZE with V-Order on the table to compact the small files into large, read-optimized ones.
• Schedule that compaction regularly (a pipeline or notebook job), because streaming ingestion will keep creating small files.
• Run VACUUM afterwards to remove the superseded files and control storage growth, with a retention window that preserves the time travel you actually need.
• If queries filter on date, partition or cluster by date so visuals scan only recent data.

Recognize the variants: if the slow query filters on a high-cardinality column instead, the answer shifts to Z-Order or liquid clustering on that column. If the model is Import instead of Direct Lake and freshness is the complaint, the answer is switching to Direct Lake — not shortening the refresh interval. If one Spark task lags far behind the others during ingestion, the answer is a skew fix, not table compaction. Matching the symptom to the layer — table layout, Spark job, warehouse plan, or model storage mode — is the skill being examined.