DP-600 Prepare Data Practice Test 3 — 15 Questions

Question 1

Refer to the exhibit. You are writing PySpark code in a Fabric notebook to combine sales and product information. Given the configuration, what is the primary behavior of this merge operation?

Accepted Answer

It returns only records where the product_id exists in both. The exhibit defines an inner join between the sales and products DataFrames using the product_id column as the key. An inner join returns only the rows where there is a match in both DataFrames. This is a standard operation in data preparation to ensure that every sales record has a corresponding valid product entity, effectively filtering out orphans that lack a referenced product record.

Answer

It returns all sales records, even if no product exists

Answer

It performs a Cartesian product

Answer

It creates a union of both tables

Question 2

When preparing data in a Lakehouse, you need to enforce a schema on incoming JSON data before loading it into a Delta table. Which Fabric tool allows you to define and apply a structural schema programmatically?

Accepted Answer

Spark Notebook. Spark notebooks in Fabric are the ideal tool for enforcing schemas. By defining a StructType schema in PySpark, you can explicitly map JSON fields to data types, handling missing or malformed data during the read process. This proactive approach ensures that Delta tables maintain high data quality and consistency, preventing type errors during query execution or report creation later in the lifecycle.

Answer

Shortcut

Answer

SQL Analytics Endpoint

Answer

Mirroring

Question 3

You are preparing a large dataset in Fabric and need to remove duplicate rows based on multiple columns. Which approach is most efficient for a Delta table?

Accepted Answer

Use the dropDuplicates method in PySpark. The most efficient way to remove duplicates in a Delta table within Fabric is by using the 'dropDuplicates' method in PySpark or executing a MERGE statement. By specifying the subset of columns, the Spark engine intelligently identifies duplicate entries and keeps only the distinct records. This operation is highly optimized for Delta formats, ensuring that the deduplication process remains performant even as the volume of the data scales significantly.

Answer

Use a T-SQL DELETE statement with a subquery

Answer

Export to Excel, clean, and re-import

Answer

Use a Dataflow Gen1

Question 4

You have a Lakehouse with sensitive customer data. You need to prepare the data for the marketing team such that Personally Identifiable Information (PII) is obfuscated. Which THREE steps should you implement?

Accepted Answer

Apply a Hashing function on PII columns. Preparing data for consumption often requires masking sensitive information to comply with privacy regulations. By filtering columns to remove unnecessary PII, applying hashing functions to mask identifiers, and creating separate gold-layer tables for the marketing team, you ensure secure data access. This approach balances data utility with privacy, following the principle of least privilege while providing the necessary insights for business analytics and reporting requirements.

Answer

Grant the marketing team access to the raw Delta table

Answer

Rename all columns to generic labels

Question 5

Refer to the exhibit. You are preparing a dataset in a Spark notebook. The operation fails with the provided error. What is the most likely cause?

Accepted Answer

The column name is misspelled or does not match the schema. The error indicates a column naming mismatch. The code references 'cust_id', but the actual schema contains 'customer_id' or 'cust_no'. In Spark, column names are case-sensitive or exact-match based on the schema defined in the Delta table. This is a common issue during data preparation when integrating data from disparate source systems that use non-standardized naming conventions, requiring a rename operation before proceeding.

Answer

The Delta table is missing

Answer

The Spark session has timed out

Answer

The data volume exceeds the cluster limit

Question 6

You are tasked with partitioning a large fact table in a Lakehouse to improve query performance. Which column should you choose as the partitioning key?

Accepted Answer

A low-cardinality date column. When partitioning a table, you should choose a column that is frequently used in filter predicates (WHERE clauses) and has a low to medium cardinality. For a fact table, date columns are the classic choice because most analytical queries filter by time ranges. Partitioning by date allows the engine to perform partition pruning, skipping irrelevant data files and significantly accelerating query performance during the analytical phase.

Answer

A high-cardinality unique ID column

Answer

All numeric columns

Answer

The table should never be partitioned

Question 7

You have a CSV file with headers in the first row. Using a Spark notebook in Fabric, which code snippet correctly reads this file into a DataFrame?

Accepted Answer

spark.read.option('header', 'true').option('inferSchema', 'true').csv(path). To correctly read a CSV file into a Spark DataFrame, you must specify the 'header' option as 'true' to treat the first row as column names, and the 'inferSchema' option as 'true' to allow the engine to automatically determine the correct data types. This is a foundational step in data preparation, ensuring the resulting DataFrame is immediately ready for further transformations and analytical operations.

Answer

spark.read.csv(path)

Answer

spark.read.option('header', 'true').csv(path)

Answer

spark.load.csv(path, header=True)

Question 8

You are using Dataflow Gen2 to transform data. You have a requirement to perform a 'fuzzy merge' to join two tables with slightly inconsistent naming. Which transformation step should you apply?

Accepted Answer

A fuzzy merge transformation. Fuzzy merge is a powerful feature in Dataflow Gen2 that uses approximate matching algorithms to join datasets where keys are not exact matches, such as 'Microsoft' vs 'MSFT'. This is essential for cleansing dirty data from disparate sources. By applying this in the Dataflow, you automate the resolution of naming inconsistencies, ensuring high-quality master data without the need for manual, error-prone data entry or hardcoded mapping tables.

Answer

A standard inner join

Answer

A conditional column

Answer

A transpose transformation

Question 9

You are configuring a Fabric Notebook to write data to a Lakehouse. Which TWO options should be set to ensure optimal performance when writing to Delta tables?

Accepted Answer

Use the partitionBy option. Optimizing write operations is crucial for maintaining performance in Delta tables. By enabling 'overwrite' mode for full refreshes and 'partitionBy' for large datasets, you minimize file fragmentation and improve downstream query speed. Partitioning organizes data into logical folders, while overwriting ensures that only necessary data is replaced, rather than performing slow, incremental updates that can lead to file bloat and metadata overhead in the storage layer.

Answer

Set the write mode to 'append'

Answer

Always use 'overwriteSchema' for every write

Answer

Set the output format to Parquet

Question 10

Which component in Fabric is responsible for orchestrating the overall data pipeline, including moving data and running transformations?

Accepted Answer

Data Pipeline. Fabric Data Pipelines serve as the central orchestration engine. They allow you to schedule, monitor, and chain together various activities such as copying data from sources, running Spark notebooks, or executing Dataflows. By managing the workflow dependencies, Pipelines ensure that data is prepared in the correct sequence, providing a unified and reliable way to automate end-to-end data integration tasks within the Microsoft Fabric ecosystem.

Answer

Lakehouse

Answer

SQL Analytics Endpoint

Answer

Semantic Model

Question 11

You are performing a 'Medallion' architecture transformation. In which layer should you perform initial data cleansing and schema enforcement?

Accepted Answer

The Silver layer. In the Medallion architecture, the Bronze layer acts as the raw landing zone. The Silver layer is the correct place to perform initial cleansing, schema enforcement, and data deduplication. By transforming Bronze to Silver, you create a trusted, standardized dataset that downstream Gold layers (business-level aggregates) can rely on. This structure ensures a clear data lineage and makes it easier to troubleshoot issues by tracing transformations through these distinct, well-defined stages.

Answer

The Bronze layer

Answer

The Gold layer

Answer

The Presentation layer

Question 12

Refer to the exhibit. Your data transformation job failed. What is the most likely cause, and how can you resolve it?

Accepted Answer

Insufficient executor memory for the transformation. The 'OutOfMemoryError' indicates that the Spark job is trying to process too much data in a single executor's memory. This often happens during heavy transformations like joins on large, unpartitioned datasets. To resolve this, you can increase the executor memory in the Spark configuration, optimize the join to reduce memory usage (e.g., using broadcast joins), or increase the number of partitions to spread the workload across more nodes, thus avoiding memory exhaustion.

Answer

The input file is corrupted

Answer

The code has a syntax error

Answer

The file path is incorrect

Question 13

You are using Fabric Dataflows Gen2 to ingest data. You notice that your Dataflow is slow. You want to see if the Dataflow is executing 'Mashup' queries on the source or the Fabric engine. Which tool should you use?

Accepted Answer

The Query Plan visualizer. In Dataflow Gen2, the 'View Query Plan' feature is the standard way to inspect how the Mashup engine is processing transformations. It shows whether query folding is occurring, which is vital for performance. By checking the query plan, you can identify steps that are not folding and adjust your logic, such as ensuring data types match or avoiding non-foldable functions, to ensure the source system does the heavy lifting.

Answer

The Spark Job Monitor

Answer

The SQL Profiler

Answer

The Pipeline Run history

Question 14

You are developing a data pipeline in Microsoft Fabric that ingests data from an on-premises SQL Server. You need to ensure that sensitive PII data is masked before it lands in the Lakehouse. Which transformation approach should you implement to maintain compliance while preserving data utility?

Accepted Answer

Use Dataflows Gen2 to apply Power Query transformations to mask PII columns during ingestion.. Implementing masking during the ingestion phase using Dataflows Gen2 allows for consistent application of data governance policies before data reaches the storage layer. This approach ensures that PII is protected at the earliest point in the data lifecycle, reducing the risk of unauthorized exposure in downstream analytics while maintaining the schema integrity required for reporting and model training tasks within the Microsoft Fabric environment.

Answer

Apply Dynamic Data Masking at the SQL Server source before extraction.

Answer

Use T-SQL views in the Lakehouse to mask columns upon query execution.

Answer

Implement row-level security on the Lakehouse table to hide sensitive rows.

Question 15

You are optimizing a Spark job in Fabric that processes large volumes of telemetry data. The job is failing due to memory overhead during a join operation on high-cardinality columns. Which TWO actions should you take to improve performance?

Accepted Answer

Use broadcast join hints for the smaller dimension table.. Optimizing join operations in Spark requires addressing memory pressure and data distribution. Broadcast joins are ideal for small tables to avoid shuffling, while salting keys helps distribute data evenly when skewed partitions exist. These techniques reduce the risk of OOM errors by preventing individual executors from becoming overwhelmed by large partitions or excessive data movement across the network during shuffle stages.

Answer

Increase the number of partitions for the join operation using repartition().

Answer

Convert the Spark DataFrames to Pandas objects for faster local processing.

Answer

Increase the driver node memory allocation in the Spark session configuration.