DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You are configuring Azure Synapse Analytics dedicated SQL pool. To optimize query performance for a large fact table that is frequently filtered on date and region columns, which table distribution and indexing strategy 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
✓
Hash distribution on region column with clustered columnstore index.
The best recommendation because hash distribution on the region column evenly distributes the data across distributions (assuming high cardinality), and clustering with a columnstore index provides excellent compression and query performance for large fact tables, especially when filtered on non-distribution columns like date. Option A (hash on date) risks data skew if date cardinality is low. Option B (replicated) is designed for small dimension tables, not large fact tables. Option C (round-robin) distributes rows randomly, preventing partition elimination and reducing filter performance.
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 distribution on date column with clustered index.
Why it's wrong here
Date may have lower cardinality leading to data skew.
- ✗
Replicated distribution with heap index.
Why it's wrong here
Replicated is for small tables; heap index is not optimized for analytics.
- ✗
Round-robin distribution with clustered index.
Why it's wrong here
Round-robin is not optimized for filtering on specific columns.
- ✓
Hash distribution on region column with clustered columnstore index.
Why this is correct
Hash distribution on a high-cardinality column improves joins and filtering; columnstore is efficient for large tables.
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.