DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a fact table with 10 billion rows. Queries frequently join this fact table to a dimension table on a column that is not the distribution column of either table. You need to reduce data movement during these joins. Which two actions should you take? (Choose two.)
⚠ Common exam trap
The trap here is thinking that any distribution change or materialized view will automatically reduce data movement, but the key is to align distribution with join columns or replicate small 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-distribute the fact table on the join column.
To reduce data movement during joins in a dedicated SQL pool, you can either replicate small dimension tables so they are available locally on all nodes, or hash-distribute the large fact table on the join column to colocate matching rows. Both actions minimize shuffle. Replicating the fact table is impractical due to its size, and other options do not directly address data 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.
- ✓
Hash-distribute the fact table on the join column.
Why this is correct
Hash-distributing the fact table on the join column ensures that rows with the same join key are colocated on the same compute node. When joining to a dimension table that is also hash-distributed on the same column (or replicated), data movement is minimized or eliminated, reducing shuffle operations and improving query performance.
- ✗
Create a materialized view that pre-joins the fact and dimension tables.
Why it's wrong here
A materialized view can improve performance for specific queries by pre-computing joins, but it requires additional storage and maintenance, and it does not reduce data movement for ad-hoc joins. It also may not be automatically used by all queries, so it is not a direct solution for reducing shuffle during joins.
- ✗
Hash-distribute the fact table on a different column.
Why it's wrong here
Changing the distribution column of the fact table might align it with the join column, potentially reducing data movement for that specific join, but it could negatively impact other queries and requires a full table rebuild. It is not a guaranteed solution and may not be the best action for all joins.
- ✗
Use a replicated table for the fact table.
Why it's wrong here
Replicating a fact table with 10 billion rows is impractical because it would create a full copy on every compute node, consuming excessive storage and slowing down loads. Replication is designed for small tables, typically under 2 GB, so this is not a viable option for a large fact table.
- ✓
Replicate the dimension table.
Why this is correct
Replicating the dimension table ensures that a full copy of the table is available on every compute node. When joining to the fact table, no data movement is required for the dimension because each node has a local copy, significantly reducing shuffle operations and improving query performance. This is ideal for small dimension 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.