Data Engineering interview question · Most asked
Explain slowly changing dimensions, especially Type 1 and Type 2.
Summary
Slowly changing dimensions describe how dimension attributes keep history. Type 1 overwrites values (no history); Type 2 inserts a new row per change with a surrogate key and validity range, so facts join to the version valid at the time of the event.
Longer Answer
Use Type 1 for corrections and attributes whose history doesn't matter, Type 2 for attributes that affect historical reports (region, segment). Type 2 tables need tests for one current row per key and non-overlapping ranges. dbt snapshots implement Type 2 automatically.
Key Points
- Type 1: overwrite.
- Type 2: new row with valid_from/valid_to.
- Choose per attribute with the business.
- dbt snapshots build Type 2.
Deep dive: ETL and Data Pipelines: Slowly Changing Dimensions
Explanation
What interviewers look for
Correct mechanics and the business decision behind the type.
More most-asked Data Engineering questions
- Batch or streaming: how do you decide?
- What is the difference between ETL and ELT, and when would you choose each?
- What is change data capture (CDC), and why is log-based CDC preferred over polling?
- What does it mean for a data pipeline to be idempotent, and how do you achieve it?
- How do you implement incremental extraction, and what can go wrong?
- How do you deduplicate events, and what does 'exactly-once' really mean?
- What is a star schema, and why is it used in data warehouses?
- What is the grain of a table, and what is join fan-out?