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

Question 1

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. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

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

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hub trigger

Question 2

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. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

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

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Azure Stream Analytics can directly output to both Power BI (for real-time dashboards) and Azure Data Lake Storage Gen2 (for historical analysis) in a single job, minimizing operational overhead by avoiding the need for multiple services or custom code. This serverless, fully managed service handles streaming data processing with low latency and integrates natively with Azure IoT Hub or Event Hubs for ingestion, making it ideal for healthcare IoT scenarios.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 3

Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use replicated tables for small dimension tables. Option C is correct because replicated tables in a dedicated SQL pool copy small dimension tables to every compute node, eliminating data movement (shuffle) during joins with large fact tables and thereby improving query performance. Option D is correct because materialized views precompute and persist the results of common aggregations, so repeated queries against large fact tables can read the smaller pre-aggregated result set instead of rescanning and re-aggregating the base data. Option A is wrong because hash distribution should be on a high-cardinality column that distributes rows evenly; low-cardinality columns cause data skew and uneven work distribution. Option B is wrong because round-robin distribution is best for staging or temporary tables, whereas fact tables benefit from hash distribution on a frequently joined column to minimize data movement. Option E is wrong because increasing DWU is a scaling action, not a query optimization technique, and doing it after every query is wasteful and does not address query design or data distribution issues.

Answer

Use hash distribution on a low-cardinality column

Answer

Use round-robin distribution for fact tables

Answer

Increase the DWU setting after every query

Question 4

Which THREE components are required to implement a real-time data processing solution using Azure Stream Analytics?

Accepted Answer

An input source such as Azure Event Hubs or IoT Hub. Azure Stream Analytics requires a streaming input source to ingest real-time data. Azure Event Hubs and IoT Hub are the primary services that provide high-throughput, low-latency event ingestion, which Stream Analytics can consume via its built-in connector. Without a streaming input, the job cannot process real-time data.

Answer

Power BI as the output sink

Answer

Azure Data Factory pipeline for orchestration

Question 5

Which TWO features of Azure Databricks help manage data governance and security for sensitive data?

Accepted Answer

Secret Scopes. Secret Scopes (B) allow secure storage and referencing of sensitive credentials (e.g., API keys, database passwords) in Azure Databricks, preventing hardcoding in notebooks. Unity Catalog (E) provides fine-grained access control, data lineage, and centralized metadata management across workspaces, enabling governance of sensitive data through policies and auditing.

Answer

Structured Streaming

Answer

Auto Loader

Answer

Delta Live Tables

Question 6

Refer to the exhibit. You have an Azure Synapse Analytics workspace. You need to ensure that data processing jobs can access the Data Lake Storage Gen2 account using a managed identity. What should you do?

Accepted Answer

Enable the system-assigned managed identity on the Synapse workspace and assign it the 'Storage Blob Data Contributor' role on the storage account. Azure Synapse Analytics supports system-assigned managed identities, which provide a secure, passwordless authentication method for accessing Azure Data Lake Storage Gen2. By enabling the managed identity on the Synapse workspace and assigning it the 'Storage Blob Data Contributor' role, you grant the workspace's data processing jobs the necessary permissions to read, write, and delete data in the storage account without managing credentials.

Answer

Use the SQL admin login credentials to access the storage account

Answer

Create a private endpoint connection between the workspace and the storage account

Answer

Configure the storage account firewall to allow access from the Synapse workspace

Question 7

You are developing a real-time data processing solution using Azure Stream Analytics. The input is an Azure Event Hubs stream with JSON data containing a 'timestamp' field. You need to output the average temperature per device every minute using a tumbling window. Which query should you use?

Accepted Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, TumblingWindow(minute, 1). A tumbling window is a fixed, non-overlapping time window that groups events into distinct time segments. Using `TumblingWindow(minute, 1)` with `TIMESTAMP BY Timestamp` ensures that the average temperature per device is computed over each one-minute interval without overlap, which matches the requirement of 'every minute'.

Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SlidingWindow(minute, 1)

Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SessionWindow(minute, 1, 1)

Answer

SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, HopWindow(minute, 1, 1)

Question 8

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. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

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

Accepted Answer

Implement a medallion architecture with bronze, silver, and gold layers.. The medallion architecture (bronze, silver, gold) is the correct pattern because it enforces immutability at the bronze layer (raw ingested data is never modified), while providing progressively refined, query-optimized views for both batch and streaming workloads in Azure Data Lake Storage Gen2. This design supports schema-on-read, enables reprocessing from raw data, and aligns with lakehouse principles for unified analytics.

Answer

Store data in a normalized relational database structure.

Answer

Use a data vault model with hubs, links, and satellites.

Answer

Design a star schema with fact and dimension tables.

Question 9

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

Accepted Answer

Derived Column transformation. The Derived Column transformation is correct because it allows you to create new columns by applying expressions to existing data. In this case, you can use string functions like `split()` or `substring()` and `locate()` to parse 'FullName' into 'LastName' and 'FirstName' based on the comma delimiter. This transformation operates row-by-row, making it ideal for simple column splits.

Answer

Pivot transformation

Answer

Aggregate transformation

Answer

Lookup transformation

Question 10

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

Accepted Answer

Increase spark.sql.shuffle.partitions to 800.. Spark's shuffle phase is governed by spark.sql.shuffle.partitions, which defaults to 200. With 200 GB of input, 200 partitions means roughly 1 GB per partition, causing large shuffle blocks, disk spills, and long task runtimes. Raising it to 800 creates smaller, more parallel partitions that better utilize the cluster's cores and reduce per-task memory pressure, which is the standard tuning lever for shuffle-heavy jobs.

Answer

Increase the number of executors to 4.

Answer

Change executor size to 'Large' to increase memory per executor.

Answer

Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

Question 11

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. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

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

Accepted Answer

Use Delta Lake's OPTIMIZE command to compact small files.. The OPTIMIZE command in Delta Lake compacts small files into larger ones, reducing the number of files that Spark must read during subsequent queries and writes. This is critical for large datasets where many small files can cause significant overhead in file listing and task scheduling. Option C is correct because Z-order optimization on frequently filtered columns improves data skipping, allowing Delta Lake to prune irrelevant files during scans, which dramatically reduces I/O and speeds up query performance.

Answer

Disable automatic schema detection to reduce overhead.

Answer

Cache the entire DataFrame in memory after reading.

Answer

Enable auto-compaction in Spark configuration.

Question 12

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

Accepted Answer

Rely on Delta Lake's built-in ACID transactions.. Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Answer

Enable Optimized Write on the Delta table.

Answer

Enable Auto Optimize on the Delta table.

Answer

Use Dynamic Partition Pruning in your Spark jobs.

Question 13

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

Accepted Answer

Power BI with DirectQuery mode and configure automatic page refresh.. Power BI with DirectQuery mode is the correct choice because it allows real-time queries directly against Azure SQL Database, supporting automatic page refresh every 30 seconds with minimal latency. Azure Analysis Services requires data processing and is not designed for sub-minute refreshes. Azure Data Explorer is optimized for time-series data, not for direct connection to Azure SQL Database for real-time dashboards. Azure Synapse Serverless SQL pool is meant for querying data lakes, not for real-time visualization with frequent refreshes.

Answer

Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.

Answer

Azure Data Explorer (ADX) with a continuous export to Power BI.

Answer

Azure Synapse Serverless SQL pool with Power BI import mode.

Question 14

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

Accepted Answer

Run OPTIMIZE to compact small files.. The OPTIMIZE command in Delta Lake compacts many small files into larger ones by rewriting data files based on the table's partitioning scheme. This reduces the number of files that need to be read during queries, significantly improving read performance. Since the notebook is slow due to many small files, OPTIMIZE directly addresses the root cause.

Answer

Run CONVERT TO DELTA on the underlying Parquet files.

Answer

Run DESCRIBE HISTORY to analyze file sizes.

Answer

Run VACUUM to delete old files.

Question 15

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

Accepted Answer

Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.. The 'Connection closed' error in a self-hosted integration runtime (SHIR) during large 50 GB transfers typically stems from resource exhaustion or unstable network connectivity on the SHIR host machine. When the SHIR runs out of memory or CPU, or when the network drops packets during long-running transfers, the TCP connection to the on-premises SQL Server is terminated mid-copy. Increasing memory/CPU and verifying network stability directly addresses the root cause of intermittent connection drops.

Answer

Increase the 'connection timeout' setting in the linked service to 30 minutes.

Answer

Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.

Answer

Disable fault tolerance in the copy activity to improve performance.