Implementing an Azure Data Solution (legacy) — Question 55

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.)

Answer options

• 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

Correct answer: B, C, E

Explanation

The correct actions are to drop the new table (B) after it has been used, copy the data into it (C), and then switch the partition of stale data to this new table (E). Options A and D are unnecessary steps, while option F would be less efficient than partition switching, which is optimized for large data sets.