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

Question 1

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.. Option A is correct because Power BI with DirectQuery mode can refresh at sub-minute intervals and query the database directly. Option B (Azure Analysis Services) is for tabular models, not real-time dashboards. Option C (Azure Data Explorer) is optimized for large-scale analytics, not for direct connection to Azure SQL Database. Option D (Azure Synapse Serverless SQL) is for querying data lakes, not for real-time dashboards.

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 2

You have an Azure Data Factory pipeline that uses a Copy activity to move data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with a timeout error. You need to improve the reliability of the data transfer. Which configuration change should you make?

Accepted Answer

Enable fault tolerance and configure skip incompatible rows.. Option C is correct because enabling fault tolerance and configuring 'skip incompatible rows' allows the Copy activity to continue processing even when some rows cause errors (e.g., type conversion failures), which can manifest as timeouts when the activity repeatedly retries the same problematic rows. This setting improves reliability by skipping rows that cannot be copied, preventing the entire pipeline from failing on intermittent data issues.

Answer

Use staged copy with an intermediate Azure Blob Storage.

Answer

Use PolyBase as the sink.

Answer

Increase the retry count in the pipeline activity.

Question 3

You are designing a near-real-time data processing solution that ingests millions of events per second from IoT devices. The data must be aggregated on a per-minute basis and stored in Azure Data Lake Storage Gen2 for long-term analytics. The solution must also support alerting when certain thresholds are exceeded. Which combination of Azure services should you use?

Accepted Answer

Azure IoT Hub, Azure Stream Analytics, and Azure Functions.. Option D is correct because Azure IoT Hub ingests device data, Azure Stream Analytics performs real-time aggregation and alerting, and Azure Functions can write aggregated results to Data Lake Storage. Option A (Azure Event Hubs) is a generic event broker but lacks device management capabilities. Option B (Azure Databricks) is more suited for complex analytics than simple aggregation. Option C (Azure Data Explorer) is for ad-hoc analytics, not for streaming aggregation with alerting.

Answer

Azure Event Hubs, Azure Data Factory, and Azure SQL Database.

Answer

Azure IoT Hub, Azure Databricks with Structured Streaming, and Azure Data Lake Storage Gen2.

Answer

Azure Event Hubs, Azure Data Explorer, and Power BI.

Question 4

You are developing a data processing pipeline in Azure Synapse Analytics that uses a SQL script to transform data in a dedicated SQL pool. The pipeline currently runs in the built-in serverless pool, but you want to migrate it to a dedicated SQL pool for better performance. What must you change in the pipeline?

Accepted Answer

Modify the SQL script to use T-SQL compatible with dedicated SQL pool.. Option A is correct because dedicated SQL pool uses T-SQL with specific syntax differences from serverless SQL pool. Option B (linked service) may also need updating but is not the primary change. Option C (integration runtime) is not necessarily required if both pools are in the same workspace. Option D (data flow) is a different activity type.

Answer

Change the linked service from serverless to dedicated SQL pool.

Answer

Replace the SQL script with a Mapping Data Flow activity.

Answer

Update the integration runtime to a self-hosted IR.

Question 5

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.. Option C is correct because OPTIMIZE compacts small files into larger ones, improving read performance. Option A (VACUUM) removes old files but does not compact. Option B (DESCRIBE HISTORY) shows history. Option D (CONVERT TO DELTA) converts Parquet but does not optimize.

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 6

You are designing a data processing solution that must handle both batch and stream processing in a unified manner. You need to choose a technology that supports a single code base for both processing modes. Which TWO technologies meet this requirement?

Accepted Answer

Azure Databricks with Delta Live Tables. Option B (Azure Databricks with Delta Live Tables) and Option D (Apache Spark Structured Streaming in Azure Synapse) support a unified batch-stream programming model. Option A (Azure Stream Analytics) is streaming-only. Option C (Azure Data Factory) is batch-only. Option E (Azure SQL Database) is not a processing engine.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure SQL Database with Change Tracking

Question 7

You are building a data processing pipeline in Azure Synapse Analytics that ingests data from Azure Blob Storage and writes to a dedicated SQL pool. You need to ensure the pipeline can handle schema changes in the source data without failing. Which TWO actions should you take?

Accepted Answer

Enable schema drift in a Mapping Data Flow activity.. Mapping Data Flows in Azure Synapse Analytics support schema drift, which allows the pipeline to dynamically handle changes in source data structure (e.g., new columns, changed data types) without failing. By enabling schema drift, the data flow can automatically detect and propagate these changes downstream, ensuring resilience against schema evolution.

Answer

Use staged copy with an intermediate storage.

Answer

Define a fixed schema in the source dataset.

Answer

Use PolyBase to load data into the dedicated SQL pool.

Question 8

You are optimizing an Azure Synapse Pipeline that processes large volumes of data using a Mapping Data Flow. The pipeline is taking too long due to data skew. Which THREE techniques can help mitigate data skew?

Accepted Answer

Use a broadcast hash join to distribute the smaller table to all nodes.. Option B (use skewed data optimization), Option D (broadcast hash join), and Option E (salting the key) are techniques to handle skew in data flows. Option A (increasing DIU) adds resources but doesn't address skew. Option C (repartition) can help but is not a direct skew mitigation; broadcast join is more effective.

Answer

Increase the Data Integration Units (DIU) in the data flow.

Answer

Repartition the data on a different column using a random distribution.

Question 9

You are building a streaming pipeline in Azure Synapse Analytics to ingest real-time sensor data from IoT devices. The data must be processed with a 2-second latency and stored in a dedicated SQL pool for reporting. The source emits JSON messages with a nested structure. Which approach should you use to ingest and transform the data?

Accepted Answer

Use Azure Stream Analytics to ingest data from Azure Event Hubs, apply a query to flatten the JSON, and output directly to the dedicated SQL pool.. Option B is correct because Azure Stream Analytics can ingest from Event Hubs, flatten JSON in real-time, and write to Synapse SQL pool via the built-in output adapter. Option A (Databricks Auto Loader) is for batch/streaming but not optimal for sub-2-second latency and nested JSON flattening without additional code. Option C (Spark structured streaming) is possible but more complex. Option D (Azure Data Factory) is for batch/orchestration, not real-time streaming.

Answer

Use Azure Synapse Spark with structured streaming to read from Event Hubs, flatten the JSON using Spark SQL, and write to the dedicated SQL pool.

Answer

Use Azure Data Factory to run a tumbling window trigger that reads from Event Hubs every 2 seconds and copies data to the dedicated SQL pool.

Answer

Use Azure Databricks with Auto Loader to ingest data from Event Hubs and write to the dedicated SQL pool.

Question 10

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.. Option A is correct because the self-hosted IR's memory and network stability are common causes of 'Connection closed' errors with large data volumes. Option B (disable fault tolerance) would actually make the issue worse. Option C (change to staging copy) might help but is not the first step. Option D (increase TTL) addresses only idle connections, not active data transfers.

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.

Question 11

You are designing a batch processing solution for a data lake. Source files arrive daily in Parquet format in Azure Data Lake Storage Gen2. The data must be cleaned, aggregated, and loaded into an Azure Synapse SQL pool. The solution should minimize compute costs and management overhead. Which technology should you use for the transformation?

Accepted Answer

Azure Synapse Pipelines with mapping data flows.. Option C is correct because Synapse Pipelines (built on ADF) can orchestrate mapping data flows that run on serverless Spark clusters, minimizing management overhead. Option A (HDInsight) requires cluster management. Option B (Databricks) incurs higher costs for simple batch. Option D (SSIS) is legacy and not cloud-native.

Answer

Azure HDInsight with Spark jobs scheduled in Azure Data Factory.

Answer

Azure Data Factory with a custom SSIS package.

Answer

Azure Databricks with an Auto Loader pipeline.

Question 12

You are building a real-time dashboard to monitor user activity on a website. The data is ingested via Azure Event Hubs and must be aggregated every minute with a 30-second late-arrival tolerance. The aggregated results should be stored in Azure Cosmos DB for low-latency reads. Which Azure service should you use to perform the windowed aggregation?

Accepted Answer

Azure Stream Analytics with a tumbling window of 1 minute and a late-arrival policy of 30 seconds.. Azure Stream Analytics is the correct choice because it natively supports windowed aggregations (tumbling, hopping, sliding, session) and allows you to define a late-arrival policy to handle out-of-order events. A tumbling window of 1 minute with a late-arrival tolerance of 30 seconds meets the requirement exactly, and the output can be directly written to Azure Cosmos DB for low-latency reads.

Answer

Azure Functions triggered by Event Hubs to aggregate data and write to Cosmos DB.

Answer

Azure Databricks with structured streaming and a sliding window.

Answer

Azure Analysis Services to process streaming data directly from Event Hubs.

Question 13

You are optimizing a data pipeline in Azure Synapse Analytics that loads data from a CSV file in ADLS Gen2 into a dedicated SQL pool using PolyBase. The load is slow and you need to improve performance. Which action would be MOST effective?

Accepted Answer

Combine the CSV files into fewer, larger files before loading.. Combining many small CSV files into fewer, larger files reduces the number of file open/close operations and minimizes the overhead of PolyBase's external file enumeration and parallel split logic. PolyBase performs best when each file is at least 256 MB, as it can then assign a full file to each distribution, avoiding the overhead of splitting tiny files across multiple threads.

Answer

Increase the service level (DWU) of the dedicated SQL pool.

Answer

Change the file format from CSV to Avro.

Answer

Use Azure Data Factory to stage the data in Azure Blob Storage before loading.

Question 14

Your company uses Azure Data Lake Storage Gen2 as the central data lake. You need to process batch data using serverless Spark jobs that can be scheduled daily. Which Azure service should you use?

Accepted Answer

Azure Synapse Analytics serverless Spark pool with pipelines.. Option C is correct because Azure Synapse Analytics provides serverless Spark pools with a built-in scheduler. Option A (Azure Databricks) is not serverless (requires cluster management). Option B (Azure Batch) is for custom compute jobs. Option D (Azure Machine Learning) is for ML workloads.

Answer

Azure Batch with custom Spark containers.

Answer

Azure Databricks with a job cluster.

Answer

Azure Machine Learning with Spark compute.

Question 15

You are troubleshooting a slow-running Azure Data Factory pipeline that copies data from an Azure SQL Database to ADLS Gen2. The pipeline uses a copy activity with the default settings. The source table has 10 million rows. Which optimization should you apply first?

Accepted Answer

Enable staged copy using an Azure Blob Storage staging location.. Option B is correct because enabling staging via Blob Storage improves performance for large cross-region or cross-service copies. Option A (parallel copies) might help but default settings already use parallelism. Option C (DIU increase) is a more expensive solution. Option D (data flow) is heavier and not needed for simple copy.

Answer

Set the 'parallel copies' property to 10.

Answer

Replace the copy activity with a mapping data flow.

Answer

Increase the data integration unit (DIU) to maximum.