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

Question 1

You have an Azure Databricks notebook that reads a large Delta table, performs a join, and writes results back with overwrite mode. Queries against the table intermittently return stale data for several minutes after the write completes. You must ensure readers immediately see the latest committed version. What should you do?

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

Accepted Answer

Set the Spark configuration spark.databricks.delta.stalenessLimit to 0 on the reading clusters.. Delta readers on Databricks cache table metadata and file listings, and the stalenessLimit setting governs how long that cache may be trusted. A non-zero limit lets readers return slightly old snapshots. Setting the limit to zero forces a transaction log check on each query, making committed writes visible immediately.

Answer

Increase the number of shuffle partitions used by the writing job.

Answer

Convert the table to a Parquet table and register it in the metastore.

Answer

Run OPTIMIZE on the Delta table after each write to compact small files.

Question 2

You are developing an Azure Databricks notebook that processes a large Delta table. You need to reduce the amount of data scanned during queries and improve performance for point lookups on a high-cardinality column named 'CustomerID'. You also want to avoid rewriting the entire table. Which two actions should you perform? (Choose two.)

Accepted Answer

Run OPTIMIZE with ZORDER BY on CustomerID.. To reduce data scanned and speed up point lookups on a high-cardinality column without rewriting the entire table, use OPTIMIZE with ZORDER BY on that column and rely on Delta Lake data skipping via collected statistics. ZORDER colocates values to improve file pruning, and statistics enable skipping files that cannot match the predicate. Column mapping, high-cardinality partitioning, and Parquet conversion do not provide these benefits.

Answer

Partition the table by CustomerID.

Answer

Convert the table to Parquet and use a Bloom filter index.

Answer

Enable Delta Lake column mapping on the table.

Question 3

You are creating an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline must run every hour and only copy rows that have been modified since the last run. You need to configure the pipeline to support incremental loading. Which feature should you use?

Accepted Answer

A tumbling window trigger with a watermark column. Incremental loading requires tracking changes since the last run. A tumbling window trigger provides a reliable, recurring schedule, and a watermark column stores the last modified timestamp. By comparing the watermark to the source's modification timestamp, the pipeline copies only new or changed rows. The other options either do not support on-premises change detection or lack the necessary state tracking.

Answer

An event-based trigger on the on-premises SQL Server

Answer

A schedule trigger with a fixed start time and no watermark

Answer

A data flow with a filter transformation on a timestamp column

Question 4

You are processing streaming data with Azure Stream Analytics. The job reads from an Event Hub and writes to a SQL database. You observe that output events are occasionally delayed and sometimes duplicated in the target table. You need to configure the job to provide at-least-once delivery and improve output throughput. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the SQL output to use batch size and retry policy settings appropriate for the workload.. At-least-once delivery in Stream Analytics is achieved by retrying failed output writes rather than dropping them, so the SQL output's retry policy is essential. Throughput improves by scaling compute with more Streaming Units and by tuning the SQL sink's batch size, which reduces per-row round trips and keeps up with the input rate.

Answer

Change the event ordering policy to 'Adjust' so late-arriving events are reordered before writing.

Answer

Enable the 'Allow duplicate events' option in the Event Hub input to prevent data loss.

Answer

Set the output error policy to 'Drop' so failed writes do not block the pipeline.

Question 5

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

Accepted Answer

Broadcast the smaller table (1 million rows) to all worker nodes.. Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Answer

Increase the cluster size to reduce shuffle overhead.

Answer

Create bucketed tables on the join key for both datasets.

Answer

Use Delta Lake and optimize file layout with OPTIMIZE command.

Question 6

You are running a Spark job in Azure Synapse Analytics that reads from a Delta Lake table and performs multiple transformations. The job fails with an out-of-memory error on the executors. Which action should you take first to resolve the issue?

Accepted Answer

Increase the executor memory setting in the Spark configuration.. An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Answer

Enable checkpointing to truncate the lineage.

Answer

Decrease the number of partitions to reduce overhead.

Answer

Use the cache() action on intermediate DataFrames.

Question 7

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Accepted Answer

Use coalesce() to reduce the number of partitions without a shuffle.. Option A is correct because coalesce() reduces the number of partitions by merging existing partitions without performing a full shuffle, which directly decreases the number of output files written and is efficient when reducing partitions. Option E is correct because repartition() with a smaller number of partitions also reduces the partition count, and although it triggers a shuffle, it results in fewer output files being written. Option B is incorrect because caching only stores the DataFrame in memory or disk to speed up repeated access; it does not change the number of partitions or output files. Option C is incorrect because bucketing organizes data into buckets for join or query optimization and does not reduce the number of files written by a DataFrame write operation. Option D is incorrect because increasing partitions with repartition() using a larger number would create more partitions and therefore more output files, worsening the small-file problem.

Answer

Enable caching on the DataFrame before writing.

Answer

Apply bucketing on a column to group data.

Answer

Increase the number of partitions using repartition() with a larger number.

Question 8

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

Accepted Answer

Use a Copy activity with pagination rules enabled in the source.. Azure Data Factory's Copy activity supports pagination rules that automatically handle paging through REST API responses. By configuring pagination rules in the source dataset, the Copy activity can iterate through pages until all data is copied, without needing explicit looping activities. This is the most efficient and recommended approach.

Answer

Use a ForEach activity to iterate over a fixed number of pages.

Answer

Use a Lookup activity to retrieve the total number of pages and then use a ForEach.

Answer

Use an Until activity to loop until the API returns no more pages.

Question 9

You are designing a streaming job in Azure Stream Analytics. The job needs to count the number of events per device type every 10 seconds. The input is from Event Hubs. Which query should you use?

Accepted Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, TumblingWindow(second, 10). A TumblingWindow(second, 10) produces non-overlapping, fixed-size 10-second windows, which is exactly what is needed to count events per device type every 10 seconds. The GROUP BY clause groups by DeviceType and the window, ensuring each device type gets its own count per window. This query meets the requirement without overlapping or sliding behavior.

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SessionWindow(second, 10, 30)

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, HoppingWindow(second, 10, 1)

Answer

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SlidingWindow(second, 10)

Question 10

You are using Azure Synapse Analytics to process streaming data from Azure Event Hubs. The data must be written to a Delta Lake table in ADLS Gen2 with exactly-once semantics. Which processing engine should you use?

Accepted Answer

Azure Databricks with Structured Streaming. Azure Databricks with Structured Streaming is the correct choice because it natively supports exactly-once semantics when writing to Delta Lake from Event Hubs. Structured Streaming uses checkpointing and a write-ahead log to ensure each record is processed exactly once, even in the face of failures. Azure Databricks runs on Spark, which integrates seamlessly with both Event Hubs (via the Event Hubs connector) and Delta Lake (as a sink). Other options are either batch-oriented or lack the necessary transactional guarantees for exactly-once delivery to Delta Lake.

Answer

Azure Synapse serverless SQL pool

Answer

Azure Synapse Pipeline with Mapping Data Flow

Answer

Azure Stream Analytics

Question 11

You are designing a data pipeline that uses Azure Data Factory to load data from an FTP server to Azure Data Lake Storage. The FTP server requires authentication with username and password. Which type of linked service should you create?

Accepted Answer

FTP. Azure Data Factory provides a native FTP connector that supports username/password authentication for connecting to FTP servers. This linked service type is specifically designed to handle the FTP protocol (RFC 959) and allows you to copy data directly from an FTP server to Azure Data Lake Storage without requiring any additional gateways or custom activities.

Answer

Azure Blob Storage

Answer

HTTP

Answer

Rest service

Question 12

You are using Azure Synapse Analytics dedicated SQL pool to run a query that joins a large fact table (10 billion rows) and a small dimension table (1 million rows). The query is slow. Which distribution strategy should you use for the dimension table to improve performance?

Accepted Answer

Replicate the dimension table to all compute nodes.. Replicating the small dimension table (1 million rows) to all compute nodes eliminates data movement during the join with the large fact table (10 billion rows). In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so the join can be performed locally on every node without shuffling data across the network, drastically reducing query latency.

Answer

Round-robin distribute the dimension table.

Answer

Hash-distribute the dimension table on its primary key.

Answer

Hash-distribute the dimension table on the foreign key column.

Question 13

You are developing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. Which authentication method should you use to ensure that the queries use the identity of the caller and adhere to Azure role-based access control (RBAC) permissions?

Accepted Answer

Microsoft Entra ID pass-through authentication.. Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

Answer

Storage account key.

Answer

Shared access signature (SAS) token.

Answer

Service principal with a secret.

Question 14

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

Accepted Answer

The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.. The most likely cause is that the watermark query returns the maximum LastModified value, and the copy query uses that same value to filter, resulting in zero rows. In a watermark-based incremental load, the copy query should filter for rows where the watermark column is greater than the last watermark value, not equal to it. Using the same value would only capture rows with that exact timestamp, which may be none if no new rows have that exact value.

Answer

The dependency condition should be 'Completed' instead of 'Succeeded'.

Answer

The WatermarkQuery activity failed, causing the CopyData activity to be skipped.

Answer

The CopyData activity runs before the WatermarkQuery activity completes.

Question 15

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. The solution must support exactly-once semantics and handle late-arriving data. Which Azure service should you use to implement this solution?

Accepted Answer

Azure Stream Analytics.. Azure Stream Analytics is the correct choice because it natively integrates with Azure Event Hubs and dedicated SQL pools, supports exactly-once semantics through checkpointing and output deduplication, and provides built-in handling for late-arriving data via configurable late arrival tolerance windows and out-of-order event policies.

Answer

Azure Data Factory with tumbling window trigger.

Answer

Azure Functions with Event Hubs trigger.

Answer

Azure HDInsight Spark Structured Streaming.