Exam DP-203 All QuestionsBrowse all questions from this exam
Go to Exam
Question 3

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 index

At 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:

    Correct 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

Discussion
hsetin

Given answer D A C is correct.

svik

Yes. Once the partition is switched with an empty partition it is equivalent to truncating the partition from the original table

vigilante89

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.

the_frix

"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

vrodriguesp

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

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

Xarvastia

DELETE is intensive for a database to run. The solution should be faster as possible.

Aaron1234

https://learn.microsoft.com/en-us/training/modules/analyze-optimize-data-warehouse-storage-azure-synapse-analytics/10-understand-rules-for-minimally-logged-operations

rocky48

Given answer - Options D A C are correct.

Dusica

D A C IS CORRECT

pmc08

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

pmc08

D is incorrect because we also need to copy the data onto the new table

supriyako

F seems wrong as it says CTAS to copy the data

Deeksha1234

correct ans.

r4nq7b6l7f

The answer should be D --> A --> C. Step 1: Create an empty table SalesFact_Work with same schema as SalesFact. Step 2: LINKDIEN MUST TRY: https://lnkd.in/d6p35taR 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.

PavanPusarapu

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

valenrami5

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

kkk5566

correct

Jawidkaderi

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.

mkthoma3

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

Dothy

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.

JJdeWit

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