You need to create a data loading pattern for a Type 1 slowly changing dimension (SCD).
Which two actions should you include in the process? Each correct answer presents part of the solution.
NOTE: Each correct answer is worth one point.
You need to create a data loading pattern for a Type 1 slowly changing dimension (SCD).
Which two actions should you include in the process? Each correct answer presents part of the solution.
NOTE: Each correct answer is worth one point.
In a Type 1 slowly changing dimension (SCD), the main actions are to update existing rows when non-key attributes have changed, and to insert new records when the natural key does not yet exist in the table. Type 1 SCDs do not maintain historical data; they simply overwrite old data with new data. This means that updating the effective end date of rows is not relevant for a Type 1 SCD, as no history is preserved. The correct actions are updating rows when non-key attributes change and inserting new records when a new natural key is encountered.
Type 1 SCD does not preserve history, therefore no end dates for table entries exists. A and D are correct.
AD makes sense given the type of SCD as no history is maintained and any updates on the columns are taken care of by replacing the values then and there. But, still a bit skeptical about C, since that is a part of SCD too in case we have certain audit columns that we would have to update. If the end_date is an audit column to update the date and time of last entry into a record, then, it would be required to be updated too.
IMHO, A & D is good. Taking into account this: A Type 1 SCD always reflects the latest values, and when changes in source data are detected, the dimension table data is overwritten. Link: https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types
AD = SCD1, BC = SCD2
AD because C is representing type2 SCD
Come on! A and D, for sure. Basic question about data warehouse (SCD)
A. Update rows when the non-key attributes have changed: In a Type 1 SCD, when a change is detected in any of the non-key attributes of an existing row, the current row is updated with the new values. This type of SCD does not keep any historical data; it simply overwrites the old data with the new data. D. Insert new records when the natural key is a new value in the table: When a new record (with a new natural key) is encountered that does not already exist in the dimension table, it is inserted as a new row. This is necessary to ensure that all new entities are captured in the dimension.
I would go with A and C: Type1 use case to make changes or correction without involving histohttps://www.examtopics.com/exams/microsoft/dp-600/view/13/#ric data. D in my opinion is type 2 inserting new record with natural key for versioning.
AD - other options do not concern scd type 1 (overwrite) but scd type 2 (insert new row)
No history for scd1