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.)
To handle the removal of data older than 12 months efficiently from a partitioned table in Azure SQL Datawarehouse, we should create a new empty table with the same schema to facilitate partition switching. This way, the old data can be quickly moved to the new empty table. Once the partition containing the stale data is switched to the new empty table, it can be easily truncated. This avoids the performance overhead associated with a DELETE statement and ensures the data removal process is efficient, as SQL Data Warehouse is optimized for partition switching and truncation. Therefore, the correct actions are to: create a new empty table named XYZ_salesfact_new that has the same schema as XYZ_salesfact, switch the partition containing the stale data from XYZ_salesfact to XYZ_salesfact_new, and truncate the partition containing the stale data.
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.
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