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

Question 1

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?

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

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 2

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.. Option B is correct because column-level security (CLS) in Azure Synapse Analytics lets you grant or deny SELECT permissions on specific columns, so analysts querying the processed data cannot view PII columns such as names or SSNs while still accessing non-sensitive fields. Option E is correct because Azure Purview (now Microsoft Purview) scans and automatically classifies data using built-in and custom sensitivity labels, giving you the visibility needed to identify where PII resides in Azure Data Lake Storage and to drive downstream protection policies. Option A is not appropriate because Power BI data masking only hides values in reports at the presentation layer; it does not protect PII during ingestion or processing in the data lake. Option C is not appropriate because an IP-based storage firewall controls network access to the storage account, not the handling or exposure of PII content during processing. Option D is not appropriate because SQL Server Always Encrypted is a feature of SQL Server/Azure SQL Database and cannot be applied to data stored in Azure Data Lake Storage.

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 3

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 4

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 the Out-of-order tolerance window in Azure Stream Analytics event ordering settings defines how long the service will buffer and reorder events that arrive out of sequence, allowing correct temporal processing within that bound. Option C is correct because the Late-arrival tolerance window specifies how long the job will wait for and accept events that arrive after the watermark, so late events can still be included in the correct time window. Option E is correct because Azure Stream Analytics guarantees exactly-once delivery only when the output adapter supports it (for example, Azure SQL Database, Blob storage, or Data Lake Storage), which is essential for a real-time processing solution that must not duplicate or drop results. Option B is not correct because Streaming Units control compute capacity and throughput, not event ordering or late-arrival handling. Option D is not correct because Event Hubs Capture stores raw events for offline reprocessing and does not itself resolve out-of-order or late-arriving events in the Stream Analytics job.

Answer

Adjust the 'Streaming units' to handle higher throughput.

Answer

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

Question 5

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 6

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("inferSchema","true").csv(path). The best choice because `inferSchema` allows Spark to automatically detect the schema from the CSV files. However, it does not handle schema evolution across files with varying schemas; each file's schema is inferred independently, and if schemas differ, you may encounter errors or missing columns. For true schema evolution, you would need to use a custom schema or union DataFrames. Options A and B are invalid: A uses `mergeSchema`, which is not a standard CSV read option—it is used for Delta Lake or Parquet—and B reads Delta format, not CSV. Option D reads without any schema inference or evolution handling.

Answer

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

Answer

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

Answer

spark.read.csv(path)

Question 7

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. A self-hosted integration runtime is required to connect to an on-premises SQL Server from Azure Data Factory, and increasing the copy activity timeout addresses the timeout errors caused by large 50 GB transfers over potentially constrained network links. This combination ensures reliable data transfer for the on-premises source.

Answer

Use PolyBase to load data in parallel

Answer

Split the copy into multiple smaller activities

Answer

Switch to Azure Integration Runtime

Question 8

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

Accepted Answer

Use serverless SQL pool to query data in place. 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

Question 9

Which THREE actions improve the performance of a Delta table in Azure Databricks? (Choose three.)

Accepted Answer

Run VACUUM to remove old versions and compact small files. Running VACUUM on a Delta table removes old file versions that are no longer referenced by the Delta transaction log, freeing up storage and reducing the number of small files that must be read during queries. This compaction of small files improves I/O efficiency and query performance, especially in tables with frequent updates or deletes.

Answer

Disable auto-optimize to reduce overhead

Answer

Increase the number of shuffle partitions

Question 10

Which TWO are valid output sinks for an Azure Stream Analytics job? (Choose two.)

Accepted Answer

Azure SQL Data Warehouse (dedicated SQL pool). Azure Stream Analytics supports Azure SQL Data Warehouse (dedicated SQL pool) as a native output sink, allowing query results to be written directly to a dedicated SQL pool for analytics workloads, so option A is correct. Azure Blob Storage is also a supported output sink, commonly used for storing streamed results in formats like JSON, CSV, or Avro, making option B correct. Azure Cosmos DB (SQL API) is a supported output, but the option as written does not match the required API designation, so it is not one of the two correct answers here. Azure Table Storage is not a supported Stream Analytics output sink, and Azure Synapse Analytics is not listed as a distinct valid sink in this context, so options D and E do not belong.

Answer

Azure Cosmos DB (SQL API)

Answer

Azure Table Storage

Answer

Azure Synapse Analytics

Question 11

Refer to the exhibit. You created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files. Queries return no rows even though the files exist. What is the most likely issue?

Accepted Answer

The LOCATION path in the external table is relative to the data source, but the data source points to the wrong container or folder. In Azure Synapse serverless SQL pools, the LOCATION clause of an external table is resolved relative to the root of the external data source (which includes the connection string and path). If the data source points to the wrong container or folder, the relative path in the external table will not find the Parquet files, and queries will return zero rows without error. This is the most likely cause when files exist but are not returned, as the engine silently returns an empty result set rather than failing. Other issues like missing credentials or wrong file format typically produce errors or different symptoms.

Answer

The CREDENTIAL is missing

Answer

The FILE_FORMAT is incorrectly specified

Answer

The DATA_COMPRESSION is not supported for Parquet

Question 12

Your team is using Azure Data Factory to orchestrate a data pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully during testing. However, after moving to production, you notice that the pipeline fails intermittently with connectivity errors. You need to ensure reliable data transfer. What should you implement?

Accepted Answer

Use Self-Hosted Integration Runtime. The Self-Hosted Integration Runtime (SHIR) is required to connect to on-premises data sources like SQL Server from Azure Data Factory. It acts as a bridge between the cloud service and the private network, providing secure and reliable data transfer. The intermittent connectivity errors likely stem from network instability or firewall issues that the SHIR can mitigate through its robust retry and buffering mechanisms.

Answer

Use Azure Integration Runtime (IR)

Answer

Use PolyBase for data transfer

Answer

Configure Azure VPN Gateway

Question 13

You are designing a data processing solution for a retail company. The solution must ingest clickstream data from a website, process it in near real-time to update user session information, and store the results in Azure Cosmos DB for low-latency queries. Which combination of Azure services should you use?

Accepted Answer

Azure Event Hubs -> Azure Functions -> Azure Cosmos DB. Azure Event Hubs ingests high-volume clickstream data, Azure Functions provides serverless, event-driven processing for near real-time updates to user session information, and Azure Cosmos DB stores the results for low-latency queries. This combination is ideal for scenarios requiring real-time processing with minimal infrastructure management. Azure Functions can be triggered directly by Event Hubs, process each event, and write to Cosmos DB with low latency.

Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure SQL Database

Answer

Azure IoT Hub -> Azure Data Factory -> Azure Blob Storage

Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure Cosmos DB

Question 14

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A fact table named Sales is partitioned by date and has a hash-distributed column ProductKey. You notice that queries filtering on OrderDate are performing poorly. You need to improve query performance for date range filters. What should you do?

Accepted Answer

Ensure the table is partitioned on the same column used in WHERE clause (OrderDate) and enable partition elimination.. Partitioning the Sales table on OrderDate enables partition elimination, which allows the query optimizer to scan only the relevant partitions when filtering by date ranges. This reduces I/O and improves performance significantly, as the fact table is already hash-distributed on ProductKey, and partitioning on a different column used in WHERE clauses is a best practice in Azure Synapse Analytics dedicated SQL pools.

Answer

Create a non-clustered index on OrderDate.

Answer

Add ProductKey as a distribution column alongside the existing hash.

Answer

Change the distribution to round-robin to spread data evenly.

Question 15

You are developing a real-time data processing solution using Azure Stream Analytics. The input is from an Azure Event Hub, and the output is to an Azure Synapse Analytics dedicated SQL pool. You need to ensure exactly-once delivery semantics to the SQL pool. What should you configure?

Accepted Answer

Use the Azure Synapse Analytics output adapter with exactly-once semantics.. The Azure Synapse Analytics output adapter for Stream Analytics supports exactly-once delivery semantics to a dedicated SQL pool. This is achieved through built-in mechanisms that ensure each event is written exactly once, even in failure scenarios. Option A is incorrect because checkpointing is used for job recovery and state management, not for guaranteeing exactly-once output delivery. Option C is incorrect because custom deserializers handle data format conversion, not delivery guarantees. Option D is incorrect because event ordering by timestamp helps with time-based processing but does not provide delivery semantics like exactly-once.

Answer

Enable checkpointing in the query.

Answer

Use a custom deserializer.

Answer

Configure event ordering by timestamp.