What is Slowly Changing Dimension Type II, and how is it represented in Clarity?

What is Slowly Changing Dimension Type II, and how is it represented in Clarity?

• A. SCD Type II preserves history by creating new surrogate records with valid_from and valid_to dates.
• B. SCD Type II deletes old records to preserve only the latest state.
• C. SCD Type II uses a single current record with a version number.
• D. SCD Type II duplicates the entire table into a separate history table.

Answer: (A)

Type II keeps a full history by adding new surrogate rows whenever a dimension member changes. Each version becomes its own row with its own surrogate key, and the time period during which that version is valid is captured with valid_from and valid_to dates. In practice, when a change occurs, you insert a new row for the updated version (with a new surrogate key and a new valid_from date) and adjust the previous version’s valid_to date to end just before that date. A current version is usually identified by a valid_to date set far in the future or by a current flag. In Clarity, this means modeling the dimension so that multiple rows can exist for a single natural member, each representing a different point in time. The surrogate key uniquely identifies each version, and the validity window (valid_from to valid_to) defines what period that version applies to. This approach preserves history in a single, coherent structure rather than overwriting or duplicating the entire table.

Type II keeps a full history by adding new surrogate rows whenever a dimension member changes. Each version becomes its own row with its own surrogate key, and the time period during which that version is valid is captured with valid_from and valid_to dates. In practice, when a change occurs, you insert a new row for the updated version (with a new surrogate key and a new valid_from date) and adjust the previous version’s valid_to date to end just before that date. A current version is usually identified by a valid_to date set far in the future or by a current flag.

In Clarity, this means modeling the dimension so that multiple rows can exist for a single natural member, each representing a different point in time. The surrogate key uniquely identifies each version, and the validity window (valid_from to valid_to) defines what period that version applies to. This approach preserves history in a single, coherent structure rather than overwriting or duplicating the entire table.