DP-203 Design and implement data storage Practice Question
You are designing a storage solution in Azure Synapse Analytics for a financial services company. The company ingests trade data into a dedicated SQL pool. The data is partitioned by trade date and queried primarily by date ranges. To improve query performance and reduce data movement, you need to choose an appropriate distribution type for the fact table. The table is large (over 2 billion rows) and frequently joined with a smaller dimension table on a non-distributed key. Which distribution type should you use?
⚠ Common exam trap
The trap here is assuming that hash-distributing on the most frequently filtered column (like date) is best, but the primary goal is to minimize data movement during joins, not to optimize for filters alone.
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-distributed on the join key column
For a large fact table frequently joined on a specific key, hash-distributing on that join key ensures that matching rows from both tables reside on the same distribution, enabling collocated joins and avoiding costly data movement. This distribution strategy is recommended when the join key has high cardinality and is used in most queries.
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-distributed on the join key column
Why this is correct
Hash-distributing on the join key column co-locates rows with the same key on the same distribution, enabling collocated joins that eliminate data movement. This is optimal for large fact tables frequently joined on that key. It also distributes data evenly if the key has high cardinality, improving query performance for joins and aggregations.
- ✗
Hash-distributed on the trade date column
Why it's wrong here
Hash-distributing on trade date would evenly spread rows across distributions, but queries filtering on date ranges would still scan all distributions, and joins on a different key would require data movement. This does not optimize for the join pattern, and date columns often have skew, leading to uneven data distribution and degraded performance.
- ✗
Round-robin distribution
Why it's wrong here
Round-robin distribution evenly spreads rows without requiring a distribution key, but it provides no co-location for joins. Since the fact table is frequently joined with a dimension table on a non-distributed key, round-robin would force data movement during joins, increasing query cost and latency. It is better suited for staging tables or tables without frequent joins.
- ✗
Replicated distribution
Why it's wrong here
Replicated distribution copies the entire table to every compute node, which is impractical for a table with over 2 billion rows. This would consume excessive storage and slow down data loading. Replicated tables are intended for small dimension tables (typically less than 2 GB compressed) to avoid data movement during joins, not for large fact tables.
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 509-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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
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.