What is a slowly changing dimension, and how do SCD Types 1, 2 and 3 differ?

What the interviewer wants to hear

• Dimension attributes such as address or segment change over time
• Type 1 overwrites the value and keeps no history
• Type 2 adds a new row per change with validity dates and a surrogate key
• Type 3 keeps the previous value in an extra column
• Choice depends on whether reports need history at the time of the fact

Model answer

A slowly changing dimension is a dimension whose attributes change now and then, like a customer address or a product category. The question is how to handle history. Type 1 simply overwrites the old value, which is fine for corrections such as a typo, but you lose the old value. Type 2 inserts a new row for each change with valid_from, valid_to and an is_current flag, and each version gets its own surrogate key. Facts are linked to the version that was valid when the event happened, so sales by old city stay correct. Type 3 adds a column such as previous_region, which gives limited before and after history without extra rows. I choose based on the business need: if reports must reflect the state at the time of the transaction, I use Type 2; otherwise Type 1 keeps things simple.

More easy Data Engineer questions

• Describe a data pipeline you built or would build, from source to dashboard.
• What is the difference between an OLTP database and an OLAP data warehouse?
• Explain a star schema and why it is used in data warehouses.
• What data quality checks would you add to a pipeline?
• Why use an orchestration tool like Airflow instead of cron jobs?
• Why would you store analytics data as Parquet rather than CSV or JSON?
• What is change data capture (CDC), and why do data teams use it?
• Explain Kafka topics, partitions, offsets and consumer groups to a new teammate.
• What does dbt do in a modern data stack, and what are its main features?