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

Question 1

You are using Azure Data Factory to copy data from Azure Blob Storage to Azure SQL Database. The copy operation fails with error 'Cannot insert duplicate key'. What is the most likely cause and solution?

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

Accepted Answer

The sink table has a primary key, and the source contains duplicate rows; enable upsert in the copy activity. The error 'Cannot insert duplicate key' indicates that the sink table in Azure SQL Database has a primary key or unique constraint, and the source data contains rows with duplicate key values. Enabling upsert in the copy activity allows Azure Data Factory to handle duplicates by updating existing rows instead of failing, resolving the conflict.

Answer

The source files are in an incompatible format; convert to CSV

Answer

The sink table schema does not match the source; update the schema

Answer

The copy activity is not using staging; enable staging

Question 2

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. Correct: C and D. Replicated tables are optimal for small dimension tables because they avoid data movement during joins. Materialized views pre-compute and store aggregation results, improving query performance for common aggregations. A is incorrect because hash distribution on a low-cardinality column can lead to data skew and uneven distribution. B is incorrect because round-robin distribution is generally used for staging tables, not fact tables in a star schema. E is incorrect because increasing DWU is a scaling action, not a query optimization design choice.

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 3

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 4

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 5

Refer to the exhibit. You have a managed identity that needs to read data from the 'data' container in Azure Data Lake Storage Gen2. The policy currently denies access. What is the most likely cause?

Accepted Answer

The condition on 'acs:RequestVersion' is preventing access because the request does not use the specified API version. The policy condition requires the request to include the API version '2021-06-08' via the `acs:RequestVersion` condition key. Managed identity requests to Azure Data Lake Storage Gen2 use a default API version that may not match this specific version, causing the deny. The condition explicitly checks the request's API version, and if it does not match, access is denied regardless of other permissions.

Answer

The resource path is malformed; it should include the blob path

Answer

The action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is incorrect; it should be 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read'

Answer

The principal is a managed identity, but the policy requires a user-assigned identity

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 building a data pipeline in Azure Synapse Analytics that ingests JSON files from Azure Data Lake Storage Gen2, transforms the data using a mapping data flow, and loads it into a dedicated SQL pool. The pipeline must support incremental loads based on a LastModified timestamp in the source files. Which configuration should you use in the mapping data flow source transformation?

Accepted Answer

Enable 'Incremental extraction' and specify the timestamp column for filtering.. Mapping data flows in Azure Synapse Analytics support incremental extraction by enabling the 'Incremental extraction' option in the source transformation and specifying a timestamp column (e.g., LastModified) to filter only new or updated rows. This allows the pipeline to load only changed data since the last run, which is essential for efficient incremental loads into a dedicated SQL pool.

Answer

Use a 'Column pattern' to detect new columns and filter rows.

Answer

Configure 'Row sampling' settings to take only new rows.

Answer

Set 'Source' to 'New rows only' in the sink transformation.

Question 8

You are designing a batch processing solution in Azure Databricks that reads Parquet files from Azure Data Lake Storage Gen2, performs aggregations, and writes results to a Delta table. The data volume is expected to grow to 10 TB per batch. You need to minimize shuffle operations during the aggregation step. Which approach should you recommend?

Accepted Answer

Partition the Delta table by the aggregation key and use the same partitioning when reading.. Partitioning the Delta table by the aggregation key and aligning the read partitioning ensures that data with the same key value is co-located in the same Spark partition. This allows the aggregation to be performed within each partition without needing to shuffle data across the network, which is the primary cause of performance bottlenecks in large-scale batch processing.

Answer

Use bucketing on a different column to reduce partition skew.

Answer

Use a broadcast join to avoid shuffle on the aggregation.

Answer

Use coalesce to reduce the number of partitions before aggregation.

Question 9

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 10

You are troubleshooting a data processing job in Azure Synapse Pipelines that fails intermittently with the error: 'Operation on target Sink failed: The request was aborted: Could not create SSL/TLS secure channel.' The pipeline reads from Azure Blob Storage and writes to an Azure SQL Database. The source and sink are in the same region. What is the most likely cause?

Accepted Answer

The self-hosted integration runtime is using TLS 1.0, which is not supported by the services.. The error 'Could not create SSL/TLS secure channel' indicates a TLS version mismatch. Azure SQL Database and Azure Blob Storage require at least TLS 1.2 for secure connections. If the self-hosted integration runtime (SHIR) is configured to use TLS 1.0, the handshake fails because the services reject the older protocol. This is the most likely cause given the intermittent nature and the specific error message.

Answer

Azure SQL Database firewall rules blocking the IP address of the integration runtime.

Answer

Transient network connectivity issues between the services.

Answer

The Azure SQL Database DTU limit has been exceeded, causing throttling.

Question 11

You are designing a data processing solution in Azure using Azure Data Lake Storage Gen2 as the storage layer. You need to ensure that data ingested from various sources is immutable and can be used for both batch and streaming workloads. Which storage design pattern should you implement?

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 12

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 13

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.. Increasing spark.sql.shuffle.partitions to 800 improves parallelism for a 200 GB shuffle-heavy workload, reducing partition sizes and shuffle time. Option A is incorrect because executor count alone does not directly address shuffle partitioning. Option B may help but is less impactful than partition tuning. Option D would worsen the problem by making partitions larger.

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 14

You are developing a streaming solution with Azure Stream Analytics that outputs results to Azure Synapse Analytics. The output requires exactly-once semantics to avoid duplicates. Which configuration should you use?

Accepted Answer

Configure the output to Azure Synapse Analytics with 'Exactly once' output mode.. Azure Stream Analytics supports an 'Exactly once' output mode for Azure Synapse Analytics, which ensures that each event is written exactly once to the destination, preventing duplicates. This mode uses a combination of checkpointing and transactional writes to guarantee idempotent delivery without requiring custom logic.

Answer

Use an Azure Function output to write data idempotently to Synapse.

Answer

Set the event serialization format to Avro and enable 'Exactly once' event delivery.

Answer

Output to Azure Blob Storage and then use a COPY statement in Synapse.

Question 15

You are designing a data processing solution using Azure Databricks. You need to read data from Azure Data Lake Storage Gen2, transform it using Spark SQL, and write to a Delta table. Which TWO configurations are required to ensure optimal performance for large datasets?

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.