DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected due to excessive data movement operations. You need to minimize data movement without changing the distribution columns. Which table design approach should you recommend?
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
Replicated tables are recommended for small dimension tables because they are copied to all compute nodes, avoiding data movement during joins. This reduces excessive data movement without changing distribution columns. Option B is incorrect because round-robin distribution distributes data evenly but does not reduce data movement for joins; it is typically used for staging tables. Option C is incorrect because using hash distribution for all tables can lead to data movement when joining on different columns, and it is not a one-size-fits-all solution. Option D is incorrect because partitioning alone does not reduce data movement; it is used for data management and pruning, not for minimizing shuffle operations.
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 replicated tables for small dimension tables
Why this is correct
Replicated tables store a full copy on each node, eliminating data shuffling for joins.
- ✗
Use round-robin distribution for dimension tables
Why it's wrong here
Round-robin distributes rows evenly but causes data movement during joins.
- ✗
Use hash distribution for all tables
Why it's wrong here
Hash distribution is good for large tables but does not eliminate data movement for joins.
- ✗
Use partitioning on join columns
Why it's wrong here
Partitioning helps with partition elimination but does not reduce data movement for joins.
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.