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

Question 1

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?

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

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 2

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 3

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.. Azure Synapse Analytics provides serverless Spark pools with built-in scheduling via pipelines, allowing you to run daily batch jobs without managing clusters. Option A (Azure Batch) is for custom compute workloads, not Spark jobs. Option C (Azure Databricks) requires a job cluster that is not serverless. Option D (Azure Machine Learning) is designed for ML workflows, not general batch data processing.

Answer

Azure Batch with custom Spark containers.

Answer

Azure Databricks with a job cluster.

Answer

Azure Machine Learning with Spark compute.

Question 4

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.. Enabling staged copy via Azure Blob Storage improves performance for large datasets by allowing the copy activity to use blob storage as an intermediate staging location, reducing load on source and destination. Option A (parallel copies) may help but default auto parallelism already handles it. Option B (mapping data flow) is heavier and not needed for simple copy. Option C (increase DIU) is a more expensive option and should not be the first optimization.

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.

Question 5

You are designing a data processing solution that must handle personally identifiable information (PII). The data will be ingested from multiple sources into Azure Data Lake Storage. Which TWO actions should you take to protect the PII data during processing?

Accepted Answer

Use column-level security in Azure Synapse Analytics to restrict access to sensitive columns.. Column-level security in Azure Synapse Analytics allows you to restrict access to specific columns containing PII, such as Social Security numbers or email addresses, without altering the underlying data. This is a native feature of Synapse that can be applied during processing to ensure that only authorized users or roles can view sensitive columns, directly addressing the requirement to protect PII during data processing.

Answer

Apply data masking in Power BI reports to hide PII from end users.

Answer

Configure the Azure Data Lake Storage firewall to allow only specific IP addresses.

Answer

Implement SQL Server Always Encrypted for all data stored in the data lake.

Question 6

You are optimizing an Azure Synapse Analytics pipeline that uses mapping data flows. The pipeline runs slowly when processing 100 GB of data. Which THREE settings should you adjust to improve performance?

Accepted Answer

Set 'partition option' to 'Round robin' with a higher number of partitions.. Options A, C, and E are correct. Setting a higher number of partitions with Round robin partitioning improves parallelism across nodes. Increasing Compute type to Memory Optimized and increasing cores provides more resources for data processing. Using Optimize shuffle reduces data movement during joins and aggregations, improving performance. Option B (tumbling window trigger) is a scheduling mechanism, not a performance tuning setting. Option D (Data flow debug) is for development and debugging, not for production performance optimization.

Answer

Set a tumbling window trigger to run the pipeline every 5 minutes.

Answer

Enable 'Data flow debug' to monitor execution details.

Question 7

You are building a real-time processing solution using Azure Stream Analytics. The solution must handle out-of-order events and late arrivals. Which THREE mechanisms should you configure in the Stream Analytics job?

Accepted Answer

Set an 'Out-of-order tolerance' window in the event ordering settings.. Azure Stream Analytics allows you to configure an 'Out-of-order tolerance' window in the event ordering settings. This window defines the maximum time difference that out-of-order events can be reordered before being considered late. By setting this tolerance, you ensure that events arriving slightly out of sequence are still processed correctly, which is critical for real-time analytics where event order matters.

Answer

Adjust the 'Streaming units' to handle higher throughput.

Answer

Enable 'Event hub capture' to store raw events for reprocessing.

Question 8

You are designing a data processing solution for an e-commerce company. The company receives millions of clickstream events per hour from their website and needs to aggregate the data by product category and windowed time intervals for real-time dashboards. You need to minimize latency and cost. Which service should you use?

Accepted Answer

Azure Stream Analytics. (Azure Stream Analytics) is the best choice because it is purpose-built for real-time stream processing, supports windowed aggregations, and integrates with Power BI for dashboards. Option A (Azure Databricks Structured Streaming) can handle streaming but is more complex and typically more expensive for simple aggregations. Option B (Azure Data Factory) is for batch data movement, not real-time. Option D (Azure Synapse Pipelines) is for orchestrating data movement, not real-time processing.

Answer

Azure Databricks Structured Streaming

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 9

A financial services firm uses Azure Synapse Analytics to process daily trade data. The data is stored in a dedicated SQL pool as partitioned tables by date. Recently, queries that filter on a specific date range have become slow. You suspect that partition pruning is not working effectively. What should you do to improve query performance?

Accepted Answer

Rebuild the columnstore indexes on the table. Rebuilding columnstore indexes reorganizes and compresses the data, which can improve partition elimination by ensuring that each partition's columnstore segments are well-structured and aligned with the partition boundaries. Over time, as data is inserted, updated, or deleted, columnstore indexes can become fragmented, leading to inefficient partition pruning and slower query performance on date-range filters.

Answer

Convert the table to a rowstore heap

Answer

Create statistics on the date column

Answer

Increase the number of partitions for the table

Question 10

Your company uses Azure Data Factory to load data from an on-premises SQL Server database to Azure Synapse Analytics. The pipeline runs daily and the load volume is about 50 GB per run. Recently, the pipeline started failing with timeout errors during the copy activity. You need to ensure reliable data transfer. What should you do?

Accepted Answer

Use a self-hosted integration runtime and increase the copy activity timeout. For on-premises data sources, a self-hosted integration runtime (IR) is required to connect to Azure Data Factory. Increasing the copy activity timeout helps handle large data volumes (50 GB) and prevents timeout errors. Option B (PolyBase) is a technique for loading data into Azure Synapse but does not address the connectivity or timeout issue. Option C (splitting into smaller activities) could reduce load per activity but does not directly resolve the timeout; increasing timeout is more direct. Option D (Azure IR) cannot access on-premises resources, so it is not applicable.

Answer

Use PolyBase to load data in parallel

Answer

Split the copy into multiple smaller activities

Answer

Switch to Azure Integration Runtime

Question 11

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database. Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Answer

Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database

Answer

Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly

Answer

Use Azure Functions to process events and write to SQL Database

Question 12

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

Accepted Answer

The copy activity is not using staging and PolyBase. The Copy activity in Azure Data Factory uses PolyBase or COPY statement (staging) to bulk load data into Azure Synapse Analytics. Without staging and PolyBase, the default insert method is row-by-row, which is extremely slow for large datasets like 100 GB of CSV files. Enabling staging with PolyBase allows parallel, high-throughput loading, which is essential for performance at this scale.

Answer

The source and sink are in different Azure regions

Answer

The Data Integration Unit (DIU) setting is too low

Answer

The source files are compressed

Question 13

You need to process streaming data from Azure Event Hubs and store the results in Azure Cosmos DB for a real-time dashboard. The solution must handle duplicate events and ensure exactly-once processing. Which Azure service should you use?

Accepted Answer

Azure Stream Analytics. (Azure Stream Analytics) is correct because it provides exactly-once processing when configured with Event Hubs and Cosmos DB output. Option A (Azure Data Factory) is batch-oriented. Option B (Azure Functions) may have at-least-once guarantees. Option D (Azure Databricks) can achieve exactly-once but requires more configuration.

Answer

Azure Data Factory

Answer

Azure Functions with Event Hubs trigger

Answer

Azure Databricks Structured Streaming

Question 14

Your team uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is inconsistent, and some queries take a long time to execute. You need to improve query performance. What should you do?

Accepted Answer

Create statistics on the columns used in joins and filters. (Create statistics on the columns used in joins and filters) is correct because serverless SQL pool relies on statistics for optimal query plans. Option A (Increase the maximum degree of parallelism) is not directly applicable. Option C (Convert to CSV) would degrade performance. Option D (Use a dedicated SQL pool) may be an option but not the best immediate step.

Answer

Increase the MAXDOP setting in the query

Answer

Move the data to a dedicated SQL pool

Answer

Convert the Parquet files to CSV format

Question 15

You are designing a data processing pipeline that ingests data from a REST API endpoint every hour. The API returns JSON data with a varying schema. You need to store the raw data in Azure Data Lake Storage Gen2 and later process it using Azure Databricks. Which file format should you use for the raw data storage?

Accepted Answer

JSON. C is correct because the raw data arrives from a REST API with a varying JSON schema, and storing it in JSON format preserves the exact structure and schema variability without data loss or transformation. JSON is schema-on-read, meaning the raw data can be ingested as-is into Azure Data Lake Storage Gen2 and later processed by Azure Databricks, which natively supports JSON parsing. This avoids premature schema enforcement that would occur with columnar or binary formats.

Answer

Parquet

Answer

CSV

Answer

Avro