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

Question 1

You have a mission-critical pipeline that processes financial transactions in Azure Synapse Analytics. The pipeline uses Azure Data Factory with a mapping data flow to transform data. You need to ensure high availability and minimal data loss in case of a regional failure. What should you implement?

Accepted Answer

Use Azure Synapse Link for Cosmos DB to enable near real-time analytics with multi-region writes.. Option D is correct because Azure Synapse Link for Cosmos DB provides near real-time sync with automatic failover. Option A (active geo-replication for SQL pool) is not native to Synapse. Option B (manual restore) has data loss. Option C (backup to GRS) does not provide automatic failover.

Answer

Store the source data in a GRS storage account and use Azure Data Factory to copy from the secondary endpoint.

Answer

Configure the pipeline to retry on failure and manually restore from backup.

Answer

Use Azure SQL Database active geo-replication as the source.

Question 2

You need to transform semi-structured JSON data into a tabular format for analysis in Azure Synapse Analytics. The data is stored in ADLS Gen2. Which feature should you use to query the JSON data directly without loading it into a table?

Accepted Answer

Use the OPENROWSET function in a serverless SQL pool.. Option A is correct because OPENROWSET in Synapse serverless SQL can query JSON files directly. Option B (COPY INTO) loads data. Option C (PolyBase) requires external tables. Option D (Azure Data Factory) is an orchestration tool.

Answer

Use Azure Data Factory to flatten the JSON and store as Parquet.

Answer

Create an external table using PolyBase.

Answer

Use the COPY INTO command in a dedicated SQL pool.

Question 3

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.. Options A and C are correct. Column-level security in Azure Synapse restricts access to sensitive columns. Azure Purview provides data classification and lineage. Option B (masking in Power BI) is a presentation-layer control. Option D (server-level firewall) is network security. Option E (always encrypted) is for SQL Server, not ADLS.

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 4

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 D are correct. Partitioning improves parallelism. Data flow debug is for development only. Optimize shuffle reduces data movement. Compute type and core count affect resource allocation. Option B (debug) is for testing. Option E (tumbling window) is for streaming.

Answer

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

Answer

Enable 'Data flow debug' to monitor execution details.

Question 5

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.. Option A is correct because 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 6

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. Option B (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 Synapse Pipelines) is for orchestrating data movement, not real-time processing. Option C (Azure Databricks) can handle streaming but is more complex and typically more expensive for simple aggregations. Option D (Azure Data Factory) is for batch data movement, not real-time.

Answer

Azure Databricks Structured Streaming

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 7

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. Option D (Rebuild the columnstore indexes) is correct because fragmented columnstore indexes can cause poor partition pruning and overall slow query performance. Option A (Increase the number of partitions) may worsen performance due to partition overhead. Option B (Switch to rowstore) would degrade performance for large tables. Option C (Create statistics on the date column) helps but is not the primary issue here; the problem is likely index fragmentation.

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 8

You need to process a large number of CSV files stored in Azure Data Lake Storage Gen2 using Azure Databricks. The files are nested in multiple folders, and the schema varies slightly between files. You want to automatically infer the schema and handle schema evolution. Which read option should you use?

Accepted Answer

spark.read.option("mergeSchema","true").csv(path). Option C (spark.read.format("csv").option("mergeSchema","true").load(path)) is correct because mergeSchema enables automatic schema inference and merging across files with different schemas. Option A loads without schema evolution. Option B infers schema but does not merge. Option D is for Delta Lake, not CSV.

Answer

spark.read.format("delta").load(path)

Answer

spark.read.option("inferSchema","true").csv(path)

Answer

spark.read.csv(path)

Question 9

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. Option B (Use a self-hosted integration runtime and increase the timeout) is correct because a self-hosted IR is required for on-premises sources, and increasing the timeout can handle larger volumes. Option A (Use Azure Integration Runtime) does not connect to on-premises. Option C (Use PolyBase) is for loading into Synapse but does not address the connectivity or timeout. Option D (Split the data into smaller chunks) might help but is not the primary solution; increasing timeout is more direct.

Answer

Use PolyBase to load data in parallel

Answer

Split the copy into multiple smaller activities

Answer

Switch to Azure Integration Runtime

Question 10

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. Option C is correct because 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 11

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. Option D (The copy activity is not using staging and PolyBase) is correct because PolyBase can significantly improve load performance into Synapse. Option A (Data Integration Unit too low) is possible but less likely than using PolyBase. Option B (Source files are compressed) should not degrade performance if using compressed format. Option C (Source and sink in different region) is not the case.

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 12

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. Option C (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 13

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. Option B (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 14

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. Option A (JSON) is correct because it preserves the original schema and is easy to store without transformation. Option B (Parquet) requires schema definition upfront. Option C (CSV) is not suitable for nested JSON. Option D (Avro) is good for streaming but less common for raw ingestion.

Answer

Parquet

Answer

CSV

Answer

Avro

Question 15

Which TWO strategies reduce data movement in Azure Synapse Analytics pipelines? (Choose two.)

Accepted Answer

Use serverless SQL pool to query data in place. Option C is correct because serverless SQL pools in Azure Synapse Analytics allow you to query data directly from files in Azure Data Lake Storage or other external sources without moving the data into a dedicated SQL pool. This eliminates data movement entirely by using the compute resources of the serverless pool to process queries in place, leveraging the OPENROWSET or CREATE EXTERNAL TABLE syntax to read data from its original location.

Answer

Use Data Flow to transform data before loading

Answer

Use Stored Procedure activity to insert data

Answer

Use Copy activity to move data from source to staging