DP-203 Practice Exam 57 — 20 Questions

Question 1

You are a data engineer at a manufacturing company. You need to process sensor data from IoT devices that arrive in real time. The data is sent to Azure Event Hubs. You need to aggregate the data over 5-minute windows and store the results in Azure Data Lake Storage Gen2 in Parquet format. The solution should minimize cost and use serverless components. Which solution should you use?

Accepted Answer

Use Azure Stream Analytics to create a query with a tumbling window of 5 minutes, and output the results to Azure Data Lake Storage Gen2 in Parquet format.. Option A is correct because Azure Stream Analytics is a serverless, cost-effective solution for real-time stream processing with windowed aggregations. It can output directly to ADLS Gen2 in Parquet. Option B is wrong because Azure Databricks with Structured Streaming requires a running cluster, which is not serverless and incurs cost. Option C is wrong because Azure Data Factory is not designed for real-time streaming. Option D is wrong because Azure Functions would require custom code and may not handle large throughput efficiently.

Answer

Use Azure Databricks with Structured Streaming to read from Event Hubs, aggregate with a sliding window, and write to ADLS Gen2 in Parquet.

Answer

Use Azure Data Factory with a tumbling window trigger to run a pipeline every 5 minutes that copies data from Event Hubs to ADLS Gen2.

Answer

Use Azure Functions with an Event Hubs trigger to aggregate data in memory and write to ADLS Gen2.

Question 2

You are developing a real-time data processing solution for a financial services company. The system ingests stock trade data from Azure Event Hubs at 50,000 events per second. Each event is a JSON object with fields: TradeID, Symbol, Price, Quantity, Timestamp. You need to calculate a 5-minute rolling average of the trade price per symbol and store the result in Azure Cosmos DB for low-latency queries. Additionally, you need to detect anomalies where the price deviates more than 10% from the rolling average within the same window, and send alerts to Azure Event Grid. You must minimize latency and ensure that the processing is stateful across multiple partitions. What should you do?

Accepted Answer

Create an Azure Stream Analytics job. Define input from Event Hubs. Use a Tumbling window of 5 minutes to compute average price per symbol. Add a custom function to compare each event's price to the average and output anomalies. Write to Cosmos DB via the Azure Cosmos DB output adapter and to Event Grid via the Event Grid output adapter.. Azure Stream Analytics can ingest from Event Hubs, perform windowed aggregations (e.g., Tumbling window for rolling average), detect anomalies using conditional logic, and output to both Cosmos DB and Event Grid. It handles partitioning automatically and is stateful. Option A is correct. Option B uses Azure Functions which are not ideal for high-throughput stateful stream processing. Option C uses Databricks Structured Streaming which is more complex to manage. Option D uses Synapse Pipelines which are batch-oriented.

Answer

Use Azure Functions with Event Hubs trigger. In each function invocation, compute the rolling average using a distributed cache (Redis) and detect anomalies. Write to Cosmos DB and Event Grid via output bindings.

Answer

Use Azure Synapse Pipelines with a Data Flow. Set up a streaming Data Flow from Event Hubs, compute rolling average using window functions, and sink to Cosmos DB and Event Grid.

Answer

Use Azure Databricks with Structured Streaming. Read from Event Hubs using Kafka API. Perform windowed aggregations and anomaly detection using Spark SQL. Write to Cosmos DB via the Azure Cosmos DB Spark connector and to Event Grid via HTTP sink.

Question 3

You are a data engineer for a global retail company. The company has a hybrid architecture with on-premises SQL Server databases and Azure Synapse Analytics. You need to design a data processing solution that ingests incremental changes from the on-premises SQL Server database (source) into Azure Synapse Analytics (sink) with low latency (under 15 minutes) and high reliability. The source database is 5 TB and experiences high transaction volume during business hours. The solution must minimize impact on the source system and handle schema changes automatically. You have the following options:

Option A: Use Azure Data Factory with a copy activity that uses a watermark column to query incremental changes every 10 minutes. The copy activity writes directly to the Synapse table using PolyBase.

Option B: Use Azure Data Factory with a mapping data flow that reads from the source using a SQL query with a watermark, performs transformations, and writes to Synapse using staging via Blob Storage and PolyBase.

Option C: Use SQL Server Integration Services (SSIS) running on Azure-SSIS Integration Runtime to extract data using change data capture (CDC) and load into Synapse.

Option D: Use Azure Databricks with Auto Loader to ingest files from a staging area that is populated by a separate log-shipping process from the source.

Which option should you choose?

Accepted Answer

Option B. Option B is correct because it handles incremental loads with low latency, uses PolyBase for efficient loading, and mapping data flow allows for schema drift handling and transformations without impacting source. Option A lacks schema drift handling. Option C requires SSIS packages and may have higher latency. Option D requires additional log-shipping, increasing complexity and latency.

Answer

Option C

Answer

Option A

Answer

Option D

Question 4

You are a data engineer at a retail company. You need to develop a data processing solution in Azure Synapse Analytics that reads sales transactions from Parquet files stored in Azure Data Lake Storage Gen2, transforms the data by aggregating daily sales per store, and writes the results to a dedicated SQL pool table for reporting. The transformation logic must be reusable and maintained in a source control system. You want to minimize administrative overhead and leverage serverless resources where possible. Which approach should you recommend?

Accepted Answer

Use a serverless SQL pool to query the Parquet files via OPENROWSET, then use CETAS to write the aggregated results to the dedicated SQL pool using an external table.. Option C is correct because a serverless SQL pool can query Parquet files directly, and the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement can transform and store results in a dedicated SQL pool table using PolyBase. This approach uses serverless resources for transformation and avoids managing Spark pools. Option A is wrong because dedicated SQL pool cannot directly read Parquet without PolyBase or external tables. Option B is wrong because Azure Data Factory with Mapping Data Flows runs on Spark clusters, incurring more overhead. Option D is wrong because using a notebook in a Spark pool requires provisioning a Spark pool, which adds administrative overhead.

Answer

Use Azure Data Factory with Mapping Data Flows to read Parquet files, perform aggregations, and write to the dedicated SQL pool.

Answer

Create an Azure Synapse Spark notebook that reads Parquet files, performs aggregation using PySpark, and writes the results to the dedicated SQL pool using the Spark Synapse connector.

Answer

Use PolyBase in a dedicated SQL pool to create external tables over the Parquet files, then use INSERT...SELECT to load aggregated data into the target table.

Question 5

You are monitoring an Azure Data Factory pipeline that runs hourly. The pipeline executes a stored procedure in an Azure SQL Database. Recently, you have observed that the pipeline occasionally fails with a 'Deadlock' error when the stored procedure runs. The Azure SQL Database is configured with the 'Read Committed Snapshot' isolation level enabled. You need to resolve the deadlock issue with minimal impact on performance. The stored procedure updates multiple tables in a single transaction and is critical for reporting. What should you do?

Accepted Answer

Add retry logic in the Data Factory pipeline for the stored procedure activity. Option A is correct because implementing retry logic in the pipeline will handle transient deadlock errors by re-executing the activity. Option B is wrong because disabling Read Committed Snapshot might reduce concurrency and increase blocking. Option C is wrong because it removes the transaction, risking data inconsistency. Option D is wrong because it does not address the deadlock directly and may not be allowed.

Answer

Change the stored procedure to use NOLOCK hints

Answer

Remove the transaction from the stored procedure

Answer

Disable the 'Read Committed Snapshot' isolation level

Question 6

Your organization uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are taking longer than expected. You need to identify which queries are consuming the most resources and optimize them. What should you do first?

Accepted Answer

Query the sys.dm_exec_requests DMV to view running queries and their resource usage.. Option A is correct because DMVs like sys.dm_exec_requests provide real-time resource consumption for serverless SQL pool. Option B is wrong because query hints may help but you need to identify problematic queries first. Option C is wrong because diagnostic settings send logs to Log Analytics, but DMVs are immediate. Option D is wrong because statistics are already maintained by the serverless pool.

Answer

Use query hints to optimize execution plans.

Answer

Enable diagnostic settings and send query logs to Log Analytics.

Answer

Create statistics on all columns used in queries.

Question 7

You are a data engineer for a healthcare company. You have a production Azure Synapse Analytics dedicated SQL pool (DW500c) that hosts patient data. The pool is used for both ETL and reporting. You need to ensure that reporting queries always get resources even during heavy ETL loads. You also need to monitor query performance and set up alerts when certain queries exceed a threshold. You have configured workload management using workload groups and classifiers. However, reporting queries are still waiting for resources when ETL is running. You check the sys.dm_pdw_exec_requests DMV and see that ETL queries are using the largest resource class. You need to modify the configuration to guarantee resources for reporting. What should you do?

Accepted Answer

Create a new workload group for reporting with min_percentage_resource set to 30%. Workload isolation is achieved by creating separate workload groups with minimum resource allocation. By setting minimum resources for the reporting workload group, it ensures a baseline of resources even during heavy ETL. Option A (increase DWU) is costly and may not isolate. Option B (importance) helps but without min resources, high concurrency can still starve. Option D (change classifier) does not guarantee resources.

Answer

Set importance to HIGH for the reporting workload group

Answer

Increase the DWU setting to DW1000c

Answer

Change the classifier for reporting queries to use the same workload group as ETL but with a different resource class

Question 8

You are a data engineer for a global e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. The environment includes a large fact table 'Sales' distributed by hash on 'CustomerID', and dimension tables 'Customer' (hash-distributed on 'CustomerID') and 'Product' (replicated). Recently, queries that join Sales and Customer are performing poorly. You run a query to check data skew on the Sales table and find that one distribution has 40% more rows than the average. Additionally, the Customer table has high data movement during joins. You need to optimize the performance of these joins. What should you do?

Accepted Answer

Change the distribution of the Customer table to replicated.. Option B is correct because changing the Customer table to replicated distribution eliminates data movement during joins. Option A is wrong because changing the distribution key of Sales to a different column may not solve skew if the key is not the join column. Option C is wrong because round-robin is not suitable for fact tables in star joins. Option D is wrong because increasing DWU may alleviate symptoms but does not fix the root cause of data movement.

Answer

Increase the data warehouse performance level (DWU) to allocate more resources.

Answer

Change the distribution of the Sales table to round-robin.

Answer

Change the distribution key of the Sales table to 'ProductID' to align with the Product table.

Question 9

You are a data engineer at a financial services company. Your Azure Synapse Analytics dedicated SQL pool contains a fact table named 'Transactions' with 10 billion rows. The table is hash-distributed on 'AccountID' and partitioned by month. You notice that queries filtering on 'TransactionDate' (a date column) are performing slowly despite partition elimination. You also observe that the 'Transactions' table is frequently joined with a 'DimAccount' dimension table on 'AccountID'. You need to optimize query performance for the most common workload: monthly reports that aggregate transaction amounts by account for the last 12 months. Additionally, you need to ensure that the solution minimizes maintenance overhead. What should you do?

Accepted Answer

Create a clustered columnstore index on the table. Option C is correct because implementing a columnstore index on the fact table will compress data and improve scan performance for aggregations. Option A is wrong because hash distribution on TransactionDate would cause data skew (many rows per date) and is not suitable for high-cardinality columns. Option B is wrong because round-robin distribution would eliminate collocation benefits for joins with DimAccount. Option D is wrong because table replication is for small dimension tables, not large fact tables.

Answer

Redistribute the table on TransactionDate using hash distribution

Answer

Change distribution to round-robin to evenly distribute data

Answer

Use table replication for the Transactions table

Question 10

You are designing a data pipeline in Azure Data Factory that reads data from an Azure SQL Database and writes to Azure Synapse Analytics dedicated SQL pool. The pipeline will run hourly and process incremental changes. You need to ensure that the pipeline handles retries for transient failures and logs all activities for audit purposes. What should you configure?

Accepted Answer

Enable pipeline logging to an Azure Storage account and set retry policy on copy activity.. Option B is correct because enabling pipeline logging to a storage account captures all activity, and retry policy in activities handles transient failures. Option A is wrong because Azure Monitor can monitor but does not log pipeline activities. Option C is wrong because Azure SQL Database audit is for database access, not pipeline. Option D is wrong because event-based trigger does not provide logging or retry.

Answer

Enable auditing on the Azure SQL Database and Azure Synapse SQL pool.

Answer

Configure a tumbling window trigger to rerun the pipeline on failure.

Answer

Use an Azure Monitor alert to notify on failures.

Question 11

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from IoT devices and store it in a dedicated SQL pool for reporting. The data volume is high (millions of events per hour), and you need to optimize for both ingestion speed and query performance. You also need to ensure that the data can be partitioned by date for efficient maintenance. Which architecture should you recommend?

Accepted Answer

Ingest data to Azure Data Lake Storage Gen2 in Delta format, then use PolyBase to load into a dedicated SQL pool partitioned by date.. Option B is correct: Stream data to Azure Data Lake Storage (ADLS) in Delta format, then use PolyBase to load into Synapse dedicated SQL pool. Delta format provides fast reads and optimizations. Option A is incorrect: Direct streaming to SQL pool is not efficient for high volumes. Option C is incorrect: Azure SQL Database is not designed for large-scale analytics. Option D is incorrect: Event Hubs Capture stores data in Avro, which is less performant for queries than Delta.

Answer

Use Azure Stream Analytics to write directly to a dedicated SQL pool with a time-based window.

Answer

Store data in Azure SQL Database with elastic scaling and use linked server queries.

Answer

Use Event Hubs Capture to store data in Avro files in Blob Storage and then query with external tables.

Question 12

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a security strategy that meets the following requirements: 1) All data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. 2) Access to the storage account must be restricted to specific virtual networks. 3) Users must authenticate using Microsoft Entra ID and be granted read-only access to the 'landing' container. You have configured the storage account with Azure Storage Service Encryption (SSE) using customer-managed keys. You have also configured firewall rules to allow access only from the required virtual network. However, users cannot access the 'landing' container even though they have the Storage Blob Data Reader role. What is the most likely issue?

Accepted Answer

The Key Vault firewall is blocking access from the storage account. When using SSE with customer-managed keys, the storage account must have the 'Allow trusted Microsoft services to access this storage account' setting enabled, or the Key Vault must allow access from the storage account. Option A is wrong because RBAC is correctly assigned. Option B is wrong because firewall is set. Option D is wrong because they have read role.

Answer

The users have not been granted access to the Key Vault

Answer

The users do not have the Storage Blob Data Reader role assigned at the container scope

Answer

The firewall is blocking the users' IP addresses even though they are in the virtual network

Question 13

You are a data engineer for a large e-commerce company. You have an Azure Synapse Analytics dedicated SQL pool that stores transactional data. The pool is currently at DWU1000c. You have a critical dashboard that runs a complex query every 5 minutes. The query scans a large fact table partitioned by date. The query performance is degrading over time as data accumulates. You need to improve performance without increasing DWUs or changing the dashboard query. You also need to minimize data movement overhead. You have the following options:

A. Create a columnstore index on the fact table with a partition alignment.
B. Create a materialized view that aggregates the data at the partition level.
C. Implement result-set caching and set the cache to expire every 5 minutes.
D. Redistribute the fact table using hash distribution on the date column.

Which option should you choose?

Accepted Answer

Implement result-set caching and set the cache to expire every 5 minutes.. Option C is correct because result-set caching stores the exact query results and can serve the dashboard query instantly if the underlying data has not changed. Since the query runs every 5 minutes, setting the cache expiration to 5 minutes ensures fresh data. Option A is wrong because the table likely already has a columnstore index (default in Synapse). Option B is wrong because materialized views require maintenance and may not match the exact query. Option D is wrong because hash distribution on date can cause data skew and does not reduce scan overhead as effectively as caching.

Answer

Redistribute the fact table using hash distribution on the date column.

Answer

Create a materialized view that aggregates the data at the partition level.

Answer

Create a columnstore index on the fact table with a partition alignment.

Question 14

Your company runs an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage daily. Recently, the pipeline has been failing with the error: 'Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation, Error details: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).' The FTP server administrator confirms that the file exists and the credentials are correct. You need to resolve the issue with minimal administrative effort. What should you do?

Accepted Answer

Check the file path and correct the case sensitivity in the dataset. The error indicates the file is not found or access denied. The most common cause is that the file path is case-sensitive on the FTP server. Verify the correct path with case sensitivity. Option A is wrong because credentials are confirmed correct. Option B is wrong because the file exists. Option D is wrong because changing the connector may not help if path is wrong.

Answer

Use an SFTP connector instead of FTP

Answer

Reset the FTP server credentials in the linked service

Answer

Ask the FTP administrator to re-upload the file

Question 15

You are configuring security for an Azure Synapse Analytics workspace that uses a serverless SQL pool. The workspace is connected to Azure Data Lake Storage Gen2 via a managed identity. You need to ensure that only the Synapse workspace can access the storage account, and no other Azure service or user can access it directly. The storage account should not be accessible from the public internet. What should you do?

Accepted Answer

Configure a private endpoint for the storage account in the same virtual network as the Synapse workspace, and disable public network access.. Option A is correct because a private endpoint for the storage account, combined with disabling public network access, ensures only the Synapse workspace can access it via the managed identity. Option B is wrong because firewall rules alone do not restrict access to a specific service. Option C is wrong because allowing trusted Microsoft services is broader than just Synapse. Option D is wrong because managed identity alone does not restrict network access.

Answer

Configure the storage account firewall to allow only the Synapse workspace's public IP address.

Answer

Enable 'Allow trusted Microsoft services to access this storage account' on the firewall.

Answer

Use Azure RBAC to assign the Storage Blob Data Contributor role to the Synapse workspace managed identity.

Question 16

You are a data engineer at Northwind Traders. You have an Azure Synapse Analytics workspace with dedicated SQL pools. You need to monitor query performance to identify slow-running queries and understand resource consumption. The solution must provide historical data for the last 30 days and allow alerting when queries exceed a certain duration. You also need to export the data to a Log Analytics workspace for correlation with other metrics. What should you use?

Accepted Answer

Enable Diagnostic Settings to stream SQL pool metrics and logs to a Log Analytics workspace, then create alert rules and use KQL queries for historical analysis.. Option B is correct: Azure Monitor diagnostic settings can send Synapse SQL pool logs and metrics to Log Analytics. Then you can create alerts and run queries over historical data. Option A is incorrect: Dynamic Management Views (DMVs) only provide current state, not historical 30-day data. Option C is incorrect: Query Store retains data for a limited time and doesn't integrate natively with Log Analytics. Option D is incorrect: Azure Storage Analytics is for storage accounts, not Synapse SQL.

Answer

Enable Diagnostic Settings for the dedicated SQL pool to send logs to Azure Storage, and query DMVs for historical data.

Answer

Use Azure Storage Analytics to analyze logs from the storage account backing the SQL pool.

Answer

Configure SQL Server Query Store and export data to Azure Blob Storage using elastic query.

Question 17

You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?

Accepted Answer

Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.. Option B is correct because ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS option rebuilds columnstore segments online without blocking. Option A is wrong because REBUILD is offline and blocks writes. Option C is wrong because dropping and recreating the index is offline. Option D is wrong because reclustering reorganizes data within a distribution but does not fix columnstore quality.

Answer

Drop and recreate the clustered columnstore index.

Answer

Re-cluster the table using a different distribution key.

Answer

Run ALTER INDEX REBUILD on the clustered columnstore index.

Question 18

Your company uses Azure Data Factory to orchestrate data pipelines that ingest data from on-premises SQL Server to Azure Data Lake Storage Gen2. The network team has implemented a firewall that only allows outbound traffic on port 443. The on-premises SQL Server is not accessible via public endpoint. You need to configure a secure connection that complies with the firewall rules and uses managed identity for authentication. What should you use?

Accepted Answer

Install a Self-hosted Integration Runtime on an on-premises VM, register it with Azure Data Factory using managed identity, and configure the pipeline to use this IR for the SQL Server connection.. Option B is correct: A self-hosted integration runtime (IR) installed on-premises with managed identity can connect to SQL Server using private network, then communicate with Azure Data Factory over port 443. Option A is incorrect: Azure IR cannot connect to on-premises SQL Server without a public endpoint. Option C is incorrect: Azure VPN Gateway requires additional network configuration and doesn't use managed identity. Option D is incorrect: ExpressRoute is a private connection but doesn't address authentication.

Answer

Use Azure ExpressRoute to connect the on-premises network to Azure, then use an Azure Integration Runtime with a VNet injection.

Answer

Set up a point-to-site VPN from Azure to on-premises and use an Azure Integration Runtime with a VNet integration.

Answer

Use an Azure Integration Runtime with a public endpoint and configure a firewall rule to allow the Azure IR IP addresses.

Question 19

Match each Azure storage service to its primary use case.

Answer

Big data analytics

Answer

Globally distributed, low-latency applications

Answer

Relational OLTP

Answer

NoSQL key-value store

Answer

Azure Data Lake Storage Gen2

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 20

A retail company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The data is loaded nightly from Azure Data Lake Storage Gen2 using PolyBase. Recently, the load process started failing with the error 'External table 'sales' is not accessible because the location does not exist or is used by another process.' You verify that the storage account, container, and file path are correct. The file is a CSV file named 'sales_20250301.csv' and it exists. Other files in the same container load successfully. What is the most likely cause of the error?

Accepted Answer

The file is being written to or is locked by another process during the PolyBase read.. The error 'location does not exist or is used by another process' specifically indicates that the file is locked by another process. In Azure Data Lake Storage Gen2, when a file is being written or modified, it can be locked by the writing process (e.g., a data ingestion pipeline or another ETL job). PolyBase attempts to read the file while it is still being written, causing the error. Option D correctly identifies this concurrency issue.

Answer

The network connectivity between Synapse and the storage account is intermittent.

Answer

The CSV file has an incorrect number of columns or contains a header row that mismatches the schema.

Answer

The storage account key used in the external data source has expired.