DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that queries against a large fact table are slow. The table is distributed using hash distribution on a column that has a high number of nulls. You need to improve query performance. What should you do?
⚠ Common exam trap
The trap here is focusing on indexing or partitioning when the root cause is distribution skew due to a poor choice of hash distribution column.
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
✓
Change the distribution column to a column with high cardinality and even distribution, such as a surrogate key.
Hash distribution on a column with many nulls leads to data skew because all nulls hash to the same distribution. This causes one distribution to have more data and processing load, slowing down queries. Choosing a distribution column with high cardinality and even distribution, such as a surrogate key, ensures balanced data across distributions and improves query performance.
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 clustered columnstore index on the table and rebuild it.
Why it's wrong here
Clustered columnstore is the default and recommended index type for large fact tables in dedicated SQL pools, but the table likely already has one. Rebuilding it may help with fragmentation but does not address the distribution skew caused by nulls. The core issue is the hash distribution on a column with many nulls, leading to data skew.
- ✗
Partition the table on the column with many nulls to isolate the null values.
Why it's wrong here
Partitioning on a column with many nulls can create a large partition for nulls, but it does not fix the distribution skew. Partitioning is for managing data lifecycle and improving query performance by partition elimination, not for balancing data across distributions.
- ✗
Change the distribution to round-robin to evenly distribute the data.
Why it's wrong here
Round-robin distribution distributes rows evenly but does not co-locate data for joins. It can improve load performance but often degrades query performance for joins, as data movement is required. Since the issue is query performance on a large fact table, round-robin is not the best choice.
- ✓
Change the distribution column to a column with high cardinality and even distribution, such as a surrogate key.
Why this is correct
Hash distribution on a column with many nulls causes skew because all nulls are placed in the same distribution. Choosing a column with high cardinality and even distribution (e.g., a surrogate key) ensures data is evenly spread across distributions, improving query parallelism and reducing data movement for joins on that column.
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.