DP-203 Design and implement data storage Practice Question
You have an Azure Synapse Analytics dedicated SQL pool with a table that uses hash distribution on CustomerID. You notice that queries joining this table with another table on OrderDate are slow. What is the most likely cause?
⚠ Common exam trap
It's easy for candidates to confuse partitioning with distribution, thinking that partitioning on the join column solves the data movement issue, when in fact distribution alignment is the critical factor for collocated joins in a distributed MPP system.
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
✓
The join columns are not aligned; data must be shuffled across distributions
D is correct because in Azure Synapse Analytics dedicated SQL pools, hash distribution distributes rows across distributions based on a hash of the distribution key (CustomerID). When joining on OrderDate, which is not the distribution key, the join columns are not aligned across distributions. This forces data movement (shuffling) where rows from one or both tables must be redistributed to match the join key, causing significant performance degradation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The table is not partitioned by OrderDate
Why it's wrong here
Partitioning by OrderDate speeds partition elimination on date filters, not hash-to-hash join cost; the join mismatch between CustomerID distribution and OrderDate join keys forces data movement. Partitioning would be correct when queries filter on OrderDate ranges, enabling partition pruning rather than fixing distribution skew during joins.
- ✗
Statistics on the join columns are outdated
Why it's wrong here
Outdated statistics can cause poor join plans, but the stem's hash distribution on CustomerID joined on OrderDate guarantees data movement regardless of statistics accuracy. Statistics maintenance is the right fix when the distribution column matches the join column and cardinality estimates drift, not when the join key differs from the distribution key.
- ✗
The table should use round-robin distribution instead
Why it's wrong here
Round-robin distribution spreads rows evenly but eliminates co-location entirely, so joins on OrderDate still require shuffling and lose the benefit of any distribution alignment. Round-robin suits staging or tables never joined on a common key, not a fact table repeatedly joined on a specific column.
- ✓
The join columns are not aligned; data must be shuffled across distributions
Why this is correct
Hash distribution places rows by CustomerID, so joining on OrderDate requires moving rows between distributions before the join completes. This shuffle, caused by misaligned join columns, is the most likely source of the slow query performance.
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 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.