Data Engineering on Microsoft Azure — Question 54

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named DimSalesPerson. DimSalesPerson contains the following columns:

• RepSourceID
• SalesRepID
• FirstName
• LastName
• StartDate
• EndDate
• Region

You are developing an Azure Synapse Analytics pipeline that includes a mapping data flow named Dataflow1. Dataflow1 will read sales team data from an external source and use a Type 2 slowly changing dimension (SCD) when loading the data into DimSalesPerson.

You need to update the last name of a salesperson in DimSalesPerson.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

Answer options

• A. Update three columns of an existing row.
• B. Update two columns of an existing row.
• C. Insert an extra row.
• D. Update one column of an existing row.

Correct answer: C, D

Explanation

The correct actions are C and D. In a Type 2 slowly changing dimension, to update the last name of a salesperson, you must insert a new row (C) with the updated information and mark the previous row as inactive. Updating one column (D) is necessary to reflect the change in the last name, while the other options do not align with the requirements of a Type 2 SCD.