How would you build a pipeline that maintains an SCD Type 2 customer dimension from a daily source extract?

What the interviewer wants to hear

• Detect changes by comparing tracked attributes, often with a hash
• Close the current row by setting valid_to and is_current false
• Insert a new version with a new surrogate key and valid_from
• Insert brand-new customers; decide how to handle deletes
• Make it idempotent and use half-open date ranges
• Test for one current row per key and no overlapping ranges

Model answer

I land the daily extract in staging, then compute a hash of the attributes I track, such as address and segment. I compare it with the current row in the dimension for each business key. Where the hash differs, a MERGE or two statements in one transaction close the old row by setting valid_to to the change time and is_current to false, then insert a new row with a new surrogate key, valid_from equal to the change time and valid_to set to a far-future date. Brand-new keys are inserted as current rows, and I agree with the business whether deletes close the row or set a deleted flag. I use half-open ranges so joins never match two versions, and I make the load rerunnable for the same date. Tests check exactly one current row per key and no overlapping ranges. In dbt, snapshots implement this pattern for me.

More medium Data Engineer questions

• Your source system sometimes sends the same event twice. How do you handle duplicates in your pipeline?
• A dbt model that used to take 5 minutes now takes 2 hours. How do you troubleshoot it?
• A stakeholder asks for a "real-time" dashboard. How do you decide whether to build streaming?
• Your Spark job fails with executor out-of-memory errors. What do you check and change?
• How would you design an incremental load for a large orders table that also receives updates to old orders?
• Your main nightly Airflow DAG failed at 3 a.m. and dashboards are due at 8 a.m. Walk me through how you handle it.
• When would you choose a lakehouse with Delta Lake or Iceberg over a cloud warehouse like Snowflake or BigQuery, or use both?
• A Spark job that used to take 20 minutes now takes 2 hours. How do you investigate?
• Product engineers keep breaking your pipelines by changing database columns without warning. How would you fix this?