DP-203 Design and implement data storage Practice Question
A company uses Azure Synapse Analytics dedicated SQL pool for data warehousing. They notice that queries against a large fact table are slow. The table is hash-distributed on ProductID, but many queries filter on OrderDate. What should the data engineer do to improve query performance?
⚠ Common exam trap
Many exam-takers confuse indexing (columnstore) with distribution strategy, assuming a non-clustered index on the filter column is sufficient, when in fact the distribution key must match the most frequent filter predicate to avoid full distribution scans.
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
✓
Change the distribution to hash on OrderDate
Changing the distribution key to OrderDate aligns the physical data layout with the most common query filter predicate. In a dedicated SQL pool, hash distribution distributes rows across distributions based on the hash of the distribution column. When queries filter on OrderDate, a hash on OrderDate enables partition elimination and distribution-level pruning, reducing data movement and improving scan performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Change the distribution to round-robin
Why it's wrong here
Does not improve filter performance.
- ✗
Replicate the table to all distributions
Why it's wrong here
Impractical for large tables.
- ✗
Create a columnstore index on OrderDate
Why it's wrong here
Does not address distribution; still incurs data movement.
- ✓
Change the distribution to hash on OrderDate
Why this is correct
Aligns distribution with filter column, minimizing data movement.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 760 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 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.