DP-203 Design and implement data storage Practice Question
You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by month. You notice that queries filtering on a specific month still scan all partitions. The table has a clustered columnstore index. What is the most likely cause?
⚠ Common exam trap
Many candidates assume partition elimination is always effective with columnstore indexes, overlooking that unclosed delta store rowgroups can disable this optimization, and they may incorrectly attribute the issue to distribution or statistics.
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
✓
The columnstore index has a large number of open rowgroups that are not compressed.
When a clustered columnstore index has a large number of open rowgroups (i.e., rowgroups that have not yet been compressed into columnstore segments), queries may fall back to the delta store, which stores data in a B-tree-like structure. This bypasses partition elimination because the delta store does not maintain the same metadata for partition pruning, causing all partitions to be scanned. Compressing open rowgroups into closed, compressed rowgroups restores proper partition elimination behavior.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The table is hash-distributed on a column other than date, preventing partition elimination.
Why it's wrong here
Partition elimination works regardless of distribution key.
- ✗
The statistics on the date column are outdated or missing.
Why it's wrong here
Statistics affect cardinality estimation, not partition elimination.
- ✓
The columnstore index has a large number of open rowgroups that are not compressed.
Why this is correct
Open rowgroups cause the entire columnstore to be scanned, bypassing partition elimination.
- ✗
The table is replicated, so partition elimination does not apply.
Why it's wrong here
Replicated tables are replicated to all distributions but still support partition elimination.
Visual reference
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 760-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.