DP-203 Develop data processing Practice Question
You have an Azure Synapse Analytics dedicated SQL pool. A nightly ELT process loads a 500 GB staging table and then applies transformations using a stored procedure. The procedure performs many single-row updates against a large fact table, and the load now exceeds its window. You need to reduce the duration of the transformation step. What should you do?
⚠ Common exam trap
The trap here is tuning memory or indexes when the real cost is the row-by-row update pattern that dedicated SQL pool handles poorly.
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
✓
Convert the single-row updates to a CTAS-based pattern that creates a new table from a SELECT joining staging and fact data, then renames it.
Dedicated SQL pool performs best with set-based, minimally logged bulk operations. Replacing many single-row updates with a CREATE TABLE AS SELECT that joins staging to the fact table, followed by a RENAME OBJECT to swap tables, eliminates row-level logging and locking, cutting the transformation step to a parallel bulk write.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable result set caching on the stored procedure so repeated executions reuse computed results.
Why it's wrong here
Result set caching helps repeated identical read queries return faster, but the procedure mutates the fact table, and cached results are invalidated by underlying data changes. It also does not apply to write operations. This feature cannot reduce the cost of applying updates and would provide no benefit for a nightly changing dataset.
- ✗
Increase the resource class of the user running the stored procedure to grant more memory per query.
Why it's wrong here
Resource classes allocate memory and concurrency slots to a query, but they cannot make a row-by-row update pattern efficient. More memory does not change the fundamental per-statement logging and locking cost of thousands of single-row updates. The transformation design, not resource allocation, is the limiting factor here.
- ✓
Convert the single-row updates to a CTAS-based pattern that creates a new table from a SELECT joining staging and fact data, then renames it.
Why this is correct
Dedicated SQL pool is optimized for bulk, set-based operations, and CREATE TABLE AS SELECT writes results in parallel with minimal logging. Building a new fact table from a join of staging and existing fact data avoids the row-by-row overhead of updates, which are slow and log-heavy. Renaming via RENAME OBJECT swaps the new table into place atomically.
- ✗
Add a clustered columnstore index to the staging table to accelerate the updates.
Why it's wrong here
The staging table is read, not updated, so its index choice does not speed the row-level updates against the fact table. Clustered columnstore is already the default and optimal for the bulk read. The bottleneck is the update pattern on the fact table, so tuning the staging table's index leaves the real problem untouched.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 509 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
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.