nerdexam
Microsoft

DP-300 · Question #136

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. The correct arrangement uses HASH distribution on OrderDateKey because HASH distribution ensures data is evenly distributed across partitions based on the hash of the specified column, and OrderDateKey is the ideal partition column since data is deleted annually by date…

Submitted by amina.ke· Mar 6, 2026Design and Implement Data Storage - specifically designing distributed tables and partitioning strategies in Azure Synapse Analytics dedicated SQL pools to optimize query performance and data lifecycle management.

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 solutions 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-300 question #136 exhibit

Answer Area

Drag items

CustomerKeyHASHROUND_ROBINREPLICATEOrderDateKeySalesOrderNumber

Correct arrangement

  • HASH
  • OrderDateKey

Explanation

The correct arrangement uses HASH distribution on OrderDateKey because HASH distribution ensures data is evenly distributed across partitions based on the hash of the specified column, and OrderDateKey is the ideal partition column since data is deleted annually by date - allowing whole partitions to be switched out (TRUNCATE or SWITCH operations) rather than row-by-row deletes, minimizing deletion time. Using OrderDateKey as both the distribution and partition column aligns the data so that partition elimination works efficiently and delete operations target entire partition segments.

Topics

#Azure Synapse Analytics#Dedicated SQL Pool#Table Distribution#Partitioning Strategy

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-300 Practice