Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Surrogate keys are system-generated unique IDs (e.g., auto-increment) for dimension rows. **Why they matter**: Natural keys change (customer ID rebrand, source system merge); surrogates are stable. (1) **SCD Type 2**: Same natural key, multiple rows (versions)—surrogate...
Surrogate keys are system-generated unique IDs (e.g., auto-increment) for dimension rows. Why they matter: Natural keys change (customer ID rebrand, source system merge); surrogates are stable. (1) SCD Type 2: Same natural key, multiple rows (versions)—surrogate distinguishes them. (2) Join performance: Integer joins are faster than string/UUID. (3) Integration: Consistent IDs across sources with different natural key formats. Scalability trade-offs: Surrogate generation must be deterministic in distributed ETL (hash-based or sequence). Cost implications: Smaller keys = less storage and faster joins. Best practice: Use in all dimension tables; generate in ETL; never reuse—even for "same" entity after change.
Red Flag: Joining facts to dimensions on natural keys that can change. Pro-Move: 'We use surrogate keys; SCD2 merge uses MD5 hash for change detection.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.