DP-203 Develop data processing Practice Test 15 — 15 Questions

Question 1

You are building an Azure Synapse Analytics serverless SQL pool solution that reads Parquet files stored in Azure Data Lake Storage Gen2. Analysts complain that a query returning a small number of rows from a large folder takes far too long because the engine scans every file. You must reduce query latency by limiting which files are considered during query planning. What should you configure?

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

Accepted Answer

Create an external table on the folder and define a partition elimination predicate on the file path column.. Filtering on a file path column exposed through an external table allows the serverless SQL pool to skip files that cannot match the predicate, cutting the amount of data enumerated and read. This is the intended mechanism for reducing planning and scan overhead over large folder hierarchies without moving data into a dedicated pool.

Answer

Enable result set caching on the serverless SQL pool and rerun the query after the first execution.

Answer

Create a database scoped credential and an external data source, then use OPENROWSET with a wildcard file path.

Answer

Create a partitioned table in a dedicated SQL pool using CREATE TABLE with a distribution hash on the file path.

Question 2

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs and writes to a Delta table. The stream must handle schema evolution and ensure that duplicate records are not inserted when the job restarts. You need to configure the write operation. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable mergeSchema option on the writeStream operation.. Schema evolution is handled by enabling mergeSchema on the write, allowing new columns to be added automatically. Deduplication on restart is achieved by using foreachBatch with a MERGE statement that upserts based on a key. Together, these actions meet both requirements. Checkpointing must persist, and autoOptimize is unrelated to correctness.

Answer

Enable autoOptimize on the Delta table.

Answer

Set the checkpointLocation to a temporary directory that is cleared on each restart.

Answer

Configure the stream to use complete mode output.

Question 3

You are building an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You need to ensure that the copy activity can handle large volumes of data efficiently and minimize the impact on the source server. Which performance optimization should you implement?

Accepted Answer

Increase the degree of copy parallelism in the copy activity.. Increasing the degree of copy parallelism allows multiple concurrent connections to the source and destination, improving throughput for large data volumes. It is a key optimization for self-hosted integration runtime scenarios, as it leverages the host's resources efficiently. Staging, binary copy, and DIU settings are either not applicable or not effective for this specific source and destination combination.

Answer

Use a binary copy with the self-hosted integration runtime.

Answer

Enable staging for the copy activity.

Answer

Configure a larger DIU (Data Integration Unit) for the copy activity.

Question 4

You are designing a data processing solution in Azure Synapse Analytics that uses a dedicated SQL pool. You need to implement a slowly changing dimension (SCD) Type 2 for a customer dimension. Which two actions should you perform? (Choose two.)

Accepted Answer

Add columns for StartDate, EndDate, and IsCurrent to the dimension table.. SCD Type 2 requires adding validity columns (StartDate, EndDate, IsCurrent) to track versions and using a MERGE statement to expire old rows and insert new ones. These two actions form the core of the pattern. Other options are either performance optimizations or represent a different SCD type that does not preserve history.

Answer

Implement a hash distribution on the business key to co-locate rows for the same customer.

Answer

Use a Type 1 update to overwrite the existing row with new values.

Answer

Create a clustered columnstore index on the dimension table to improve update performance.

Question 5

You are creating an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage. The copy must run every hour and only transfer rows that have been modified since the last run. What should you use to track the modified rows?

Accepted Answer

A tumbling window trigger with a watermark column in the source.. The tumbling window trigger is designed for recurring time-series processing and provides the window start and end times as system variables. By using these variables in a query against a watermark column, the pipeline can extract only rows modified within that window. This method is stateful and ensures each row is processed exactly once per window without external tracking.

Answer

A schedule trigger with a fixed time and a lookup activity to get the maximum value from the destination.

Answer

A manual trigger that runs after each Oracle transaction.

Answer

An event-based trigger that responds to changes in the Oracle database.

Question 6

You are developing an Azure Databricks notebook that reads Parquet files from Azure Data Lake Storage Gen2, performs transformations, and writes the results to a Delta table. You need to ensure that the write operation is idempotent so that rerunning the notebook does not duplicate data. Which Delta Lake feature should you use?

Accepted Answer

MERGE INTO with a unique key condition. To make a write operation idempotent, you need to ensure that rerunning the job does not duplicate data. MERGE INTO with a unique key condition performs upserts, so existing rows are updated and new rows are inserted only if they do not exist. This guarantees idempotency. The other options either append duplicates, overwrite data, or require manual intervention.

Answer

Append mode with a deduplication step

Answer

Delta Lake time travel to roll back the transaction

Answer

Overwrite mode with partition replacement

Question 7

You are developing an Azure Databricks notebook that reads JSON files from Azure Data Lake Storage Gen2, transforms the data, and writes it to a Delta table. The JSON files contain nested arrays and inconsistent schemas across files. You need to ensure that the transformation handles schema evolution and produces a flattened structure for analysis. What should you do?

Accepted Answer

Use the 'mergeSchema' option when reading the JSON files and then use the 'explode' function on the arrays.. To handle inconsistent schemas and nested arrays, you should enable 'mergeSchema' during read to unify schemas across files, and then use 'explode' to flatten arrays into individual rows. This approach ensures all data is captured and transformed into a tabular format suitable for analysis. Other options do not address both requirements simultaneously.

Answer

Use the 'inferSchema' option and then apply the 'struct' function to flatten the arrays.

Answer

Read the JSON files with the default settings and use the 'flatten' transformation in a Delta Live Tables pipeline.

Answer

Use the 'recursiveFileLookup' option and then use the 'map' function to flatten the arrays.

Question 8

You are building a mapping data flow in Azure Data Factory that joins a large fact stream from Azure Data Lake Storage Gen2 with a small dimension table from Azure SQL Database. The dimension table changes rarely, and the join is causing the flow to shuffle millions of rows across the Spark cluster. You need to minimize the shuffle while keeping the join correct. What should you configure?

Accepted Answer

Set the join type to Inner and enable the 'Broadcast' optimization on the smaller dimension stream.. When one side of a join is small, broadcasting that side to every executor removes the need to shuffle the large side across the network. Keeping the join type as Inner preserves the required result set, unlike switching to an outer join. Partitioning or increasing partitions on the fact stream still moves millions of rows, so broadcast on the dimension is the correct optimization.

Answer

Partition the fact stream by the join key using a Hash partition before the join.

Answer

Set the join type to Left Outer and enable the 'Broadcast' optimization on the dimension source.

Answer

Increase the number of partitions on the fact stream to spread the join across more executors.

Question 9

You are optimizing an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You need to improve throughput for large tables. What should you do?

Accepted Answer

Enable parallel copy by setting the degree of copy parallelism and use partitioned extraction based on a numeric column.. For large tables with a self-hosted integration runtime, enabling parallel copy with partitioned extraction based on a numeric column allows concurrent reads and improves throughput. DIUs do not apply to self-hosted runtime, binary copy is not for relational sources, and PolyBase is for loading into analytical databases, not Blob Storage.

Answer

Use a binary copy to avoid serialization and deserialization overhead.

Answer

Configure the copy activity to use a staging linked service with PolyBase.

Answer

Increase the Data Integration Units (DIUs) for the copy activity.

Question 10

You manage a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hub and writes to Azure Synapse Analytics. Events arrive with an ApplicationProperty named 'priority' that is not present in the event payload. You need to use this property inside a Stream Analytics query to route high-priority events to a separate output. What should you do?

Accepted Answer

Configure the Event Hub input in the Stream Analytics job to expose the ApplicationProperty as a column, then reference that column in the query.. Event Hub ApplicationProperties travel in the broker envelope, not the message body, so they are invisible to a Stream Analytics query until the input is configured to expose them as columns. Once surfaced, the property behaves like any other field and can drive routing logic such as a CASE expression feeding multiple outputs.

Answer

Use the GetMetadata activity in Azure Data Factory to read the ApplicationProperty and pass it to the Stream Analytics job.

Answer

Enable the compatibility level 1.2 setting on the Stream Analytics job and query the property using the GetProperty built-in function.

Answer

Add a JavaScript user-defined function that reads the property from the input stream and returns its value.

Question 11

You are implementing a streaming solution using Azure Stream Analytics that reads from Azure Event Hubs and writes to Azure Cosmos DB. The job must process events in real-time and handle duplicate events. You need to ensure that the output to Cosmos DB is idempotent and avoids duplicates. Which feature should you configure?

Accepted Answer

Implement deduplication using a self-join with a temporal window.. Deduplicating events in the Stream Analytics query using a self-join with a temporal window allows you to filter out duplicate events based on a unique key. When combined with an upsert output to Cosmos DB that uses a deterministic document ID, the writes become idempotent, ensuring that duplicates are not persisted. Other options like compatibility level, upsert alone, or tumbling windows do not address duplicate event handling in the input stream.

Answer

Use a tumbling window to aggregate events and eliminate duplicates.

Answer

Set the compatibility level to 1.2.

Answer

Enable the 'Upsert' option in the Cosmos DB output.

Question 12

You are implementing a Spark Structured Streaming job in Azure Databricks that reads from an Azure Event Hubs topic and writes to a Delta Lake table. The job must process each event exactly once, even if the cluster restarts. The output table must be updated with late-arriving data using event-time watermarks. Which combination of settings should you use?

Accepted Answer

Use foreachBatch with a merge operation and enable checkpointing on a reliable storage location.. Exactly-once streaming into Delta Lake with late-data handling is achieved by combining reliable checkpointing with an idempotent sink. foreachBatch enables a MERGE statement that upserts based on a business key, and watermarks bound how late data can arrive. Append, update, or complete output modes alone do not provide both idempotency and durable progress tracking, so they fail the restart and late-data requirements.

Answer

Use the default append output mode with a checkpoint location and rely on Delta Lake's transaction log for exactly-once.

Answer

Use a tumbling window with watermark and write to the Delta table using the complete output mode.

Answer

Set the Spark configuration spark.sql.streaming.checkpointLocation to a local path and use update mode.

Question 13

You are using Azure Data Factory to orchestrate a data processing workflow. The workflow must run a Python script that performs complex transformations, and then run a Databricks notebook that loads the transformed data into a Delta table. You need to implement these activities. Which two activities should you use? (Choose two.)

Accepted Answer

Custom activity. To run a Python script, the Custom activity is appropriate because it can execute custom code on a compute resource like Azure Batch. To run a Databricks notebook, the Databricks Notebook activity is the correct choice. The other activities either target different services or cannot run the required code. Thus, the combination of Custom activity and Databricks Notebook activity fulfills the workflow.

Answer

Azure Function activity

Answer

Databricks Python activity

Answer

Stored Procedure activity

Question 14

You are implementing a solution in Azure Synapse Analytics using dedicated SQL pools. You need to load data from a large parquet file in Azure Data Lake Storage Gen2 into a fact table. The fact table is hash-distributed on a surrogate key. You must minimize load time and ensure data is distributed evenly. What should you do?

Accepted Answer

Use the COPY statement to load the data directly into the hash-distributed fact table.. The COPY statement is the most efficient way to load large parquet files from Azure Data Lake Storage Gen2 into a dedicated SQL pool table. It provides high throughput and can load directly into the final hash-distributed table, avoiding staging and extra data movement. This minimizes load time while ensuring even distribution if the distribution key is appropriate.

Answer

Use Azure Data Factory with a copy activity to load the data into the hash-distributed fact table.

Answer

Use PolyBase to load the data into a heap table, then use INSERT...SELECT to move data into the hash-distributed fact table.

Answer

Use PolyBase to load the data into a staging table that is round-robin distributed, then use CTAS to create the final hash-distributed table.

Question 15

You are tuning an Azure Synapse Analytics dedicated SQL pool that ingests data through PolyBase and CTAS operations. Queries joining a large fact table to a small dimension table show heavy data movement in the execution plan. You need to reduce shuffle during joins without changing the table schemas. Which two actions should you perform? (Choose two.)

Accepted Answer

Create statistics on the join columns of both tables and keep them updated.. Reducing join shuffle in a dedicated SQL pool comes from giving the optimizer good cardinality information and from choosing a broadcast join for the small side. Updated statistics drive better plan selection, and the replicated hint copies the small dimension to every distribution so the large fact table stays in place instead of being repartitioned.

Answer

Increase the resource class of the user running the query to allocate more memory per query.

Answer

Enable result set caching on the dedicated SQL pool for repeated join queries.

Answer

Change the fact table distribution to ROUND_ROBIN to balance storage across distributions.