DP-203 Develop data processing Practice Question
Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?
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
Option C is correct because replicated tables in a dedicated SQL pool copy small dimension tables to every compute node, eliminating data movement (shuffle) during joins with large fact tables and thereby improving query performance. Option D is correct because materialized views precompute and persist the results of common aggregations, so repeated queries against large fact tables can read the smaller pre-aggregated result set instead of rescanning and re-aggregating the base data. Option A is wrong because hash distribution should be on a high-cardinality column that distributes rows evenly; low-cardinality columns cause data skew and uneven work distribution. Option B is wrong because round-robin distribution is best for staging or temporary tables, whereas fact tables benefit from hash distribution on a frequently joined column to minimize data movement. Option E is wrong because increasing DWU is a scaling action, not a query optimization technique, and doing it after every query is wasteful and does not address query design or data distribution issues.
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 low-cardinality column
Why it's wrong here
Low-cardinality hash keys create data skew, forcing uneven distribution across distributions and slowing parallel queries. It tempts because hash distribution is the recommended technique for large fact tables; it would be correct when the chosen column has high cardinality and even value spread.
- ✗
Use round-robin distribution for fact tables
Why it's wrong here
Round-robin distribution prevents effective partition elimination and forces data movement for joins on fact tables. It tempts because round-robin is the simple default for staging or loading tables; it would be correct for small dimension or temporary tables where even spread matters more than join locality.
- ✓
Use replicated tables for small dimension tables
Why this is correct
Replicating small dimension tables places a full copy on every compute node, eliminating data movement during joins with large fact tables. This directly removes the shuffle overhead that dominates query time in a dedicated SQL pool's distributed architecture.
- ✓
Create materialized views for common aggregations
Why this is correct
Materialised views precompute and persist aggregation results, so recurring queries read stored data instead of rescanning and re-aggregating large fact tables. The dedicated SQL pool automatically rewrites matching queries to use the view, cutting execution time for common aggregations.
- ✗
Increase the DWU setting after every query
Why it's wrong here
DWU changes trigger data redistribution and take minutes to complete, so per-query scaling adds latency rather than removing it. It tempts because scaling genuinely raises compute capacity; it would be correct when sustained workload growth demands a permanent, planned DWU increase.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 509 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.