Answer-first summary for fast verification
Answer: Insert an extra row., Update one column of an existing row.
In a Type 2 Slowly Changing Dimension (SCD) implementation, the goal is to preserve historical data while tracking changes over time. When updating a salesperson's last name: **Correct Actions:** - **C (Insert an extra row):** A new row must be inserted with the updated last name, the current date as the StartDate, and a NULL or future EndDate to indicate it is the current active record. This preserves the historical last name in the existing row. - **D (Update one column of an existing row):** The existing row's EndDate must be updated to the current date (the date of the change) to mark it as a historical record. This ensures the previous last name is retained with its valid time period. **Why Other Options Are Incorrect:** - **A (Update three columns of an existing row):** This would overwrite the existing last name, losing historical data, which violates Type 2 SCD principles. - **B (Update two columns of an existing row):** This typically implies updating both the last name and another column (e.g., EndDate or an IsActive flag). However, updating the last name in the existing row would erase history. Since there is no IsActive column in the table schema, only the EndDate needs updating, making this option misleading. **Key SCD Type 2 Principles Applied:** - **Historical Preservation:** The existing row retains the old last name with its original StartDate and an updated EndDate. - **Current Record Tracking:** The new row with the updated last name becomes the current active record. - **Schema Consideration:** The table includes StartDate and EndDate columns but no explicit IsActive flag, so date ranges are used to determine active records. Only the EndDate column requires updating in the existing row.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
You have an Azure Synapse Analytics dedicated SQL pool with a table named DimSalesPerson that has the following columns:
• RepSourceID • SalesRepID • FirstName • LastName • StartDate • EndDate • Region
You are building an Azure Synapse Analytics pipeline that uses a mapping data flow named Dataflow1. This data flow reads sales team data from an external source and implements a Type 2 slowly changing dimension (SCD) to load data into DimSalesPerson.
You need to update the last name of a salesperson in the DimSalesPerson table.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
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.
No comments yet.