Exam DP-203 topic 1 question 3 discussion

Given answer D A C is correct.

The answer should be D --> A --> C. Step 1: Create an empty table SalesFact_Work with same schema as SalesFact. Step 2: Switch the partition (to be removed) from SalesFact to SalesFact_Work. The syntax is: ALTER TABLE <source table> SWITCH PARTITION <partition number> to <destination table> Step 3: Delete the SalesFact_Work table.

Given answer is correct: Step 1: Create an empty table SalesFact_Work with same schema as SalesFact (that it will contains reocords older than 3 years) Step 2: Switch the partition from SalesFact to SalesFact_Work. SO we're only doing metadata operations Step 3: Delete the SalesFact_Work table containing stale data and we're not losing any time or blocking target table

"Partition switching can be used to quickly remove or replace a section of a table. For example, a sales fact table might contain just data for the past 36 months. At the end of every month, the oldest month of sales data is deleted from the table. This data could be deleted by using a delete statement to delete the data for the oldest month. However, deleting a large amount of data row-by-row with a delete statement can take too much time, as well as create the risk of large transactions that take a long time to rollback if something goes wrong. A more optimal approach is to drop the oldest partition of data. Where deleting the individual rows could take hours, deleting an entire partition could take seconds." https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#benefits-to-loads

FOR 3RD STEP: why should we drop the table instead of truncating? so that we can use it for subsequent months right?

Is given answer correct (D, A, C) ?

Why not use 'ALTER TABLE' to avoid the need for creating a new table next time and simply switch the partition instead?

correct

Given answer - Options D A C are correct.

Hi Can someone tell me why we cannot simply execute a delete statement? Thanks

D A C IS CORRECT

very interesting questions: Every partition has a name, which indicated by the mmYYYY perhaps. So, if we know the name of the partition, we can drop that partition directly: DROP PARTITION SCHEME partition_scheme_name [ ; ] However, if there is an index on the table DOPR Partition will not work. So, the it is correct. DAC.

Answer is F - A - C https://docs.microsoft.com/es-es/archive/blogs/apsblog/azure-sql-dw-performance-ctaspartition-switching-vs-updatedelete

correct ans.

D,A,C Azure Synapse does not support truncating partitions. Currently, that is feature is only tied to MS SQL Server.

Step 1: Create an empty table named SalesFact_work that has the same schema as SalesFact. Step 2: Switch the partition containing the stale data from SalesFact to SalesFact_Work. Step 3: Drop the SalesFact_Work table.

D A C is the right option. For more information, this doc discusses exactly this example: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition