Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: SCD tracks dimension attribute changes; type choice affects history and storage. **Type 1**: Overwrite; no history. **Type 2**: New row; effective dates; current flag; full history. **Type 3**: Previous value column; limited history. **Type 4**: History...
Architectural Logic: SCD tracks dimension attribute changes; type choice affects history and storage. Type 1: Overwrite; no history. Type 2: New row; effective dates; current flag; full history. Type 3: Previous value column; limited history. Type 4: History table. Type 6: Hybrid (1+2+3). Most Common: Type 2 for customer, product dimensions. Implementation: MERGE or INSERT/UPDATE; change detection via hash. Scalability: Type 2 row growth; surrogate keys. Best Practice: Surrogate keys; effective_from, effective_to; current_flag; handle late arrivals; choose type by business requirement.
Red Flag: Type 1 for customer address—loses audit trail. Pro-Move: Type 2 for dimensions needing history; mention surrogate key generation; late-arrival logic (effective date overlap).
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.