A dbt model that used to take 5 minutes now takes 2 hours. How do you troubleshoot it?
What the interviewer wants to hear
- Check what changed: data volume, code, warehouse size, concurrency
- Read the query profile or explain plan
- Look for exploding joins, missing filters or spills
- Use partition pruning and clustering
- Make the model incremental if it rebuilds full history
Model answer
I start with what changed: a recent code commit, much more data, a smaller warehouse, or other heavy jobs running at the same time. Then I open the query profile in Snowflake or BigQuery to see which step takes the time and how many rows go in and out. Common causes are a join that explodes because a key is no longer unique, a filter that stopped pruning partitions because a function wraps the date column, or memory spilling to disk. I check the join keys for duplicates and fix the grain. I make sure filters use the partition or cluster column directly. If the model rebuilds years of history every run, I change it to an incremental model that only processes new or changed rows. After the fix I add a test or alert on run time, so a slowdown is caught early.
Can you answer it under time pressure?
Take a timed medium round and get scored on every answer.
More medium Data Engineer questions
- Your source system sometimes sends the same event twice. How do you handle duplicates in your pipeline?
- 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.
- How would you build a pipeline that maintains an SCD Type 2 customer dimension from a daily source extract?
- 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?