DP-203 Develop data processing Practice Question
You are partitioning a large fact table in Azure Synapse Dedicated SQL Pool by date. The table is used for queries that filter on CustomerID and Date. You want to minimize data movement. Which distribution strategy should you use?
⚠ Common exam trap
Many exam-takers assume partitioning and distribution should be on the same column (Date) to optimize date-range queries, but this ignores that distribution on the join key (CustomerID) is what minimizes data movement for the most common query pattern involving both CustomerID and Date filters.
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
✓
Hash distribution on CustomerID
Hash distribution on CustomerID is correct because queries filtering on CustomerID and Date will benefit from collocated joins and aggregations when CustomerID is the distribution key. Since the table is large and partitioned by Date, hash distribution on CustomerID minimizes data movement by ensuring that rows with the same CustomerID reside on the same distribution node, allowing filters on Date to be applied locally within each partition.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Round-robin distribution
Why it's wrong here
Round-robin distributes randomly; queries joining on CustomerID would cause data movement.
- ✓
Hash distribution on CustomerID
Why this is correct
Hash distribution on CustomerID ensures collocation for joins on that key, reducing data movement.
- ✗
Replicate distribution
Why it's wrong here
Replicate distribution copies the entire table to each distribution node, which avoids data movement for joins but does not reduce movement for queries filtering on CustomerID and Date. The table is large, so replicating it wastes storage and fails to minimise data movement during partition elimination. It is tempting because replication eliminates shuffling in star-join scenarios with small dimension tables, where full copies are feasible.
- ✗
Hash distribution on Date
Why it's wrong here
Hash on Date would be less effective for joins on CustomerID.
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 760-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.