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

Question 1

A data engineering team is building a batch processing solution for a financial services company. Data is ingested daily from multiple sources into Azure Data Lake Storage Gen2 in CSV format. The data must be transformed (filtered, aggregated, joined) and loaded into Azure Synapse Analytics dedicated SQL pool. The team must optimize for cost and performance. The total data volume is 2 TB per day. The team has the following options:

Option A: Use Azure Data Factory pipelines with copy activity to load raw CSV files into Synapse staging tables, then use T-SQL stored procedures in Synapse to perform transformations.

Option B: Use Azure Databricks with Auto Loader to incrementally ingest CSV files, perform transformations in Spark, and write the results to Synapse using the Spark Synapse connector.

Option C: Use Azure Data Factory with mapping data flows to transform the data in a serverless environment and then write to Synapse.

Option D: Use Azure Synapse Pipelines (built on ADF) with a notebook activity that runs a PySpark notebook in Synapse Spark pool to transform and load data.

Which option should the team choose to minimize cost and management overhead while meeting performance requirements?

Accepted Answer

Option C. Correct answer: B (Option C — Azure Data Factory mapping data flows). Mapping data flows execute on a serverless Azure Data Factory integration runtime, scaling automatically and costing only per run, which minimizes cost and management overhead. Answer choice A (Option B, Azure Databricks with Auto Loader) requires managing Spark clusters. Answer choice C (Option A, Azure Data Factory copy activity plus T-SQL stored procedures) requires staging tables and stored procedures. Answer choice D (Option D, Synapse Pipelines with a notebook activity) requires managing Synapse Spark pools. Therefore, mapping data flows are the most cost-effective and lowest-overhead option.

Answer

Option B

Answer

Option A

Answer

Option D

Question 2

You are designing a data processing solution for a retail company that uses Azure Databricks. The solution needs to process streaming sales data from Event Hubs and batch data from Azure Data Lake Storage Gen2. You need to ensure that the solution can handle late-arriving data and maintain exactly-once semantics. Which TWO technologies should you use?

Accepted Answer

Delta Lake. Delta Lake is correct because it provides ACID transactions, schema enforcement, and time travel capabilities, which are essential for handling late-arriving data and ensuring exactly-once semantics when combined with Structured Streaming. It allows you to merge late records into existing Delta tables using merge operations (upserts) without corrupting the data state.

Answer

PolyBase

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 3

You are a data engineer for a healthcare company that processes patient data. You have an Azure Databricks workspace with a cluster configured for data processing. You need to implement a solution that processes streaming data from Azure Event Hubs, enriches it with reference data stored in Azure Cosmos DB, and writes the output to Delta Lake in Azure Data Lake Storage Gen2. The solution must ensure that the data processing is fault-tolerant and can handle schema evolution. The reference data is updated infrequently. You need to choose an approach that minimizes complexity and cost. What should you do?

Accepted Answer

Use Azure Databricks Structured Streaming to read from Event Hubs, use a streaming static join to enrich with reference data from Cosmos DB, and write to Delta Lake. Enable schema evolution on the Delta table.. Azure Databricks Structured Streaming can read from Event Hubs, and a streaming static join allows enriching with reference data from Cosmos DB (which is updated infrequently). Writing to Delta Lake with schema evolution enabled handles schema changes. This approach minimizes complexity and cost by using native Databricks features without additional services.

Answer

Use Azure Databricks Auto Loader with Delta Live Tables to ingest streaming data, and use Change Data Capture from Cosmos DB to update the reference data inline.

Answer

Use Azure Data Factory to copy data from Event Hubs to Azure Data Lake Storage Gen2 in batches, then use Azure Databricks to process and enrich with Cosmos DB.

Answer

Use Azure Stream Analytics to ingest from Event Hubs, join with Cosmos DB reference data, and output to Azure Data Lake Storage Gen2 in Parquet format.

Question 4

You are designing a data processing solution for an e-commerce company that uses Azure Synapse Analytics. The solution must process clickstream data from a web application. The data arrives in JSON format through Azure Event Hubs. You need to load the data into a dedicated SQL pool every 5 minutes with minimal latency. The data volume is about 100 MB every 5 minutes. You want to use PolyBase for loading. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a Copy activity to copy data from Event Hubs to Azure Data Lake Storage Gen2 as JSON files, then use a PolyBase activity to load from ADLS Gen2 to the dedicated SQL pool.. Using Azure Data Factory to copy data from Event Hubs to ADLS Gen2 as JSON files, then using PolyBase to load into the dedicated SQL pool, is the recommended approach. PolyBase can efficiently load large volumes from ADLS Gen2, and Data Factory provides a scalable, low-latency pipeline. This approach leverages PolyBase's parallel loading capabilities and is cost-effective for 100 MB every 5 minutes.

Answer

Use Azure Stream Analytics to transform the JSON data and output directly to the dedicated SQL pool.

Answer

Use Azure Databricks to read from Event Hubs, transform the data, and write to the dedicated SQL pool using JDBC.

Answer

Use PolyBase directly from Event Hubs to dedicated SQL pool by creating an external data source that points to Event Hubs.

Question 5

You are a data engineer at a financial services company. You are developing a data processing pipeline that uses Azure Data Factory to copy transactional data from an Azure SQL Database to Azure Data Lake Storage Gen2. The pipeline runs daily and processes about 10 GB of data. You need to implement error handling for the pipeline. Specifically, if the copy activity fails due to a transient error, the pipeline should retry automatically. If the retry fails, the pipeline should log the error and send an email alert to the operations team. What should you do?

Accepted Answer

Configure the copy activity with retry policy (retry count = 2, retry interval = 30 seconds). Add a failure path to a web activity that calls an Azure Logic App to send an email.. Configuring the copy activity's retry policy (retry count and interval) handles transient failures automatically at the activity level. Adding a failure dependency path to a Web activity that invokes a Logic App provides the required logging and email alert when retries are exhausted. This directly satisfies both the retry and notification requirements.

Answer

Use Azure Functions to implement custom retry logic and send email.

Answer

Create an Azure Monitor alert for failed pipeline runs and configure an action group to send an email.

Answer

Set the pipeline retry to 2 and add a storage event trigger on the error file.

Question 6

You are designing a data lakehouse architecture in Azure using Delta Lake. The solution needs to process batch and streaming data from multiple sources, including IoT devices and CRM systems. You need to ensure data quality by enforcing schema validation and handling schema evolution. You also need to provide a unified catalog for querying. Which service should you use?

Accepted Answer

Azure Databricks Unity Catalog. Azure Databricks Unity Catalog is the only option that provides a unified governance layer across workspaces with built-in support for Delta Lake schema enforcement, schema evolution, and a centralized metastore for querying. It natively integrates with Delta Lake's transaction log to enforce schema-on-write and supports MERGE/ALTER operations for evolution. The other services provide storage or query capabilities but not unified cataloging with schema governance.

Answer

Azure Purview

Answer

Azure Data Lake Storage Gen2

Answer

Azure Synapse Analytics serverless SQL pool

Question 7

You are designing a data processing solution for a retail company that uses Azure Synapse Analytics. The solution must process point-of-sale (POS) data from multiple stores. The data arrives in CSV files in Azure Data Lake Storage Gen2. Each store sends a file every hour. You need to process the files as they arrive and load the data into a dedicated SQL pool. The solution must handle late-arriving files (files that arrive after the scheduled processing time) and ensure that the data is consistent. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a Copy activity to load data into a staging table in the dedicated SQL pool, then use a Stored Procedure activity to merge the data into the final table.. The recommended approach for handling late-arriving files and ensuring data consistency when loading into a dedicated SQL pool is to use Azure Data Factory with a Copy activity to load data into a staging table, then use a Stored Procedure activity to merge the data into the final table. This pattern allows for upserts and handles late-arriving data by merging based on business keys.

Answer

Use Azure Data Factory with a Copy activity to load data into a staging table, then use a Data Flow activity to perform upserts.

Answer

Use Azure Databricks to read the CSV files, perform upserts, and write to the dedicated SQL pool using JDBC.

Answer

Use PolyBase to create external tables on the CSV files and then use CREATE TABLE AS SELECT to load into the dedicated SQL pool.

Question 8

You are a data engineer at a manufacturing company. You need to process sensor data from IoT devices that arrive in real time. The data is sent to Azure Event Hubs. You need to aggregate the data over 5-minute windows and store the results in Azure Data Lake Storage Gen2 in Parquet format. The solution should minimize cost and use serverless components. Which solution should you use?

Accepted Answer

Use Azure Stream Analytics to create a query with a tumbling window of 5 minutes, and output the results to Azure Data Lake Storage Gen2 in Parquet format.. Azure Stream Analytics is a fully managed, serverless real-time analytics service that natively supports tumbling windows and can output directly to Azure Data Lake Storage Gen2 in Parquet format. It minimizes operational cost and management overhead because there are no clusters to provision, and it integrates directly with Event Hubs as an input.

Answer

Use Azure Databricks with Structured Streaming to read from Event Hubs, aggregate with a sliding window, and write to ADLS Gen2 in Parquet.

Answer

Use Azure Data Factory with a tumbling window trigger to run a pipeline every 5 minutes that copies data from Event Hubs to ADLS Gen2.

Answer

Use Azure Functions with an Event Hubs trigger to aggregate data in memory and write to ADLS Gen2.

Question 9

You are designing a data processing solution for a marketing company that uses Azure Synapse Analytics. The solution needs to process customer data from multiple sources, including CRM and web analytics. The data must be cleansed and transformed before loading into a dedicated SQL pool. The transformations include string manipulations, date conversions, and lookups. You need to choose a serverless transformation approach that integrates with Azure Synapse pipelines. Which approach should you use?

Accepted Answer

Use mapping data flows in Azure Synapse pipelines.. Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless. Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

Answer

Use Azure Stream Analytics to transform the data in real time.

Answer

Use PolyBase to load data and then use T-SQL stored procedures to transform.

Answer

Use Azure Databricks notebooks with Spark to perform transformations.

Question 10

You are developing a data processing pipeline for a gaming company that uses Azure Databricks. The pipeline processes game event data from Azure Event Hubs. You need to detect cheating patterns by analyzing events in real time. The solution must be able to handle high throughput and low latency. The output should be written to Azure Cosmos DB for real-time dashboards. Which approach should you use?

Accepted Answer

Use Azure Databricks Structured Streaming to read from Event Hubs, use Spark SQL and machine learning to detect cheating patterns, and write to Cosmos DB using the Azure Cosmos DB Spark connector.. Azure Databricks Structured Streaming is designed for scalable, low-latency stream processing and integrates with Event Hubs and Cosmos DB via connectors. It allows using Spark SQL and machine learning libraries to detect cheating patterns in real time. This approach handles high throughput and provides the necessary analytics capabilities. The other options either lack the required real-time processing or the advanced analytics.

Answer

Use Azure Functions with Event Hubs trigger to process each event and write to Cosmos DB.

Answer

Use Azure Data Factory with continuous copy to load data into Cosmos DB and then use Azure Synapse Analytics to detect patterns.

Answer

Use Azure Stream Analytics to query the stream for cheating patterns and output to Cosmos DB.

Question 11

You are building an Azure Stream Analytics job that processes JSON telemetry from Azure Event Hubs. The events contain a nested array field named `readings` with sensor values. You need to transform the data so that each sensor reading becomes a separate output row, and then write the results to Azure Synapse Analytics. Which two actions should you perform? (Choose two.)

Accepted Answer

Use the CROSS APPLY operator with the GetArrayElements function to expand the `readings` array into individual events.. The requirement is to flatten a nested array into separate rows. In Azure Stream Analytics, this is achieved by using GetArrayElements with either CROSS APPLY or a JOIN in a subquery. Both methods return one row per array element, which can then be written to Synapse Analytics. Other options do not perform the necessary row expansion.

Answer

Use the STREAMING TIMESTAMP BY clause to reorder events and then apply a windowing function to explode the array.

Answer

Configure the output to Azure Synapse Analytics with the 'Allow user-defined functions' setting enabled.

Answer

Use the JavaScript UDF to parse the JSON and return a single concatenated string of all readings.

Question 12

You are building an Azure Stream Analytics job that reads from an Azure Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. You need to compute a 5-minute tumbling window aggregation that outputs only once per window after all events for that window have arrived. Which query construct should you use?

Accepted Answer

GROUP BY with TUMBLINGWINDOW and a 5-minute window, emitting results when the window closes. Tumbling windows are fixed-size, non-overlapping, and emit a single result when the window closes, which aligns exactly with the need to output once per 5-minute interval after all events arrive. Other window types or external scheduling mechanisms either emit too frequently or cannot guarantee completeness of the window before output.

Answer

A self-join on the input stream using DATEDIFF to group events into 5-minute buckets

Answer

A persistent SQL table in the dedicated SQL pool that stores events, with a scheduled stored procedure running every 5 minutes

Answer

GROUP BY with HOPPINGWINDOW and a 5-minute window and 1-minute hop

Question 13

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub. Each event contains a nested array property named 'readings' with multiple sensor values. You need to output one row per sensor reading to an Azure Synapse Analytics dedicated SQL pool. The query must flatten the array. Which query syntax should you use?

Accepted Answer

SELECT deviceId, reading.value FROM input CROSS APPLY GetArrayElements(input.readings) AS reading. Azure Stream Analytics flattens nested arrays using the built-in GetArrayElements function combined with CROSS APPLY. GetArrayElements returns a table with a 'value' column for each element, and CROSS APPLY expands those elements into separate rows. Referencing reading.value projects the scalar sensor value, producing one output row per reading as required.

Answer

SELECT deviceId, readings.value FROM input UNNEST(input.readings) AS readings

Answer

SELECT deviceId, reading FROM input CROSS APPLY GetArrayElements(input.readings) AS reading

Answer

SELECT deviceId, EXPLODE(input.readings) AS value FROM input

Question 14

You are building an Azure Stream Analytics job that reads JSON telemetry from an Azure Event Hub, calculates a 5-minute tumbling window average per device, and writes results to an Azure Synapse Analytics dedicated SQL pool. The stream must handle occasional bursts of late-arriving events by including events that arrive up to 3 minutes after the window closes. You need to configure the job's event ordering settings to meet the late-arrival requirement while minimizing memory usage. What should you do?

Accepted Answer

Set the late arrival tolerance to 3 minutes and the out-of-order tolerance to 0 seconds.. Late-arriving events are handled by the late arrival tolerance, which delays window finalization to include events that arrive after the window end. Out-of-order tolerance is separate and controls reordering within the stream. To meet the 3-minute late-arrival requirement while minimizing memory, set late arrival tolerance to 3 minutes and keep out-of-order tolerance low, as only late arrivals need extended buffering.

Answer

Increase the streaming units for the job and leave the default event ordering settings unchanged.

Answer

Set the out-of-order tolerance to 3 minutes and the late arrival tolerance to 0 seconds.

Answer

Set both the late arrival tolerance and the out-of-order tolerance to 3 minutes.

Question 15

You are implementing a real-time analytics solution using Azure Stream Analytics. The job ingests data from Azure Event Hubs and must output to an Azure SQL Database. You need to ensure that the job can handle out-of-order events and produce accurate aggregations over 5-minute windows. Which setting should you configure?

Accepted Answer

Set the 'Out-of-order tolerance' to 5 minutes and use a Tumbling Window of 5 minutes.. To handle out-of-order events and aggregate over fixed 5-minute windows, configure the out-of-order tolerance to 5 minutes and use a Tumbling Window. The tolerance allows late events to be processed, and the Tumbling Window provides non-overlapping, fixed-duration aggregations. Other window types or settings do not meet both requirements simultaneously.

Answer

Set the 'Late arrival tolerance' to 5 minutes and use a Sliding Window of 5 minutes.

Answer

Set the 'Event ordering' to 'Adjust' and use a Session Window with a 5-minute timeout.

Answer

Set the 'Out-of-order tolerance' to 0 and use a Hopping Window of 5 minutes with a 1-minute hop.