DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You are a data engineer for a retail company that stores sales data in an Azure Synapse Analytics dedicated SQL pool. You need to optimize query performance for a large fact table that is frequently joined with a much smaller dimension table. The queries often filter on a date column and aggregate sales amounts. Which technique should you implement to improve query performance?
⚠ Common exam trap
The trap here is assuming that any index or distribution will suffice, but using a row-based index or a hash distribution on the wrong key can introduce data movement and slow down aggregations.
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 clustered columnstore index on the fact table and a replicated distribution for the dimension table.
A clustered columnstore index on the fact table provides high compression and fast aggregation, while a replicated distribution for the smaller dimension table eliminates data movement during joins. Together, these optimizations reduce I/O and network overhead, delivering significant performance gains for queries that filter on date and aggregate sales amounts. This is a best practice for star schema workloads in dedicated SQL pools.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a heap on the fact table and a hash-distributed dimension table on the join key.
Why it's wrong here
A heap is a table without a clustered index, which is inefficient for large fact tables with frequent aggregations and filters. Hash-distributing the dimension table on the join key may cause data movement during joins if the fact table is not distributed on the same key. This setup would likely result in slower query performance due to lack of indexing and potential shuffle operations.
- ✗
Partition the fact table by date and use a hash distribution on the dimension table's primary key.
Why it's wrong here
Partitioning the fact table by date can improve query performance for date filters, but without a columnstore index, aggregations may still be slow. Hash-distributing the dimension table on its primary key might not align with the join key, causing data movement. This technique alone does not address the need for efficient column-based aggregation and local joins.
- ✓
Create a clustered columnstore index on the fact table and a replicated distribution for the dimension table.
Why this is correct
A clustered columnstore index is ideal for large fact tables because it provides high compression and fast column-based aggregations. Using a replicated distribution for the smaller dimension table ensures that joins are performed locally on each compute node, eliminating data movement. This combination minimizes I/O and shuffle operations, significantly improving query performance for the described workload.
- ✗
Create a clustered index on the date column of the fact table and a round-robin distribution for the dimension table.
Why it's wrong here
A clustered index on the date column might help with date filters, but it is not as efficient as a columnstore index for aggregations over large datasets. Round-robin distribution for the dimension table would cause data movement during joins because the distribution is arbitrary, leading to increased network traffic and slower queries. This approach does not optimize the join performance.
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 509-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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
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.