DP-203 Exam - Question 3

Question

DRAG DROP -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 indexAt 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. Select and Place:.

Examice · Accepted Answer

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.

SQL Data Warehouse supports partition splitting, merging, and switching. To switch partitions between two tables, you must ensure that the partitions align on their respective boundaries and that the table definitions match.

Loading data into partitions with partition switching is a convenient way stage new data in a table that is not visible to users the switch in the new data.

Step 3: Drop the SalesFact_Work table.

Reference:

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition

hsetin · Answer

Given answer D A C is correct.

vigilante89 · Answer

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  SWITCH PARTITION  to

Step 3:
Delete the SalesFact_Work table.

the_frix · Answer

"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

AvithK · Answer

truncate partition is even quicker, why isn't that the answer, if the data is dropped anyway?

covillmail · Answer

DAC is correct

vrodriguesp · Answer

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

UzairMir · Answer

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

poornipv · Answer

what is the correct answer for this?

SabaJamal2010AtGmail · Answer

Given answer is correct

indomanish · Answer

Partition switching help us in loading large data set quickly . Not sure if it will help in purging data as well.

RamGhase · Answer

i could not understand how answer handled to remove data before 36 month

Deeksha1234 · Answer

correct ans.

pmc08 · Answer

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

Dusica · Answer

D A C IS CORRECT

rocky48 · Answer

Given answer - Options D A C are correct.

wfrf92 · Answer

Is this correct ????

Dileepvikram · Answer

The data copy to back up table is not mentioned in the answer

dragos_dragos62000 · Answer

Correct!

AnonAzureDataEngineer · Answer

Seems like it should be:
1. E
2. A
3. C

PallaviPatel · Answer

D A C is correct.

theezin · Answer

Why not included deleting sales data older than 36 months which is mentioned in question?

JJdeWit · Answer

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

Dothy · Answer

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.

mkthoma3 · Answer

D,A,C

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

Jawidkaderi · Answer

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.

kkk5566 · Answer

correct

valenrami5 · Answer

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

PavanPusarapu · Answer

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

nani191 · Answer

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

EmnCours · Answer

FAC is correct 
https://learn.microsoft.com/fr-fr/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition

technoguy · Answer

DAC is simpler abd best approah here

DP-203 Exam - Question 3

Discussion