Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Natural keys**: Business identifiers (product_code, SSN); can change; composite; meaningful. **Surrogate keys**: System-generated (auto-increment, UUID); stable; single column; no business meaning. **Why surrogate for warehousing**: SCD Type 2 requires stable PK when natural...
Natural keys: Business identifiers (product_code, SSN); can change; composite; meaningful. Surrogate keys: System-generated (auto-increment, UUID); stable; single column; no business meaning. Why surrogate for warehousing: SCD Type 2 requires stable PK when natural key changes; simpler joins (single int vs. composite); hash-based dedup. Why keep natural: Validation, debugging, audit. Trade-off: Surrogate adds column; natural can leak PII. Cost: Surrogate join is faster (smaller key); SCD without surrogate needs complex merge logic.
Red Flag: Using natural keys for SCD Type 2—product code changes break history. Pro-Move: 'We use surrogate PK + natural key as attribute; hash(natural_key) for dedup in staging before upsert.'
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.