Monitor and optimize an analytics solution
The operations third of the exam: knowing where to look when something breaks, reading the error message correctly, and applying the right lever — not just the biggest one — when something is slow.
- 3.1 Monitor Fabric items — ingestion, transformation, semantic model refresh, alerts
- 3.2 Identify and resolve errors — pipelines, Dataflow Gen2, notebooks, Eventhouse, Eventstream, T-SQL, shortcuts
- 3.3 Optimize performance — Lakehouse tables, pipelines, Warehouse, Eventstream/Eventhouse, Spark, queries
3.1 Monitor Fabric items
Know your monitoring surfaces
Half of this objective is simply picking the right tool. Four surfaces, four different questions:
| Surface | Answers | Scope |
|---|---|---|
| Monitoring hub (Monitor) | "Did it run, how long did it take, why did it fail?" | All runs across items you can access — pipelines, notebooks, Spark, dataflows, refreshes |
| Capacity Metrics app | "What is burning CUs? Are we throttled?" | Capacity-wide consumption, overloads, carryforward |
| Workspace monitoring | "Give me queryable logs and metrics I can build alerts on" | Per-workspace diagnostic logs landed into an Eventhouse, queried with KQL |
| Purview audit log | "Who accessed, shared or exported this?" | Tenant-wide security/compliance events |
Workspace monitoring
When enabled on a workspace, Fabric provisions a monitoring Eventhouse and streams diagnostic logs and metrics into it. Because the data lands in a KQL database, you can query it with KQL, build real-time dashboards on it, and attach Activator rules — which is how you get genuinely custom alerting rather than the built-in per-item notifications.
Monitoring data ingestion
| What | Where | Signals that matter |
|---|---|---|
| Pipeline Copy activity | Monitoring hub → run → activity output | rowsRead, rowsCopied, rowsSkipped, throughput, duration, used DIUs, parallel copies |
| Copy job | The Copy job item's run history | Rows moved, incremental watermark position |
| Dataflow Gen2 | Refresh history on the item | Per-query duration and row counts, staging vs destination phases |
| Mirroring | The mirrored database's replication view | Replication lag, rows replicated, per-table status, stopped/errored tables |
| Eventstream | Eventstream runtime view / data insights | Incoming vs outgoing events, backlog, errors per destination |
Caveat — read rows read against rows written
A Copy activity that succeeds can still be wrong. If rowsRead ≠ rowsCopied, rows were filtered, skipped by fault tolerance, or dropped by type incompatibility. When a question says "the pipeline succeeded but the target has fewer rows than expected", check rowsSkipped and the fault-tolerance log — do not chase permissions.
Monitoring data transformation
| Surface | Shows |
|---|---|
| Monitoring hub → Spark run | Duration, status, submitter, attached Lakehouse, executor allocation |
| Spark UI — Jobs | Jobs and their stages; where the wall-clock time went |
| Spark UI — Stages | Task duration distribution (max vs median exposes skew), shuffle read/write, spill |
| Spark UI — SQL/DataFrame | The physical plan: join strategies, scans, exchanges, AQE rewrites |
| Spark UI — Executors | Per-executor memory, disk, GC time, failed tasks |
| Spark UI — Storage | Cached DataFrames and how much actually fit in memory |
| Query insights (Warehouse) | Historical T-SQL query performance — see Warehouse optimization |
Reading the Stages tab for skew
In a healthy stage, task durations cluster tightly. If the max task duration is far above the median and one task processes far more input, you have data skew — a few keys dominate a partition. That is a different fix from "the whole job is slow", so the exam uses this signal to separate skew answers from cluster-size answers.
Monitoring semantic model refresh
- Refresh history on the semantic model shows each refresh's start, duration, status and error detail.
- The monitoring hub lists refreshes alongside other runs so you can correlate them with the pipeline that triggered them.
- Refresh failure notifications can be emailed to the owner and to additional contacts from the model's settings.
- Direct Lake models avoid scheduled refresh for data — they read Delta directly — but still reframe to pick up new data, and can fall back to DirectQuery when a guardrail is exceeded, which shows up as a sudden performance change rather than an error.
Caveat — refresh a Direct Lake model?
Direct Lake does not import data, so "schedule a refresh to load the new rows" is the wrong answer for it — new Delta commits are picked up by reframing. If a Direct Lake report suddenly gets slower with no error, suspect DirectQuery fallback caused by exceeding a capacity guardrail, or an unoptimized (small-file-heavy) Delta table underneath.
Configuring alerts
| Alert type | Set up where | Fires on |
|---|---|---|
| Activator rule | Activator item, or "Set alert" on a real-time dashboard / Power BI visual / Eventstream | A data condition — value crosses a threshold, changes, or stays in a state |
| Job failure notification | Pipeline settings → notification activity, or item settings | Run failure or completion — email or Teams |
| Refresh failure notification | Semantic model / dataflow settings | Refresh failure, to owner + contacts |
| Capacity notification | Admin portal → capacity settings | Capacity approaching or exceeding its limit |
| KQL-based alert | Activator on a KQL queryset / workspace monitoring Eventhouse | Any condition you can express in KQL |
Activator in one line
Activator (previously Data Activator / Reflex) is Fabric's no-code detect-and-act engine: it watches a stream, a KQL query or a report visual, evaluates rules on objects, and triggers an action — email, Teams message, or run a Fabric item such as a pipeline or notebook. When a question asks for "automatically start the pipeline when X happens in the data", Activator is the mechanism.
3.2 Identify and resolve errors
find the failed run and activity"] H --> C{"Read the error class"} C -->|"Auth / credentials"| A["Refresh connection
check service principal, expired secret"] C -->|"Network / gateway"| N["Gateway offline or unreachable
firewall, private endpoint"] C -->|"Schema / type"| S["Mapping mismatch
fix mapping or cast explicitly"] C -->|"Timeout"| T["Raise timeout, add parallelism
reduce batch size"] C -->|"Capacity / throttling"| K["Capacity Metrics app
scale, reschedule, reduce concurrency"] C -->|"Permission"| P["Workspace role, item permission
source-side IAM"]
Pipeline errors
| Symptom / error | Cause | Fix |
|---|---|---|
| Invalid data source credentials | Expired secret, rotated password, deleted service principal | Update the connection; prefer workspace identity or managed identity over stored secrets |
| Copy activity failed — type mismatch | Source and sink column types incompatible | Fix the explicit column mapping, cast in the source query, or enable fault tolerance to skip bad rows |
| Activity timed out | Long-running source query or oversized batch | Raise the activity timeout, filter the source, increase parallel copies |
| Gateway unreachable / offline | On-premises data gateway service stopped or blocked | Restart the gateway, verify outbound connectivity and cluster health |
| Capacity throttled / rejected | CU exhaustion on the capacity | Capacity Metrics app → scale up, move the workspace, or reschedule heavy jobs |
| Sink table locked / concurrent write | Two writers targeting the same Delta table | Serialize the writes; Delta's optimistic concurrency will fail one of them |
| Expression evaluation failed | Null or wrong-typed value in a dynamic expression | Guard with coalesce()/if(); check the upstream activity's actual output shape |
Design pipelines to fail well
Use activity dependency conditions (Success / Failure / Completion / Skipped) to build error paths, set retry count and interval on transient-prone activities, and add a Fail activity with a clear message so downstream monitoring shows the business reason rather than a generic error.
Dataflow Gen2 errors
| Symptom | Cause | Fix |
|---|---|---|
| Out of memory / resource exceeded | Heavy transformation evaluated in the mashup engine | Enable staging so the work is pushed down; split the dataflow; reduce the data pulled |
| Evaluation timeout | Too many steps, or query folding broken | Restore query folding — move filters earlier, avoid steps that break folding |
| Data source error on refresh | Credentials expired or source unavailable | Re-authenticate the connection; check gateway for on-prem sources |
| Destination write failed | Target missing, no permission, or schema conflict | Verify the destination exists, check the update method (append vs replace) and column mapping |
| Duplicate column names | Collision after a merge/expand | Rename before combining |
Caveat — query folding is the real lever
When a Dataflow Gen2 folds, the transformation is translated into a query the source executes; when folding breaks, everything is pulled into the mashup engine and processed locally — which is where memory and timeout failures come from. Filters and column removal placed early usually fold; custom functions, some type changes and index columns usually break it. "Dataflow is slow / runs out of memory" → staging plus restoring folding, not a bigger capacity.
Notebook and Spark errors
| Error | Cause | Fix |
|---|---|---|
| OutOfMemoryError (executor) | Partition too large, skew, or a broadcast that was too big | Repartition, fix skew, lower the broadcast threshold, larger node size |
| OutOfMemoryError (driver) | collect() / toPandas() pulling a huge result to the driver | Aggregate first, or write to a table instead of collecting |
| AnalysisException: Table or view not found | No default Lakehouse attached, wrong name, or endpoint metadata lag | Attach the Lakehouse, use a fully qualified name or ABFSS path |
| ConcurrentAppendException / ConcurrentModification | Two jobs writing the same Delta table | Serialize writers, or partition so they touch disjoint files |
| FileNotFoundException on read | Files removed by VACUUM while a long query or stale snapshot referenced them | Re-read the table; do not vacuum below the retention window |
| Session expired / timed out | Idle session reclaimed | Re-run; adjust session timeout; use high concurrency mode |
| ModuleNotFoundError | Library not installed in the environment | Add it to the Environment (persistent) or %pip install (session-only) |
| Py4JJavaError | Wrapper around a JVM-side failure | Read the nested stack trace and the Spark UI — the real cause is inside |
%pip install is not persistent
%pip install lasts only for the current session. For a library every run needs — and for reproducibility across notebooks and pipeline-triggered runs — add it to an Environment and attach that environment to the notebook or workspace.
Eventhouse errors
| Symptom | Cause | Fix |
|---|---|---|
| Ingestion failures | Schema or format mismatch between events and the table | Check the ingestion mapping; inspect .show ingestion failures |
| Data arrives late (minutes) | Default batching ingestion policy aggregating small batches | Tune the batching policy or enable streaming ingestion for low latency |
| Query timeout / memory error | Unbounded scan, expensive join, missing time filter | Filter on the time column first, reduce the range, pre-aggregate with a materialized view |
| Old data missing | Retention policy soft-deleted it | Extend retention — data past retention is gone, not merely uncached |
| Historical queries slow | Data outside the hot cache window | Extend the caching policy for the period you query |
Caveat — caching policy vs retention policy
Retention decides how long data exists. Caching (hot cache) decides how much of it is held on fast local SSD. Data past its cache window is still queryable, just slower; data past its retention window is deleted. "Queries on last year's data are slow" → extend caching. "Last year's data is gone" → retention was too short. Hot cache should always be ≤ retention.
Eventstream errors
| Symptom | Cause | Fix |
|---|---|---|
| Source connection failure | Bad connection string, expired SAS, wrong consumer group | Re-create the connection; give the Eventstream its own consumer group |
| Deserialization errors | Events don't match the declared format/schema | Correct the data format (JSON/Avro/CSV) and the schema |
| Backlog / input lag growing | Ingress exceeds processing throughput | Simplify the processing graph, increase source partitions, reduce destination fan-out |
| Destination write failure | Target unavailable, permission denied, or schema conflict | Check destination status and permissions; validate the schema mapping |
| Events missing downstream | A filter operator dropping more than intended | Inspect the processor preview at each operator |
Consumer groups
Two readers sharing one Event Hubs consumer group compete for the same partitions and each sees only part of the data. Every independent consumer — each Eventstream, each Spark job — should have its own consumer group. "Some events are missing and another job started recently" is this.
T-SQL errors
| Error | Cause | Fix |
|---|---|---|
| Invalid object name | Wrong schema, wrong database, or endpoint metadata not yet synced | Qualify as database.schema.object; refresh the SQL endpoint metadata |
| Cannot INSERT/UPDATE — read-only | Writing through a Lakehouse SQL analytics endpoint | Write with Spark, or move the table to a Warehouse |
| Permission denied on object/column | Missing GRANT, or an explicit DENY from CLS | Grant the needed permission; remember DENY always wins |
| Conversion failed | Implicit conversion of incompatible types | Explicit CAST/TRY_CAST; fix upstream typing |
| Unsupported feature / syntax | Fabric Warehouse T-SQL is a subset — identity columns, some constraints, MERGE nuances, triggers | Use a supported pattern (CTAS, explicit key generation) |
| Query rejected / long queue | Capacity throttling | Capacity Metrics app; reduce concurrency or scale |
Caveat — constraints are not enforced
Fabric Warehouse supports PRIMARY KEY / UNIQUE / FOREIGN KEY only as NOT ENFORCED metadata. They inform the optimizer but do not stop you inserting duplicates or orphans. If a question asks how to guarantee uniqueness, the answer is to enforce it in the load logic (MERGE on the key, dedupe before insert), not to add a constraint.
OneLake shortcut errors
| Symptom | Cause | Fix |
|---|---|---|
| Access denied through the shortcut | Source-side credentials expired, or the caller lacks source permission — both layers apply | Update the connection; grant access at the source (S3 IAM, ADLS RBAC/ACL) |
| Target not found / broken shortcut | Source path moved, renamed or deleted | Re-create the shortcut against the current path |
| Shortcut appears but has no tables | Target isn't a valid Delta folder, or was created under Files/ instead of Tables/ | Point at a proper Delta table folder under Tables/ |
| Queries are slow | Cross-service/cross-cloud latency, or chained shortcuts | Use query acceleration (RTI), mirror the data, or copy it locally |
| Stale or unexpected schema | Source schema changed underneath | Refresh metadata; re-create the shortcut |
| Blocked from external tools | OneLake external-access setting disabled | Re-enable at tenant/workspace level (see Domain 1) |
3.3 Optimize performance
Optimizing a Lakehouse table
compact"] Q1 -->|"Filtered queries scan everything"| Z["OPTIMIZE ZORDER BY
on the filter columns"] Q1 -->|"Storage cost from old versions"| V["VACUUM
respect 7-day retention"] Q1 -->|"Reads slow across the board"| VO["V-Order enabled
plus OPTIMIZE"] Q1 -->|"Huge table, always filtered by date"| P["Partition by a
low-cardinality column"]
- Table maintenance can be run ad hoc from the Lakehouse UI (Run OPTIMIZE / VACUUM), from Spark SQL, or scheduled in a pipeline or notebook.
- Auto Compaction and Optimize Write reduce the small-file problem at write time so you need fewer manual OPTIMIZE runs.
- Aim for reasonably large files (roughly a few hundred MB up to ~1 GB) — that is the sweet spot for Parquet scan efficiency.
- Deletion vectors make deletes/updates cheap by marking rows instead of rewriting files; run OPTIMIZE periodically to materialize them.
- Full detail on OPTIMIZE / VACUUM / V-Order / Z-Order is on the foundations page.
Optimizing a pipeline
| Lever | How | When it helps |
|---|---|---|
| Copy less | Incremental instead of full; project only needed columns; filter at the source | Always the biggest win |
| Parallel copy | Raise degree of copy parallelism; partition the source read | Large single-table copies |
| DIUs | Increase Data Integration Units on the Copy activity | Throughput-bound copies |
| Staged copy | Enable staging | Cross-region moves, or sinks that need bulk load |
| ForEach parallelism | Turn off isSequential, tune the batch count | Many independent tables — but watch capacity |
| Flatten dependencies | Run independent branches concurrently instead of chaining | Long pipelines with false sequencing |
| Right activity | Push a set-based transform into a stored procedure or notebook instead of row-wise pipeline logic | Pipelines used as a transformation engine |
Parallelism is not free
Raising ForEach batch counts and copy parallelism consumes more CUs at once and can push the capacity into throttling, making everything slower. If the capacity is already near its limit, the fix is scheduling and scope reduction — not more concurrency.
Optimizing a data warehouse
| Technique | Detail |
|---|---|
| Statistics | Fabric auto-creates and maintains them, but you can CREATE STATISTICS / UPDATE STATISTICS manually when plans look wrong after a big load |
| Correct data types | Avoid oversized varchar(max)-style columns — they inflate memory grants and slow everything |
| CTAS | Preferred for large transforms; minimally logged compared with row-by-row INSERT |
Avoid SELECT * | Columnar storage means unread columns cost nothing — until you ask for them |
| Result set caching | Repeated identical queries served from cache |
| Materialized views | Pre-compute expensive aggregations and keep them current automatically |
| Star schema | The engine is optimized for it; deeply normalized snowflakes join poorly at scale |
| NOT ENFORCED keys | Declaring PK/FK (not enforced) still gives the optimizer useful cardinality information |
-- Historical query performance (queryinsights schema)
SELECT TOP 20 *
FROM queryinsights.long_running_queries
ORDER BY median_total_elapsed_time_ms DESC;
SELECT TOP 20 *
FROM queryinsights.frequently_run_queries
ORDER BY number_of_runs DESC;
SELECT command, submit_time, total_elapsed_time_ms, status
FROM queryinsights.exec_requests_history
ORDER BY total_elapsed_time_ms DESC;
-- Currently executing requests (DMV)
SELECT * FROM sys.dm_exec_requests WHERE status <> 'Completed';Know the three query insights views
exec_requests_history = every request. long_running_queries = the slow ones, aggregated by query shape. frequently_run_queries = the ones worth optimizing because they run constantly. Pair them: the best optimization target is usually frequently run and moderately slow, not the single slowest ad-hoc query.
Optimizing Eventstreams and Eventhouses
| Target | Lever | Effect |
|---|---|---|
| Eventstream | Source partitioning / partition key | More parallel processing; pick a key with even distribution |
| Simplify the processor graph | Fewer operators and destinations reduce per-event cost | |
| Trim the payload | Drop unused fields early with "manage fields" | |
| Eventhouse | Caching (hot) policy | Keeps the queried window on fast storage — the main query-latency lever |
| Retention policy | Controls storage cost and how far back data exists | |
| Materialized views | Maintain aggregates incrementally so dashboards don't re-scan raw data | |
| Update policies | Transform/route at ingestion time into a curated table (ETL at ingest) | |
| Partitioning & batching policies | Tune extent shape and ingestion batching for throughput vs latency |
// Hot cache: keep 30 days on fast storage
.alter table SensorReadings policy caching hot = 30d
// Retention: keep data for one year
.alter table SensorReadings policy retention softdelete = 365d
// Pre-aggregate hourly so dashboards never scan raw events
.create materialized-view HourlySensorAgg on table SensorReadings
{
SensorReadings
| summarize AvgTemp = avg(Temperature), MaxTemp = max(Temperature)
by bin(Timestamp, 1h), DeviceId
}
// Inspect physical layout
.show table SensorReadings extents
| summarize Extents = count(), Rows = sum(RowCount), Size = sum(OriginalSize)Optimizing Spark performance
salt the key · AQE skew join · repartition"] UI -->|"Large shuffle read/write"| SH["Reduce shuffle
broadcast the small side · filter earlier"] UI -->|"Spill to disk"| SP["Memory pressure
more partitions · larger nodes · avoid huge broadcast"] UI -->|"Long scan, few rows used"| SC["Prune
predicate pushdown · partitions · Z-Order"] UI -->|"Thousands of tiny input files"| SF["OPTIMIZE the table
optimize write on ingest"] UI -->|"Minutes before the first task"| CS["Cold start
use a starter pool"]
| Technique | How | Use when |
|---|---|---|
| Broadcast join | broadcast(small_df) or autoBroadcastJoinThreshold | Small dimension joined to a large fact — avoids a shuffle entirely |
| Adaptive Query Execution | On by default in Fabric | Runtime re-planning: coalesces shuffle partitions, converts to broadcast, splits skewed joins |
| Salting | Add a random suffix to the hot key, join, then aggregate away | Severe skew AQE can't resolve |
| Predicate pushdown / column pruning | Filter and select early | Always — least data read is the cheapest optimization |
| coalesce vs repartition | coalesce(n) reduces partitions without a full shuffle; repartition(n) shuffles for even distribution | coalesce before write; repartition to fix imbalance |
| cache / persist | df.cache() | A DataFrame reused several times — not for single-pass work |
| Native execution engine | Enable in Spark workspace settings | Broad speed-up; unsupported operators fall back silently |
| Avoid Python UDFs | Use built-in functions, or pandas/Arrow UDFs if unavoidable | Row-at-a-time Python UDFs break Catalyst optimization and serialize every row |
| High concurrency mode | Share sessions across notebooks | Many users/small jobs paying repeated start-up cost |
from pyspark.sql.functions import broadcast, col
# Broadcast the small side to avoid a shuffle join
result = fact_df.join(broadcast(dim_df), "customer_id")
# Filter before joining — pushdown and less shuffle
recent = fact_df.filter(col("order_date") >= "2026-01-01")
# Reduce output files without a full shuffle
result.coalesce(8).write.format("delta").mode("overwrite").saveAsTable("gold_orders")
# Cache only when the DataFrame is genuinely reused
base = recent.filter(col("region") == "EU").cache()
total, distinct_customers = base.count(), base.select("customer_id").distinct().count()Caveat — the three Spark traps
1. coalesce() only reduces partitions and avoids a full shuffle; it cannot rebalance skew. Use repartition() when distribution matters.
2. Broadcasting a table that is too large moves the OOM from the executors to the driver. Broadcast the genuinely small side only.
3. AQE is already on — answers proposing to manually tune spark.sql.shuffle.partitions or hand-fix skew are usually distractors, because AQE coalesces partitions and splits skewed joins at runtime.
Optimizing query performance
| Principle | Applies to |
|---|---|
| Read less — filter early, select only needed columns | Every engine |
| Enable pruning — partition elimination, Z-Order, KQL time filters | Lakehouse, Warehouse, Eventhouse |
| Pre-compute — materialized views, aggregate tables, gold layer | Warehouse, Eventhouse, Lakehouse |
| Keep statistics fresh | Warehouse |
| Right-size files — OPTIMIZE, avoid over-partitioning | Lakehouse, Direct Lake models |
| Join order and strategy — small side broadcast, star schema shape | Spark, Warehouse |
| Avoid row-by-row logic — set-based over cursors/UDFs | Warehouse, Spark |
Scenario quick reference
| Scenario | Deciding constraint | Answer |
|---|---|---|
| Find why last night's pipeline failed | Run history | Monitoring hub |
| Find which item is exhausting the capacity | CU consumption | Capacity Metrics app |
| Prove who exported a dataset | Security forensics | Purview audit log |
| Custom alerting on queryable Fabric logs | KQL over diagnostics | Workspace monitoring + Activator |
| Start a pipeline when a metric crosses a threshold | Detect and act | Activator rule |
| Copy succeeded but rows are missing | Silent filtering | Compare rowsRead / rowsCopied / rowsSkipped |
| Dataflow Gen2 out of memory | Local evaluation | Enable staging + restore query folding |
Notebook fails on collect() | Driver memory | Aggregate first / write to a table |
| Library missing on every scheduled run | Persistence | Add to the Environment, not %pip install |
| FileNotFoundException after maintenance | Retention | VACUUM removed files still referenced |
| Cannot INSERT via T-SQL | Read-only endpoint | Use a Warehouse (or write with Spark) |
| Duplicates despite a primary key | Not enforced | Dedupe in the load — constraints are metadata only |
| KQL queries on old data are slow | Cache window | Extend the caching policy |
| Old KQL data has disappeared | Retention window | Retention policy too short |
| Eventstream backlog growing | Throughput | Simplify processing, add partitions |
| Some events never arrive at one consumer | Shared reader | Give each consumer its own consumer group |
| One Spark task runs 20× longer than the rest | Skew | Salt the key / rely on AQE skew join |
| Slow join, one table is tiny | Shuffle avoidance | Broadcast join |
| Thousands of small output files | Write shape | coalesce() before write + OPTIMIZE |
| Which Warehouse queries to tune first | Impact | queryinsights.frequently_run_queries |
| Notebooks take minutes to start | Cold start | Starter pool |