nerdexam
Microsoft

DP-203 · Question #96

Drag and Drop Question You have an Azure subscription. You plan to build a data warehouse in an Azure Synapse Analytics dedicated SQL pool named pool1 that will contain staging tables and a…

The correct answer is Replicated; Hash; Round-robin. For the dimensional model, dimension tables (small, frequently joined tables like DimDate, DimProduct) should use Replicated distribution so each compute node has a full copy, eliminating data movement during joins and maximizing query performance. Fact tables (large tables…

Submitted by kavita_s· Mar 30, 2026Design and Implement Data Storage - specifically designing distributed table strategies in Azure Synapse Analytics dedicated SQL pools to optimize both data loading and query performance.

Question

Drag and Drop Question You have an Azure subscription. You plan to build a data warehouse in an Azure Synapse Analytics dedicated SQL pool named pool1 that will contain staging tables and a dimensional model. Pool1 will contain the following tables. You need to design the table storage for pool1. The solution must meet the following requirements: - Maximize the performance of data loading operations to Staging.WebSessions. - Minimize query times for reporting queries against the dimensional model. Which type of table distribution should you use for each table? To answer, drag the appropriate table distribution types to the correct tables. Each table distribution type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Answer:

Exhibit

DP-203 question #96 exhibit

Answer Area

Drag items

HashReplicatedRound-robin

Correct arrangement

  • Replicated
  • Hash
  • Round-robin

Explanation

For the dimensional model, dimension tables (small, frequently joined tables like DimDate, DimProduct) should use Replicated distribution so each compute node has a full copy, eliminating data movement during joins and maximizing query performance. Fact tables (large tables like FactSales) should use Hash distribution on a join key to co-locate related rows and avoid shuffling during joins with dimension tables. Staging tables like Staging.WebSessions should use Round-robin distribution because it distributes data evenly across all distributions with no overhead, maximizing parallel load ingestion speed when the final query pattern is unknown or irrelevant at load time.

Topics

#Azure Synapse Analytics#Dedicated SQL Pool#Table Distribution#Data Warehouse Design

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-203 Practice