Which SCD type uses a separate history table and a surrogate key to map to original and historic data?

Which SCD type uses a separate history table and a surrogate key to map to original and historic data?

• A. Type 0 SCD
• B. Type 2 SCD
• C. Type 4 SCD
• D. Type 6 SCD

Answer: (C)

Type 4 uses a separate history table and a surrogate key to link current and historic data. In this approach, the main dimension table holds the current values for each business key, identified by a stable surrogate key. All prior versions of that record are stored in a separate history table, which also uses the same surrogate key to tie back to the current row. The history table may include timestamps or version indicators to show when each past version was valid. When a change occurs, the new values go to the current table, and the old values are written to the history table with the corresponding surrogate key. This separation keeps the current view clean in the main table while preserving a complete history that can be queried via the surrogate key linkage.

Type 4 uses a separate history table and a surrogate key to link current and historic data. In this approach, the main dimension table holds the current values for each business key, identified by a stable surrogate key. All prior versions of that record are stored in a separate history table, which also uses the same surrogate key to tie back to the current row. The history table may include timestamps or version indicators to show when each past version was valid. When a change occurs, the new values go to the current table, and the old values are written to the history table with the corresponding surrogate key. This separation keeps the current view clean in the main table while preserving a complete history that can be queried via the surrogate key linkage.