Role : Data Engineer
CTC : 25 LPA
Exp : 5+ years
Difficulty Level : MEDIUM
1️⃣ PySpark: You have a dataset with user_id, timestamp, and transaction_amount.
Write PySpark code to calculate each user’s average transaction in the last 30 days using window functions.
2️⃣ SQL: Write a query to find the second highest salary in each department and handle cases where there’s only one employee.
3️⃣ Describe your end-to-end data pipeline — ingestion, transformation, storage, and orchestration tools used.
4️⃣ How do you optimize Spark jobs that are causing OOM (Out of Memory) errors?
5️⃣ How would you design a pipeline to ingest incremental data from S3 to Snowflake/BigQuery daily?
6️⃣ Explain how Airflow manages dependencies between tasks in your project.
7️⃣ What data validation checks do you perform before loading data to the warehouse?
8️⃣ What’s the difference between repartition() and coalesce() in PySpark and when to use each?
9️⃣ How do you handle schema drift in your pipeline when the source CSV file structure changes?
🔟 Describe a failed pipeline scenario from your project and how you debugged it.
11️⃣ Design a data lake architecture for a streaming + batch use case.
12️⃣ How would you handle late-arriving data in a real-time Kafka-to-BigQuery pipeline?
13️⃣ Explain Spark shuffle and how to minimize its cost.
14️⃣ Difference between broadcast join and sort-merge join — which one performs better for skewed data?
15️⃣ How would you monitor data quality and freshness in production pipelines?
16️⃣ What’s one project where you faced conflicting priorities — how did you manage deadlines?
17️⃣ How do you collaborate with data analysts and product owners when defining transformation logic?
18️⃣ Describe a time when a pipeline went down in production — what was your escalation and recovery process?
19️⃣ How do you balance automation vs manual checks in critical pipelines?
20️⃣ What’s your career growth plan in Data Engineering — which tools or platforms are you mastering next?