Why are surrogate keys preferred in data warehouses for dimension tables?

• A. They speed up data loading by bypassing constraints
• B. They provide stable, compact keys, avoid changing natural keys, and keep history consistent when natural keys change
• C. They enforce referential integrity by themselves
• D. They always replace primary keys in the warehouse

Answer: (B)

Surrogate keys provide a stable, compact identifier for dimension rows that is separate from the business data used as the natural key. In a data warehouse, the natural business key can change due to corrections, rebranding, mergers, or redefinitions. If you relied on that natural key as the primary key, every change would force updates across many rows in related tables, and historical accuracy could be compromised. A surrogate key, generated by the ETL process, remains fixed for a given dimension row even when its business attributes change. This stability makes it easy to preserve history, especially when implementing slowly changing dimensions, because each version can be identified by its surrogate key while the natural key can evolve without breaking references. Surrogate keys are also typically small integers, which makes joins to fact tables faster and storage more efficient. They also help you map natural keys to surrogate keys during loading without propagating business-key changes into existing relationships. Note that surrogate keys do not automatically enforce referential integrity by themselves—you still define foreign key constraints to ensure valid references—but they are commonly used as the primary key for dimension tables, providing a robust foundation for scalable, time-aware data warehousing.