DP-203 Develop data processing Practice Question
Your organization uses Azure Synapse Analytics dedicated SQL pool to store sales data. You need to design a data loading process for a nightly batch that inserts new rows and updates existing rows based on the business key. The table has a clustered columnstore index. Which approach minimizes table fragmentation?
⚠ Common exam trap
DP-203 often tests the misconception that MERGE is the 'best practice' upsert for Synapse dedicated SQL pools, when in fact MERGE and row-level DML on clustered columnstore indexes cause fragmentation that CTAS + partition switching avoids.
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
✓
Create a staging table, load data, then use CTAS and partition switching to replace the target partition.
CTAS with partition switching is the recommended pattern for dedicated SQL pools because it writes new data into a new distribution/partition and swaps it in via ALTER TABLE ... SWITCH, avoiding row-by-row UPDATE/DELETE operations that create fragmentation and delta-store bloat on clustered columnstore indexes. Because the target partition is replaced atomically, the clustered columnstore index remains well-compressed with minimal deleted-row overhead.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use UPDATE for existing rows and INSERT for new rows.
Why it's wrong here
Row-by-row UPDATE and INSERT on a clustered columnstore index creates many small rowgroups, and each UPDATE marks rows deleted and inserts new ones, fragmenting the index. This suits small dimension tables, not high-volume nightly fact loads where CTAS or partition switching rebuilds rowgroups cleanly.
- ✗
Use DELETE and INSERT statements in a single transaction.
Why it's wrong here
DELETE followed by INSERT marks deleted rows and writes new rowgroups, leaving deleted-row overhead and fragmentation in the clustered columnstore index until rebuild. This pattern suits small correction batches, not large nightly upserts where partition switching replaces whole rowgroups atomically.
- ✗
Use a MERGE statement to perform upserts.
Why it's wrong here
MERGE performs row-level updates and inserts against the clustered columnstore index, generating many small rowgroups and deleted rows that fragment it. MERGE suits modest dimension upserts, not high-volume nightly fact loads where CTAS into a staging table then partition switching avoids fragmentation.
- ✓
Create a staging table, load data, then use CTAS and partition switching to replace the target partition.
Why this is correct
Staging plus CTAS and partition switching writes new columnstore rowgroups atomically, avoiding the small-rowgroup fragmentation that row-by-row inserts or updates cause in a clustered columnstore index. This satisfies the minimise-fragmentation requirement for the nightly batch.
Go deeper
Related to this question
About these practice questions
One of 509 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.