nerdexam
Microsoft

DP-203 · Question #44

Drag and Drop Question You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analytics. SalesFact contains sales data from the past 36 months and has the following characte

The correct answer is Create an empty table named SalesFact_Work that has the same schema as SalesFact.; Switch the partition containing the stale data from SalesFact to SalesFact_Work.; Drop the SalesFact_Work table.. The correct sequence is: (1) Create an empty SalesFact_Work table with the same schema as SalesFact, (2) Switch the partition containing stale data from SalesFact to SalesFact_Work, then (3) Drop SalesFact_Work. This pattern leverages partition switching, which is a metadata-only

Submitted by deeparc· Mar 30, 2026Design and Implement Data Storage - specifically optimizing data loading, archiving, and partition management strategies in Azure Synapse Analytics dedicated SQL pools (DP-203 / Azure Data Engineer Associate)

Question

Drag and Drop Question You have a table named SalesFact in an enterprise data warehouse in Azure Synapse Analytics. SalesFact contains sales data from the past 36 months and has the following characteristics: - Is partitioned by month - Contains one billion rows - Has clustered columnstore indexes At the beginning of each month, you need to remove data from SalesFact that is older than 36 months as quickly as possible. Which three actions should you perform in sequence in a stored procedure? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. Answer:

Exhibit

DP-203 question #44 exhibit

Answer Area

Drag items

Switch the partition containing the stale data from SalesFact to SalesFact_Work.Truncate the partition containing the stale data.Drop the SalesFact_Work table.Create an empty table named SalesFact_Work that has the same schema as SalesFact.Execute a DELETE statement where the value in the Date column is more than 36 months ago.Copy the data to a new table by using CREATE TABLE AS SELECT (CTAS).

Correct arrangement

  • Create an empty table named SalesFact_Work that has the same schema as SalesFact.
  • Switch the partition containing the stale data from SalesFact to SalesFact_Work.
  • Drop the SalesFact_Work table.

Explanation

The correct sequence is: (1) Create an empty SalesFact_Work table with the same schema as SalesFact, (2) Switch the partition containing stale data from SalesFact to SalesFact_Work, then (3) Drop SalesFact_Work. This pattern leverages partition switching, which is a metadata-only operation in Azure Synapse Analytics that completes near-instantly regardless of data volume - making it far faster than row-by-row or batch deletes on a billion-row table. The empty staging table is required as the target of the partition switch, and dropping it afterward cleans up the stale data efficiently.

Topics

#Azure Synapse Analytics#Partition Switching#Clustered Columnstore Index#Data Warehouse Performance Optimization

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-203 Practice