DP-203 Develop data processing Practice Question
You are using Azure Synapse Analytics to process data in a dedicated SQL pool. You need to ensure that queries against a large fact table perform well. The fact table is partitioned by date and distributed by a product key. Which two actions should you take? (Choose two.)
⚠ Common exam trap
The trap here is assuming that traditional row-store indexes like nonclustered indexes are beneficial, when columnstore indexes are the preferred choice for fact tables in dedicated SQL pools.
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.
For a large fact table in a dedicated SQL pool, a clustered columnstore index provides optimal compression and query performance. Hash distribution on the product key ensures that joins with dimension tables on that key are co-located, reducing data movement. Together, these actions address both storage and distribution for analytical queries.
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 nonclustered index on the date column.
Why it's wrong here
Nonclustered indexes on a columnstore table are generally not recommended because they can interfere with columnstore compression and add overhead. The date column is already used for partitioning, which provides partition elimination. A nonclustered index would not improve performance for the analytical queries typical of a fact table and may degrade load performance.
- ✓
Create a clustered columnstore index on the fact table.
Why this is correct
Clustered columnstore indexes are the default and most efficient storage format for large fact tables in dedicated SQL pools. They provide high compression and batch mode execution, significantly improving query performance for analytical workloads. They are particularly effective when combined with partitioning and distribution, as they allow segment elimination and parallel scans.
- ✗
Create a replicated table for the fact table.
Why it's wrong here
Replicated tables are designed for small dimension tables, not large fact tables. Replicating a large fact table would consume significant storage and cause lengthy build times, and it is not supported for tables larger than 2 GB. This would not improve performance and would likely cause failures or excessive resource usage.
- ✗
Use round-robin distribution for the fact table.
Why it's wrong here
Round-robin distribution spreads data evenly but does not co-locate related rows. It forces data movement during joins on the product key, leading to higher query costs. For a fact table frequently joined on product key, hash distribution is superior. Round-robin is better for staging tables or tables without common join keys.
- ✓
Use hash distribution on the product key.
Why this is correct
Hash distribution on the product key co-locates rows with the same product key on the same distribution, which minimizes data movement during joins with dimension tables on that key. This reduces shuffle operations and improves query performance for common star-schema joins. It also balances data evenly across distributions if the key has high cardinality.
Go deeper
Related to this question
About these practice questions
One of 509 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 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.