Answer: surrogate primary key, effective start date, effective end date

To create a Type 2 Slowly Changing Dimension (SCD) in Azure Synapse Analytics, three additional columns are required to track historical changes effectively: - **Surrogate Primary Key (A)**: This is essential because the existing business key (SupplierSystemID) cannot uniquely identify each version of a record in a Type 2 SCD. As multiple versions of the same supplier may exist over time, a surrogate key provides a unique identifier for each row, ensuring referential integrity and efficient joins with fact tables. - **Effective Start Date (B)**: This column indicates when a particular version of the supplier record becomes active. It is crucial for defining the validity period of each record version, allowing historical queries to determine which version was applicable at any given time. - **Effective End Date (E)**: This column specifies when a record version is superseded by a new version. Typically set to a high date (e.g., '9999-12-31') for the current active record, it enables clear demarcation of historical periods and simplifies filtering for current records. **Why other options are less suitable:** - **Business Key (C)**: The existing SupplierSystemID column already serves as the business key, so adding another business key is redundant and unnecessary. - **Last Modified Date (D)**: This tracks when a record was last updated but does not define validity periods for historical versions, making it insufficient for Type 2 SCD requirements. - **Foreign Key (F)**: This is used for relationships between tables (e.g., linking dimensions to facts) and is not a core component for implementing Type 2 SCD versioning within the dimension table itself. These three columns (A, B, E) align with Azure Synapse Analytics best practices for Type 2 SCDs, ensuring accurate historical tracking and efficient data management.

Author: LeetQuiz Editorial Team