DP-203 Design and implement data storage Practice Question
Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source has a fact table with 500 million rows and several dimension tables. You need to choose the best distribution strategy for the fact table to minimize data movement during joins. Which distribution type should you use?
⚠ Common exam trap
Test-takers frequently confuse replicated distribution as a general performance booster, but they fail to recognize that replicating a large fact table is impractical and that hash distribution on the join key is the correct strategy to minimize data movement for large fact tables.
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 the foreign key column used in joins
Hash distribution on the foreign key column used in joins ensures that rows with the same join key are co-located on the same distribution node. This minimizes data movement because the join can be performed locally on each node without shuffling data across the compute nodes, which is critical for a 500-million-row fact table.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Hash distribution on the foreign key column used in joins
Why this is correct
Hash distribution on the join key colocates matching rows on the same compute node, so joins between the fact table and dimensions occur locally rather than shuffling 500 million rows across nodes. This directly satisfies the requirement to minimise data movement during joins.
- ✗
No distribution (single distribution)
Why it's wrong here
Single distribution consolidates the table onto one node, so the 500-million-row fact table cannot use parallel processing and joins force movement. It tempts for small tables, and would be correct only where a table is small enough to fit comfortably on one distribution.
- ✗
Replicated distribution
Why it's wrong here
Replicated distribution copies the full table to every compute node, which suits small dimension tables but not a 500-million-row fact table. It tempts because it eliminates data movement for joins, and would be correct for the smaller dimension tables being joined.
- ✗
Round-robin distribution
Why it's wrong here
Round-robin spreads rows evenly without regard to values, so joins on the fact table's key require shuffling data across nodes. It tempts as the default for staging tables, and would be correct for tables never joined, such as raw load targets.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 509 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.