DP-600 Prepare Data Practice Test 5 — 15 Questions

Question 1

You are preparing a large dataset in a Fabric Lakehouse using PySpark. You notice significant data skew on the 'CustomerID' column, causing some executors to run much longer than others. Which TWO actions will help resolve the skew during a join operation?

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

Accepted Answer

Broadcast the smaller table involved in the join.. Addressing data skew is vital for maintaining performance in distributed Spark jobs. Skew occurs when one or few partitions hold significantly more data than others, leading to executor bottlenecks. By implementing techniques like salting or broadcast joins, you force Spark to distribute the workload more evenly, preventing the 'long tail' effect where one task waits for a single overloaded executor to finish its processing before the entire job proceeds.

Answer

Increase the number of partitions for the entire dataframe.

Answer

Reduce the spark.sql.shuffle.partitions configuration.

Answer

Enable AQE and increase the shuffle partitions.

Question 2

You need to ingest data from an on-premises SQL Server to a Fabric Lakehouse. You are required to maintain a secure connection without exposing the server to the public internet. Which component must you install to facilitate this connection?

Accepted Answer

Self-hosted integration runtime / Data Gateway. The Microsoft Data Gateway acts as a secure bridge between on-premises data sources and Microsoft Fabric. It handles the communication between the cloud service and the local infrastructure without requiring open inbound ports on the firewall. This is a critical security requirement for enterprises, ensuring that sensitive internal data can be integrated into Fabric pipelines while adhering to strict organizational networking and security compliance standards.

Answer

Azure ExpressRoute

Answer

Azure API Management

Answer

Azure Bastion

Question 3

You are creating a Fabric Dataflow Gen2 to transform data from a CSV file. Which feature should you use if you want to preview the impact of your transformations on a sample set of data before applying them to the entire dataset?

Accepted Answer

Power Query Editor. Dataflow Gen2 provides a visual interface with built-in data profiling and previewing capabilities. Using sample data allows engineers to validate transformation logic efficiently without processing the entire dataset, which saves compute resources and time. This iterative development process is fundamental to ensuring data quality, as it enables the quick identification of errors or unintended consequences in transformation steps before they are committed to the final data load or pipeline execution.

Answer

DirectQuery mode

Answer

Notebook Markdown cell

Answer

Pipeline Debug mode

Question 4

You have a large Delta table in a Fabric Lakehouse. You need to ensure that the data remains performant for read-heavy workloads. Which THREE actions should you take to maintain optimal storage and query performance?

Accepted Answer

Run the OPTIMIZE command periodically.. Maintaining Delta tables is crucial for long-term performance in Fabric. Over time, tables accumulate small files, metadata bloat, and fragmented physical layouts, which degrade query engine performance. Performing periodic maintenance tasks ensures that the table remains 'healthy.' These actions minimize overhead for compute engines during query optimization and execution, ensuring that the Lakehouse provides consistent, low-latency performance as data volumes grow over time.

Answer

Truncate the transaction log periodically.

Answer

Rebuild the entire table every night.

Question 5

Refer to the exhibit. You are configuring a copy activity to ingest JSON data into a Delta table. You notice that the target table schema is not reflecting the nested structure of the JSON correctly. What should you do to fix this?

Accepted Answer

Define an explicit mapping or schema in the copy activity.. When dealing with complex, nested JSON data, inferring the schema automatically often fails to capture the desired hierarchical structure. Providing an explicit schema definition allows the copy activity to correctly map nested objects into Struct or Array types within the Delta table. This is critical for data integrity, as it ensures downstream analytics tools can correctly interpret the data structure without complex runtime casting or logic errors.

Answer

Change the encoding to ASCII.

Answer

Use a Data Flow instead of a Copy Activity.

Answer

Convert the JSON to CSV before ingestion.

Question 6

You are managing a Lakehouse in Microsoft Fabric and need to provide access to a specific sub-folder within the Files section to a group of data scientists. Which method is the most secure way to achieve this?

Accepted Answer

Create a shortcut to the sub-folder in a separate workspace.. Fabric security is governed by Workspace roles and OneLake security permissions. For granular access, you should utilize the underlying OneLake access controls or implement Shortcuts that expose specific data subsets. This ensures compliance with the principle of least privilege, preventing unauthorized access to sensitive datasets elsewhere in the Lakehouse while allowing data scientists to perform their work without needing broad workspace-level permissions that grant unnecessary control over other assets.

Answer

Grant the data scientists the Contributor role in the workspace.

Answer

Copy the sub-folder to every user's personal storage.

Answer

Modify the file system permissions via the Windows OS.

Question 7

You have a Fabric pipeline that processes sensitive customer records. You are required to log the start and end times of every pipeline run to an audit table. Which activity should you use to capture this metadata?

Accepted Answer

Script Activity. The 'Set Variable' or 'Script' activity can be used to capture system metadata, such as 'pipeline().RunId' and 'utcNow()', which are essential for audit logging. By logging these timestamps into a SQL table, you establish a clear lineage and performance history for your data pipelines. This is standard in enterprise data engineering for monitoring SLA compliance and troubleshooting execution delays in complex, multi-stage data processing workflows.

Answer

Copy Activity

Answer

Lookup Activity

Answer

Data Flow Gen2

Question 8

Refer to the exhibit. You are using a notebook to read data from a source SQL database. To ensure the read operation is parallelized, what technique should you apply?

Accepted Answer

Use the partitionColumn, lowerBound, upperBound, and numPartitions options.. Reading data in parallel is essential for performance when dealing with large SQL tables. By specifying partition columns and boundaries in the Spark read configuration, you instruct the engine to split the data into multiple chunks that can be fetched concurrently by different executors. This significantly reduces the total read time compared to a single-threaded read, which would otherwise become a major bottleneck in the ETL pipeline.

Answer

Set the fetch size to 1.

Answer

Run the query inside a single threaded loop.

Answer

Increase the executor memory to infinite.

Question 9

You are implementing a Medallion architecture in Microsoft Fabric. You have a requirement to ensure that only cleaned, validated data reaches the Silver layer. Which tool is best suited for this gatekeeping process?

Accepted Answer

Dataflow Gen2. Dataflow Gen2 or Spark Notebooks are the optimal tools for implementing quality gates in a Medallion architecture. They allow you to apply complex validation rules, schema enforcement, and data cleansing logic before persisting data to the Silver layer. This architecture ensures that the Silver layer contains high-quality, reliable data, which is essential for downstream consumption by BI reports and data science models, effectively preventing 'garbage in, garbage out' scenarios in the Lakehouse.

Answer

Copy Activity

Answer

Shortcut creation

Answer

Warehouse SQL endpoint

Question 10

You are troubleshooting a slow-running Spark job in a Fabric notebook. Which TWO metrics should you analyze to identify potential bottlenecks in your data processing job?

Accepted Answer

Task duration distribution.. The Spark UI provides critical diagnostic information for understanding job performance. Analyzing task duration and data shuffle metrics allows you to pinpoint where the bottleneck lies. Skewed tasks and excessive data movement across the network are the two most common causes of performance degradation in distributed systems. By identifying these issues, engineers can apply targeted optimizations like salting or broadcast joins to bring the job execution time within acceptable performance thresholds.

Answer

Total number of files in the Lakehouse.

Answer

Workspace storage capacity.

Answer

The number of users logged into the workspace.

Question 11

Refer to the exhibit. You have a pipeline that runs daily, but you notice it fails frequently due to temporary API rate-limiting. Given the configuration in the exhibit, what change would best handle this issue?

Accepted Answer

Add an 'Until' activity to implement a custom wait/back-off.. The current configuration shows a high concurrency setting but lacks an explicit back-off or wait-time interval. By increasing the retry interval rather than just the retry count, you give the external API time to clear its rate-limiting windows before the next attempt. This is a standard strategy for interacting with throttled APIs, ensuring that your pipeline respects service limits while still successfully completing the requested data ingestion task.

Answer

Increase the concurrency to 10.

Answer

Disable the trigger.

Answer

Set the retry count to 0.

Question 12

You are using Fabric Dataflow Gen2 and need to merge data from two different sources. Which transformation would you use to combine the datasets based on a common key, similar to a SQL JOIN operation?

Accepted Answer

Merge. The Merge transformation in Dataflow Gen2 is specifically designed to perform join operations between two queries. It allows for various join kinds like Left Outer, Right Outer, and Inner, mirroring standard SQL behavior. Understanding this mapping is essential for relational data modeling within Fabric, as it enables the integration of disparate data sources into a unified structure suitable for downstream analytical processing in the Lakehouse or Warehouse.

Answer

Append

Answer

Filter

Answer

Group By

Question 13

An organization is migrating a 2 TB dataset from an on-premises SQL Server to a Microsoft Fabric Lakehouse. The data contains complex transformations that require high performance and the ability to handle data skewed across multiple partitions. Which tool should you use to ensure the most scalable and performant ingestion and transformation process?

Accepted Answer

Fabric Notebooks. Fabric Notebooks utilize Apache Spark, which is designed for large-scale distributed data processing. For a 2 TB dataset with complex logic and data skew, Spark's ability to customize partitioning and leverage cluster resources provides the necessary scalability that visual tools might lack. This ensures that the data preparation process remains efficient as the dataset grows and becomes more complex over time.

Answer

Dataflows Gen2

Answer

Data Factory Copy Activity

Answer

T-SQL Stored Procedures

Question 14

You need to access data stored in an Azure Data Lake Storage (ADLS) Gen2 account from a Microsoft Fabric Lakehouse without physically moving or copying the data. The goal is to minimize storage costs and maintain a single source of truth. Which feature should you implement?

Accepted Answer

OneLake Shortcuts. Shortcuts in Microsoft Fabric allow you to virtualize data from external sources like ADLS Gen2 directly into your Lakehouse. This approach avoids data duplication, reduces storage costs, and ensures that any updates in the source are immediately reflected in Fabric. It is a core feature for building a unified data mesh architecture without the overhead of ETL.

Answer

Data Factory Pipelines

Answer

Mirroring

Answer

V-Order Optimization

Question 15

You are designing a Medallion architecture in Microsoft Fabric. You have a Silver layer table that is frequently updated with new records and changes to existing records. You need to ensure that the process maintains historical versions of the data for auditing while optimizing query performance for the latest state. Which implementation strategy is best?

Accepted Answer

Implement SCD Type 2 in a Delta table. Implementing Slowly Changing Dimension (SCD) Type 2 logic within a Delta table allows for historical tracking of changes over time. By using Delta Lake's native support for ACID transactions and time travel, you can audit changes. Combining this with V-Order and effective partitioning ensures that queries for the current state remain highly performant despite the growth of historical records.

Answer

Overwrite the Silver table daily

Answer

Use SCD Type 1 in a Warehouse

Answer

Create a new table for every refresh