DP-203 Design and implement data storage Practice Question
You are designing a data storage solution in Azure Synapse Analytics. You need to store large volumes of semi-structured data in a dedicated SQL pool. The data will be used for analytical queries that often filter on a date column and join on a customer ID column. You want to optimize query performance. Which two actions should you perform? (Choose two.)
⚠ Common exam trap
The trap here is assuming that any distribution or partitioning strategy improves performance; only those aligned with query patterns (join keys and filter columns) provide real benefits.
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
✓
Use hash distribution on the customer ID column for large tables that are frequently joined.
Hash-distributing large tables on the customer ID column co-locates rows for joins, reducing data movement. Partitioning large fact tables on the date column enables partition elimination for date filters. Together, these actions optimize the most common query patterns. Other options either increase data movement or are not suitable for large-scale analytical workloads.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use round-robin distribution for all tables to ensure even data spread.
Why it's wrong here
Round-robin distribution evenly spreads data but does not co-locate related rows. Joins on customer ID would require shuffling data across distributions, increasing query time. Round-robin is best for staging tables or tables without clear join keys. Using it for all tables ignores join optimization and can degrade performance for analytical queries.
- ✓
Use hash distribution on the customer ID column for large tables that are frequently joined.
Why this is correct
Hash distribution on a frequently joined column co-locates rows with the same key on the same distribution, minimizing data movement during joins. For large tables, this significantly improves query performance. It is a recommended practice in dedicated SQL pool to distribute large fact tables on their common join key, such as customer ID, to reduce shuffling.
- ✗
Use replicated distribution for all dimension tables regardless of size.
Why it's wrong here
Replicating dimension tables is beneficial for small tables, but for large dimension tables, replication can cause performance issues due to the overhead of maintaining copies on each compute node. Replication is recommended for tables smaller than 2 GB compressed. Using it for all dimension tables regardless of size can lead to increased storage and slower loads.
- ✓
Partition large fact tables on the date column to enable partition elimination.
Why this is correct
Partitioning on a date column allows the query engine to skip partitions that do not match the filter, reducing the amount of data scanned. This is especially effective when queries frequently filter on date ranges. In dedicated SQL pool, partitioning on a date column with a sufficient number of partitions (e.g., daily) improves performance and simplifies data management.
- ✗
Store the semi-structured data as JSON in a single column and use JSON functions for queries.
Why it's wrong here
While dedicated SQL pool supports JSON functions, storing semi-structured data as JSON in a single column prevents effective distribution and partitioning. Queries filtering on date or joining on customer ID would need to parse JSON at runtime, which is inefficient. For analytical workloads, it is better to shred JSON into relational columns to enable distribution and partitioning optimizations.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 509 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 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.