Exam DP-203 topic 1 question 34 discussion

The answer is ABE. A type 2 SCD requires a surrogate key to uniquely identify each record when versioning. See https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types under SCD Type 2 “ the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member.” A business key is already part of this table - SupplierSystemID. The column is derived from the source data.

WHAT ARE YOU GUYS TALKING ABOUT??? You are really misleading other people!!! No issue with the provided answer. Should be BCE!!! Check this out: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation?view=sql-server-ver15 "The Slowly Changing Dimension transformation requires at least one business key column." [Surrogate key] is not mentioned in this Microsoft documentation AT ALL!!!

The answer is ABE. A type 2 SCD requires a surrogate key to uniquely identify each record when versioning.

Why not D: Last Modified Date ??????

Correct Answer: ABE

In my humble opinion the SupplierSystemID column is the business key: it remains an IDENTIFY column to represent the surrogate key

Copilot Sent by Copilot: To create a Type 2 Slowly Changing Dimension (SCD) in Azure Synapse Analytics, you should add the following three columns: A. Surrogate primary key This uniquely identifies each version of a record, allowing you to track changes over time. B. Effective start date This indicates when a particular version of the record became active. E. Effective end date This indicates when a particular version of the record was superseded by a new version. These columns help maintain historical data by creating new records for each change, rather than updating existing records. This way, you can track the history of changes to the supplier data.

The surrogate key is a must, so the proposed answer is incorrect in my view. The start date and end date for each record have always been an over-design in my view. This means that you have to maintain both records. The latest record of the set with the same business key is the latest record, and you only need one date, the valid from date, in that row. I have long stopped versioning with valid from and valid to dates. You can easily use a simple lag function to grab a previous valid to date for the previous record, which is then the valid from date for the latest record. So why still use both dates?

The answer is ABE, you need subrogate key,

ABE SCD Type 2 doesn't work with business key it requires a surrogate key to uniquely identify each record when versioning., for example - an employee can have a unique key(i.e. Business key) in the organization, but when you create SCD Type 2, you need to preserve historical data, hence then even a unique key of an Employee can't uniquely different the record but Surrogate key will do it.

ABE; SupplierSystemId is a business key

I think ABE, see as below: "A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member. It also includes columns that define the date range validity of the version (for example, StartDate and EndDate) and possibly a flag column (for example, IsCurrent) to easily filter by current dimension members." https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

Surrogate primary key (ensure to include edited records) - A, effective start date - B, effective end date - E

Should be ABE. According to a Microsoft learning SCD Type 2 requires a surrogate key. "A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member." https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

The surrogate key is an artificially generated key, usually an incremental or globally unique identifier. Business key derived from the actual business data and has business meaning. Examples could include a product code, customer ID, employee ID, or any other identifier that has relevance in the business context. I think this is it's business key Effective end and start date

Selected Answer: ABE The answer is ABE. A type 2 SCD requires a surrogate key to uniquely identify each record when versioning. See https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types under SCD Type 2 “ the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member.” A business key is already part of this table - SupplierSystemID. The column is derived from the source data.

I'm working now more then 13years with this stuff, ABE is correct. CHOPIN is wrong. a 2 SCD needs a unique ID, this is the surrogate key, besides, in the table given, there is already a business key , is the first column A business key is NOT unique in an 2 SCD hallo