DP-203 Develop data processing Practice Question
A financial services firm uses Azure Synapse Analytics to process daily trade data. The data is stored in a dedicated SQL pool as partitioned tables by date. Recently, queries that filter on a specific date range have become slow. You suspect that partition pruning is not working effectively. What should you do to improve query performance?
⚠ Common exam trap
A common mix-up: candidates assume poor partition pruning is always due to incorrect partitioning strategy or missing statistics, overlooking the fact that columnstore index fragmentation can prevent effective partition elimination even when the partition scheme is correct.
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
✓
Rebuild the columnstore indexes on the table
Rebuilding columnstore indexes reorganizes and compresses the data, which can improve partition elimination by ensuring that each partition's columnstore segments are well-structured and aligned with the partition boundaries. Over time, as data is inserted, updated, or deleted, columnstore indexes can become fragmented, leading to inefficient partition pruning and slower query performance on date-range filters.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Rebuild the columnstore indexes on the table
Why this is correct
Rebuilding reduces fragmentation and improves partition pruning.
- ✗
Convert the table to a rowstore heap
Why it's wrong here
Rowstore is slower for large analytical queries.
- ✗
Create statistics on the date column
Why it's wrong here
Statistics help but fragmentation is the likely root cause.
- ✗
Increase the number of partitions for the table
Why it's wrong here
More partitions can increase overhead and not fix fragmentation.
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.