You are designing a slowly changing dimension (SCD) for supplier data in an Azure Synapse Analytics dedicated SQL pool.
You plan to keep a record of changes to the available fields.
The supplier data contains the following columns.
Which three additional columns should you add to the data to create a Type 2 SCD? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
To create a Type 2 Slowly Changing Dimension (SCD) in Azure Synapse Analytics dedicated SQL pool for supplier data, three additional columns are necessary. First, a surrogate primary key is needed to uniquely identify each record when versioning; this ensures that each version of a record has a unique identifier, which is crucial in maintaining historical data. Next, the effective start date column indicates when a particular version of the supplier data becomes effective or active. Finally, the effective end date column indicates when a particular version of the supplier data becomes obsolete or inactive, and it is usually set to a specific value (e.g., '9999-12-31') to indicate the current active record. These columns together allow the management of historical changes to the supplier data and facilitate tracking of different versions over time.
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.
Correct
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!!!
Yes, because SupplierSystemID is unique. But Microsoft questions are terribly misleading here. People think that SupplierSystemID is business key because of Supplier in it. Also, there are some really not good and not sufficient examples on Learn. See https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types
I don't understand. 1) What in your opinion should then be the business key. Can you explain please. 2) SupplierSysteID ist uniqe in the source system. Is there a definition that the column need to be unique also in the DataWarehouse? If no, there ist the possibility to use it as business key. Am I wrong?
No you're not wrong, the unique identifier form the ERP system is the Business Key
Search for Business Keys in that page. and make sure you wear specs :D
https://www.mssqltips.com/sqlservertip/5602/why-surrogate-keys-are-needed-for-a-sql-server-data-warehouse/
https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types Under Type 2 SCD, it indicates that it must have a surrogate key
To create a Type 2 Slowly Changing Dimension (SCD) in Azure Synapse Analytics dedicated SQL pool for supplier data, you would need to add the following three additional columns: A. Surrogate Primary Key: This column is a unique identifier for each supplier record and is used as a primary key in the dimension table. B. Effective Start Date: This column indicates the date when a particular version of the supplier data becomes effective or active. E. Effective End Date: This column indicates the date when a particular version of the supplier data becomes obsolete or inactive. It is usually set to a specific value (e.g., '9999-12-31') to indicate the current active record. With these three additional columns, you can effectively manage historical changes to the supplier data and track different versions of each supplier record over time.
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
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.
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
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.
How come it can be anything other than ABE. ABE is the correct answer.
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; 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
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
Surrogate Key Effective Start Date Effective End Date
BCE is answer
The Supplier Key from the ERP system is the business key, hence we need a surrogate key