DP-700 domain - 33% of the exam

Monitor and Optimize an Analytics Solution

Monitor and Optimize an Analytics Solution is 33% of the DP-700 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

85
Practice questions
8
Objectives
58
Medium
27
Hard

Where people go wrong

  • Confusing the Monitoring hub with the Capacity Metrics app

    The wrong answers pick the nearest-sounding monitoring surface. The Capacity Metrics app is offered for lists of failed runs and for Copy activity row counts, while a run list or the Monitoring hub is offered for throttling and per-item compute. The Monitoring hub lists runs of pipelines, Dataflows Gen2 and notebooks with status and start time, and drilling in reaches activity output. The Capacity Metrics app shows utilisation, throttling and consumption by item over a chosen time range.

    See it in a sample question
  • Mixing up the three alert mechanisms

    The wrong answers swap which signal each alert watches. A refresh failure notification is offered for a business measure or for capacity throttling, and a capacity utilisation alert is offered for a measure. The refresh failure notification emails the owner when a scheduled semantic model refresh fails. A Data Activator trigger watches a report measure or an item event, such as a failed refresh, and sends a Microsoft Teams message without polling. Throttling is a capacity-level signal, tracked and alerted on through the Capacity Metrics app.

    See it in a sample question
  • Crediting OPTIMIZE, VACUUM and V-Order with each other's job

    The wrong answers hand one Delta maintenance command another's effect. VACUUM is offered to speed scans of small or scattered files, and OPTIMIZE is offered to reclaim storage. OPTIMIZE bin-compacts many small files into fewer larger ones and leaves the old files in place. VACUUM physically deletes data files that are unreferenced and older than the retention window. V-Order changes layout inside files but neither cuts the file count nor deletes any. Z-ordering co-locates rows on the filter columns so whole files can be skipped.

    See it in a sample question
  • Blaming indexes, distribution or caching for stale statistics

    A Warehouse plan estimates far fewer rows than the step returns, and the wrong answers reach for a clustered index, redistribution, result-set caching or a join hint. Fabric Warehouse does not use traditional clustered indexes, and a hint masks the symptom. Result-set caching speeds an identical repeat query on unchanged tables but does not change row estimates. The cause is statistics built before recent loads, so updating statistics on the predicate columns lets the optimiser estimate cardinality and choose the join.

    See it in a sample question
  • Fixing a broken OneLake shortcut with table maintenance

    When a shortcut fails, the wrong answers run VACUUM or OPTIMIZE, or refresh the SQL analytics endpoint metadata. Those act on Delta files inside a table or on endpoint metadata, not on what a shortcut stores: a target path and a credential. A renamed folder gives a path-not-found error, fixed by repointing the shortcut. An authorisation error points to expired or rotated S3 keys, or to a service principal that lost its Storage Blob Data Reader role.

    See it in a sample question

Objectives in this domain

What to study

The official documents this domain's practice questions cite most.

Sample questions from this domain

Free sampleMonitor and Optimize an Analytics Solutionmedium

Several Microsoft Fabric ingestion pipelines and notebooks in a workspace began running noticeably slower and some operations were delayed during a busy afternoon. The engineer suspects the shared capacity was overloaded and wants to confirm whether compute demand exceeded the capacity and triggered throttling at that time. Which tool gives the clearest evidence?

  • AThe Monitoring hub run list, filtered to the afternoon, showing each pipeline and notebook run's status and duration for the period in question.
  • BThe refresh history of each affected item, which records the start and end time of every run so the engineer can confirm the capacity was throttling that afternoon.
  • CThe Spark UI for one slow notebook, whose stage timeline shows the cluster was overloaded and therefore confirms the whole capacity was throttling that afternoon.
  • DThe Microsoft Fabric Capacity Metrics app, whose utilisation and throttling views show whether compute demand exceeded the capacity and caused throttling during that window. Correct
Use the Capacity Metrics app to confirm whether compute demand exceeded a Fabric capacity and triggered throttling. The Microsoft Fabric Capacity Metrics app aggregates compute usage across all items on a capacity and plots utilisation against the capacity limit, including overload and throttling indicators for each timepoint; this capacity-level telemetry is what proves whether afternoon demand exceeded the capacity, which per-run status, refresh history, and a single Spark UI cannot show.

Why A is wrong: The run list shows individual run status and duration but not whether the capacity itself was overloaded or throttling, so it shows the symptom rather than confirming the capacity cause.

Why B is wrong: Per-item refresh history gives run timings but no capacity utilisation or throttling signal, so it cannot confirm that compute demand exceeded the capacity during the slowdown.

Why C is wrong: The Spark UI explains one application's internal stages but reflects a single job, not the shared capacity's utilisation, so it cannot establish that the capacity as a whole exceeded its limit and throttled.

Why D is correct: The Capacity Metrics app charts capacity utilisation, overload, and throttling over time, so it is the tool that confirms whether afternoon demand exceeded the capacity and slowed the items.

Free sampleMonitor and Optimize an Analytics Solutionmedium

An operations team must be alerted in a Microsoft Teams channel whenever a key measure in a Power BI report on a Fabric semantic model crosses a defined business threshold, for example when the live order backlog exceeds a set value. They want a no-code way to watch the value and act when the condition is met. Which Microsoft Fabric capability is designed for this?

  • AConfigure a scheduled refresh failure notification on the semantic model so the team is messaged whenever the underlying refresh does not complete successfully.
  • BSet up a capacity utilisation alert in the admin portal so the team is notified when compute usage on the capacity passes a configured percentage.
  • CBuild a paginated report subscription that delivers the report on a recurring schedule so the team can read the current backlog value each time it arrives.
  • DCreate a Data Activator trigger on the report measure that sends a message to the Teams channel when the value crosses the defined threshold. Correct
Use a Data Activator trigger to alert a Teams channel when a Power BI measure crosses a business threshold, with no code. Data Activator observes values such as a Power BI report measure and runs an action when a defined condition is met, so it is built for sending a Teams message when the backlog exceeds a threshold; refresh notifications, capacity alerts, and subscriptions either watch a different signal or deliver on a schedule rather than on the condition.

Why A is wrong: Refresh failure notifications fire on a failed refresh, not on a business measure crossing a threshold, so they do not watch the order backlog value the team cares about.

Why B is wrong: A capacity utilisation alert monitors compute consumption, not a report measure, so it cannot detect when the business backlog value exceeds its threshold.

Why C is wrong: A scheduled subscription delivers the report at fixed times regardless of the value, so it neither watches the threshold continuously nor triggers an action when the condition is met.

Why D is correct: Data Activator monitors a Power BI measure and fires an action such as a Teams message when a condition is met, giving the team the no-code, event-driven alert they require.

Free sampleMonitor and Optimize an Analytics Solutionhard

A wide Lakehouse Delta table is already compacted with OPTIMIZE, yet a key set of analytical queries remains slow. These queries consistently filter on both a region column and a product-category column together, but the data was loaded in arrival order, so matching rows are scattered evenly across all files and almost no files can be skipped. The team wants the engine to skip more files for these multi-column filters without re-partitioning the table. Which optimisation best achieves this?

  • AApply Z-ordering on the region and product-category columns so co-located values let the engine skip files through data-skipping statistics. Correct
  • BRun VACUUM so the scattered older files are purged and the queries then read only the recent files that contain the filtered rows.
  • CIncrease the V-Order sorting so each file is internally sorted by region and category, which lets the engine skip whole files for these filters.
  • DRun OPTIMIZE again with a larger target file size so fewer, bigger files reduce how many the multi-column filter has to open.
Use Z-ordering on the columns queries filter together so co-located values let data-skipping statistics prune more files. Data skipping relies on per-file min and max statistics, which only help when matching rows are clustered; Z-ordering reorganises the data so rows sharing region and category land in the same files, narrowing those statistics so the engine can skip files that cannot satisfy the multi-column filter, which plain compaction and V-Order do not do.

Why A is correct: Z-ordering clusters rows with similar region and category values into the same files, tightening the per-file min and max statistics so data skipping can prune files that cannot match the multi-column filter.

Why B is wrong: VACUUM removes unreferenced files for storage reclamation and does not cluster matching rows together, so the filtered rows stay scattered and file skipping does not improve.

Why C is wrong: V-Order improves within-file encoding and read efficiency but does not co-locate matching rows across files, so it cannot create the cross-file clustering that drives file-level data skipping.

Why D is wrong: Larger files from plain compaction still hold rows in arrival order, so matching rows remain spread across every file and the engine still cannot skip files for the region and category filters.

Free sampleMonitor and Optimize an Analytics Solutionhard

A reporting query against a Microsoft Fabric Warehouse fact table has slowed badly over the past month while the data volume has grown. Inspecting the plan, an engineer sees the optimiser estimating a few hundred rows for a filtered date predicate when the step actually returns millions, which leads it to pick a nested-loop join and underspill memory. No table or column structure has changed. What is the most likely cause and the appropriate first action?

  • AThe Warehouse lacks a clustered index on the date column, so the engineer should create one to give the optimiser a sorted access path for the predicate.
  • BThe result-set cache has expired, so the engineer should rerun the query twice to repopulate the cache and let subsequent executions read the cached result.
  • CThe statistics on the date column are stale relative to recent loads, so the engineer should update statistics so the optimiser estimates cardinality accurately and chooses a better join. Correct
  • DThe table distribution has drifted toward skew, so the engineer should rebuild the table with a different hash distribution column to even out the row estimate.
Recognise that a large estimated-versus-actual row gap on a growing Warehouse table points to stale statistics, fixed by updating statistics. The optimiser builds join strategy and memory grants from statistics that summarise column value distribution; when recent loads outpace the last statistics refresh, estimates lag reality and a nested-loop plan is chosen for what is really a large set. Updating statistics restores accurate cardinality so the optimiser selects a hash join sized for the true row count.

Why A is wrong: Fabric Warehouse does not use traditional clustered indexes, so this action is not available; the cardinality misestimate stems from stale statistics, not a missing index access path.

Why B is wrong: Result-set caching speeds up repeated identical queries but does not change the optimiser's row estimates or join choice, so it cannot fix a plan built on a wrong cardinality estimate.

Why C is correct: A large gap between estimated and actual rows on a growing table is the classic signature of stale statistics; refreshing them lets the optimiser size the join correctly and stop picking a nested loop for millions of rows.

Why D is wrong: Distribution skew affects parallel data placement, not the per-column row estimate the optimiser derives from a predicate, and the stem states no structure changed, so redistribution does not address the cardinality error.

Free sampleMonitor and Optimize an Analytics Solutionmedium

A OneLake shortcut in a Microsoft Fabric Lakehouse points to a container in an Azure Data Lake Storage Gen2 account using a delegated connection backed by a service principal. The shortcut worked for months, then all reads through it began failing with an authorisation error from the storage account. The path, container, and shortcut definition are unchanged, and the storage account is online. Which cause should the engineer investigate first?

  • AThe Lakehouse SQL analytics endpoint metadata sync stalled, so the engineer should refresh the endpoint to restore reads through the affected shortcut.
  • BThe shortcut switched to passthrough authentication, so each consumer's own identity is now evaluated and most consumers lack direct access to the storage account.
  • CThe service principal behind the delegated connection lost its Storage Blob Data Reader role on the storage account, so it can no longer authorise reads through the shortcut. Correct
  • DThe source container was renamed by the storage team, so the shortcut now resolves a path that no longer exists and returns a path-not-found error on every read.
Delegated ADLS shortcuts authorise reads with a stored service principal, so a removed role assignment or expired secret breaks previously working reads. A delegated OneLake shortcut to ADLS Gen2 uses the connection's stored credential for every read, and that service principal must retain a Storage Blob Data Reader, Contributor, or Owner role; an authorisation error with an unchanged path most often means the role assignment was revoked or the credential expired, so the credential and its storage role are the first thing to check.

Why A is wrong: Endpoint metadata sync affects newly created tables, not authorisation from the storage provider; an explicit authorisation error points at the delegated credential, not the metadata layer.

Why B is wrong: Shortcuts do not silently change their authentication model, and ADLS Gen2 shortcuts use the delegated credential rather than passthrough, so this misattributes the failure to a mode that is not in use.

Why C is correct: Delegated ADLS shortcuts authorise every read with the stored service principal, which needs a Storage Blob Data Reader or higher role; if that role assignment was removed or its secret expired, previously working reads fail with an authorisation error.

Why D is wrong: A rename would raise a path-not-found error rather than an authorisation error, and the scenario states the container name is unchanged, so this does not fit the symptom.

Other domains in this exam

See also the DP-700 cert hub, the study guide, and the cheat sheet.

Examworthy is not affiliated with or endorsed by Microsoft. Original, blueprint-aligned practice material only.