DP-203 Develop data processing Practice Question
You are writing a T-SQL query against a dedicated SQL pool in Azure Synapse Analytics. The query aggregates a fact table containing billions of rows by joining it to a small dimension table. You observe that the join produces a large amount of data movement and the query runs slowly. You need to reduce data movement for this recurring pattern. What should you do?
⚠ Common exam trap
The trap here is assuming that indexing or added memory changes how rows are distributed for a join.
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
✓
Replicate the small dimension table so a copy exists on every distribution.
In a dedicated SQL pool, join performance depends heavily on whether matching rows already reside on the same distribution. A replicated table places a complete copy of the small dimension on every distribution, letting the engine perform the join locally against the large fact table. This eliminates the shuffle or broadcast of the dimension and is the recommended pattern for recurring joins with small dimensions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the resource class of the user running the query.
Why it's wrong here
A higher resource class grants more memory and concurrency slots to the session, which can help memory-intensive operations. It does not alter table distribution or join strategy, so the underlying shuffle of rows between distributions would persist and the query would still be dominated by data movement.
- ✗
Change the fact table's distribution to ROUND_ROBIN.
Why it's wrong here
Round-robin distribution spreads rows evenly but provides no join key alignment. Joining a round-robin fact table to a dimension still requires moving data to co-locate matching keys, so data movement would not be reduced and could even become less predictable than the current design.
- ✓
Replicate the small dimension table so a copy exists on every distribution.
Why this is correct
A replicated table keeps a full copy on each distribution, so joins between a large distributed fact table and a small dimension can be completed locally without shuffling rows across the data movement service. For recurring joins against a genuinely small dimension, this removes the broadcast or shuffle step and is the standard way to cut data movement in a dedicated SQL pool.
- ✗
Add a columnstore index to the dimension table.
Why it's wrong here
Columnstore indexes improve scan and compression efficiency for analytical reads, but they do not change how rows are distributed across the sixty distributions. The join would still need to relocate rows to align keys, so the dominant cost of data movement in this scenario would remain.
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.