Data Engineer Interview Questions

Design a data pipeline to ingest CDC (Change Data Capture) events from a MySQL database into a Snowflake data warehouse in near real-time.

You need to run a DAG that processes 10,000 files daily — one task per file. How do you design this in Airflow without creating 10,000 static tasks?

Your PySpark job has data skew on a join key (user_id) where 1% of users account for 60% of the data. How do you handle it?

You have a 10TB Snowflake table queried daily by 50 analysts with very different filter patterns. How do you optimize it?

You have a Spark job that ingests 500GB of clickstream data daily. It is failing with OOM errors on the executor. How do you debug and fix it?

Your Snowflake query is slow even with a large warehouse. How do you investigate and optimize it?

Your team discovers that 20% of user_id values in the orders table do not match any record in the users table. How do you handle this as a data engineer?

You have a streaming PySpark job reading from Kafka that processes IoT sensor events. Events can arrive up to 10 minutes late. How do you handle late data correctly?

Your Airflow DAG that runs at midnight has been running for 6 hours and is still not complete. SLA breached. What do you do and how do you prevent it next time?

Your pipeline SLA requires data to be available in the warehouse by 6 AM. Yesterday it missed at 7:30 AM. Walk me through your incident response.

How does Airflow handle backfilling? Design a DAG that safely backfills 90 days of missing data without overloading the database.

Explain Snowflake multi-cluster architecture. How does it handle concurrency, and what is the difference between a Virtual Warehouse and the Cloud Services layer?