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

Question 1

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Using Azure Stream Analytics with output to both Power BI (real-time) and Data Lake Storage (historical) is a common pattern with minimal overhead. Option A is wrong because Azure Functions would require custom code for batching. Option B is wrong because Event Hubs doesn't provide real-time dashboards. Option C is wrong because Azure Databricks would require cluster management.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 2

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

Accepted Answer

Partition the data flow by a key column. Partitioning the data flow by a key column can improve performance by allowing parallel processing. Option A is wrong because using a staging table adds overhead. Option B is wrong because increasing batch size may cause memory issues. Option D is wrong because data flows do not use PolyBase.

Answer

Use a staging table to load data before transformation

Answer

Increase the batch size in the data flow

Answer

Use PolyBase to load data into the dedicated SQL pool

Question 3

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

Accepted Answer

Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations. Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Answer

Cache the data in memory after reading

Answer

Use bucketing with a fixed number of buckets

Answer

Partition the data by a high-cardinality column

Question 4

You are developing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion into a dedicated SQL pool. You need to ensure that data from streaming sources is available for queries within 5 seconds. Which approach should you use?

Accepted Answer

Use Azure Stream Analytics with a custom SQL function that writes directly to the dedicated SQL pool. Using Azure Stream Analytics with a custom function to write to the dedicated SQL pool via a stored procedure can achieve low latency. Option A is wrong because Azure Data Factory is batch-only. Option B is wrong because Event Hubs Capture writes in batches (minutes). Option D is wrong because Spark Structured Streaming to Data Lake and then PolyBase introduces additional latency.

Answer

Use Azure Databricks with Structured Streaming, write to Data Lake Storage, and then use PolyBase to load into SQL pool

Answer

Use Azure Data Factory with tumbling window triggers to load data from Event Hubs every 5 seconds

Answer

Use Event Hubs Capture to write to Data Lake Storage, then use PolyBase to load into the SQL pool every 5 seconds

Question 5

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?

Accepted Answer

The sink table has a primary key, and the source contains duplicate rows; enable upsert in the copy activity. The error indicates duplicate rows violating a primary key or unique constraint. Enabling upsert behavior allows the copy activity to handle duplicates. Option A is wrong because source format doesn't cause duplicates. Option B is wrong because using staging doesn't fix duplicates. Option D is wrong because sink table schema is not the cause.

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 6

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: A and D. Using replicated tables avoids data movement for small dimension tables, and materialized views pre-compute aggregations. B is wrong because hash distribution on a high-cardinality column is generally good. C is wrong because increasing DWU improves performance but is not a design optimization; it's a scaling action. E is wrong because round-robin distribution is not optimal for star schemas.

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 7

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

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 9

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 condition `acs:RequestVersion` requires the request to use a specific API version, which may not be met. Option A is wrong because the action is correct for reading containers. Option B is wrong because the resource path is correct. Option C is wrong because the principal is a managed identity.

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 10

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

Refer to the exhibit. You run the KQL query in Azure Data Explorer. What is the output?

Accepted Answer

Name: Alice, Age: 30; Name: Charlie, Age: 35. The KQL query uses the `take` operator to return a specified number of rows from the table. Since the query does not include an `order by` clause, the rows returned are non-deterministic but will be the first rows encountered in the data shard. In this case, the query `take 2` returns two rows, which are Alice (Age 30) and Charlie (Age 35), as shown in the exhibit. The `project` operator then selects only the Name and Age columns, so the output is exactly those two rows with those columns.

Answer

Id: 1, Name: Alice, Age: 30; Id: 3, Name: Charlie, Age: 35

Answer

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Answer

Name: Alice, Age: 30; Name: Bob, Age: 25; Name: Charlie, Age: 35

Question 12

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.. Option C is correct because the 'Incremental extraction' option in a mapping data flow source transformation allows you to filter rows based on a last modified timestamp column, enabling incremental loads. Option A is wrong because 'Column pattern' is used for detecting changes in column names, not row-level timestamps. Option B is wrong because 'Row sampling' selects a random subset of rows, not based on timestamps. Option D is wrong because 'Source metadata' in the sink transformation is unrelated to incremental loading.

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 13

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.. Option B is correct because partitioning the input data on the aggregation key ensures that each partition contains all rows for a given key, eliminating the need for a full shuffle. Option A is wrong because bucketing with a different column would not avoid shuffle on the aggregation key. Option C is wrong because coalesce reduces partitions but does not avoid shuffle. Option D is wrong because broadcast join is for joining small tables, not for aggregations.

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 14

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). Option B is correct because 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 15

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.