DP-600 Prepare Data — Timed 20 Questions

Question 1

Your organization is implementing a medallion architecture in Microsoft Fabric. You need to ingest raw CSV data from an Azure Data Lake Storage (ADLS) Gen2 account into a Lakehouse. The data requires complex transformations, including pivoting and multi-level joins, before it is ready for the Silver layer. Which tool should you use to provide a low-code environment for these transformations?

20 minute time limit — choose an answer to begin.

Accepted Answer

Dataflows Gen2. Selecting the right ingestion tool in Microsoft Fabric depends on the required complexity and user skill set. Dataflows Gen2 provide a visual, low-code interface using the Power Query engine, making it ideal for citizen developers to perform complex transformations like pivoting and merging without writing Spark code. This ensures data is properly prepared before landing in the Silver layer.

Answer

Data Factory Pipelines with a Copy activity

Answer

T-SQL Stored Procedures in a Warehouse

Answer

Spark Job Definitions

Question 2

Refer to the exhibit. You are reviewing a configuration for a maintenance task on a Delta table within a Microsoft Fabric Lakehouse. What is the primary benefit of ensuring the 'v_order' property is set to true during this operation?

Accepted Answer

It optimizes Parquet files for faster read performance by Fabric engines.. V-Order is a Microsoft-proprietary write-time optimization for the Parquet file format used in Fabric. By enabling V-Order, the data is sorted and compressed in a way that allows Fabric engines, such as the SQL analytics endpoint and Power BI, to perform exceptionally fast reads. This optimization is crucial for high-performance reporting and large-scale data scans.

Answer

It enables ACID transactions for concurrent users.

Answer

It implements Liquid Clustering for dynamic data layout.

Answer

It automatically evolves the schema when new columns are detected.

Question 3

You have a Lakehouse in Microsoft Fabric that contains several Delta tables. You need to provide a SQL-based analyst with the ability to query these tables using their preferred desktop tool, such as SQL Server Management Studio (SSMS). What should you provide to the analyst?

Accepted Answer

The SQL connection string of the SQL analytics endpoint.. Every Lakehouse in Microsoft Fabric automatically includes a SQL analytics endpoint. This endpoint provides a read-only T-SQL interface to the Delta tables stored in the Lakehouse. By providing the SQL connection string, analysts can use standard SQL tools like SSMS or Azure Data Studio to query the data efficiently without needing to learn Spark.

Answer

The Workspace ID and the Lakehouse GUID.

Answer

The ABFS path to the Delta table folders.

Answer

A Personal Access Token (PAT) for the Fabric REST API.

Question 4

You are using a Microsoft Fabric Notebook to process a large dataset stored in a Lakehouse. You need to ensure that the Spark job uses the most efficient method to handle many small files that have accumulated in the Delta table over time. Which operation should you perform?

Accepted Answer

Execute the OPTIMIZE command on the table.. Small files can significantly degrade Spark performance due to excessive metadata overhead and inefficient I/O. The OPTIMIZE command in Delta Lake compacts these small files into larger, more efficient Parquet files. This process improves query performance and is a standard maintenance task for managing data health within a Fabric Lakehouse.

Answer

Run the VACUUM command on the table.

Answer

Change the table's partition scheme to a more granular column.

Answer

Set the 'spark.sql.shuffle.partitions' to a higher value.

Question 5

You are developing a data pipeline in Microsoft Fabric. You need to choose between using a Shortcut and the 'Copy activity' to bring data from an existing Azure Data Lake Storage (ADLS) Gen2 account into your Fabric Lakehouse. What are THREE primary advantages of using Shortcuts over the Copy activity? (Select THREE)

Accepted Answer

Data remains in its original source location without being duplicated.. Shortcuts in Microsoft Fabric provide a way to virtualize data without moving or duplicating it. This approach reduces storage costs, ensures data is always up-to-date by pointing directly to the source, and eliminates the need to manage complex ETL pipelines for simple data access, facilitating a more agile data architecture.

Answer

You can apply V-Order optimization to the source files through the Shortcut.

Answer

Shortcuts automatically convert CSV files into the Delta Lake format.

Question 6

Refer to the exhibit. This SQL code is executed in a Microsoft Fabric Notebook. What is the primary purpose of this operation in the context of data preparation?

Accepted Answer

To perform an UPSERT operation to synchronize the Silver table with new data.. The MERGE statement is a critical tool for maintaining a Silver layer in a medallion architecture. It allows for UPSERT logic, where existing records are updated with new information and new records are inserted. This ensures that the Silver layer provides a clean, deduplicated, and current state of the business entities.

Answer

To create a historical snapshot of the inventory for auditing.

Answer

To delete records from the Silver table that no longer exist in the Bronze table.

Answer

To validate that the data types in the Bronze table match the Silver table.

Question 7

You are managing a large-scale Lakehouse and notice that query performance on a specific Delta table is inconsistent as the data grows. You decide to implement Liquid Clustering. How does Liquid Clustering differ from traditional Z-Ordering in Microsoft Fabric?

Accepted Answer

Liquid Clustering supports incremental data clustering without rewriting existing data.. While Liquid Clustering is more efficient, it still performs data rewrites during the clustering process. The key differentiator is that it does not require a full table rewrite or manual maintenance of partition columns, and it adapts to clustering keys dynamically. The explanation should clarify that it automates the clustering process rather than implying it avoids rewriting data entirely.

Answer

Liquid Clustering requires manual re-partitioning every time data is appended.

Answer

Z-Ordering is more efficient for tables where the clustering keys change frequently.

Answer

Liquid Clustering can only be applied to tables with fewer than three columns.

Question 8

In Microsoft Fabric, you are using Dataflows Gen2 to clean a dataset. You want to ensure that if a specific transformation step fails, the entire dataflow execution stops and reports an error. Which feature or setting is used to manage the destination and ensure data integrity during the load?

Accepted Answer

Dataflow destinations. Dataflows Gen2 allow users to define output destinations. By configuring these destinations, you can control how data is loaded into Lakehouses or Warehouses. The 'undo' and 'staging' mechanisms ensure that if a failure occurs during the process, the system maintains a consistent state, preventing partial or corrupt data loads.

Answer

Incremental refresh

Answer

Gateway clusters

Answer

Query folding

Question 9

Refer to the exhibit. You are reviewing the M-code for a Dataflow Gen2. To ensure that the 'FilteredRows' step is executed on the source SQL Server rather than in the Fabric Mashup engine, what concept must be maintained?

Accepted Answer

Query Folding. Query folding is the ability of Power Query to translate M-code into the native language of the source system, such as T-SQL. When folding occurs, the source system handles the filtering and column selection, which is much more efficient than transferring all data to Fabric for processing. Maintaining folding is critical for performance.

Answer

Data Source Privacy Levels

Answer

Schema Drift

Answer

Fast Copy

Question 10

You are preparing a dataset in a Microsoft Fabric Notebook using PySpark. You have a DataFrame 'df' with a column 'Price' that contains some null values. You need to replace these null values with the average price of the entire dataset. Which approach is most appropriate?

Accepted Answer

Calculate the mean of 'Price' and use df.fillna(mean_val, subset=['Price']).. In Spark, handling missing data is a common data preparation task. Calculating the mean and using the 'fillna' or 'fill' method allows for imputation, which maintains the dataset's size while minimizing the statistical impact of missing values. This is a standard practice for preparing data for machine learning or reporting.

Answer

Use df.na.drop(subset=['Price']) to remove the rows.

Answer

Apply a filter where 'Price' is not null using df.filter(df['Price'].isNotNull()).

Answer

Use the 'V-Order' function to automatically fill missing values.

Question 11

You are optimizing a Lakehouse for high-concurrency read operations from Power BI. You want to ensure that the Delta tables are in the best possible state for Direct Lake mode. Which THREE actions should you take during the 'Prepare Data' phase? (Select THREE)

Accepted Answer

Enable V-Order on all Delta tables during the write process.. Direct Lake mode in Power BI performs best when data is stored in the Delta Parquet format with optimizations that favor fast scanning. This includes ensuring V-Order is applied, compacting small files to reduce I/O overhead, and maintaining up-to-date statistics so the query optimizer can generate efficient execution plans for the reports.

Answer

Convert all Delta tables into the CSV format for better compatibility.

Answer

Ensure that the tables are partitioned on high-cardinality columns.

Question 12

Your team is using Microsoft Fabric to build a data platform. You need to provide a way for data scientists to explore raw data files in a Lakehouse using Python, while also allowing SQL developers to query the same data as tables. What is the most efficient way to achieve this?

Accepted Answer

Use a Lakehouse and save data in the Delta format in the 'Tables' section.. In Microsoft Fabric, the Lakehouse item is designed for multi-engine access. Data scientists can use Spark and Python to interact with files, while the automatic discovery of Delta tables makes the same data available through the SQL analytics endpoint. This dual-access model promotes collaboration without data duplication.

Answer

Store the data in a Warehouse and use the Spark SQL connector.

Answer

Create two copies of the data: one in OneLake for Python and one in Azure SQL for SQL.

Answer

Use Dataflows Gen2 to export data to a CSV file for each group.

Question 13

You are optimizing a large Delta table in a Microsoft Fabric Lakehouse. Users report slow query performance due to a high volume of small files and unsorted data across frequently filtered columns. Which TWO actions should you perform? Each correct answer presents part of the solution.

Accepted Answer

Execute the OPTIMIZE table_name ZORDER BY (column_name) command in a Spark notebook.. Optimizing Delta tables involves compacting small files into larger optimal sizes using the OPTIMIZE command and physically clustering data on frequently filtered columns using Z-ORDER BY. Together, these commands drastically reduce file scanning overhead and improve query execution performance across analytical workloads.

Answer

Run the VACUUM table_name RETAIN 0 HOURS command to instantly purge all historical file versions.

Answer

Configure partitioning on every column that has high cardinality to maximize pruning efficiency.

Answer

Alter the table properties to increase the default checkpoint interval to every 500 commits.

Question 14

You are building a data transformation pipeline using Dataflow Gen2 in Microsoft Fabric. You need to combine two queries containing customer information and sales transactions based on a shared customer ID column, ensuring that all records from the customer table are retained even if they have no matching sales. Which join kind should you select?

Accepted Answer

Left Outer join. A Left Outer join retains all records from the left table (customers) and matches corresponding records from the right table (sales). Unmatched customer rows return null values for sales columns, satisfying the requirement to keep all customers regardless of transaction history.

Answer

Inner join

Answer

Right Anti join

Answer

Full Outer join

Question 15

You are designing a data ingestion pipeline in Microsoft Fabric. You need to ingest data from an on-premises SQL Server database into a Lakehouse. Which integration runtime component should you configure to ensure secure connectivity?

Accepted Answer

Self-hosted Integration Runtime. To connect to on-premises data sources, you must install and configure a self-hosted integration runtime. This acts as a bridge between the local network and the Microsoft Fabric environment, allowing the pipeline to reach behind the firewall. This is critical for data security because it avoids opening inbound ports in your corporate firewall, relying instead on outbound communication over port 443 to the cloud service.

Answer

Azure Integration Runtime

Answer

Managed Virtual Network Integration Runtime

Answer

Power BI Gateway

Question 16

You are optimizing a Spark job in Fabric that processes large Parquet files. You notice high memory consumption and frequent spills to disk. Which TWO actions should you take to improve performance?

Accepted Answer

Increase the number of Spark partitions. Optimizing memory usage involves managing how data is partitioned and read. Increasing the number of partitions allows for better parallel processing, preventing individual executors from overloading. Additionally, choosing appropriate file formats or using predicate pushdown ensures that only necessary data is loaded into memory. These steps minimize memory pressure, reduce disk spilling, and significantly decrease total execution time for large-scale data transformation workflows in Spark.

Answer

Decrease the number of Spark partitions

Answer

Increase the executor memory size

Answer

Use the default Spark configuration without changes

Question 17

Refer to the exhibit. You are using Data Wrangler in Fabric to clean a dataset. You apply a transformation that matches the JSON configuration shown. What is the effect of this operation on your dataset?

Accepted Answer

All rows with missing customer_id values are removed.. The JSON configuration indicates a data cleansing rule designed to enforce referential integrity or data quality by removing incomplete records. By dropping rows where the 'customer_id' is null, the transformation ensures that every record in the final dataset contains a valid identifier. This is a standard preprocessing step that prevents downstream errors in joins or aggregations where a missing primary key would otherwise cause data discrepancies or runtime failures.

Answer

The column customer_id is deleted from the table.

Answer

The null values in customer_id are replaced with 0.

Answer

The pipeline execution stops immediately.

Question 18

You need to store raw, unstructured data in a Microsoft Fabric Lakehouse. Which folder structure is the recommended best practice for organizing data in the 'Files' section?

Accepted Answer

Use a Bronze, Silver, Gold folder hierarchy.. The recommended approach for organizing data in a Lakehouse follows a medallion architecture: Bronze (raw), Silver (cleansed), and Gold (curated). For the 'Files' section specifically, placing raw data into a 'Bronze' folder maintains a clear separation of concerns. This organization ensures that data pipelines have a predictable path to read from and write to, which facilitates easier maintenance and governance as the data matures through the lifecycle.

Answer

Store all files directly in the root directory.

Answer

Create a unique folder for every individual user.

Answer

Rename all files to include the ingestion date.

Question 19

You are creating a shortcut in a Microsoft Fabric Lakehouse to access data stored in an Azure Data Lake Storage (ADLS) Gen2 account. What is the primary benefit of using a shortcut instead of copying the data?

Accepted Answer

Shortcuts avoid data duplication and synchronization overhead.. Shortcuts provide a virtualized link to the data without the need to move it. The primary benefit is the elimination of data duplication, which reduces storage costs and maintenance overhead. Because the data remains in the source location, any updates made to the source files are instantly reflected in Fabric. This approach ensures a 'single source of truth' and avoids the latency and complexity associated with periodic ingestion and synchronization jobs.

Answer

Shortcuts increase the query speed by caching data.

Answer

Shortcuts automatically convert files to Delta format.

Answer

Shortcuts provide read/write access to all file types.

Question 20

You have a dataset with a highly skewed distribution in a join key. When performing a join in a Spark notebook, you observe performance degradation. Which technique should you use to handle this skew?

Accepted Answer

Salting. Data skew occurs when one key is over-represented, causing a single executor to handle significantly more work than others, leading to bottlenecks. To resolve this, you should perform 'salting'. By appending a random integer to the skewed key in both datasets before joining, you distribute the data more evenly across the cluster partitions. After the join, you remove the salted integer to restore the original key values and complete the operation.

Answer

Broadcast join

Answer

Increasing the number of executor cores

Answer

Reducing the number of partitions