DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. You need to minimize data movement during query execution for joins between the fact table and smaller dimension tables. What should you do?
⚠ Common exam trap
The trap here is assuming that hash distribution on the primary key always aligns with join columns, which is often not the case in 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
✓
Distribute the fact table using hash distribution on the join column and replicate the dimension tables.
Hash distributing the fact table on the join column co-locates matching rows, and replicating small dimension tables places them on all compute nodes. This combination eliminates data movement during joins, which is critical for performance in dedicated SQL pools. The other options either use inappropriate distribution or do not fully minimize movement.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Distribute the fact table using hash distribution on the join column and replicate the dimension tables.
Why this is correct
Hash distributing the fact table on the join column ensures that rows with the same join key are co-located on the same distribution. Replicating the smaller dimension tables makes them available on all compute nodes, eliminating the need to shuffle dimension data during joins. This minimizes data movement and improves query performance for star-schema joins.
- ✗
Distribute the fact table using hash distribution on the primary key and replicate the dimension tables.
Why it's wrong here
Hash distribution on the primary key may not align with join columns, causing data movement during joins. Replicating dimension tables is good, but the fact table distribution should be on the join column, not necessarily the primary key. If the primary key is not the join key, this approach can still result in shuffling. The goal is to minimize data movement, so the distribution column should match the join column.
- ✗
Distribute the fact table using round-robin distribution and replicate the dimension tables.
Why it's wrong here
Round-robin distribution evenly spreads data but does not co-locate rows with the same join key. This forces data movement during joins, which is what you want to avoid. Replicating dimensions helps, but the fact table's round-robin distribution will still cause shuffling for joins on non-distribution columns.
- ✗
Distribute the fact table using hash distribution on the join column and distribute the dimension tables using round-robin distribution.
Why it's wrong here
While hash distributing the fact table on the join column is correct, round-robin distribution for dimension tables does not co-locate dimension rows with matching fact rows. This would still require data movement during joins. Replicating dimension tables is preferred when they are small, as it eliminates shuffling entirely.
Go deeper
Related to this question
About these practice questions
One of 509 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 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.