DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You are optimizing the performance of an Azure Synapse Analytics dedicated SQL pool. Which TWO actions can help reduce data movement during query execution?
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
✓
Use replicated tables for small dimension tables.
Options B and E are correct. Using replicated tables for small dimension tables avoids data movement during joins because the table is copied to all distributions. Distributing fact tables on the join key columns ensures that matching rows are co-located, reducing shuffling. Option A is incorrect: using hash distribution on a column not used in joins increases data movement because data is redistributed unnecessarily. Option C is incorrect: round-robin distribution distributes rows evenly but often requires data movement for joins. Option D is incorrect: increasing the resource class allocates more resources but does not directly reduce 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.
- ✗
Use hash distribution on a column that is not used in joins.
Why it's wrong here
This can cause unnecessary data shuffling.
- ✓
Use replicated tables for small dimension tables.
Why this is correct
Replicated tables copy data to all nodes, avoiding movement for joins.
- ✗
Use round-robin distribution for large fact tables.
Why it's wrong here
Round-robin randomizes data distribution, increasing data movement.
- ✗
Increase the resource class for the loading user.
Why it's wrong here
Resource class affects concurrency and memory, not data movement.
- ✓
Distribute fact tables on the join key columns.
Why this is correct
Hash distribution on join keys ensures data is co-located, reducing movement.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 760 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.