Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: SCD implementation depends on type; MERGE is the workhorse for atomicity. **SCD1**: UPDATE dim SET col = new_val WHERE key = X. **SCD2**: INSERT new row with effective_from; UPDATE current row's effective_to; set current_flag. **SCD3**: Add prev_col; on...
Architectural Logic: SCD implementation depends on type; MERGE is the workhorse for atomicity. SCD1: UPDATE dim SET col = new_val WHERE key = X. SCD2: INSERT new row with effective_from; UPDATE current row's effective_to; set current_flag. SCD3: Add prev_col; on change, shift current→prev. ETL Flow: (1) Extract changed records. (2) Lookup dimension. (3) Apply SCD logic (MERGE or INSERT/UPDATE). (4) Generate surrogate keys. Hash Comparison: For Type 2, hash attributes to detect change. Scalability: Use staging for change detection; batch MERGE. Cost: SCD2 increases row count; plan for history growth. Best Practice: Audit columns (created_at, updated_by); handle late-arriving dimensions.
Red Flag: SCD2 without effective_to or current_flag—incomplete. Pro-Move: Reference hash-based change detection; MERGE for atomicity; late-arrival handling (e.g., overlap logic).
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.