You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a partitioned fact table named dbo.Sales and a staging table named stg.Sales that has the matching table and partition definitions.
You need to overwrite the content of the first partition in dbo.Sales with the content of the same partition in stg.Sales. The solution must minimize load times.
What should you do?
The most efficient way to overwrite the content of the first partition in dbo.Sales with the content of the same partition in stg.Sales is to switch the first partition from stg.Sales to dbo.Sales. This method, known as partition switching, is a metadata-only operation that minimizes load times. The correct command would be to switch the partition from the staging table (stg.Sales) to the target table (dbo.Sales), effectively replacing the target partition’s contents.
The correct answer is C
this must be C. since the need is to overwrite dbo.Sales with the content of stg.Sales. SWITCH source TO target
This is quite a weird situation because according to Microsoft documentation: "When reassigning a table's data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it MUST BE EMPTY." (https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=azure-sqldw-latest&preserve-view=true#switch--partition-source_partition_number_expression--to--schema_name--target_table--partition-target_partition_number_expression-) Therefore none of the options would be possible if considering that both tables are not empty on that partition. Then I have no idea what would be the correct answer, although I answered C.
ALTER TABLE stg.Sales SWITCH PARTITION 1 TO dbo.Sales PARTITION 1;
B is correct ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2; ALTER TABLE FactInternetSales SPLIT RANGE (20010101); https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition?context=%2Fazure%2Fsynapse-analytics%2Fcontext%2Fcontext
Why I am seeing B and C are both same?
SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ] SOURCE TO DETENTION
actually B is correct. We have to understand the sentence as: Change the content of the first partition of dbo.Sales with the content of the first partition of stage.Sales. Thus, switch the content of first partition of dbo.Sales to/with the first partition of stage.Sales.
Logical is C
Response is B
The correct answer is C
switch the first partition of sales to stage.sales's partition
it is the way on wording....tricky. might be not native speaker or they shall ask differt way...
Partition switching is a powerful technique that allows you to move data between tables quickly and with minimal logging. It’s especially useful when dealing with large datasets. You switch the staging table into the production table, and the production table becomes the staging table. https://dba.stackexchange.com/questions/231918/best-solution-to-switch-staging-table-to-main-table-avoid-access-when-main-tabl
The most efficient approach to overwrite the content of the first partition in dbo.Sales with the content of the same partition in stg.Sales while minimizing load times is to switch the first partition from stg.Sales to dbo.Sales. This is option C.
ALTER TABLE stg.Sales SWITCH PARTITION 1 TO dbo.Sales PARTITION 1 WITH (TRUNCATE_TARGET = ON);
When reassigning a table's data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty. Thus, B is correct because it is the first step to remove the data from dbo.Sales. Then we need to another step which is C: to move the partition with data from stg.Sales to dbo.Sales.