DP-700 Practice Exam 3 — 20 Questions

Question 1

You are building a data pipeline in Microsoft Fabric using a Copy Activity to ingest millions of small JSON files from an Azure Data Lake Storage Gen2 container into a Lakehouse table. The performance is significantly slower than expected due to high request latencies. Which optimization technique should you implement?

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

Accepted Answer

Enable staging in the Copy Activity settings to temporarily buffer data before writing it to the destination.. Enabling staging allows the Copy Activity to stage data in an intermediary storage account before writing to the Lakehouse, which efficiently compresses and batches requests. This drastically reduces the number of individual PUT operations against the storage endpoint, directly mitigating the throttling and latency issues commonly associated with processing massive volumes of small files in distributed analytics environments.

Answer

Increase the Data Integration Units (DIUs) to the maximum allowable limit of 256 for the pipeline activity.

Answer

Convert all source JSON files into single-line newline-delimited JSON format prior to executing the copy pipeline.

Answer

Switch the destination from a Delta Lake table in the Lakehouse to a raw staged folder within the Files section.

Question 2

You are designing a data transformation layer using Spark in Microsoft Fabric. Which THREE of the following are primary benefits of using Delta Lake format over traditional Parquet files? (Choose three)

Accepted Answer

ACID transaction support. Delta Lake adds a transaction log (the Delta Log) to the Parquet file format. This enables ACID transactions, preventing data corruption and partial writes. It also supports schema evolution, allowing the data structure to change over time without breaking downstream applications. Finally, 'Time Travel' allows users to query previous versions of data, which is invaluable for auditing, debugging, and reproducing analytical results. These features are critical for modern, reliable data platforms.

Answer

Smaller storage footprint than Parquet

Answer

Faster cold-start execution for Spark clusters

Question 3

You are developing a Notebook in Microsoft Fabric to process high-volume sensor data. You need to optimize performance using Delta Lake features. Which TWO actions should you perform? (Choose two)

Accepted Answer

Enable Z-Ordering on frequently filtered columns. Optimizing Delta Lake tables involves managing file sizes and data distribution. Z-Ordering optimizes multi-column queries by co-locating related data, significantly reducing read amplification. Compaction (using OPTIMIZE) merges small files into larger, more efficient files, which directly improves scan performance. Implementing these features is essential for maintaining query speed as data grows in the Lakehouse, ensuring that the Spark engine spends less time scanning unnecessary file blocks during execution.

Answer

Manually delete old Parquet files from the file system

Answer

Convert the table to CSV format

Answer

Increase the number of partitions to equal total file count

Question 4

A data engineer needs to provide access to data stored in an external Azure Data Lake Storage (ADLS) Gen2 account without moving the data into the Fabric Lakehouse. Which feature of Microsoft Fabric should be used?

Accepted Answer

Shortcuts. Shortcuts in Microsoft Fabric allow users to virtualize data by creating a pointer to an external storage location. This enables the data to appear as if it is stored locally in the Lakehouse, allowing Spark and SQL engines to query it directly. This minimizes data duplication, reduces storage costs, and simplifies data management across different environments.

Answer

Data Factory Mirroring

Answer

Pipeline Copy Activity

Answer

Eventstreams

Question 5

You are monitoring a Fabric pipeline. You need to identify which activity failed within the pipeline execution. Which tool should you use?

Accepted Answer

Fabric Pipeline Monitoring view. The 'Pipeline Monitoring' view in the Fabric workspace provides a centralized dashboard to view the status, start time, end time, and duration of all pipeline activities. Clicking on a specific pipeline run allows you to drill down into individual activities to inspect error logs, which is essential for diagnosing failures. This visibility is crucial for maintaining operational excellence and ensuring that data pipelines meet their SLAs in a production environment.

Answer

Power BI Desktop

Answer

Azure Resource Manager (ARM) templates

Answer

OneLake file explorer

Question 6

You are building a pipeline and need to ensure that a downstream activity only runs if a previous activity succeeds. Which pipeline feature should you use?

Accepted Answer

An 'On Success' connector. The 'On Success' constraint on the pipeline connector links two activities in a dependency chain. This ensures that the second activity starts only if the first one finishes without errors. This is fundamental for building reliable, production-grade workflows where the order of operations and the integrity of the data processing chain are paramount for successful data delivery to the end users.

Answer

A ForEach activity

Answer

A Wait activity

Answer

A Filter activity

Question 7

You are optimizing a Spark job in Fabric. Which TWO of the following techniques will help reduce the execution time? (Choose two)

Accepted Answer

Cache frequently used DataFrames. Reducing execution time in Spark requires efficient resource utilization and minimizing unnecessary data processing. Caching DataFrames that are reused multiple times prevents redundant computations, while enabling adaptive query execution allows Spark to dynamically optimize the physical plan based on runtime statistics. Together, these strategies significantly lower latency and resource consumption, leading to a much faster and more cost-effective data transformation process within the Fabric Notebook environment.

Answer

Always set the number of partitions to 1

Answer

Convert all data to JSON

Answer

Increase the idle timeout

Question 8

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?

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 9

You are configuring a Dataflow Gen2 to perform incremental updates for a large fact table. Which TWO steps are essential to ensure the dataflow only processes new or changed records during each run?

Accepted Answer

Create a parameter to store the last successful refresh date.. Incremental loading in Dataflows Gen2 requires a mechanism to identify new data and a logic to handle how that data is integrated into the destination. This typically involves using parameters to filter source data based on a 'last modified' date and configuring the destination to append new records.

Answer

Enable 'High Concurrency' mode for the Dataflow.

Answer

Use the 'Remove Duplicates' transformation on the entire table.

Answer

Configure a 'Tumbling Window' trigger in the Lakehouse.

Question 10

You are designing a data pipeline in Microsoft Fabric to ingest incremental data from an on-premises SQL Server. You need to ensure that only records modified since the last pipeline run are ingested. Which approach should you implement to maintain optimal performance and minimal data redundancy?

Accepted Answer

Configure the Copy activity to use a dynamic query with a watermark column from a control table.. Implementing a watermark column strategy is the standard practice for incremental loading in Fabric data pipelines. By storing the last processed timestamp or incremental ID in a control table, the pipeline can dynamically filter incoming data. This approach significantly reduces compute costs and ingestion time compared to full table loads, ensuring that only delta changes are processed while maintaining data consistency across the lakehouse destination.

Answer

Execute a full truncate and load operation using a Lookup activity before the Copy activity.

Answer

Use a Dataflow Gen2 to perform a full join between the source and the existing lakehouse table.

Answer

Enable Change Data Capture (CDC) on the source SQL Server and ingest all logs into a CSV file.

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.