nerdexam
Microsoft

DP-203 · Question #241

Drag and Drop Question You plan to create a table in an Azure Synapse Analytics dedicated SQL pool. Data in the table will be retained for five years. Once a year, data that is older than five years…

The correct answer is HASH; OrderDateKey. HASH distribution on OrderDateKey ensures data is evenly distributed across partitions based on the date column, which is the key used for yearly deletions. Using OrderDateKey as both the distribution and partition column allows partition elimination to work efficiently…

Submitted by ahmad_uae· Mar 30, 2026Design and Implement Data Storage – selecting appropriate distribution and partitioning strategies in Azure Synapse Analytics dedicated SQL pools to optimize query performance and data management operations.

Question

Drag and Drop Question You plan to create a table in an Azure Synapse Analytics dedicated SQL pool. Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted. You need to ensure that the data is distributed evenly across partitions. The solution must minimize the amount of time required to delete old data. How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value 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 #241 exhibit

Answer Area

Drag items

CustomerKeyHASHROUND_ROBINREPLICATEOrderDateKeySalesOrderNumber

Correct arrangement

  • HASH
  • OrderDateKey

Explanation

HASH distribution on OrderDateKey ensures data is evenly distributed across partitions based on the date column, which is the key used for yearly deletions. Using OrderDateKey as both the distribution and partition column allows partition elimination to work efficiently, meaning entire partitions of old data (e.g., a full year) can be dropped with a simple partition switch or truncate operation rather than row-by-row deletes, dramatically minimizing deletion time. This combination satisfies both requirements: even distribution and fast deletion of time-based data.

Topics

#Azure Synapse Analytics#Dedicated SQL Pool Distribution#Table Partitioning#Data Lifecycle Management

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-203 Practice