DP-203 Design and implement data storage Practice Question
Your company uses Azure Synapse Analytics for its enterprise data warehouse. The main fact table, OrdersFact, is distributed using hash on OrderID. It has 10 billion rows. The table is partitioned by month. Recently, the data engineering team added a new column 'OrderStatus' that is used in many queries with filters like 'WHERE OrderStatus = 'Shipped''. These queries are scanning the entire table because the partition pruning is not effective. You need to improve query performance for these status-based queries without redesigning the entire table. What should you do?
⚠ Common exam trap
Many exam-takers confuse partitioning with indexing, assuming that repartitioning by the filter column (OrderStatus) will solve the scan problem, but in a columnstore environment, a non-clustered columnstore index is the correct way to enable efficient predicate filtering without altering the physical table design.
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
✓
Create a non-clustered columnstore index on OrderStatus
Creating a non-clustered columnstore index on OrderStatus allows SQL Server to perform batch mode processing and predicate pushdown, enabling efficient rowgroup elimination without requiring a full table scan. This index stores OrderStatus in a compressed, columnar format that can be quickly filtered, and it does not require redesigning the existing hash distribution or monthly partitioning.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Repartition the table by OrderStatus
Why it's wrong here
Repartitioning by status could cause data skew and does not replace partitioning by month.
- ✓
Create a non-clustered columnstore index on OrderStatus
Why this is correct
A non-clustered columnstore index on OrderStatus allows efficient filtering.
- ✗
Change the distribution key to include OrderStatus
Why it's wrong here
Changing distribution key would require rebuilding the table and may not improve status queries.
- ✗
Create a materialized view that aggregates by OrderStatus and month
Why it's wrong here
A materialized view could help but is overkill if indexing solves the problem.
Go deeper
Related to this question
About these practice questions
One of 760 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.