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 on OrderDate enables partition elimination but does not align data across distributions for joins. The primary issue is distribution key mismatch, not partitioning.
- ✗
Statistics on the join columns are outdated
Why it's wrong here
Outdated statistics can degrade performance, but the most likely cause is the need for data movement due to misaligned join columns. Statistics alone would not explain the severity.
- ✗
The table should use round-robin distribution instead
Why it's wrong here
Round-robin distribution distributes rows without any key, requiring data movement for any join. Hash distribution is typically better for large fact tables; round-robin would not resolve the issue.
- ✓
The join columns are not aligned; data must be shuffled across distributions
Why this is correct
Correct. Since the join is on OrderDate but the table is hash-distributed on CustomerID, the join columns are not aligned across distributions. This forces data shuffling, which is the primary cause of slow performance.
Go deeper
Related to this question
About these practice questions
One of 760 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.