DP-700 Monitor and Optimize an Analytics Solution Practice Test 3 — 15 Questions

Question 1

Your team is using a Fabric Lakehouse to store Parquet files. You notice that the storage costs are increasing faster than expected, and there are many old versions of the data being retained. Which maintenance task should you schedule to optimize OneLake storage costs without affecting the current production data?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Run the VACUUM command on the Delta tables.. Delta tables in Fabric maintain a history of changes to support features like 'Time Travel'. However, this history consumes storage space over time as old, unreferenced Parquet files are kept. The VACUUM command is the standard maintenance operation to permanently delete these old files, balancing historical data needs with storage efficiency.

Answer

Run the OPTIMIZE command on all tables.

Answer

Change the workspace license to 'Pro'.

Answer

Delete the '_delta_log' folder in OneLake.

Question 2

A Power BI report using a semantic model in 'Direct Lake' mode is performing poorly. You want to determine if the queries are falling back to 'DirectQuery' mode due to memory constraints or unsupported DAX features. Which tool is best suited for this specific optimization task?

Accepted Answer

DAX Studio. Direct Lake mode provides the performance of Import mode by reading Delta files directly from OneLake, but it can fall back to DirectQuery if certain conditions are not met. Using tools that can capture trace events and query execution plans is vital for identifying these fallbacks and ensuring the model remains in the high-performance Direct Lake state.

Answer

Spark UI

Answer

SQL Server Management Studio (SSMS) Activity Monitor

Answer

OneLake Explorer

Question 3

You are managing a large-scale data ingestion pipeline that runs every hour. Recently, the pipeline has started to fail with 'Concurrency Limit Exceeded' errors. You have several other pipelines running in the same workspace. How should you optimize the environment to resolve this error?

Accepted Answer

Implement a staggered schedule for the pipelines in the workspace.. Fabric capacities have limits on the number of concurrent operations that can run. When multiple pipelines or activities start simultaneously, they can exceed these limits. Implementing a more staggered schedule or using pipeline concurrency settings ensures that the workload stays within the allowed limits of the capacity SKU without failing.

Answer

Use the 'Invoke Pipeline' activity with 'Wait on completion' disabled.

Answer

Increase the 'Max concurrent runs' setting in the pipeline properties.

Answer

Switch all pipelines to use the 'Small' Spark pool size.

Question 4

Refer to the exhibit. You are reviewing the monitoring logs for your Fabric capacity. What is the most likely cause of the error shown in the exhibit?

Accepted Answer

The Fabric capacity is experiencing throttling.. The '429 Too Many Requests' error indicates that the Fabric capacity has reached its limits for concurrent requests or resource consumption. This occurs when the workload exceeds the allocated capacity units (CU) or the throughput limits set for that specific capacity type. To resolve this, you must analyze if the workload needs more resources, requires optimization, or needs to be distributed across different capacities.

Answer

The underlying data source is offline.

Answer

The user does not have read permissions.

Answer

The query contains a syntax error.

Question 5

You want to monitor the health and performance of your Microsoft Fabric pipelines. Which tool should you use to view detailed execution logs and identify bottlenecks in your data movement?

Accepted Answer

Fabric Monitoring Hub. The Fabric Monitoring Hub provides a centralized view of all activities, including pipeline executions. It allows you to track status, duration, and failures for all items within your workspace. By accessing the execution details of a specific pipeline run, you can inspect activity-level durations, which is essential for identifying bottlenecks in your ELT/ETL processes and ensuring your data workflows remain within expected performance windows.

Answer

Power BI Desktop

Answer

Azure Resource Graph

Answer

Microsoft Purview

Question 6

Which THREE features are provided by the Fabric Capacity Metrics app for monitoring performance?

Accepted Answer

Identifying which users are consuming the most capacity.. The Fabric Capacity Metrics app is the standard tool for monitoring capacity health. It provides insights into when a capacity is overloaded, allowing you to identify which users or items are driving that consumption. By monitoring background operations and interactive queries, engineers can determine if they need to scale up their capacity or optimize existing workloads that consume too many compute resources.

Answer

Modifying the underlying SQL Server instance settings.

Answer

Automatically scaling the capacity SKU.

Question 7

You are optimizing a Spark job in a Fabric notebook that joins a large fact table with a small dimension table. The join operation is causing high memory usage and slow performance. What technique should you implement?

Accepted Answer

Use a broadcast join hint.. Broadcasting the smaller table sends a copy of that data to every executor node, preventing the need for a shuffle operation. A shuffle is a costly network-intensive operation where data is redistributed across the cluster. By using a broadcast join, you minimize network traffic and memory pressure, significantly improving the join speed and resource efficiency for large-scale data processing in Spark.

Answer

Increase the number of partitions for the large table.

Answer

Force a cross join.

Answer

Enable dynamic partition pruning.

Question 8

Your organization uses Power BI in Fabric. Users report that reports are slow to load. You want to identify which specific visuals are causing the performance bottleneck. Which tool should you use?

Accepted Answer

Power BI Performance Analyzer. Performance Analyzer is the built-in tool in Power BI Desktop designed to capture the execution duration of every visual on a report page. It provides a breakdown of time spent on DAX queries, visual rendering, and other processes. This allows developers to isolate slow-performing visuals, optimize the underlying DAX expressions, or simplify the report page layout to enhance the end-user experience.

Answer

SQL Server Profiler

Answer

Fabric Monitoring Hub

Answer

Power BI Service Audit Logs

Question 9

You are optimizing a Data Warehouse in Microsoft Fabric. Which TWO strategies will effectively improve the performance of read-heavy workloads?

Accepted Answer

Implement Clustered Columnstore Indexes.. For read-heavy workloads in a data warehouse, indexing and proper table design are critical. Clustered Columnstore Indexes provide massive data compression and are optimized for analytical queries that scan large volumes of data. Additionally, implementing partitions allows the engine to prune data at the storage level, ensuring that only relevant partitions are scanned, thus reducing the total I/O and accelerating query execution.

Answer

Increase the number of rows per transaction.

Answer

Use row-based storage exclusively.

Answer

Disable data compression.

Question 10

Refer to the exhibit. The refresh operation for a Power BI semantic model took 45 minutes to complete. What is the most efficient way to reduce the refresh time for this specific model?

Accepted Answer

Configure incremental refresh.. Incremental refresh is the most effective strategy for large datasets. Instead of reloading all 5 million rows every time, you configure the model to load only new or updated data based on a date/time column. This dramatically reduces the amount of data processed per refresh, lowering execution time and resource utilization on the Fabric capacity while keeping reports up to date.

Answer

Upgrade to a higher capacity SKU.

Answer

Use Power BI Desktop to manually refresh.

Answer

Delete all historical data.

Question 11

You have a Fabric notebook that uses multiple Spark libraries. You notice that the startup time for your notebook is very slow. What is the best way to optimize this?

Accepted Answer

Use a pre-configured Fabric Environment.. Environment definitions in Microsoft Fabric allow you to pre-configure and pre-install the necessary Spark libraries. By creating an environment and attaching it to your notebook, you eliminate the need to run %pip install commands at runtime. This removes the overhead of installing packages every time the Spark session starts, leading to significantly faster session startup times and more consistent compute configurations for your data engineering tasks.

Answer

Install libraries using %pip at the top of the notebook.

Answer

Store all libraries in a Lakehouse folder.

Answer

Increase the Spark cluster size.

Question 12

You are troubleshooting a slow-running SQL query in a Fabric Data Warehouse. The query plan shows a high cost associated with a 'Broadcast Nested Loop Join'. What should you do to optimize this?

Accepted Answer

Update the table statistics.. A Nested Loop Join is often inefficient for large datasets because it iterates through the outer table for every row in the inner table. If the optimizer chose a broadcast join on a large table, it might be due to outdated statistics. Updating statistics allows the query optimizer to make better decisions about join types, likely switching to a more efficient hash join that is better suited for large-scale data processing.

Answer

Increase the number of compute nodes.

Answer

Disable the query optimizer.

Answer

Rewrite the query using a cursor.

Question 13

When monitoring Fabric Data Pipelines, which TWO metrics are most useful for identifying performance issues in data copy activities?

Accepted Answer

Data throughput (MB/s).. In a copy activity, identifying data throughput and the duration of the 'Data Integration Unit' (DIU) utilization is critical. If the throughput is low, it suggests network constraints or inefficient serialization/deserialization. Monitoring the duration allows engineers to understand if the activity is hitting concurrency limits or if source/destination systems are struggling to keep up with the data volume requested by the copy operation.

Answer

Number of active users in the workspace.

Answer

Total number of rows in the destination table.

Answer

The name of the user who triggered the pipeline.

Question 14

Refer to the exhibit. The query is performing a full table scan on a 1TB table. What is the most effective way to optimize this query?

Accepted Answer

Replace 'SELECT *' with specific column names.. Selecting only the necessary columns (column projection) is a fundamental best practice in analytical query optimization. By avoiding 'SELECT *', you reduce the amount of data read from storage and transferred over the network. In column-oriented stores like those in Fabric, this allows the engine to only read the relevant columns, drastically reducing I/O and latency for queries on large datasets.

Answer

Increase the cluster memory size.

Answer

Add a clustered index to all columns.

Answer

Enable query parallelism.

Question 15

You are using the Microsoft Fabric Capacity Metrics app and notice that your capacity is frequently hitting the limit for 'Interactive' operations. What should you do first?

Accepted Answer

Use the metrics app to identify top-consuming items.. Before scaling up the capacity, you must identify the source of the high utilization. The Capacity Metrics app allows you to drill down into the operations that are consuming the most resources. By identifying the specific semantic models, reports, or users that are driving the high consumption of interactive operations, you can optimize those specific items, which may prevent the need for expensive upgrades to the capacity SKU.

Answer

Immediately scale up the capacity SKU.

Answer

Clear the browser cache for all users.

Answer

Delete all unused reports in the workspace.