Type 1 SCD Data Loading Pattern

Transform data
Answer Correct answer: A, D — Update rows when non-key attributes change and insert new records when the natural key is new.

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.

  1. Update rows when the non-key attributes have changed. Correct Answer
  2. Insert new rows when the natural key exists in the dimension table, and the non-key attribute values have changed.
  3. Update the effective end date of rows when the non-key attribute values have changed.
  4. Insert new records when the natural key is a new value in the table. Correct Answer

Community Votes

AD
100%

100% of anonymous learners picked answer AD. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

Type 1 SCD tests the understanding of overwrite behavior, trapping learners who confuse it with Type 2 SCD history preservation.

A Type 1 slowly changing dimension (SCD) overwrites existing data without preserving history. This guide confirms that updating changed rows and inserting new keys are the correct actions for a Type 1 SCD.

Choosing B or C, which belong to Type 2 SCD, because learners confuse the mechanisms for preserving historical records.

Community Discussion (12 comments)

fabric1 👍 22 Selected: AD
Type 1 SCD does not preserve history, therefore no end dates for table entries exists. A and D are correct.
Rakesh16 👍 1 Selected: AD
A & D is the answer
Joly 👍 1
A and D
Ahmadpbi 👍 1
AD because C is representing type2 SCD
woliveiras 👍 1 Selected: AD
Come on! A and D, for sure. Basic question about data warehouse (SCD)
282b85d 👍 2 Selected: AD
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.
2dc6125 👍 1
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.
stilferx 👍 2 Selected: AD
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
Nefirs 👍 1 Selected: AD
AD - other options do not concern scd type 1 (overwrite) but scd type 2 (insert new row)
sraakesh95 👍 4 Selected: AD
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.
thuss 👍 3 Selected: AD
AD = SCD1, BC = SCD2
Momoanwar 👍 1 Selected: AD
No history for scd1

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Expert Analysis

Why the Answer Is Correct

For a Type 1 SCD, the system does not preserve historical data; it simply overwrites existing attributes with the new values. Therefore, option A is correct because it describes the update-in-place behavior. Option D is also correct because any new natural key requires a new record to be inserted into the dimension table, a standard step for any SCD type.

Why the Other Options Are Wrong

Options B and C describe behaviors specific to a Type 2 SCD, which preserves historical data by maintaining multiple versions of a record. Option B involves inserting a new row when an attribute changes, creating a new version rather than overwriting. Option C involves updating an effective end date to expire the old record, which is a mechanism used in Type 2 SCD to track validity periods.

Community Comment Notes

The community overwhelmingly agrees that options A and D represent Type 1 SCD, while B and C represent Type 2 SCD. As user thuss succinctly put it, "AD = SCD1, BC = SCD2". Another commenter noted that "Type 1 SCD does not preserve history, therefore no end dates for table entries exists", which directly eliminates option C.

Official Reference

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

Exam Strategy

Memorize the core distinction between SCD types: Type 1 overwrites (no history), Type 2 adds new rows (history). When you see "end date" or "insert new row for attribute change", immediately think Type 2 and eliminate those options for Type 1 questions.

Related Analysis

You have a Fabric tenant that contains a semantic model. The

S-Grade

Note: This question is part of a series of questions that pr

S-Grade

You have a Fabric tenant that contains a warehouse. You are

S-Grade

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide