DP-203 Design and implement data storage Practice Question
You are designing a data storage solution in Azure Synapse Analytics dedicated SQL pool. The solution must support efficient loading of large volumes of data from external sources and provide high query performance for reporting. You need to choose two table distribution types that are most appropriate for large fact tables and dimension tables respectively. (Choose two.)
⚠ Common exam trap
The trap here is assuming that round-robin distribution is always best for even data spread, or that external tables can replace internal tables for performance-critical fact tables.
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
✓
Replicated table
For a dedicated SQL pool, large fact tables should be hash-distributed on a column frequently used in joins to minimize data movement. Small dimension tables should be replicated so that joins with fact tables avoid data movement. Round-robin is for staging, and external and temporary tables are not distribution types. This combination optimizes query performance for star schema 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.
- ✗
External table
Why it's wrong here
An external table points to data stored outside the dedicated SQL pool, such as in Data Lake Storage. While useful for querying external data, it does not provide the same performance as internal tables and is not a distribution type. It is not suitable for the core fact and dimension tables that require high performance.
- ✓
Replicated table
Why this is correct
Replicated tables store a full copy of the table on every compute node. They are best for small dimension tables because they eliminate the need for data movement during joins with large fact tables. This reduces query latency and improves performance for star schema queries.
- ✓
Hash-distributed table
Why this is correct
Hash-distributed tables distribute rows across distributions based on a hash of a chosen column. They are ideal for large fact tables because they enable parallel processing and minimize data movement during joins when the distribution column is used in join predicates. This improves query performance for large tables.
- ✗
Temporary table
Why it's wrong here
A temporary table is a session-scoped table used for intermediate results, not a permanent distribution type. It does not address the need for efficient storage and query performance for large fact and dimension tables. Temporary tables are typically used for staging or complex transformations.
- ✗
Round-robin table
Why it's wrong here
Round-robin distribution spreads rows evenly across all distributions without a key. It is useful for staging tables or when no clear distribution key exists, but it often causes data movement during joins, which degrades performance for large fact tables and dimension tables in a star schema. It is not the optimal choice for either.
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.