DP-203 Question #96: Real Exam Question with Answer & Explanation

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:

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.

No community discussion yet for this question.