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, and every query pays file-open, metadata and network overhead per file before it scans a row. A table with a few large files almost always outperforms the same data spread across thousands of tiny ones. There are two ways to deal with it: compact after the fact or prevent small files at write time.

Compacting: OPTIMIZE

Bin compaction with OPTIMIZE groups small files into bins and rewrites them as fewer, larger files. On Runtime 1.3 the target is about 1 GB per file; newer runtimes can use an adaptive target file size that picks a smaller target for smaller tables. Run it from the Lakehouse table maintenance menu, a notebook or a scheduled job:

OPTIMIZE sales_orders;

Preventing: optimize write and auto compaction

• Optimize write bins data per partition before writing, so each write produces fewer, larger files. It reduces new small files but leaves existing ones untouched, and it cannot rescue an over-partitioned table (each tiny partition still gets its own files).
• Auto compaction checks file health after each write and, when small files have built up, runs an OPTIMIZE automatically. Enable it for an existing table with the delta.autoOptimize.autoCompact table property; the session setting spark.databricks.delta.autoCompact.enabled turns it on for new tables created in that session. Microsoft recommends it for ingestion with frequent small writes such as streaming micro-batches, because nobody has to remember a schedule.

The catch: auto compaction runs synchronously in the writing job, after the commit, so that job pays the compaction time. When write latency is strict (a micro-batch must commit within seconds), schedule OPTIMIZE as a separate job on its own Spark pool instead; Delta's optimistic concurrency lets an append-only stream and a compaction job commit independently.

V-Order: a read optimization you opt into

V-Order is a Fabric write-time optimization of Parquet files (special sorting, row group distribution and compression) that makes Direct Lake and the SQL engines read faster, at the cost of slower writes. The files stay standard Parquet. Three facts decide most scenarios:

• It is off by default in new workspaces. New workspaces use the writeHeavy resource profile, which disables V-Order to favour ingestion speed. Read-heavy tables have to opt in.
• It has scopes. A session or environment setting affects every write in that scope; the delta.parquet.vorder.enabled table property targets one table; a writer option targets one write. Enabling it only on read-heavy gold tables keeps bronze ingestion fast.
• Settings only change future writes. Setting the property or session config does nothing to files already written. OPTIMIZE <table> VORDER applies V-Order to the files it compacts, regardless of the session or table setting (files already at the target size may not be rewritten, but the small, fragmented files that hurt reads are); plain OPTIMIZE follows whatever those settings say.

VACUUM versus log retention

VACUUM deletes data files the table no longer references (for example, the small files an OPTIMIZE replaced) once they are older than the retention threshold. It does not reduce the number of active files, so it never speeds up queries over a fragmented table; that is OPTIMIZE's job. Two different retention settings are in play:

So storage bloat from old files is a VACUUM problem, and keeping a given window of time travel means setting VACUUM retention to at least that window.

Beyond compaction, how rows are arranged across files decides how much data each query can skip. Partition on a low-cardinality column most queries filter on, and only when partitions stay large; cluster high-cardinality filter columns with Z-Order or liquid clustering.

Partitioning splits the table into folders by a column value (commonly year or month), and queries that filter on that column read only matching folders (partition elimination). Two rules keep it healthy:

• Low cardinality only. Partitioning on a customer or device ID with thousands of values creates thousands of tiny partitions, each with its own small files, and queries that filter on something else (such as a date) must open files in every partition. Stacking partition columns (date times store) multiplies the count the same way.
• Size partitions against the target file size. Compaction works inside each partition, so a partition smaller than the OPTIMIZE target (about 1 GB on Runtime 1.3) stays as undersized files forever. A table of a few gigabytes split by month or day is over-partitioned; leave small tables unpartitioned and use clustering instead.

Z-Order (OPTIMIZE ... ZORDER BY (col)) rewrites files so each holds a narrow range of the chosen column, which makes file-level min/max statistics selective and lets the engine skip most files for high-cardinality filters. It is applied only during that OPTIMIZE; every extra column weakens it, and Z-Ordering on one column gives no locality for the others (Z-Order on customer alone leaves a date filter skipping little). Do not confuse it with V-Order, which is also an OPTIMIZE option but changes encoding, not which rows share a file.

Liquid clustering declares clustering columns on the table (up to four) and lets you change them later without rewriting everything. It cannot be combined with partitioning, so a liquid-clustered table is unpartitioned. Like Z-Order, it clusters data only when OPTIMIZE runs (scheduled, or triggered by auto compaction): a table that only ever receives appends has its clustering columns declared but no clustering applied.

Lakehouse table optimization at a glance

Spark performance hinges on how much data moves between executors and how evenly the work is split. Start with partition tuning: too few partitions leaves cores idle, too many buries the job in scheduling overhead and produces small output files; reduce partitions (for example with coalesce) before writing.

Broadcast joins remove the shuffle. When one side of a join is small, copying it to every executor lets each node join locally. Spark broadcasts automatically only when a side is below spark.sql.autoBroadcastJoinThreshold (10 MB by default); a broadcast hint forces it for a side somewhat above that threshold that still fits comfortably in executor memory. A side of many gigabytes cannot sensibly be broadcast.

Data skew shows up as one task running far longer than the rest because one key owns most rows. Pick the fix by where the skew is:

• Adaptive query execution (AQE) is on by default (Spark 3.2 and later) and can be switched off or on in session or environment properties. Its skew-join handling splits oversized partitions of a sort-merge join at runtime, so a skewed join between two large tables can be fixed with no code change by making sure AQE is enabled.
• Salting adds a random suffix to the hot key so its rows spread over several partitions. It needs code, and it is the fix for skewed aggregations (aggregate on the salted key, then aggregate again on the real key), which AQE skew handling does not cover.
• When the hot key is NULL or a placeholder value that the join or aggregation does not need, filter it out or process it separately; that removes the skew without salting.
• More shuffle partitions never fix skew: all rows of one key still land in one partition.

Caching (df.cache() or persist) pays off when one expensive DataFrame feeds several actions; without it, Spark recomputes the whole lineage for every action. A temporary view stores only the query plan, not the data, so it does not avoid the recomputation. Caching something used once just wastes memory.

The platform-level Spark levers change how fast each operator runs and how long you wait for compute, without touching the transformation logic.

The native execution engine runs supported Spark SQL and DataFrame operators as vectorized native (C++) code over Parquet and Delta, which helps CPU-bound joins and aggregations with no code change. Enable it for an environment on its Acceleration tab, or for a session with spark.native.enabled; it is available on Runtime 1.3 and 2.0. Unsupported work silently falls back to the regular JVM engine instead of failing. Known fallback causes:

• Spark structured streaming (not supported).
• JSON and XML inputs (convert them to Delta upstream to get native scans).
• Comparisons between mismatched date types (cast so both sides match).
• ANSI mode on Runtime 1.3.

To confirm what ran natively, look at the Spark UI plan: native operators appear as nodes whose names end in Transformer (or as NativeFileScan); a plan with only ordinary JVM operators fell back.

Resource profiles are predefined bundles of Spark settings tuned for a workload: writeHeavy (the default for new workspaces: fast ingestion, V-Order off), readHeavyForSpark and readHeavyForPBI (read-optimized layouts, V-Order on for the Power BI profile), plus custom. Set one per environment or session with spark.fabric.resourceProfile. They tune data layout and write behaviour, not the number of executors.

Pools and start-up time

• Starter pools come from pre-warmed clusters, so sessions start in seconds, but they use medium nodes only.
• Custom pools let you choose node size and autoscale bounds (with dynamic allocation of executors). Size them to the parallelism a job actually uses: executors that are allocated but sit idle still consume capacity, so a pool whose Spark UI shows spare executors throughout a run is larger than the work needs.
• High concurrency mode shares one Spark session between notebooks. For pipelines, enable it in the workspace settings and give the Notebook activities the same session tag; notebooks from the same user with the same Spark compute, default lakehouse and libraries then reuse a running session (up to five notebooks per session). It is the fix when short pipeline notebooks on a custom pool spend most of their time starting up.

Warehouse performance rests on loading in batches, giving the optimizer accurate information and asking it for less data.

Load in batches, not trickles. Each INSERT, UPDATE or DELETE transaction writes new Parquet files, so an application inserting single rows several times a second fragments the table and slows reads until background compaction catches up. Stage the data and load it with COPY INTO, CREATE TABLE AS SELECT or one INSERT ... SELECT.

Statistics let the optimizer estimate row counts and choose join strategies. Fabric Warehouse creates and updates them automatically, but after a large load that changes a table's size substantially, a plan built on stale statistics shows a large gap between estimated and actual rows; UPDATE STATISTICS (or CREATE STATISTICS on a key column) fixes it.

Avoid SELECT *. Storage is columnar, so every column in the select list costs I/O, memory and data movement; project only the columns the query uses, filter early and aggregate before joining where valid.

Right-size data types. The engine budgets memory and moves rows based on the declared type, so a maximum-length string type for short codes, or an 8-byte integer for values that fit a smaller one, inflates memory grants and data movement on every query. Parquet stores strings at their actual length, so the cost is in execution, not storage. Two related rules:

• Type parity on join and filter columns. A varchar key joined to an int key forces a conversion on every row. Give both sides the same type (preferably the integer for a numeric key) in the schema rather than casting in each query.
• Size strings at the source when Spark writes the table. The SQL analytics endpoint reads the types Spark wrote and cannot change a lakehouse table schema. An unsized Spark STRING (or varchar(n) with n of 2,000 or more) appears as varchar(8000); a Spark varchar(n) under 2,000 appears as varchar(4*n). Declare varchar(n) columns in a Spark SQL CREATE TABLE sized to the data.

Data clustering (CLUSTER BY in a warehouse CREATE TABLE ... WITH clause) co-locates rows by columns that queries filter on, so the engine skips files whose value ranges do not match. It is the Warehouse counterpart of Lakehouse clustering, useful for large tables filtered on selective columns.

Two automatic caches serve Warehouse and SQL analytics endpoint queries, and they solve different problems:

Result set caching eligibility. Not every query qualifies. A query is not cached when it uses runtime constants such as GETDATE() (pass the date in as a literal instead), when none of its tables reaches about 100,000 rows, or when the estimated result is larger than about 10,000 rows. Check what happened in queryinsights.exec_requests_history: result_cache_hit is 0 when the query was not applicable, 1 when it created a cache entry, and 2 when it was served from the cache.

Cold starts. The first run after data is loaded (or after the cache was evicted) reads from OneLake into cache and is slower than later runs. Do not judge a query by its first run: a nonzero data_scanned_remote_storage_mb in queryinsights.exec_requests_history marks a cold run, and benchmarking should use warm runs (with result set caching turned off for the test if you want to measure execution itself).

Query Insights views (exec_requests_history, long_running_queries, frequently_run_queries) are where you find which queries to tune first: the slow ones and the frequent ones.

Pipeline optimization is first about copying less, then about copying in parallel. Fully reloading a large table when only a small fraction of its rows change between runs is the biggest waste: switch to an incremental load driven by a watermark column (or change tracking) and filter at the source query rather than after the copy.

The ForEach activity runs sequentially or in parallel, and its batch count sets how many iterations run at once. Many small independent items (hundreds of files, dozens of tables) are the case for turning sequential mode off and setting a batch count that the source can sustain.

For one large copy, the Copy activity has two separate controls:

The two settings solve different limits: throughput optimization raises the resources behind the copy, while parallelism only helps when the source can be read in several streams, which for a database usually means a partitioned source read. Database sources offer partition options: Physical partitions of table (only when the table is physically partitioned) and Dynamic range (splits the read on a column such as an integer key or date into ranges, for unpartitioned tables). With parallel range reads, a single huge table copies in a fraction of the time.

Staging helps when the destination loads far faster through a bulk path from an interim store; enable it in the Copy activity only when the destination needs it. And question the design: many small activities each carry scheduling overhead, so one notebook may beat a chain of them.

Real-time performance comes from sizing the stream for its input, filtering early and paying transformation and aggregation costs once at ingestion rather than on every query.

Eventstreams

• Event throughput level (in the Eventstream settings) sizes the stream: Low for under 10 MB/s, Medium for 10 to 100 MB/s, High for over 100 MB/s. An Eventstream that falls behind after traffic growth needs a higher level. Retention (1 to 90 days) is a separate setting about how long events are kept, not speed.
• Filter per destination inside the stream. Put a Filter operator only on the path to the destination that needs the subset (for an Eventhouse, use event processing before ingestion, or a derived stream), so other destinations keep the full feed and the Eventhouse does not ingest data it will discard.
• Lakehouse destination batching (minimum rows, maximum duration) controls how many events go into each write and therefore the size of the files created. It shapes file size, not stream capacity.

Eventhouse caching versus retention

The caching policy decides how much recent data stays in the hot cache (local SSD and memory); the retention policy decides how long data exists at all. Set the hot window to the range queries actually read (if reports only look back over the last month, about a month needs to be hot) and set retention from compliance needs; never cut required retention to gain speed.

Update policies: transform once at ingestion

An update policy runs a KQL function on each batch ingested into a source table and writes the result into a typed target table, so parsing and filtering happen once instead of in every query (a stored function still parses on every call). Two details are tested:

• Pass-through staging table. If nobody queries the raw table, set its retention soft-delete period to 0 seconds so raw data is not persisted. A source with 0-second soft delete requires the update policy to be transactional (IsTransactional true).
• Transactional means all or nothing. If the transformation fails, the whole ingestion fails and can be retried, rather than the source succeeding while the target silently misses the batch.

Materialized views: aggregations kept current

A materialized view is defined by an aggregation (summarize, including arg_max() or take_any() for latest-record or deduplication per key) and is updated incrementally as data arrives. It is not for row-by-row reshaping; that is an update policy's job. Conversely, an update policy only sees the current ingestion batch, so it cannot keep a "latest status per device" across batches; an arg_max materialized view can.

Querying a view by name combines the materialized part with the delta (records not yet materialized), so results are fully current but cost more during ingestion bursts. materialized_view("ViewName") reads only the materialized part: faster and possibly slightly stale, which suits dashboards that tolerate a few minutes of lag.

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 does not live in OneLake. Choose DirectQuery mainly when data cannot be materialized into OneLake.

DirectQuery fallback

A Direct Lake model that connects through a SQL analytics endpoint or warehouse ("Direct Lake on SQL endpoints") can fall back to DirectQuery, sending queries to the SQL engine instead of reading Delta files, which is much slower. Two causes matter:

• SQL views. A view has no Delta files to read, so any query touching a model table based on a view always falls back. Materialize the view's logic as a Delta table and bind the model table to it.
• Guardrails. Each capacity SKU caps things such as Parquet files per table, row groups and rows per table. At F64 the cap is 5,000 Parquet files per table, and F128 has the same cap, so a table fragmented by small appends falls back; compact it (and keep it compacted) instead of scaling up.

DirectQuery events in Performance analyzer or a trace on that table confirm fallback. The model's Direct Lake behavior property decides whether fallback is allowed: Automatic (fall back when needed), DirectLakeOnly (queries that would fall back fail instead) or DirectQueryOnly. Direct Lake on OneLake models read OneLake directly and do not fall back to DirectQuery; if a table exceeds a guardrail, refresh fails and the model cannot be queried until the table is brought back within limits (typically by compacting it).

Direct Lake performance loops back to table maintenance: it reads Parquet, so compacted, V-Ordered tables are what make it fast, and a table with thousands of small files both reads slowly and risks a guardrail.

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 ... VORDER on the table to compact the small files into large, read-optimized ones (V-Order is off by default in new workspaces, so existing files likely lack it).
• Keep it compacted, because streaming ingestion will keep creating small files: enable auto compaction, or schedule OPTIMIZE on a separate pool if the stream has a strict commit latency.
• Run VACUUM afterwards to remove the superseded files and control storage growth, with a retention window (7 days by default) that preserves the time travel you actually need.
• If queries filter on date and the table is large enough for partitions near the target file size, partition by date; otherwise cluster.

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 a Direct Lake model shows DirectQuery events, check for view-based tables and file-count guardrails. 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.