DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
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?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add retry logic in the Data Factory pipeline for the stored procedure activity
Adding retry logic in the Data Factory pipeline allows the pipeline to automatically retry the stored procedure activity when a deadlock error occurs. Deadlocks are transient and often resolve on retry, minimizing impact on performance without changing the database isolation level or transaction integrity. Option A is wrong because using NOLOCK hints can lead to dirty reads and data inconsistency, which is unacceptable for a critical reporting procedure. Option B is wrong because removing the transaction would break the atomicity of the multiple table updates, potentially leaving data in an inconsistent state. Option D is wrong because disabling Read Committed Snapshot (RCSI) would likely increase blocking and contention, potentially making deadlocks worse or causing other performance issues.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Change the stored procedure to use NOLOCK hints
Why it's wrong here
May cause dirty reads and not allowed in some environments
- ✗
Remove the transaction from the stored procedure
Why it's wrong here
Could lead to data inconsistency
- ✓
Add retry logic in the Data Factory pipeline for the stored procedure activity
Why this is correct
Retries handle transient deadlocks gracefully
- ✗
Disable the 'Read Committed Snapshot' isolation level
Why it's wrong here
Could increase blocking, not solve deadlocks
Visual reference
Go deeper
Related to this question
Learn chapter
Introduction to Azure Data Engineering
Key term
Data Transformation Pipelines
Data transformation pipelines are automated sequences of steps that take raw data from a source, clean and reshape it into a usable format, and then load it into a destination for analysis or storage.
Key term
Azure Data Factory
Azure Data Factory is a cloud-based data integration service that lets you create, schedule, and orchestrate data pipelines to move and transform data from various sources to destinations.
About these practice questions
This DP-203 question is part of Courseiva's 760-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.