Exam DP-200 topic 6 question 76 discussion

Actual exam question from Microsoft's DP-200

Question #: 76
Topic #: 6

A company has an Azure SQL Datawarehouse. They have a table named whizlab_salesfact that contains data for the past 12 months. The data is partitioned by month. The table contains around a billion rows. The table has clustered columnstore indexes. At the beginning of each month you need to remove the data from the table that is older than 12 months.
Which of the following actions would you implement for this requirement? (Choose three.)

A. Create a new empty table named XYZ_salesfact_new that has the same schema as XYZ_salesfact
B. Drop the XYZ_salesfact_new table
C. Copy the data to the new table by using CREATE TABLE AS SELECT (CTAS)
D. Truncate the partition containing the stale data
E. Switch the partition containing the stale data from XYZ_salesfact to XYZ_salesfact_new
F. Execute the DELETE statement where the value in the Date column is greater than 12 months

Show Suggested Answer

Suggested Answer: BCE 🗳️
An example of this is given in a blog post. To achieve this, we first need to copy the data onto a new table using the ג€CREATE TABLE AS SELECTג€ command.
Then we switch the partition and then delete the staging table.
Option ג€Create a new empty table named XYZ_salesfact_new that has the same schema as XYZ_salesfactג€ is incorrect because we also need to copy the data onto the new table.
Option ג€Truncate the partition containing the stale dataג€ is incorrect because we need to switch the partition.
Option ג€Execute the DELETE statement where the value in the Date column is greater than 12 monthsג€ is incorrect because issuing the DELETE statement would take time.
Reference:
https://blogs.msdn.microsoft.com/apsblog/2018/06/18/azure-sql-dw-performance-ctaspartition-switching-vs-updatedelete/

by Sumercin at July 19, 2021, 3:55 p.m.

Comments

Submit Cancel

Sumercin

Highly Voted 3 years, 10 months ago

I would say A instead of C because there's no need to copy data, just 'delete' the last month by sending it to the new table wich later is gonna be dropped. So for me the correct answer is A, B E.

upvoted 8 times

...

Gitty

Most Recent 3 years, 9 months ago

Provided answer BCE is correct. ""The preferred method is to utilize a methodology of CTAS and partition switching in lieu of UPDATE and DELETE operations wherever possible."" https://docs.microsoft.com/en-gb/archive/blogs/apsblog/azure-sql-dw-performance-ctaspartition-switching-vs-updatedelete

upvoted 1 times

...