DP-203 Design and implement data storage Practice Question
You manage an Azure Synapse Analytics dedicated SQL pool that stores a 4 TB fact table named FactSales. The table is currently distributed using ROUND_ROBIN and has a clustered columnstore index. Most analytical queries join FactSales to a much smaller dimension table DimProduct on ProductKey and then filter by DateKey. You need to redesign the physical storage to minimize data movement during these joins and improve query performance. What should you do?
⚠ Common exam trap
The trap here is assuming that any hash distribution improves performance, when the distribution key must match the join column to actually eliminate data movement.
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 of FactSales to HASH(ProductKey) and ensure DimProduct is replicated.
For large fact tables in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during joins. Replicating the smaller dimension table ensures its rows are present on every compute node, so the join can be performed locally. This design directly addresses the scenario's need to reduce data movement and improve query 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.
- ✗
Keep ROUND_ROBIN distribution and add a nonclustered index on ProductKey in FactSales.
Why it's wrong here
ROUND_ROBIN distribution evenly spreads rows without regard to join keys, so joins on ProductKey will always require shuffling data across nodes. Adding a nonclustered index on ProductKey may help point lookups but does not change the distribution, and in a columnstore-heavy analytical workload it adds overhead. Data movement remains the dominant cost.
- ✗
Change the distribution of FactSales to HASH(DateKey) and create a partition on ProductKey.
Why it's wrong here
Hash distributing on DateKey colocates rows by date, but the join is on ProductKey, so rows with the same ProductKey would still be spread across distributions, causing data movement. Partitioning on ProductKey is also ineffective because partitioning is designed for large date ranges or predictable slices, not for high-cardinality join keys. This approach does not solve the join data movement problem.
- ✓
Change the distribution of FactSales to HASH(ProductKey) and ensure DimProduct is replicated.
Why this is correct
Hash distributing the large fact table on the frequently joined column ProductKey colocates rows with the same key on the same compute node. Replicating the small dimension table makes its rows available on every node. This combination eliminates data movement during joins on ProductKey, which is the primary performance bottleneck described in the scenario.
- ✗
Recreate FactSales as a replicated table and create a hash distribution on DimProduct using ProductKey.
Why it's wrong here
Replicating a 4 TB fact table would duplicate the entire table to every compute node, consuming excessive storage and slowing down loads. Replication is intended for small dimension tables, not large fact tables. Additionally, changing the distribution of the dimension table alone does not align with the join key on the fact table, so data movement would remain high for FactSales joins.
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.