DP-203 Certification Practice Question #38

Question

You are building an Azure Synapse Analytics dedicated SQL pool that will contain a fact table for transactions from the first half of the year 2020.
You need to ensure that the table meets the following requirements:
✑ Minimizes the processing time to delete data that is older than 10 years
✑ Minimizes the I/O for queries that use year-to-date values
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Accepted Answer

The requirements point directly to date-based partitioning in a dedicated SQL pool. Year-to-date queries benefit from partition elimination when partitions are aligned to the date key, and data older than the retention period can be removed efficiently by switching or dropping old partitions instead of issuing large `DELETE` operations. Microsoft guidance for dedicated SQL pool tables recommends partitioning fact data by date when retention windows and time-based query pruning matter, which matches the strong community consensus for `RANGE RIGHT` on `TransactionDateId`.

More DP-203 practice questions