DP-203 Design and implement data storage Practice Question
You are migrating an on-premises SQL Server database to Azure. The database has a large fact table (500 GB) and several dimension tables (10 GB total). Reporting queries join the fact table with dimension tables and aggregate by date. Which Azure service and table design should you recommend to minimize query latency?
⚠ Common exam trap
Candidates often confuse replicated tables as a universal performance booster, not realizing that replicating a large fact table is impractical and that hash distribution on the join key is the correct pattern for large fact tables in a distributed MPP environment.
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
✓
Azure Synapse SQL Pool with hash distribution on the fact table's foreign key and round-robin for dimension tables
Azure Synapse SQL Pool with hash distribution on the fact table's foreign key ensures that related rows from the fact and dimension tables are co-located on the same compute node, minimizing data movement during joins. Round-robin distribution for the small dimension tables is appropriate since they are under 1 GB each and can be broadcast to all nodes, further reducing shuffle overhead. This design optimizes parallel query execution for large fact table aggregations by date.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Azure Synapse SQL Pool with replicated tables for both fact and dimension tables
Why it's wrong here
Replicating large fact tables is not feasible due to storage and overhead.
- ✗
Azure SQL Database Hyperscale with columnstore indexes
Why it's wrong here
Hyperscale is for OLTP workloads, not data warehousing.
- ✓
Azure Synapse SQL Pool with hash distribution on the fact table's foreign key and round-robin for dimension tables
Why this is correct
Hash distribution enables co-location joins, improving query performance.
- ✗
Azure SQL Database with rowstore indexes and a single database
Why it's wrong here
Azure SQL DB is not designed for large fact tables with heavy aggregation queries.
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 760-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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.