DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table with billions of rows. The table is distributed using hash distribution on the SaleDate column. Queries that join this fact table with a small dimension table (Product) on ProductID are slow because the join requires shuffling data across distributions. Which design change would most improve the performance of these join queries?
⚠ Common exam trap
It's easy for candidates to confuse partitioning with distribution, thinking partitioning on SaleDate will help the join on ProductID, but partitioning only segments data within a distribution and does not reduce cross-distribution data movement for joins on a different 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
✓
Replicate the Product dimension table to all distributions.
Replicating the Product dimension table to all distributions eliminates the need to shuffle data across distributions during the join. In Azure Synapse dedicated SQL pool, hash distribution distributes rows across 60 distributions based on the hash of the distribution column (SaleDate). When joining on ProductID, which is not the distribution column, data must be moved between distributions. Replicating the small dimension table ensures each distribution has a local copy, allowing the join to be performed without data movement, significantly improving 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.
- ✗
Change the distribution of the fact table to round-robin.
Why it's wrong here
Round-robin distribution spreads rows evenly across distributions without any key-based placement, so the ProductID join keys are scattered. When a query joins the fact table to Product, each distribution must exchange rows with every other distribution to find matching keys, forcing a full data shuffle. This movement is the exact performance bottleneck the query is experiencing, so changing to round-robin does not fix it and may even add overhead despite balanced row counts.
- ✓
Replicate the Product dimension table to all distributions.
Why this is correct
Replication stores a full copy of the Product dimension table on every distribution in the dedicated SQL pool. When the fact table joins Product on ProductID, each distribution can perform the join locally using its own copy, eliminating all data movement and shuffle across distributions. This is the recommended approach for small-to-medium dimension tables in a star schema and directly resolves the join performance problem described.
- ✗
Partition the fact table by SaleDate.
Why it's wrong here
Partitioning on SaleDate changes how data is stored and pruned for queries that filter by date ranges, but it does not affect the physical placement of rows across distributions for the ProductID join. A join between fact and dimension still requires matching rows to be on the same distribution, and since ProductID is not the distribution key, data movement still occurs. Partitioning by SaleDate is useful for maintenance or date-based scans, but it will not reduce the join shuffle.
- ✗
Create a nonclustered index on ProductID in the fact table.
Why it's wrong here
A nonclustered index on ProductID can speed up point lookups or small range scans, but in a dedicated SQL pool it does not alter the distribution of the fact table. The join still requires matching ProductID values from both tables to be co-located on the same distribution, and because they are not, the query engine must shuffle rows regardless of any index. Additionally, nonclustered indexes are not the primary performance-tuning mechanism for MPP join workloads, and maintaining them on a large fact table adds storage and write overhead.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
Key term
Dedicated SQL pool
A Dedicated SQL pool is a cloud-based analytics service in Azure Synapse Analytics that provides a managed, scalable environment for running large-scale data warehousing queries using Transact-SQL.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 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-900 exam.