You have an Azure Synapse Analytics dedicated SQL pool named pool1.
You plan to implement a star schema in pool and create a new table named DimCustomer by using the following code.
You need to ensure that DimCustomer has the necessary columns to support a Type 2 slowly changing dimension (SCD).
Which two columns should you add? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
To support a Type 2 slowly changing dimension (SCD), which tracks historical changes by creating multiple records with different start and end dates, you need both an EffectiveStartDate and an EffectiveEndDate to identify the period during which each record version is active. The EffectiveStartDate indicates when a particular version of the record became valid, and the EffectiveEndDate indicates when it ceased to be valid. This allows for accurate historical tracking of changes to the data over time. The other columns listed do not specifically support the functionality required for Type 2 SCD. Therefore, the two columns that should be added are EffectiveStartDate and EffectiveEndDate.
Surrogate is already there as customerkey column
that's the business key, not the surrogate key. If a new record is generated, there would be a duplicate key. SK is necessary to identify the record
No, the 'CustomerKey' is the Surrogate Key. Moreover, a Business Key also already exists in DimCustomer table by the name 'CustomerSourceID'. So, B&E are the correct options.
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.
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 --> 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.
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
I'm confused by the NOT NULL for the EffectiveEndDate. What value is this column going to hold for the row that holds the current information ?
OK seen elsewhere that typically would be e.g. Dec-31-9999
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.
B and D we need a unique row identifier
If RowID was the surrogate, wouldn't it be an IDENTITY column? Therefore, it has to be B and E. Right? Please explain if this doesn't make sense make sense
https://www.sqlshack.com/implementing-slowly-changing-dimensions-scds-in-data-warehouses/ "For the SCD Type 2, we need to include three more attributes such as StartDate, EndDate and IsCurrent" IsCurrentRow is already present! ... ;-) CustomerKey (in reality is the RowID that many guys wants to add here), effectiveEndDate will probably set to: 31.12.9999, (to justify the not null). My final answer wil lbe : B and E.
what is the answer ?
For SCD Type 2 we need record valid from and to date i.e effective date to identify latest row for that id.
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 and E
B and D ,its a star schema on which has a fact table include a customerID property.
after think twice ,B&E
B and D makes more sense, since inserted date is there already