DP-203 Design and implement data storage Practice Question
Your company uses Azure Synapse Analytics dedicated SQL pool to store a fact table with 2 billion rows. You need to improve query performance for a workload that frequently aggregates sales by date and product category. Which distribution and index type should you use?
⚠ Common exam trap
Test-takers frequently choose round-robin distribution (Option C) thinking it balances data evenly, but they overlook that it causes data shuffling for any aggregation on a non-distribution column, while hash distribution on the grouping column avoids that overhead entirely.
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 on product_category and use a clustered columnstore index.
Hash-distributing on product_category ensures that rows with the same product category are co-located on the same distribution, enabling local aggregation without data movement. A clustered columnstore index provides high compression and batch-mode processing, which is ideal for large fact tables and analytical workloads that aggregate millions of rows by columns like date and product_category.
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 on product_category and use a clustered columnstore index.
Why this is correct
Hash distribution on the join/aggregation key improves performance; columnstore is ideal for large data volumes.
- ✗
Replicate the table and use a clustered index.
Why it's wrong here
Replication is not practical for 2 billion rows; clustered index is less efficient than columnstore.
- ✗
Round-robin distribution and a clustered columnstore index.
Why it's wrong here
Round-robin causes data shuffling during aggregation, hurting performance.
- ✗
Hash-distribute on date and use a clustered index.
Why it's wrong here
Hash on date scatters product categories, requiring data movement for aggregation by category.
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.