DP-203 Practice Test 43 — 15 Questions

Question 1

You are monitoring Azure Stream Analytics job performance. The job is falling behind in processing real-time data. You notice that the SU (Streaming Unit) utilization is consistently at 90% or higher. What is the most appropriate action to improve throughput?

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

Accepted Answer

Increase the number of Streaming Units (SUs). When SU utilization is consistently at 90% or higher, the job is resource-constrained, which leads to backpressure and falling behind. The most direct way to improve throughput is to increase the number of Streaming Units (SUs), as this adds more compute capacity. Option A (changing output to use a partition scheme) can improve performance in some cases but is not the immediate step and may not resolve high SU utilization. Option B (reducing window duration) can cause data loss or inaccurate results, and does not address the resource bottleneck. Option D (decreasing event ordering tolerance) also risks data loss and is not a throughput improvement measure.

Answer

Change the output to use a partition scheme

Answer

Reduce the window duration in the query

Answer

Decrease the event ordering tolerance

Question 2

Refer to the exhibit. An Azure Policy is defined to enforce network security on storage accounts. What does this policy do?

Accepted Answer

Denies storage accounts that allow public network access from all networks. The Azure Policy in the exhibit uses the 'Deny' effect with a condition that checks if the 'networkAcls.defaultAction' property is set to 'Allow'. When 'defaultAction' is 'Allow', the storage account permits traffic from all networks, including the internet. The policy denies such configurations to enforce network security by requiring that public network access be restricted.

Answer

Denies storage accounts that do not have any IP rules defined

Answer

Denies storage accounts that have firewall rules configured

Answer

Denies storage accounts that have public network access disabled

Question 3

A company is using Azure Data Factory to copy data from an on-premises SQL Server to Azure Blob Storage. The data must be encrypted in transit using TLS 1.2. The on-premises SQL Server is configured to support TLS 1.2. Which Data Factory property should be configured?

Accepted Answer

The typeProperties property in the linked service to include 'Encrypt=True' in the connection string. TLS 1.2 encryption for data in transit between Azure Data Factory and an on-premises SQL Server is enforced by adding 'Encrypt=True' and 'TrustServerCertificate=False' to the connection string within the typeProperties of the linked service. This ensures the SQL Server Native Client uses TLS 1.2 for the connection, aligning with the requirement.

Answer

The encryptedCredential property in the linked service

Answer

The connectVia property in the linked service

Answer

The integrationRuntime property in the dataset

Question 4

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

Accepted Answer

The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.. The WatermarkQuery activity retrieves the maximum LastModified value from the source. If the CopyData activity's source query filters for rows where LastModified equals that value (e.g., using a parameter reference), it will only copy rows with that exact timestamp. In an incremental load scenario, the correct filter should be LastModified greater than the previous watermark value, not equal to the current maximum. Since the current maximum is the highest timestamp, no rows will have a timestamp greater than it, resulting in zero rows copied. Option A is wrong because the dependency condition 'Succeeded' is appropriate and the pipeline succeeded. Option B is wrong because the WatermarkQuery activity succeeded (no failure indicated). Option D is wrong because the WatermarkQuery activity runs before the CopyData activity due to the dependency, so no ordering issue exists.

Answer

The dependency condition should be 'Completed' instead of 'Succeeded'.

Answer

The WatermarkQuery activity failed, causing the CopyData activity to be skipped.

Answer

The CopyData activity runs before the WatermarkQuery activity completes.

Question 5

You are designing a data transformation solution for a retail company. The company receives daily CSV files from 200 stores via SFTP. The files must be cleaned, validated, and aggregated before loading into Azure Synapse dedicated SQL pool. The solution must minimize administrative overhead and support easy monitoring. Which approach do you recommend?

Accepted Answer

Use Azure Data Factory with Mapping Data Flows to clean, validate, and aggregate the data, then load into Synapse SQL pool. Azure Data Factory (ADF) with Mapping Data Flows provides a fully managed, code-free ETL service that can read CSV files from SFTP, perform cleaning, validation, and aggregation at scale using Spark clusters, and load the results directly into Azure Synapse dedicated SQL pool via the PolyBase sink. This minimizes administrative overhead by eliminating infrastructure management and supports easy monitoring through ADF’s built-in integration with Azure Monitor and pipeline run views.

Answer

Use Azure Functions to process each file and write to Synapse via REST API

Answer

Use PolyBase external tables to load raw data and then use T-SQL stored procedures for transformation

Answer

Use Azure Databricks with Python notebooks to process the files and write to Synapse

Question 6

You are building a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hubs input with 10 partitions. The query performs a GROUP BY on a column that is not the partition key. To ensure consistency, which partitioning scheme should you use?

Accepted Answer

Use 'PartitionBy' with the GROUP BY column. When performing a GROUP BY on a column that is not the partition key, you must use the PARTITION BY clause in the query to ensure that all rows with the same grouping value are processed by the same Stream Analytics node. This guarantees consistency and correctness of the aggregation, as it avoids data being split across multiple nodes without proper alignment.

Answer

Use 'Passthrough' partitioning

Answer

Increase the number of SUs to handle skew

Answer

Use 'INTO' with a 'PARTITION BY' clause

Question 7

Your team is developing a data processing solution in Azure Synapse Analytics. You need to ensure that the solution can automatically scale compute resources based on workload demand for serverless SQL pools. Which feature should you configure?

Accepted Answer

Enable auto-resume and auto-pause on the serverless SQL pool endpoint. Serverless SQL pools in Azure Synapse Analytics automatically scale compute resources based on workload demand without requiring any configuration. None of the provided options enable this automatic scaling. Auto-resume and auto-pause only control the pool's active state, not its compute size. Dedicated SQL pool features like auto-scaling, cache sizing, and workload classification do not apply to serverless pools.

Answer

Set a cache size for the serverless SQL pool

Answer

Configure a dedicated SQL pool with auto-scaling

Answer

Use workload classification to assign resources

Question 8

You need to monitor the performance of an Azure Synapse Analytics dedicated SQL pool. Which DMV should you query to find queries that are currently running and their execution status?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests shows currently running requests in a dedicated SQL pool. Option A is wrong because sys.dm_pdw_nodes is a system view, not for queries. Option B is wrong because sys.dm_pdw_request_steps shows steps of completed or running requests, not high-level status. Option C is wrong because sys.dm_pdw_errors shows errors, not running queries.

Answer

sys.dm_pdw_nodes

Answer

sys.dm_pdw_request_steps

Answer

sys.dm_pdw_errors

Question 9

A company uses Azure Synapse Analytics dedicated SQL pool for data warehousing. They notice that queries against a large fact table are slow. The table is hash-distributed on ProductID, but many queries filter on OrderDate. What should the data engineer do to improve query performance?

Accepted Answer

Change the distribution to hash on OrderDate. Changing the distribution key to OrderDate aligns the physical data layout with the most common query filter predicate. In a dedicated SQL pool, hash distribution distributes rows across distributions based on the hash of the distribution column. When queries filter on OrderDate, a hash on OrderDate enables partition elimination and distribution-level pruning, reducing data movement and improving scan performance.

Answer

Change the distribution to round-robin

Answer

Replicate the table to all distributions

Answer

Create a columnstore index on OrderDate

Question 10

Which TWO actions should you take to secure data in Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Accepted Answer

Apply dynamic data masking (DDM) to obfuscate sensitive data.. The correct answers are D and E. Dynamic data masking (DDM) obfuscates sensitive data, and row-level security (RLS) restricts data access at the row level. Option A is incorrect because PolyBase is used for data loading, not security. Option B is incorrect because result-set caching improves performance but does not secure data. Option C is incorrect because workload classification governs resource allocation, not security.

Answer

Use PolyBase to load data from external sources.

Answer

Enable result-set caching for query performance.

Answer

Configure workload classification for resource governance.

Question 11

You are designing an Azure Stream Analytics job to process real-time IoT data from thousands of devices. The job must handle late-arriving events (up to 1 hour late) and out-of-order events (up to 5 minutes). Which two temporal policies should you configure?

Accepted Answer

Out of order tolerance window: 5 minutes; Late arrival tolerance window: 1 hour. Azure Stream Analytics uses two temporal policies to handle event timing: the late arrival tolerance window and the out-of-order tolerance window. The late arrival tolerance window defines how long the system waits for events that arrive after their timestamp. The out-of-order tolerance window specifies the maximum time difference allowed for events that arrive out of sequence. In this scenario, you need a late arrival tolerance of 1 hour and an out-of-order tolerance of 5 minutes. Option A directly configures these values correctly. Option C is incorrect because 'watermark delay' is not a configurable temporal policy in Azure Stream Analytics; it is a concept used in Spark Structured Streaming. Therefore, only Option A is correct.

Answer

Out of order tolerance window: 1 hour; Late arrival tolerance window: 5 minutes

Answer

Watermark delay: 1 hour; Out of order tolerance: 5 minutes

Answer

Use Event Hubs capture to handle late events; no additional configuration needed

Question 12

You have an Azure Synapse Analytics serverless SQL pool that queries data in Azure Data Lake Storage Gen2. You need to ensure that only users with specific Microsoft Entra ID groups can access the data through the serverless SQL pool. What should you configure?

Accepted Answer

Grant the Microsoft Entra ID group CONNECT permission on the serverless SQL pool and configure ACLs on the storage to allow read access for the group. Controlling access involves two layers: the serverless SQL pool and the underlying storage. Users must have both CONNECT permission on the SQL pool to query and appropriate ACLs on the storage to read the data. Granting the Microsoft Entra ID group CONNECT permission and configuring ACLs on the storage account to allow read access for the group ensures that only those users can access data through the serverless SQL pool. Option A is incorrect because the Storage Blob Data Reader role alone does not grant SQL-level permissions. Option C is incorrect because firewall rules are network-level and do not provide granular user access. Option D is incorrect because SAS tokens are not recommended for user-level access and bypass Entra ID permissions.

Answer

Grant the Microsoft Entra ID group the Storage Blob Data Reader role on the storage account

Answer

Configure a firewall rule to allow only the Microsoft Entra ID group IP ranges

Answer

Use a shared access signature (SAS) token with the SQL pool and distribute it to users

Question 13

You are designing a data pipeline to ingest streaming data from IoT devices into Azure Synapse Analytics. The data must be available for querying with minimal latency, but you also need to handle spikes in throughput without data loss. Which service should you use as the ingestion layer?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct choice because it is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from millions of IoT devices. It provides at-least-once delivery, supports partitioning for massive scale, and integrates natively with Azure Synapse Analytics via the Synapse Pipeline or Event Hubs Capture to handle throughput spikes without data loss.

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Answer

Azure IoT Hub

Question 14

You are designing a near real-time analytics solution for a retail company. The company has a transactional database in Azure SQL Database that records sales transactions. The data must be available in Azure Synapse Analytics dedicated SQL pool for reporting with less than 15 minutes of latency. The data volume is about 10 GB per day. You need to design the data ingestion pipeline. You also need to ensure that the pipeline can handle schema changes (e.g., new columns added to the source table) without manual intervention. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a change data capture (CDC) mapping data flow to read changes from SQL Database and write to Synapse dedicated SQL pool. Enable schema drift to handle new columns.. Azure Data Factory's mapping data flows support Change Data Capture (CDC) to incrementally load only changed rows from Azure SQL Database, meeting the <15-minute latency requirement for 10 GB/day. The 'Enable schema drift' option in mapping data flows automatically handles new columns added to the source table without manual intervention, which is essential for schema evolution.

Answer

Use Azure Databricks with Auto Loader to read from SQL Database and write to Synapse using Delta Lake.

Answer

Use Azure Data Share to share the SQL Database tables with Synapse and schedule snapshots every 15 minutes.

Answer

Use Azure Synapse Pipelines with a copy activity to perform a full load every 15 minutes.

Question 15

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You notice that the copy activity fails intermittently with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed'. The on-premises SQL Server is under heavy load during business hours. What is the most likely cause?

Accepted Answer

The SQL Server is experiencing resource contention or timeout due to heavy load.. The error 'SqlOperationFailed' on the source side indicates that the SQL Server itself is failing to complete the query or data extraction operation. Under heavy load, the SQL Server may experience resource contention (CPU, memory, I/O) or reach query timeout thresholds, causing the copy activity to fail intermittently. This is consistent with the described scenario of heavy load during business hours.

Answer

The Azure Blob Storage account is throttling requests.

Answer

The authentication method to SQL Server is incorrect.

Answer

The self-hosted integration runtime is not connected to the network.