Exam DP-203 topic 1 question 101 discussion

Actual exam question from Microsoft's DP-203

Question #: 101
Topic #: 1

HOTSPOT
-

You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a fact table named Table1. Table1 contains sales data. Sixty-five million rows of data are added to Table1 monthly.

At the end of each month, you need to remove data that is older than 36 months. The solution must minimize how long it takes to remove the data.

How should you partition Table1, and how should you remove the old data? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Show Suggested Answer

Suggested Answer:

by jongert at Jan. 6, 2024, 8:51 a.m.

Comments

Submit Cancel

jongert

Highly Voted 1 year, 5 months ago

Correct, dedicated SQL pool divides partitions into 60 databases and should aim for at least 1M rows per distribution (although not mentioned in the question, clustered columnstore compression becomes efficient at this scale). Therefore, partitioning by day would result in too small partitions. Having partitioned by months, we can use switch to move it using metadata operations only, which makes the operation extremely efficient.

upvoted 10 times

...