Type 1 SCD Data Loading Pattern

DP-600 By islever Editorial Last updated

Transform data

Short answer

AD Update rows when non-key attributes change and insert new records when the natural key is new.

Question

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

Key Takeaways

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.

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

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

This link opens the official vendor documentation in a new tab.

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.

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.

Discussion 12 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors, not of islever. The comments shown first are the most substantive of the thread — ranked by depth, community endorsement and relevance; expand the list below to read the rest.

From public community threads 12

  1. fabric1 👍 22
    Type 1 SCD does not preserve history, therefore no end dates for table entries exists. A and D are correct.
  2. sraakesh95 👍 4
    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.
  3. 282b85d 👍 2
    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.
  4. stilferx 👍 2
    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
  5. 2dc6125 👍 1
    I would go with A and C: Type1 use case to make changes or correction without involving histoa public discussion thread on this question data. D in my opinion is type 2 inserting new record with natural key for versioning.
Show 7 more comments
  1. Nefirs 👍 1
    AD - other options do not concern scd type 1 (overwrite) but scd type 2 (insert new row)
  2. woliveiras 👍 1
    Come on! A and D, for sure. Basic question about data warehouse (SCD)
  3. Ahmadpbi 👍 1
    AD because C is representing type2 SCD
  4. thuss 👍 3
    AD = SCD1, BC = SCD2
  5. Rakesh16 👍 1
    A & D is the answer
  6. Momoanwar 👍 1
    No history for scd1
  7. Joly 👍 1
    A and D

Reader comments & corrections

No reader corrections 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.

Fabric Deployment Pipeline Folder Structure Behavior

S-grade

Accessing OneLake Shortcuts in Fabric Notebooks

S-grade

Direct Lake Fallback to DirectQuery with Row-Level Security

S-grade

Displaying All Rows in Power BI Python Visuals

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