Hard — DP-700 Practice Quiz

Question 1

A Fabric administrator notices that interactive report queries in a workspace have started returning delayed responses in the afternoon, and some background semantic model refreshes are being rejected. The workspace is assigned to an F64 capacity. The admin opens the Microsoft Fabric Capacity Metrics app to investigate. Which observation would most directly confirm that the capacity is throttling operations due to accumulated overutilization?

Accepted Answer

The 'Throttling' chart on the Compute page shows smoothed usage exceeding the interactive-delay and rejection limits, and the 'System events' table lists throttling events.

Answer

The number of active users in the workspace exceeds the per-capacity user license limit for F64.

Answer

The OneLake storage consumption for the workspace has exceeded the included storage quota for the SKU.

Answer

The 'Items' table shows several semantic models larger than the maximum model size supported by the SKU.

Question 2

Users of a Power BI report backed by a Fabric warehouse complain that visuals take an unusually long time to render during business hours. You open the Microsoft Fabric Capacity Metrics app and see that during peak periods the capacity shows sustained overloading, with the timepoint detail indicating a high volume of carryforward from earlier background operations. Interactive operations are experiencing an added delay before execution. Which throttling stage is the capacity currently in, and what does it mean for these interactive queries?

Accepted Answer

Interactive Delay — future interactive requests are delayed at submission time because recent overuse exceeds the 10-minute threshold, but they still complete.

Answer

Interactive Rejection — all interactive requests are immediately blocked and return errors until the overload clears.

Answer

Background Rejection — scheduled background jobs are rejected while interactive queries continue to run normally without any delay.

Answer

No throttling occurs; the delay is caused only by missing statistics on the warehouse tables and is unrelated to capacity utilization.

Question 3

Your team uses a Fabric deployment pipeline with three stages: Development, Test, and Production. A Development-stage workspace contains a lakehouse, a warehouse, and a semantic model that connects to the warehouse. After you deploy all items from Development to Test for the first time, you notice the semantic model in Test still points to the warehouse in the Development workspace instead of the one in Test. You want future and current deployments to automatically repoint the semantic model to the warehouse within the same target stage without manually editing connections after every deployment. What should you do?

Accepted Answer

Configure a data source deployment rule on the semantic model in the Test stage to point to the Test warehouse.

Answer

Rely on the deployment pipeline's automatic binding, which repoints dependencies when all related items are deployed together in the same operation.

Answer

Recreate the semantic model manually in the Test workspace and delete the deployed copy.

Answer

Enable Git integration on the Test workspace so the connection strings sync from the repository.

Question 4

A data engineering team manages a Fabric workspace containing lakehouses, notebooks, and semantic models. They want to enforce peer review of every code change through pull requests before changes are merged, while also promoting validated content across Development, Test, and Production workspaces without manually rebuilding items. Which combination of lifecycle management features should they configure to satisfy BOTH requirements?

Accepted Answer

Use Git integration with a feature-branch workflow for pull-request review, and use deployment pipelines to promote items across the three workspace stages.

Answer

Use deployment pipelines alone, relying on stage comparison and deployment rules to handle code review and promotion.

Answer

Use Git integration alone, committing directly to the main branch and syncing each workspace to a different branch for promotion.

Answer

Export items as database projects, review the SQL in a repository, and manually import them into each workspace stage.

Question 5

A data engineering team uses Git integration to manage source control for their Fabric workspace, which is connected to a feature branch. Separately, the team lead has configured a deployment pipeline with Development, Test, and Production stages. A developer completes changes in the workspace, commits them to Git, and now needs to promote validated content from Development to Test. Which statement correctly describes how these two lifecycle management features work together in this scenario?

Accepted Answer

Git integration handles versioning and collaboration for a single workspace, while deployment pipelines promote content between separate workspaces assigned to each stage.

Answer

Deployment pipelines and Git integration are mutually exclusive; enabling one automatically disables the other for the workspace.

Answer

Committing changes to Git automatically triggers deployment to the next pipeline stage without any manual promotion step.

Answer

Deployment pipelines read content directly from the connected Git branch rather than from the workspace, so the commit must be merged to main before promotion.

Question 6

An Eventhouse in your Real-Time Intelligence solution stores 18 months of telemetry data for compliance, but analysts only run interactive dashboards against the most recent 30 days. Queries against recent data have become noticeably slower over time, and capacity monitoring shows high memory pressure on the Eventhouse. You need to improve interactive query performance for recent data while retaining all 18 months for occasional audits, keeping costs reasonable. What should you configure?

Accepted Answer

Set the hot cache (caching policy) to 30 days while keeping the retention policy at 18 months on the KQL database or table.

Answer

Reduce the retention policy to 30 days and export older data to a lakehouse via a scheduled pipeline.

Answer

Set both the hot cache policy and the retention policy to 18 months so all data is available for fast queries.

Answer

Create a materialized view that filters the last 30 days and query the view instead of the base table.

Question 7

A retail company streams point-of-sale transactions into a Fabric Eventstream. Analysts need to run sub-second KQL queries against the incoming data with full indexing and the lowest possible query latency, while also making the same data available in the lakehouse for periodic Spark batch reporting without duplicating storage. Which approach should you implement for the Eventhouse destination?

Accepted Answer

Land the data in a native Eventhouse (KQL) table for low-latency queries, then create a OneLake shortcut from the lakehouse to that table for Spark batch reporting

Answer

Create a OneLake shortcut in the Eventhouse pointing to a lakehouse Delta table, and query the shortcut with KQL

Answer

Write the Eventstream output only to a lakehouse Delta table and query it directly with KQL from the Eventhouse

Answer

Copy the data into both a native Eventhouse table and a separate lakehouse Delta table using a scheduled pipeline

Question 8

A retail analytics team ingests clickstream events into an Eventhouse via an Eventstream. Raw JSON events land in a table named RawEvents. The team needs every incoming row to be automatically parsed, filtered to remove bot traffic, and written to a curated table with a flattened schema — with the transformation applied at ingestion time inside the KQL database, requiring no external orchestration and minimal added latency. Which approach should they use?

Accepted Answer

Create a KQL update policy on the curated table that runs a transformation query against RawEvents at ingestion time

Answer

Schedule a Dataflow Gen2 to read RawEvents every 5 minutes, transform, and load the curated table

Answer

Add a Spark Structured Streaming job that reads the Eventhouse table and writes the curated output

Answer

Create a KQL materialized view over RawEvents to deduplicate and flatten the data

Question 9

A telemetry pipeline lands IoT device readings into a KQL database table in an Eventhouse. Devices frequently resend the same reading (identified by DeviceId and ReadingTimestamp) due to network retries, producing duplicate rows. Analysts must always query only the most recent version of each reading with minimal query latency, and the deduplication logic should be precomputed and kept current automatically as new data arrives. What should you implement?

Accepted Answer

A materialized view using arg_max() over the ingestion time, grouped by DeviceId and ReadingTimestamp

Answer

An update policy that writes deduplicated rows into a second table on each ingestion batch

Answer

A stored function that applies summarize arg_max() at query time whenever analysts run reports

Answer

A retention policy that automatically deletes older duplicate rows after a short soft-delete window

Question 10

A retail company streams point-of-sale transactions into an Eventhouse (KQL database) in Microsoft Fabric. Analysts need a query that, for each store, continuously computes the total sales amount over the most recent 15 minutes, recalculated every 5 minutes, so overlapping periods are reported to detect sudden spikes. Which KQL windowing approach should you use to satisfy this requirement?

Accepted Answer

Use a hopping window logic where the window size is 15 minutes and the hop (advance) is 5 minutes, producing overlapping periods.

Answer

Use the summarize operator with bin() on 15-minute buckets to create fixed, non-overlapping tumbling windows.

Answer

Use the summarize operator with bin() on 5-minute buckets so every window covers exactly one interval.

Answer

Use the row_window_session() function to group transactions by inactivity gaps between purchases.

Question 11

A retail company streams clickstream events into Microsoft Fabric. Analysts need to run interactive ad-hoc queries that filter and aggregate the last 90 days of events with sub-second response times, using time-series operators. The same raw data must also be retained for 7 years at the lowest possible storage cost for occasional compliance audits. Which storage design best meets both requirements?

Accepted Answer

Ingest into an Eventhouse for the hot 90-day window, and use a OneLake availability / export to Delta so older data lands cheaply in a lakehouse for long-term retention.

Answer

Store all 7 years of data in an Eventhouse (KQL database) and query it directly for both interactive analytics and compliance audits.

Answer

Store all data only in a lakehouse Delta table and query it with the SQL analytics endpoint for both interactive and compliance needs.

Answer

Store the 90-day data in a Warehouse using T-SQL and archive older data with a Dataflow Gen2 to a separate Warehouse.

Question 12

A data engineer runs a Spark notebook in Fabric that joins a large fact DataFrame with several transformations. The job completes but takes 45 minutes, far longer than expected. In the Spark UI for the run, one stage shows a very high 'Shuffle Spill (Disk)' value and several tasks running much longer than others. What is the MOST likely root cause to investigate first?

Accepted Answer

Data skew causing uneven partition sizes and memory pressure that spills shuffle data to disk

Answer

The Delta table has too many V-Order optimized files

Answer

The notebook is using the wrong sensitivity label, throttling reads

Answer

High-concurrency mode is disabled on the Spark session

Question 13

You manage a Fabric lakehouse containing a Delta table named Employees that holds payroll data. The finance team must query all columns, but a broader analyst group must be able to see every row while being blocked from viewing the Salary and BonusAmount columns. You want to enforce this directly in OneLake so the restriction applies consistently whether the data is accessed through the SQL analytics endpoint, Spark notebooks, or the lakehouse itself. What should you configure?

Accepted Answer

Create a OneLake security role that grants the analyst group read access to the Employees table but excludes the Salary and BonusAmount columns via column-level security.

Answer

Apply dynamic data masking to the Salary and BonusAmount columns on the SQL analytics endpoint for the analyst group.

Answer

Create a T-SQL view that omits the sensitive columns and grant the analyst group SELECT permission only on that view.

Answer

Assign the analyst group the Viewer workspace role and remove their access to the Employees table folder.

Question 14

A Fabric lakehouse contains a Delta table with sensitive HR salary data. You have defined a OneLake security role named 'AnalystRole' that grants read access only to non-sensitive columns of the table. A user, Maya, needs to query the table through the SQL analytics endpoint and via Spark notebooks, but she must never see the salary column. Maya is currently assigned the Viewer role on the workspace. What is the correct approach to ensure Maya's access is restricted to the non-sensitive columns across both access paths?

Accepted Answer

Remove Maya from the workspace Viewer role and add her only as a member of the OneLake security role 'AnalystRole', which enforces the column restriction consistently across OneLake access paths.

Answer

Keep Maya in the Viewer role and rely on it, because the Viewer role automatically inherits OneLake security role restrictions.

Answer

Add Maya to the workspace Admin role so the OneLake security role can be applied to her queries.

Answer

Create a T-SQL column-level GRANT on the SQL analytics endpoint only, since OneLake security roles do not apply to Spark notebooks.

Question 15

A data engineer runs a Fabric pipeline whose single Copy activity loads data from an on-premises SQL Server (via an on-premises data gateway) into a lakehouse Delta table. The activity succeeds but takes far longer than expected. In the Copy activity's monitoring detail view, the engineer sees that 'Duration' is dominated by a large value under 'Time to first byte' and 'Queue' time, while 'Throughput' and 'Data read/written' rates are low. What is the MOST likely cause of the slow performance?

Accepted Answer

The source query and gateway are the bottleneck — the gateway machine is under-resourced or the source query is slow, delaying data before the copy engine can transfer it

Answer

The lakehouse Delta table needs OPTIMIZE and Z-ORDER to reduce the number of small files being written

Answer

The pipeline schedule is triggering during a capacity throttling window, so the activity is being smoothed

Answer

Incremental refresh is not enabled on the destination table, forcing a full reload each run

Question 16

A nightly pipeline uses a single Copy activity to move 500 GB from an on-premises SQL Server (via an on-premises data gateway) into a Fabric Lakehouse Delta table. The copy takes over 6 hours, and you must reduce the duration. When you inspect the Copy activity's monitoring details, you see the Data Integration Units (DIU) used are automatically capped and throughput plateaus far below the destination's capability. What is the MOST likely bottleneck and the appropriate first action?

Accepted Answer

The on-premises data gateway is the bottleneck; scale out by adding gateway nodes and increasing the gateway's concurrent job/CPU limits

Answer

The destination Lakehouse is throttled; enable V-Order and Z-Order on the target Delta table before the copy runs

Answer

Insufficient DIUs; manually raise the DIU setting to 256 on the Copy activity to remove the automatic cap

Answer

The source query lacks statistics; run UPDATE STATISTICS on the SQL Server table to speed up the extract

Question 17

A data engineer runs a nightly PySpark notebook in Fabric that joins a 2 TB fact table with a 400 GB dimension table. The job completes, but Spark UI shows that most tasks finish in under a minute while a handful of tasks run for over 40 minutes, dominating total runtime. The delayed tasks all process the same small number of join keys that account for the majority of the fact rows. What is the most effective way to reduce the overall job duration?

Accepted Answer

Enable Adaptive Query Execution (AQE) with skew join optimization so Spark automatically splits the oversized skewed partitions into smaller balanced tasks

Answer

Increase the number of executors and executor cores in the Spark pool to add more parallelism across all tasks

Answer

Broadcast the 400 GB dimension table to every executor so the shuffle-based join is avoided entirely

Answer

Run OPTIMIZE with V-Order on both Delta tables to compact small files before the join executes

Question 18

A data engineer builds a Spark Structured Streaming job in a Fabric notebook that reads IoT sensor readings from an Eventstream into a Delta table. The requirement is to compute a 10-minute tumbling-window average temperature per device. Sensor events can arrive up to 5 minutes late, and the team wants Spark to bound the amount of in-memory state it must keep for aggregation while still admitting reasonably late events. Which approach correctly implements this?

Accepted Answer

Apply withWatermark on the event-time column with a 5-minute delay threshold, then group by a 10-minute window and device to aggregate the average

Answer

Group by a 10-minute window and device without a watermark, and set spark.sql.streaming.statefulOperator.stateTimeout to 5 minutes

Answer

Use foreachBatch to manually filter out events older than 5 minutes before writing each micro-batch to the Delta table

Answer

Set the trigger interval to 5 minutes so late events are naturally included, then group by device only and compute a running average

Question 19

A data engineer manages a Fabric warehouse where a large fact table (2 billion rows) is frequently joined to a date dimension in analytical queries. Query monitoring shows most execution time is spent redistributing data between compute nodes during these joins. The engineer wants to reduce this data movement without changing the query logic. Which action is most likely to improve join performance?

Accepted Answer

Ensure statistics are up to date and rely on the warehouse's automatic distribution optimization for the join keys

Answer

Create a nonclustered columnstore index on the join columns of both tables

Answer

Convert the warehouse to a lakehouse and enable V-Order on the fact table

Answer

Increase the capacity SKU so more compute is available during the join

Question 20

A data engineer manages a Fabric Warehouse with a large fact table. A frequently run reporting query filters on a single OrderDate value but consistently scans the entire table, causing high CU consumption. The engineer opens the estimated query plan and confirms a full table scan. Statistics were recently updated and the query already uses a WHERE clause on OrderDate. What is the MOST effective next step to reduce the amount of data read by this query?

Accepted Answer

Rely on Fabric Warehouse automatic min/max rowgroup elimination by ensuring the fact data is physically ordered (sorted) by OrderDate during load.

Answer

Create a nonclustered columnstore index on the OrderDate column to force an index seek.

Answer

Add the OPTION (RECOMPILE) query hint so the optimizer regenerates the plan on every execution.

Answer

Enable result set caching so subsequent runs of the query return instantly.

Question 21

A Fabric data warehouse contains a large fact table, FactSales (1.2 billion rows), that is frequently joined to a large dimension table, DimCustomer (40 million rows), on CustomerKey. Analysts report that queries joining these two tables are slow, and when you inspect the query plan you see a large data movement (shuffle/redistribution) operation before the join. Both tables are currently loaded without any distribution strategy specified. What is the most effective way to reduce this data movement and improve join performance?

Accepted Answer

Distribute both FactSales and DimCustomer using hash distribution on CustomerKey so matching rows are co-located.

Answer

Change DimCustomer to a replicated table and leave FactSales round-robin distributed.

Answer

Create a nonclustered columnstore index on CustomerKey in both tables.

Answer

Enable result set caching so repeated join queries return instantly.

Question 22

A scheduled T-SQL stored procedure in a Fabric Warehouse joins a large fact table to several dimensions and fails intermittently with a query timeout during peak hours. The Capacity Metrics app shows the capacity is not throttled, and the same procedure succeeds when run manually off-peak. You need to identify the most likely root cause and a targeted resolution without over-provisioning. What should you do first?

Accepted Answer

Review the query execution plan and statistics; update stale statistics on the joined tables and rewrite the query to reduce data movement so it completes within the timeout window.

Answer

Increase the Fabric capacity SKU to a higher tier to guarantee more compute for the procedure during peak hours.

Answer

Enable result set caching on the Warehouse so the procedure returns cached results and avoids re-executing during peak load.

Answer

Convert the Warehouse tables to a Lakehouse and run the join in a Spark notebook to bypass the T-SQL engine timeout.

Question 23

A data engineering team runs Fabric notebooks that must read data from an Azure SQL database secured behind a private endpoint with public network access disabled. The team needs Spark workloads in their Fabric workspace to reach the database over the Microsoft backbone without exposing it to the public internet. Which workspace-level configuration should they enable?

Accepted Answer

Enable managed private endpoints (managed virtual network) for the workspace and create a managed private endpoint targeting the Azure SQL database

Answer

Configure a workspace identity and grant it Contributor access to the Azure SQL server

Answer

Assign the workspace to a domain and enable OneLake external data sharing

Answer

Enable high-concurrency Spark sessions so notebooks share a single secured session