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

Question 1

You are implementing a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The query groups events into 30-second windows. You need to ensure that the job can handle late-arriving events up to 2 minutes after the window closes without dropping them. What should you configure?

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

Accepted Answer

Set the event ordering policy's late arrival tolerance to 2 minutes in the job's Event Ordering settings.. Late-arriving events are handled by the late arrival tolerance in the event ordering policy. This setting defines how long Stream Analytics waits for events that arrive after the window end before finalizing results. Configuring it to 2 minutes ensures events up to 2 minutes late are included. Other options affect performance or window semantics but not late-event inclusion.

Answer

Change the window type from Tumbling to Sliding with a 2-minute duration.

Answer

Enable the 'Adjust event ordering' option and set the out-of-order tolerance to 2 minutes.

Answer

Increase the streaming units of the Stream Analytics job to accommodate the late events.

Question 2

You are building an Azure Stream Analytics job that ingests telemetry from Azure Event Hubs and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The job must compute a five-minute tumbling window average per device and tolerate events that arrive up to three minutes late. During testing, you observe that events arriving after the window closes are silently dropped. You need to ensure late events are included in the correct window result. What should you configure in the Stream Analytics job?

Accepted Answer

Configure the late arrival tolerance on the temporal window to 00:03:00.. Tumbling windows emit once the window period elapses, and by default any event whose timestamp falls in that period but arrives after emission is discarded. The late arrival tolerance setting explicitly extends the window's acceptance period, so a three-minute value preserves the delayed device telemetry and aggregates it into the correct window. Scaling or retention settings do not affect timestamp-based window membership.

Answer

Set the event ordering policy's Out-of-order events tolerance to 00:03:00.

Answer

Increase the streaming units allocated to the job to at least six.

Answer

Set the Event Hubs consumer group's message retention to seven days.

Question 3

You are building an Azure Synapse Analytics pipeline that uses a Mapping Data Flow to transform data from Azure Data Lake Storage Gen2. The data flow includes a derived column transformation that uses a custom expression to calculate a new field. You need to debug the data flow and preview the output at each transformation. Which feature should you use?

Accepted Answer

Enable the 'Data flow debug' toggle and use the Data Preview tab in each transformation.. Azure Synapse Mapping Data Flows provide a debug mode that enables interactive data preview at each transformation. By turning on the debug toggle and using the Data Preview tab, you can see the results of expressions and transformations immediately, which is essential for validating logic before deploying the pipeline.

Answer

Add a Sink transformation and write to a temporary folder, then read the data manually.

Answer

Run the pipeline with a trigger and check the output in the Monitor hub.

Answer

Use the 'Debug' button on the pipeline canvas and set breakpoints on the data flow activity.

Question 4

You are developing an Azure Data Factory pipeline that processes data from an on-premises SQL Server. The pipeline uses a self-hosted integration runtime. You need to ensure that the pipeline can handle schema changes in the source table without failing. What should you do?

Accepted Answer

Use a Mapping Data Flow with 'Allow schema drift' enabled and a sink that supports schema evolution.. Mapping Data Flows with 'Allow schema drift' enabled can dynamically handle schema changes by reading and writing columns that are not predefined. This prevents pipeline failures due to new or removed columns. When paired with a sink that supports schema evolution, such as Delta Lake, the data flow can automatically adapt to changing schemas, ensuring continuous data processing.

Answer

Configure the Copy activity to use a stored procedure that dynamically generates the column list.

Answer

Enable the 'Allow schema drift' option in the Copy activity source settings.

Answer

Set the 'Auto create table' option in the sink dataset to handle new columns.

Question 5

You are implementing a mapping data flow in Azure Data Factory that joins a large fact table in Azure Synapse Analytics with a slowly changing dimension (SCD) table in Azure SQL Database. The fact table has 500 million rows and the dimension has 2 million rows. You need to optimize the join performance and minimize data movement. The dimension table is small enough to fit in memory. Which join type should you configure in the data flow?

Accepted Answer

Broadcast join. Broadcast join is designed for scenarios where one dataset is small enough to fit in memory. By broadcasting the dimension table, the large fact table is not shuffled, minimizing data movement and improving performance. Other join types like sort-merge or hash without broadcast would require shuffling the large fact table, leading to unnecessary data movement and slower execution.

Answer

Sort-merge join

Answer

Cross join

Answer

Hash join

Question 6

You are developing an Azure Stream Analytics job that ingests telemetry from Azure Event Hubs and writes results to an Azure Synapse Analytics dedicated SQL pool. The job must compute a 5-minute tumbling window aggregation and write the aggregated rows to the dedicated SQL pool. You need to configure the output so that each window's aggregated rows are written efficiently. What should you do?

Accepted Answer

Configure the output to use Azure Synapse Analytics and specify the database and table. Set the batch size to a value that matches the expected number of rows per window.. Stream Analytics provides a native Azure Synapse Analytics output that uses bulk insert mechanisms. The batch size setting controls rows per bulk operation, which directly impacts write efficiency. Writing directly to the dedicated SQL pool avoids intermediate storage and extra processing steps, meeting the requirement for efficient writes of windowed aggregates.

Answer

Configure the output to use Event Hubs and then use a Synapse pipeline to read from Event Hubs and write to the dedicated SQL pool.

Answer

Configure the output to use Blob Storage and then use an Azure Data Factory Copy activity to load the data into the dedicated SQL pool.

Answer

Configure the output to use Azure SQL Database and then create a linked server in the dedicated SQL pool to pull the data.

Question 7

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs using Structured Streaming. The stream writes to a Delta Lake table. You need to ensure that the stream can recover from failures and continue processing from where it left off without reprocessing all data. You also need to minimize the impact on the source. What should you configure?

Accepted Answer

Set the checkpoint location to a path in Azure Data Lake Storage Gen2 and use the Delta table as the sink.. Checkpointing is the mechanism that enables Structured Streaming to recover from failures by storing progress information in durable storage. Combining a checkpoint location in Azure Data Lake Storage Gen2 with a Delta Lake sink ensures exactly-once processing and allows the stream to resume from the last offset. Other options either lack checkpointing or introduce unnecessary complexity and source load.

Answer

Use the foreachBatch sink and manually store the offset in an Azure SQL Database.

Answer

Enable auto-compaction on the Delta table and set the trigger to continuous.

Answer

Set the startingOffsets option to earliest and enable watermarking.

Question 8

You are using Azure Databricks to process a large dataset stored in Azure Data Lake Storage Gen2. The data is in Parquet format and you need to optimize read performance for a query that filters on a specific column. What should you do?

Accepted Answer

Partition the data by the filter column when writing the Parquet files.. Partitioning Parquet data by the filter column allows Spark to skip reading irrelevant partitions, drastically reducing I/O. This is a fundamental optimization for large datasets in data lakes. Other options either do not address the read pattern or could worsen performance. Partitioning is a best practice for improving query performance on filtered columns.

Answer

Cache the entire dataset in memory using the Databricks cache.

Answer

Increase the number of shuffle partitions in the Spark session configuration.

Answer

Convert the Parquet files to CSV format to enable predicate pushdown.

Question 9

You have an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage every night. The on-premises server cannot accept inbound connections, and no VPN or ExpressRoute is available. You need to enable connectivity with minimal administrative overhead. What should you deploy?

Accepted Answer

A self-hosted integration runtime installed on a machine in the on-premises network.. The self-hosted integration runtime is the standard Azure Data Factory mechanism for reaching on-premises data sources when inbound connectivity is not possible. It initiates outbound HTTPS connections to the Data Factory service, so it works without VPN, ExpressRoute, or firewall changes. It also supports the Oracle connector and Blob Storage sink required by this nightly copy pipeline.

Answer

An Azure VPN Gateway with a site-to-site connection to the on-premises network.

Answer

An Azure ExpressRoute circuit with private peering to the on-premises datacenter.

Answer

An Azure Integration Runtime with a managed virtual network and private endpoints to the Oracle server.

Question 10

You are implementing a data processing solution in Azure Databricks. The solution reads JSON files from Azure Data Lake Storage Gen2, performs complex transformations using PySpark, and writes the results to a Delta table. You need to ensure that the write operation is idempotent and can recover from failures without duplicating data. Which approach should you use?

Accepted Answer

Use the merge operation with a unique key to upsert records into the Delta table.. The merge operation in Delta Lake enables upserts based on a unique key, ensuring that rerunning the job after a failure does not duplicate data. It provides ACID transactions and idempotence, which are critical for reliable data processing. Append mode and overwrite mode do not offer the same guarantees, and temporary files with Copy activity lack transactional integrity.

Answer

Write the output in append mode and use a deduplication step after each run.

Answer

Write the output using the overwrite mode to replace the entire Delta table on each run.

Answer

Write the output to a temporary Parquet file and then use a Copy activity to move it to the Delta table.

Question 11

You are developing an Azure Databricks notebook that processes a large Delta Lake table. You must add a derived column that depends on the latest value of a watermark stored in a small reference table, and the notebook must refresh this value before each micro-batch. You need to ensure the reference data is re-read on every micro-batch rather than cached once. Which approach should you use?

Accepted Answer

Use the foreachBatch sink and, inside the function, query the reference table with a fresh read on each invocation.. Structured Streaming captures static DataFrames when a query starts, so joins against them do not see later changes. foreachBatch executes a user-defined function for each micro-batch and permits arbitrary operations, including a fresh read of the reference table. Reading the watermark inside that function ensures the latest value is used for the derived column on every micro-batch, which is precisely the stated requirement.

Answer

Persist the reference table with the MEMORY_AND_DISK storage level before starting the stream.

Answer

Broadcast the reference table as a static DataFrame and join it to the streaming DataFrame.

Answer

Enable the spark.databricks.delta.cache.enabled option on the streaming query.

Question 12

You are developing an Azure Databricks notebook that reads a large Delta table, performs a join with a smaller reference table, and writes the result back to Delta Lake. The job runs on a cluster with autoscaling enabled and frequently spills to disk during the join. You need to reduce shuffle and improve performance without changing the result. Which action should you take?

Accepted Answer

Broadcast the smaller reference table in the join using a broadcast hint.. When joining a large Delta table with a small reference table, broadcasting the small table avoids shuffling the large table entirely. Each executor receives a copy of the small table and performs the join locally, which removes the network exchange and disk spills observed in the job. The result set is identical, and the memory cost is modest because the broadcast side is small.

Answer

Repartition the large Delta table by the join key before the join.

Answer

Cache the large Delta table in memory before performing the join.

Answer

Increase the spark.sql.shuffle.partitions value from the default to a much larger number.

Question 13

You are building an Azure Data Factory pipeline that processes files from Azure Blob Storage. The pipeline uses a Mapping Data Flow to transform the data and then writes the output to Azure Data Lake Storage Gen2. You need to ensure that the Data Flow can handle schema drift, where incoming files may have additional columns not present in the initial schema. What should you configure in the Data Flow?

Accepted Answer

Enable 'Allow schema drift' in the source transformation and use 'Auto mapping' in the sink transformation.. In Mapping Data Flow, schema drift is enabled at the source transformation, allowing it to read columns not defined in the projection. The sink must also be configured to write those columns, typically using auto mapping. This combination ensures that additional columns are processed and persisted, which is necessary when incoming files have evolving schemas.

Answer

Define a fixed schema in the source projection and use a Derived Column transformation to add new columns.

Answer

Use a Parameterized dataset and pass the schema as a parameter at runtime.

Answer

Set the source dataset to use a wildcard file path and enable 'Recursive' in the source options.

Question 14

You are writing a T-SQL query against a dedicated SQL pool in Azure Synapse Analytics. The query aggregates a fact table containing billions of rows by joining it to a small dimension table. You observe that the join produces a large amount of data movement and the query runs slowly. You need to reduce data movement for this recurring pattern. What should you do?

Accepted Answer

Replicate the small dimension table so a copy exists on every distribution.. In a dedicated SQL pool, join performance depends heavily on whether matching rows already reside on the same distribution. A replicated table places a complete copy of the small dimension on every distribution, letting the engine perform the join locally against the large fact table. This eliminates the shuffle or broadcast of the dimension and is the recommended pattern for recurring joins with small dimensions.

Answer

Increase the resource class of the user running the query.

Answer

Change the fact table's distribution to ROUND_ROBIN.

Answer

Add a columnstore index to the dimension table.

Question 15

You are developing an Azure Data Factory pipeline that must call an external REST API, parse the JSON response, and load selected fields into an Azure SQL Database. The API requires a bearer token that expires every hour, so the pipeline must obtain a fresh token before each call. You need to implement the token acquisition and header injection without writing custom code in a data flow. What should you use?

Accepted Answer

A Web activity to fetch the token, followed by a Copy activity whose REST source uses the token in an Authorization header.. The pipeline needs to fetch a short-lived bearer token and attach it to an outgoing REST call. A Web activity can invoke the token endpoint and return the token value, which is then supplied as an Authorization header on the REST source of a Copy activity. This pattern is fully declarative, refreshes the token on every run, and satisfies the no-custom-code constraint.

Answer

A REST linked service configured with a system-assigned managed identity.

Answer

A Web activity that calls the token endpoint and a Set variable activity to store the token.

Answer

A Mapping Data Flow with a REST source and a derived column that generates the token.