DP-203 Practice Test 12 — 15 Questions

Question 1

You are designing a streaming data solution for IoT devices that generate 10,000 events per second. The data must be processed with sub-second latency and then stored in Azure Data Lake Storage Gen2 for archival. Which Azure service should you use for the stream processing?

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

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for real-time stream processing with sub-second latency, and it natively integrates with Azure Data Lake Storage Gen2 for output. It can handle 10,000 events per second using its streaming unit scaling, and its SQL-like query language allows for low-latency transformations without the overhead of cluster management.

Answer

Azure HDInsight including Spark Structured Streaming

Answer

Azure Data Factory

Answer

Azure Event Hubs

Question 2

A manufacturing company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data arrives in JSON format with a nested structure. You need to transform the data into a tabular format for downstream analytics using Azure Synapse Pipelines. Which data flow transformation should you use?

Accepted Answer

Flatten transformation. The Flatten transformation in mapping data flows unpacks nested arrays into rows. Option A is wrong because the Aggregate transformation groups data but does not flatten nested structures. Option C is wrong because the Window transformation calculates aggregated values over a range of rows. Option D is wrong because the Pivot transformation rotates rows to columns.

Answer

Aggregate transformation

Answer

Window transformation

Answer

Pivot transformation

Question 3

Which TWO options are valid methods to load data from on-premises SQL Server into Azure Synapse Analytics?

Accepted Answer

SQL Server Integration Services (SSIS) package. Both SQL Server Integration Services (SSIS) and Azure Data Factory are fully capable of loading data from on-premises SQL Server into Azure Synapse Analytics. SSIS provides a mature, high-performance ETL tool that can directly target Synapse using the SQL Server Destination or the Azure Synapse Analytics Destination. Azure Data Factory, with its self-hosted integration runtime and incremental copy feature, offers a modern, cloud-native orchestration solution for scheduled and reliable data ingestion. PolyBase from external table, while useful for querying external data, is not primarily a data loading method; it requires additional steps to persist the data into Synapse tables and does not directly load from on-premises SQL Server without external staging.

Answer

PolyBase from external table

Question 4

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

Accepted Answer

Azure Synapse Analytics dynamic data masking. Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs. Azure Data Lake Storage does not provide masking.

Answer

Azure API Management policies

Answer

Azure Data Lake Storage access control lists (ACLs)

Answer

Azure Purview classification and labeling

Question 5

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hub trigger

Question 6

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

Accepted Answer

Check the input data schema and ensure it matches the query. Input deserialization errors indicate that the incoming data format does not match the schema defined in the Stream Analytics query. Increasing watermark delay is a symptom of this mismatch, as the job cannot parse events correctly and falls behind. Checking and aligning the input schema with the query is the first and most direct troubleshooting step.

Answer

Change the output to a different sink

Answer

Increase the number of Streaming Units (SUs)

Answer

Set the watermark delay threshold higher

Question 7

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

Accepted Answer

Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*). Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Answer

Increase the maximum query concurrency limit

Answer

Provision a dedicated SQL pool with more DTUs

Answer

Create a clustered columnstore index on the date column

Question 8

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. You notice that some queries are being queued due to resource class conflicts. What should you implement to optimize performance and reduce queuing?

Accepted Answer

Configure workload management with workload groups and classifiers. Workload management with workload groups and classifiers allows you to assign queries to different resource classes and prioritize them, directly addressing resource class conflicts and reducing queuing. Option A is incorrect: scaling the pool to a higher DWU increases overall resources but does not specifically manage resource class contention; it may also incur additional cost without solving the root issue. Option C is incorrect: materialized views improve query performance by pre-aggregating data but do not affect concurrency or queuing. Option D is incorrect: result-set caching reduces repeated computation for identical queries but does not resolve queuing caused by resource class conflicts.

Answer

Scale the dedicated SQL pool to a higher DWU level

Answer

Create materialized views for the most common aggregations

Answer

Enable result-set caching for frequently run queries

Question 9

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is partitioned by date and ingested daily. You notice that the Delta table has many small files, causing slow read performance. Which strategy should you recommend to optimize the table for faster queries?

Accepted Answer

Run OPTIMIZE on the table to compact small files.. Running OPTIMIZE on a Delta Lake table compacts many small files into larger ones, reducing the number of files that need to be read during queries. This directly addresses the slow read performance caused by the small file problem, which is common in daily partitioned ingestion. OPTIMIZE uses bin-packing to merge files up to a target size (default 256 MB), improving scan efficiency without changing the data.

Answer

Run ZORDER BY on the date column.

Answer

Run VACUUM to delete old files.

Answer

Increase the number of partitions by adding a new partition column.

Question 10

You are designing a data processing solution using Azure Databricks. The solution must use Delta Lake for ACID transactions and must optimize storage costs by automatically compacting small files. Which feature should you enable?

Accepted Answer

Enable auto-optimize on the Delta table.. Auto-optimize (also called Optimized Writes / auto compaction) is a Delta Lake table property that automatically compacts small files during writes, eliminating the need for manual OPTIMIZE jobs. It combines with optimized writes to reduce the small-file problem at ingestion time, directly lowering storage and query costs. This is the native, built-in feature designed for the stated requirement.

Answer

Run OPTIMIZE command in a scheduled job.

Answer

Set retention duration for vacuum to 7 days.

Answer

Enable Z-order indexing on the Delta table.

Question 11

You need to design a near-real-time data processing solution that ingests IoT telemetry data from millions of devices. The data must be aggregated per minute and stored in Azure Cosmos DB for low-latency queries. Which Azure service combination should you use?

Accepted Answer

Azure Event Hubs -> Azure Stream Analytics -> Azure Cosmos DB. Azure Stream Analytics provides native, low-latency windowed aggregation (e.g., TumblingWindow for per-minute aggregates) directly on data ingested from Event Hubs, and it has a built-in output sink to Azure Cosmos DB. This combination meets the near-real-time requirement without needing an intermediate compute or storage layer, minimizing end-to-end latency.

Answer

Azure Event Hubs -> Azure HDInsight (Kafka) -> Azure Cosmos DB

Answer

Azure IoT Hub -> Azure Databricks (Structured Streaming) -> Azure Cosmos DB

Answer

Azure Event Hubs -> Azure Data Factory -> Azure Cosmos DB

Question 12

Drag and drop the steps to implement Slowly Changing Dimension (SCD) Type 2 in Azure Synapse Analytics dedicated SQL pool into the correct order.

Accepted Answer

Stage data, then identify changes, then expire old records, then insert new versions.. SCD Type 2: stage data, merge to find changes, expire old records, insert new versions, and update attributes (if needed).

Answer

Stage data, then insert new versions, then expire old records.

Answer

Identify changes, then stage data, then expire old records, then insert new versions.

Answer

Stage data, then expire old records, then identify changes, then insert new versions.

Question 13

Which THREE options are valid ways to transform data in Azure Synapse Analytics?

Accepted Answer

Use T-SQL scripts in a dedicated SQL pool.. T-SQL scripts are a native and primary method for transforming data within a dedicated SQL pool in Azure Synapse Analytics. You can use CREATE TABLE AS SELECT (CTAS), INSERT...SELECT, and other T-SQL statements to perform complex transformations like aggregations, joins, and data cleansing directly on the distributed data, leveraging the MPP (Massively Parallel Processing) engine for high performance.

Answer

Use Power Query online in Synapse pipelines.

Answer

Use Azure Machine Learning pipelines for data wrangling.

Question 14

Drag and drop the steps to set up Azure Data Lake Storage Gen2 hierarchical namespace for a data lake into the correct order.

Accepted Answer

Enable hierarchical namespace on storage account, create a container, create directories, set permissions, upload data.. The storage account must have hierarchical namespace enabled. Then create a container, directories, set permissions, and upload data.

Answer

Enable hierarchical namespace on storage account, create directories, create a container, set permissions, upload data.

Answer

Enable hierarchical namespace on storage account, create a container, set permissions, create directories, upload data.

Answer

Create a container, enable hierarchical namespace on storage account, create directories, set permissions, upload data.

Question 15

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

Accepted Answer

Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.. The 'Connection closed' error in a self-hosted integration runtime (SHIR) during large 50 GB transfers typically stems from resource exhaustion or unstable network connectivity on the SHIR host machine. When the SHIR runs out of memory or CPU, or when the network drops packets during long-running transfers, the TCP connection to the on-premises SQL Server is terminated mid-copy. Increasing memory/CPU and verifying network stability directly addresses the root cause of intermittent connection drops.

Answer

Increase the 'connection timeout' setting in the linked service to 30 minutes.

Answer

Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.

Answer

Disable fault tolerance in the copy activity to improve performance.