Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 3 discussion

Actual exam question from Microsoft's DP-203

Question #: 3
Topic #: 1

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:

Show Suggested Answer

Suggested 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

by wfrf92 at May 9, 2021, 3:37 a.m.

Comments

Submit Cancel

hsetin

Highly Voted 3 years, 9 months ago

Given answer D A C is correct.

upvoted 49 times

svik

3 years, 9 months ago

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

upvoted 4 times

...

vigilante89

Highly Voted 9 months ago

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.

upvoted 13 times

...

technoguy

Most Recent 3 months ago

DAC is simpler abd best approah here

upvoted 1 times

...

EmnCours

7 months ago

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

upvoted 1 times

...

vrodriguesp

9 months ago

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

upvoted 4 times

...

the_frix

9 months ago

"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

upvoted 5 times

...

nani191

9 months, 1 week ago

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

upvoted 1 times

...

PavanPusarapu

1 year, 2 months ago

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

upvoted 1 times

...

valenrami5

1 year, 2 months ago

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

upvoted 1 times

...

kkk5566

1 year, 9 months ago

correct

upvoted 1 times

...

rocky48

1 year, 11 months ago

Given answer - Options D A C are correct.

upvoted 2 times

...

UzairMir

2 years, 1 month ago

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

upvoted 3 times

Xarvastia

2 years, 1 month ago

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

upvoted 3 times

...

Aaron1234

2 years, 1 month ago

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

upvoted 2 times

...

Dusica

2 years, 5 months ago

D A C IS CORRECT

upvoted 2 times

...

Jawidkaderi

2 years, 8 months ago

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.

upvoted 1 times

...