DP-700 Practice Questions 2 — 50 Questions

Question 1

You need to ingest data from an on-premises SQL Server database into a Fabric Lakehouse. What must be installed and configured on a local server to facilitate this connection?

Accepted Answer

On-premises Data Gateway.. The On-premises Data Gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that isn't in the cloud) and Microsoft cloud services like Fabric. It is a requirement for accessing local data sources that are not publicly accessible over the internet.

Answer

Azure Data Factory Integration Runtime.

Answer

SQL Server Integration Services (SSIS).

Answer

Microsoft Entra Connect.

Question 2

You are monitoring long-running Spark jobs in your Fabric workspace. You want to identify which specific stages of the job are consuming the most time. Which tool should you use?

Accepted Answer

Spark UI. The Spark UI (part of the Fabric monitoring tools) provides a granular view of job execution, including stage-by-stage breakdowns. This allows engineers to identify bottlenecks where specific stages might be skewing or taking longer than expected. Understanding the stage breakdown is essential for tuning Spark performance, as it highlights inefficient operations like excessive shuffling, data skew, or inefficient data partitioning that can delay the overall completion of complex data pipelines.

Answer

Fabric Capacity Metrics app

Answer

OneLake file explorer

Answer

Azure Monitor logs

Question 3

You are monitoring a Microsoft Fabric Lakehouse. You notice that queries against a specific Delta table are performing slowly despite the table having a small data size. You need to identify the root cause of the performance degradation. What should you examine first?

Accepted Answer

The total number of files within the table directory.. High latency in Delta tables often stems from an excessive number of small files, which increases metadata overhead. Optimizing file size via compaction is a standard best practice in Fabric. By examining the table's file metadata, you can determine if a 'small file problem' exists, which is a common performance bottleneck in analytical workloads. Monitoring these metrics allows you to proactively trigger maintenance tasks like OPTIMIZE and VACCUUM to ensure efficient query execution and storage utilization.

Answer

The number of partitions in the table metadata.

Answer

The CPU utilization of the Spark pool.

Answer

The storage account throughput limits.

Question 4

When creating a Dataflow Gen2, what is the primary advantage of using a staging-enabled destination?

Accepted Answer

It enables query folding and performance optimization. Staging-enabled destinations in Dataflow Gen2 allow the mashup engine to perform intermediate data operations in the Fabric lakehouse. This improves performance by enabling query folding and reducing the data transfer load on the source system. It effectively offloads complex transformations to the cloud storage, ensuring that the final data load is optimized and consistently formatted for downstream usage in the Fabric ecosystem.

Answer

It increases the number of concurrent users

Answer

It reduces the cost of storage

Answer

It automatically encrypts the data

Question 5

You are optimizing a Fabric pipeline that processes data from a high-frequency sensor source. You notice significant data skew during the join operation. Which three strategies should you implement to mitigate this skew?

Accepted Answer

Apply salting to the skewed join key.. Data skew occurs when one partition or key carries significantly more data than others, causing some executors to work much longer than others. Using salting to redistribute skewed keys, enabling skewed join optimization, or pre-aggregating the skewed dataset are proven techniques to balance the workload across the cluster. Implementing these strategies ensures that no single worker becomes a bottleneck, leading to more predictable execution times and preventing memory failures in large-scale data processing jobs.

Answer

Increase the number of partitions to the maximum.

Answer

Change the file format to CSV.

Question 6

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 7

You have a Fabric pipeline that runs a notebook. The notebook takes longer to start each time. What is the most likely cause?

Accepted Answer

The Spark session initialization is delayed.. Spark notebooks in Fabric utilize 'Serverless Spark' pools. If the pool is not 'warm' or if the session initialization is competing for resources, the startup time increases. Understanding how session management works in Fabric is key to optimizing performance. Pre-warming pools or maintaining session persistence can help reduce these startup latencies, which are often overlooked but contribute significantly to the total end-to-end execution time of automated analytical data pipelines.

Answer

The Lakehouse storage is too full.

Answer

The notebook code has too many comments.

Answer

The user does not have sufficient permissions.

Question 8

Refer to the exhibit. You are reviewing the execution plan for a query running on a Fabric SQL Analytics Endpoint. The query is performing slower than expected. Based on the JSON snippet of the plan, what is the most likely cause of the performance bottleneck?

Accepted Answer

The BroadcastExchange is moving too much data for a large table join.. Analyzing query plans in the SQL Analytics Endpoint is vital for performance tuning. The presence of a BroadcastExchange for a table that results in a very high output count after a HashJoin suggests that the data being moved across the network is excessive. This often happens when the engine incorrectly estimates the size of a table or when data is not properly distributed.

Answer

The RemoteScan is failing to find the Parquet files in OneLake.

Answer

The HashJoin is using an unsupported data type for the join condition.

Answer

OneLake storage is currently in a read-only state due to maintenance.

Question 9

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 10

Refer to the exhibit. A data engineer is troubleshooting a Spark job that frequently fails with 'Out of Memory' (OOM) errors despite the configuration shown. The job processes a 500GB dataset with many wide transformations. What is the most effective configuration change to resolve the OOM errors?

Accepted Answer

Increase executorMemory and decrease executorCores per executor.. OOM errors in Spark often occur when the ratio of memory to cores is too low for the data being processed, or when the shuffle partitions are too large. While increasing the number of executors helps with parallelism, it doesn't solve memory pressure per task. Adjusting the executor memory or reducing the cores per executor ensures each task has more available heap space.

Answer

Set dynamicAllocation to false and use 50 static executors.

Answer

Increase the executorCores to 8 to allow more tasks to run.

Answer

Set vorderEnabled to false to reduce the memory overhead.

Question 11

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 12

You are analyzing a performance bottleneck using the 'Timepoint Detail' page in the Fabric Capacity Metrics app. Which THREE pieces of information can you find here to help identify the specific cause of a capacity overage? (Select THREE)

Accepted Answer

The name of the operation and the item that triggered it.. The Timepoint Detail page is the most granular view in the Capacity Metrics app. It allows you to see exactly what was happening during a specific 30-second window when the capacity was under load. This detail is essential for identifying 'noisy neighbors' or specific jobs that are consuming more than their fair share of resources.

Answer

The physical IP address of the Spark executor nodes.

Answer

The SQL execution plan for every query in the timepoint.

Question 13

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 14

Which feature in Microsoft Fabric allows you to track the movement and transformation of data from source to destination?

Accepted Answer

Data Lineage. Data lineage provides a visual representation of the path data takes across a system. In Fabric, this is critical for debugging failures and understanding dependencies. By tracking lineage, you can quickly determine how a change in an upstream data source impacts downstream reports, facilitating better impact analysis and ensuring data quality across the entire analytical ecosystem.

Answer

Data Pipeline

Answer

OneLake

Answer

Notebook

Question 15

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 16

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.

Question 17

You are auditing a Fabric workspace to ensure optimal performance. You want to see the execution history and duration of all Spark notebooks run within the last 30 days. Where should you look?

Accepted Answer

The Workspace Monitor Hub. The Workspace Monitoring hub provides a centralized interface for viewing the activity of Spark-based items. By accessing the monitor tab, you can view job status, start times, and durations. This is vital for identifying long-running jobs that may be causing bottlenecks and for ensuring that automated pipeline runs are adhering to the expected time-to-completion service level agreements.

Answer

The Fabric Capacity Metrics App

Answer

Microsoft Purview Compliance Portal

Answer

The OneLake File Explorer

Question 18

Which tool in Fabric provides the most detailed view of the billable capacity usage for your tenant?

Accepted Answer

The Fabric Capacity Metrics app. The Fabric Capacity Metrics app is the authoritative source for understanding how your consumption maps to billing. It translates technical usage metrics into visual reports that show how much compute you are using relative to your purchased SKU. This is critical for budget management and for identifying which departments or workspaces are the primary drivers of your organization's cloud expenditure.

Answer

The Microsoft Cost Management portal

Answer

Azure Advisor

Answer

The Fabric Admin Portal

Question 19

You are configuring a deployment pipeline to move a Lakehouse from a Test workspace to a Production workspace. Which THREE types of deployment rules can you apply to ensure the Lakehouse functions correctly in the Production environment?

Accepted Answer

Data source rules. Deployment rules are essential for tailoring Fabric items to their destination environment. For Lakehouses and semantic models, you can define rules that change the underlying data sources or parameters. This prevents the Production environment from accidentally referencing Test data and allows for a seamless transition that maintains data integrity and security across the lifecycle stages.

Answer

Lakehouse name rules

Answer

User permission rules

Question 20

Your organization uses Microsoft Fabric with three distinct workspaces for Development, Test, and Production. You need to automate the movement of a Lakehouse and its associated semantic model through these environments while ensuring that the Production environment uses a different data source connection string than the Development environment. Which feature should you implement?

Accepted Answer

Deployment Pipelines with Deployment Rules. Deployment pipelines in Microsoft Fabric provide a structured workflow for promoting content across environments. By using deployment rules, you can automatically update parameters such as connection strings or data source paths during the promotion process. This ensures that the Production environment remains isolated and points to the correct enterprise-grade data sources without requiring manual intervention or code changes.

Answer

Workspace Git Integration

Answer

OneLake Data Access Roles

Answer

Fabric REST API with Power BI Cmdlets