Exam DP-203 topic 1 question 72 discussion

Surrogate is already there as customerkey column

I think, there is already a column called InsertedDate, therefore E is not necessary. So we just need another column to track the end date, which is B. And RowID should be a surrogate key in this case.

Its a bad question. Looking at the table creation query CustomerKey is not clearly a surrogate key due to the lack of IDENTITY or NEWID() etc. You cannot have a SCD 2 table without a surrogate key. So assuming CustomerKey is not a surrogate key (due to the evidence provided in the query) you need that -> D We only have one spot left. I would prefer to have both effective start and end date but if we had to choose the choice is effectiveEndDate because InsertedDate can act as the defacto effectiveStartDate -> B If you can prove to me the CustomerKey is a surrogate key I will change my mind. The code says no. And before you tell me CustomerSourceId is the business key I would say the CustomerSourceId in the context of a DimCustomer table likely represents an identifier used to link the customer record to its original source system. But its really all conjecture...go with the code and the code says that there ain't no surrogate key present.

Answer: BE. Link - https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

Correct Answer: BE

wrong answer by Xam topic correct answer with solution is-: [EffectiveStartDate] [datetime] NOT NULL: This column tracks when the current record started being valid, helping to define the validity period of the data. [EffectiveEndDate] [datetime] NOT NULL: This column marks the end of the record's validity period when a new version of the data is inserted. These two columns will allow you to differentiate between active and historical records and handle Type 2 SCD effectively. BE

We need a surrogate key. The Inserteddate is NOT NULL, so will act as EffectiveStartDate. However, ModifiedDate is also NOT NULL, so it cannot work as EffectiveEndDate because the current record must have NULL for its EffectiveEndDate. So, we need EffectiveEndDate. Answer: D,E.

the right answer depends on a few topics not clear: - CustumerKey may be a surrogate key? - InsertedDate may be a the "EffectiveStartDate" - modifiedDate may be the "EffectiveEndDate"

The subrogatekey is already

B --> end date is missing, and required by SCD2 D --> (not E), RowID is required since in SCD2 you're adding the same CustomerID twice, even with a different end date. So, you need a way to uniquely identify a row in the table, that's going no longer to be the customer identifier in general.

It should be BD. If CustomerKey was surrogate key then IDENTITY should have been mentioned in Column definition.

There is already a hash key that serves as the surrogate, if I'm not mistaken. Inserted and modified are probably dates from the source data, not from the work being done here, so you need to add the start/end dates.

The date of insertion and the expiration date from when to when is something else. You can insert data now, but either with future validity or with past validity (correcting errors, for example). So options : BE

B and E

B and D we need a unique row identifier

B and D ,its a star schema on which has a fact table include a customerID property.

B and D makes more sense, since inserted date is there already