Optimizing Pipelines, Warehouses, Real-Time Items and Queries

What it is

Every Fabric engine gets faster for the same reasons: it reads less data, moves data in parallel, and does repeated work once. Pipelines gain from parallel copies and incremental loads, warehouses from statistics and large batched loads, Eventhouses from caching and precomputed views, and every query from filtering early.

Optimization also saves capacity units, because Fabric charges compute for the work each item does.

How it works

Optimize a pipeline

Parallelism has limits: a source database or API can throttle or slow down under too many parallel reads, so raise it step by step.

Optimize a data warehouse

COPY INTO dbo.Sales
FROM 'https://contoso.blob.core.windows.net/sales/2026/*.parquet'
WITH (FILE_TYPE = 'PARQUET');

CREATE STATISTICS stats_Sales_CustomerKey ON dbo.Sales (CustomerKey) WITH FULLSCAN;
UPDATE STATISTICS dbo.Sales (stats_Sales_CustomerKey);

Each single-row insert writes new small Parquet files and log entries, so trickle inserts are slow and fragment the table. The warehouse compacts files automatically in the background, but batching is still much faster.

The first run of a query can be slower because data is not yet cached on the compute nodes. Compare repeated runs before deciding a change helped.

Optimize Eventstream and Eventhouse

Eventstream

• Filter out unwanted events and drop unneeded fields early, so less data flows to every destination.
• Use direct ingestion to an Eventhouse when no in-flight processing is needed; it has fewer steps.
• Send events from a high-volume source to one eventstream and branch it to several destinations, rather than reading the same source many times.

Eventhouse

.alter table Telemetry policy caching hot = 30d

.alter-merge table Telemetry policy retention softdelete = 365d

Queries over data outside the hot cache still work, but they read from slower storage.

Optimize query performance

KQL

• Filter on the datetime column first, such as where Timestamp > ago(1h).
• Use has (whole term, uses the index) rather than contains (substring scan) where possible, and == rather than the case-insensitive =~.
• project only the columns you need and summarize before a join.
• Put the smaller table on the left side of a join, or use lookup for a dimension table.

Logs
| where Timestamp > ago(1h)
| where Message has "timeout"
| summarize Errors = count() by Service
| lookup kind=leftouter ServiceOwners on Service

T-SQL and Spark SQL

• Avoid SELECT * and wrapping filter columns in functions, such as WHERE YEAR(OrderDate) = 2026; write WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01' instead.
• Keep statistics current in the warehouse; keep lakehouse tables compacted and V-Ordered so the SQL analytics endpoint and Direct Lake read them quickly.

A worked example

A nightly pipeline copies a 500 million row table in five hours, and a KQL dashboard over the copied logs is slow.

The engineer switches to an incremental load on ModifiedDate, sets a dynamic range partition on the source key, and raises copy parallelism; the load drops to minutes. In the Eventhouse, a caching policy of 30 days covers the dashboard's range, a materialized view precomputes hourly counts, and queries now filter on Timestamp first and use has instead of contains.

How the exam asks about this

• "A ForEach copies 40 tables one after another." Turn off sequential and set a batch count.
• "Thousands of single-row inserts make a warehouse load slow." Load in batches with COPY INTO or INSERT ... SELECT.
• "Dashboards query the last 14 days of an Eventhouse table and are slow." Set a caching policy covering at least 14 days, and consider a materialized view.