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

Question 1

You are developing an Azure Databricks notebook that processes JSON files stored in Azure Data Lake Storage Gen2. You need to read the files into a DataFrame and automatically infer the schema. Which code should you use?

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

Accepted Answer

spark.read.json("abfss://container@storage.dfs.core.windows.net/path"). The JSON data source in Spark automatically infers the schema when no schema is specified. Using spark.read.json with an abfss:// path reads the files from ADLS Gen2 and returns a DataFrame with inferred columns. The other options either misuse the API, use the wrong file format reader, or return unparsed text, so they do not meet the requirement.

Answer

spark.read.format("json").schema("infer").load("abfss://container@storage.dfs.core.windows.net/path")

Answer

spark.read.text("abfss://container@storage.dfs.core.windows.net/path")

Answer

spark.read.option("inferSchema", "true").csv("abfss://container@storage.dfs.core.windows.net/path")

Question 2

You are authoring an Azure Databricks notebook that reads Parquet files from Azure Data Lake Storage Gen2 and must write results to a Delta table. Users report that queries against the Delta table return stale data after each notebook run, even though the write succeeds. You need to ensure readers always see the latest committed data. What should you do?

Accepted Answer

Ensure the write commits through the Delta transaction log and readers query the table by name, not by a cached DataFrame. Delta Lake provides snapshot isolation via its transaction log, so a committed write is immediately visible to subsequent reads. Stale results typically come from caching a DataFrame or holding an old snapshot rather than from the table itself. Reading the table by name, without a cached DataFrame, ensures the reader resolves the latest committed version at query time.

Answer

Write the results with overwrite mode to the same Delta table path each run

Answer

Convert the Delta table to a Parquet table and rely on the file system for consistency

Answer

Call refreshTable or invalidate the cache on the reader session after each write

Question 3

You are implementing a Spark Structured Streaming job in Azure Databricks that reads from an Azure Event Hubs topic and writes to a Delta table. The job must handle late-arriving data up to 10 minutes and aggregate counts per device every 5 minutes. Which combination of settings should you use?

Accepted Answer

Use a tumbling window of 5 minutes and set watermark to 10 minutes on the event timestamp.. A tumbling window of 5 minutes with a 10-minute watermark on the event timestamp satisfies both the aggregation interval and the late-data tolerance. The tumbling window ensures non-overlapping 5-minute counts, and the watermark allows events up to 10 minutes late to be included. Other options use incorrect window sizes, slide intervals, or watermark durations that fail the requirements.

Answer

Use a sliding window of 5 minutes with a 10-minute slide interval and set watermark to 5 minutes.

Answer

Use a tumbling window of 10 minutes and set watermark to 5 minutes.

Answer

Use a hopping window of 5 minutes with a 5-minute hop and set watermark to 10 minutes.

Question 4

You are developing an Azure Databricks notebook that processes JSON files stored in Azure Data Lake Storage Gen2. You need to read the files into a DataFrame and automatically infer the schema. Which code should you use?

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

Accepted Answer

Use event-time processing with a watermark of 30 minutes and apply a windowed aggregation with a tumbling window of 10 minutes.. Using event-time processing with a watermark of 30 minutes ensures that late-arriving data up to 30 minutes old is processed correctly. A tumbling window of 10 minutes defines the aggregation intervals, and the watermark automatically cleans up state for windows that are older than the watermark, preventing unbounded state growth. This approach balances correctness and resource usage.

Answer

Use a tumbling window with a 30-minute watermark and configure the state store with a retention duration of 30 minutes.

Answer

Use event-time processing with a watermark of 30 minutes and apply a 'dropDuplicates' operation on the event ID within the watermark.

Answer

Use a sliding window with a 30-minute watermark and set the output mode to 'complete'.

Question 5

You are implementing a medallion architecture in Azure Databricks. The silver layer must contain deduplicated, conformed records, and the gold layer must serve aggregated reporting tables. You need to choose Delta Lake operations that support incremental, idempotent updates as new bronze files arrive. Which two operations should you use? (Choose two.)

Accepted Answer

Stream bronze files into the silver table with Structured Streaming and foreachBatch applying MERGE. Incremental, idempotent updates in a medallion pipeline rely on keyed upserts rather than full rebuilds. MERGE INTO delivers atomic, key-based convergence, and Structured Streaming with foreachBatch applies that same MERGE per micro-batch while checkpointing progress. Together they let new bronze files flow into silver without duplication, and the gold aggregates can then be computed from the conformed silver data.

Answer

Use CREATE OR REPLACE TABLE AS SELECT to rebuild the silver table from all bronze files each run

Answer

Write the gold aggregates with overwrite mode partitioned by report date

Answer

OPTIMIZE the silver table with ZORDER BY the business key after each load

Question 6

You are building a streaming pipeline in Azure Stream Analytics that reads JSON events from an Azure Event Hub and writes to Azure Synapse Analytics. The events include a nested array of sensor readings. You need to flatten this array so each reading becomes a separate row. Which Stream Analytics feature should you use?

Accepted Answer

CROSS APPLY with the ARRAY elements. CROSS APPLY is designed to unnest arrays in Stream Analytics queries. It takes an array expression and returns a row for each element, allowing downstream processing of individual sensor readings. The other options either aggregate, add metadata, or cannot emit multiple rows. Thus, CROSS APPLY is the only feature that meets the flattening requirement.

Answer

The TIMESTAMP BY clause with a partition key

Answer

A JavaScript user-defined function that iterates over the array and emits multiple rows

Answer

A tumbling window with a GROUP BY on the sensor ID

Question 7

You are developing an Azure Databricks notebook that processes JSON files stored in Azure Data Lake Storage Gen2. You need to read the files into a DataFrame and automatically infer the schema. Which code should you use?

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

Accepted Answer

Use the MERGE INTO statement to upsert records into the Delta table.. To handle late data and update existing records in a Delta table from a stream, you should use MERGE INTO within foreachBatch. This allows upserts based on a unique key, ensuring that late-arriving events update the correct records. Append, overwrite, and ignore modes do not provide the required update capability.

Answer

Use the append mode to write new records to the Delta table.

Answer

Use the ignore mode to skip writing if the table already exists.

Answer

Use the overwrite mode to replace the entire Delta table with the new batch.

Question 8

You are implementing a Spark Structured Streaming job in Azure Databricks that consumes from an Azure Event Hubs topic and writes to a Delta table. The stream must tolerate reprocessing after a cluster restart without producing duplicate rows in the Delta table. You need to configure the write path accordingly. (Choose two.)

Accepted Answer

Enable checkpointing by setting the checkpointLocation option on the writeStream.. Exactly-once behavior at the sink requires both a durable record of processed offsets and an idempotent write. Checkpointing supplies the first by persisting progress so a restart resumes correctly, and a MERGE keyed on a business identifier supplies the second by making replay harmless. Neither append mode, one-time triggers, nor file compaction can prevent logical duplicates because none of them compare row identity across batches.

Answer

Set the trigger to Trigger.Once so the stream reads the topic only one time.

Answer

Enable auto compaction on the Delta table to merge small files and eliminate repeated rows.

Answer

Configure the write mode as append and rely on the Delta table schema to reject duplicates.

Question 9

You are implementing a mapping data flow in Azure Data Factory that processes data from an Azure SQL Database. The data flow includes a derived column transformation that adds a new column based on a complex expression. You need to ensure the expression handles null values appropriately. Which function should you use to replace null values with a default?

Accepted Answer

coalesce. The coalesce function is designed to return the first non-null value from a list, making it ideal for replacing nulls with a default. In a derived column transformation, you can write coalesce(column, 'default') to substitute nulls. Other functions like isNull, nullIf, and iif serve different purposes: isNull checks for null, nullIf creates null, and iif requires a condition. Therefore, coalesce is the correct function for this scenario.

Answer

nullIf

Answer

iif

Answer

isNull

Question 10

You are implementing a data processing solution in Azure Synapse Analytics using Spark pools. The solution reads Parquet files from Azure Data Lake Storage Gen2, performs transformations, and writes the results to a dedicated SQL pool. You need to optimize the write performance to the dedicated SQL pool. Which technique should you use?

Accepted Answer

Use the PolyBase connector with a staging location in Azure Blob Storage.. The PolyBase connector is the most efficient way to write large datasets from Azure Synapse Spark pools to a dedicated SQL pool. It stages the data in Azure Blob Storage or Data Lake Storage Gen2 and then uses PolyBase to load it in parallel, which is much faster than JDBC batch inserts. This approach minimizes the load on the SQL pool and leverages its bulk load capabilities.

Answer

Use the 'spark.sql.sources.partitionOverwriteMode' setting to overwrite partitions.

Answer

Use the JDBC connector with batch inserts and set the batch size to 10,000 rows.

Answer

Write the data to a Parquet file in Data Lake Storage Gen2 and then use a Synapse pipeline to load it.

Question 11

You are creating an Azure Synapse Analytics pipeline that must copy data from an Azure SQL Database into a dedicated SQL pool. The destination table already exists and the pipeline must append new rows without truncating existing data. Which staging and load option should you configure in the Copy activity?

Accepted Answer

Set the sink write behavior to insert and use PolyBase or COPY statement staging. When the destination table already exists and new rows must be appended, the dedicated SQL pool sink uses the insert write behavior, which does not truncate. Pairing it with staging through PolyBase or the COPY statement gives the high-throughput bulk load path recommended for this connector pair, preserving existing rows while adding the incoming set.

Answer

Set the sink write behavior to truncate and rely on the pre-copy script to reinsert

Answer

Use a stored procedure sink that executes a MERGE statement against the destination table

Answer

Set the sink write behavior to upsert and provide a key column mapping

Question 12

You are developing an Azure Synapse Analytics serverless SQL pool solution that queries Parquet files in Azure Data Lake Storage Gen2. Analysts run ad-hoc queries with predicates on a high-cardinality column named TransactionId, and each query scans the entire folder, causing high cost. You need to reduce the amount of data scanned per query without changing the file format. What should you do?

Accepted Answer

Create a partitioned external table where the folder structure is organized by a low-cardinality key and query only the relevant partitions.. Serverless SQL pool reduces cost by reading fewer bytes, and partition elimination is the primary lever for that when the underlying layout supports it. Organizing folders by a low-cardinality key and exposing it through a partitioned external table allows the engine to skip irrelevant directories. Predicates on a high-cardinality column cannot prune files because their values are scattered across every file.

Answer

Create an external table over the folder and enable statistics on the TransactionId column.

Answer

Create a partitioned table in a dedicated SQL pool and load the Parquet data into it.

Answer

Use OPENROWSET with a filepath() predicate to restrict the query to specific files.

Question 13

You maintain an Azure Stream Analytics job that reads from an Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. During peak load the job produces late-arriving events that are dropped, and downstream reports show missing rows. You must retain and process events that arrive after the watermark by up to several minutes without changing the input. What should you configure?

Accepted Answer

Set the event ordering policy out-of-order tolerance window to the required number of minutes.. Late-arriving events are governed by the event ordering policy, whose out-of-order tolerance window defines how long the job accepts events behind the current watermark. Setting the window to cover the observed delay lets those events participate in temporal queries, restoring the missing rows without touching the upstream Event Hubs configuration or scaling compute.

Answer

Change the output to use a partitioned write pattern with a batch size of one row.

Answer

Configure the Event Hubs input to use a consumer group with a longer retention period.

Answer

Increase the streaming units allocated to the job so more events are processed per second.

Question 14

You are using Azure Data Factory to copy data from an Azure SQL Database to an Azure Data Lake Storage Gen2 account. The copy activity is failing intermittently with a timeout error. You need to improve the throughput and reliability of the copy operation. What should you do?

Accepted Answer

Increase the degree of copy parallelism and enable staged copy.. Intermittent timeouts often occur when a single connection cannot sustain the load. Increasing the degree of copy parallelism opens more concurrent connections, distributing the load. Staged copy separates the read and write phases, reducing the chance of timeouts during the write to ADLS Gen2. Together, these settings improve both throughput and reliability for large-scale copies.

Answer

Configure the copy activity to use a single thread with a larger batch size.

Answer

Set the copy activity's fault tolerance to skip incompatible rows.

Answer

Change the source dataset to use a stored procedure that returns all rows at once.

Question 15

You are creating an Azure Data Factory data flow that transforms data from an Azure SQL Database. The data flow must filter rows based on a column value and then aggregate the results. Which transformation should you use first?

Accepted Answer

Filter transformation. Filtering rows before aggregation reduces the amount of data processed by the Aggregate transformation, improving performance and ensuring that only relevant rows are aggregated. The Filter transformation is designed to select rows based on a condition, making it the appropriate first step. Other transformations like Aggregate, Derived Column, or Join do not directly filter rows and would be less efficient or incorrect in this context.

Answer

Derived Column transformation

Answer

Join transformation

Answer

Aggregate transformation