In a star schema, what is the primary purpose of a surrogate key in a dimension table?

In a star schema, what is the primary purpose of a surrogate key in a dimension table?

• A. Link to source transactional keys
• B. Maintain historic versions independent of source keys
• C. Improve query performance by indexing the natural key
• D. Enforce referential integrity in source system

Answer: (B)

Surrogate keys in a dimension table provide a stable, warehouse-only identifier that is independent of the source system’s keys. This separation lets you capture how a dimension looks over time without being tied to changes in the source’s business keys. The main benefit is historization: when a dimension member changes (for example, a customer’s address or classification), you assign a new surrogate key to a new version of that member while keeping the old row intact. The fact table continues to reference the appropriate surrogate key, preserving accurate history for all past analyses. By decoupling from source keys, you avoid cascading updates to historical data and can implement slowly changing dimensions cleanly. Other options don’t fit this primary purpose: linking to source transactional keys reintroduces volatility and makes history hard to maintain; indexing a natural key is a performance detail, not the core reason for surrogate keys; and enforcing referential integrity in the source system is outside what the warehouse key design aims to do.

Surrogate keys in a dimension table provide a stable, warehouse-only identifier that is independent of the source system’s keys. This separation lets you capture how a dimension looks over time without being tied to changes in the source’s business keys. The main benefit is historization: when a dimension member changes (for example, a customer’s address or classification), you assign a new surrogate key to a new version of that member while keeping the old row intact. The fact table continues to reference the appropriate surrogate key, preserving accurate history for all past analyses. By decoupling from source keys, you avoid cascading updates to historical data and can implement slowly changing dimensions cleanly.

Other options don’t fit this primary purpose: linking to source transactional keys reintroduces volatility and makes history hard to maintain; indexing a natural key is a performance detail, not the core reason for surrogate keys; and enforcing referential integrity in the source system is outside what the warehouse key design aims to do.