Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**SCD Types**: Type 1—overwrite, no history; Type 2—effective_from/to, is_current; Type 3—previous value column. Type 2 most common. **Implementation**: On change: set effective_to=now() on current; insert new with effective_from=now(), is_current=1. Query: WHERE is_current=1...
SCD Types: Type 1—overwrite, no history; Type 2—effective_from/to, is_current; Type 3—previous value column. Type 2 most common.
Implementation: On change: set effective_to=now() on current; insert new with effective_from=now(), is_current=1. Query: WHERE is_current=1 or effective_from<=@date AND (effective_to IS NULL OR effective_to>@date). Delta merge/upsert. Include audit columns.
Pro-Move: 'We use Type 2 for customer dimension; point-in-time joins for historical accuracy in reporting.'
According to DataEngPrep.tech, this is one of the most frequently asked General/Other interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.