The experience pushed me to think beyond just tools and queries.

Every question was designed to test not just what I knew, but how I applied it in high-scale, real-time environments.

Round 1: SQL & Data Manipulation

The first round was all about SQL performance, data transformations, and query optimizations.

1. Write a query to find the top 3 customers with the highest transaction volume in the last 90 days. Optimize it for a billion-row table.
2. You have a table with duplicate records. Write an efficient query to remove duplicates while keeping the latest record based on timestamp.
3. How would you pivot rows into columns in SQL and vice versa?
4. Explain the difference between ROW_NUMBER, RANK, and DENSE_RANK. When would you use each?
5. You need to join two large tables with billions of rows. How do you minimize shuffle and optimize performance?

Round 2: System Design & Pipeline Architecture

1. Design a data pipeline to ingest, process, and store 100 million events per day. Ensure it can handle spikes during peak hours.
2. How would you ensure data consistency between Kafka and a downstream data lake?
3. If one stage of your ETL pipeline fails, how would you ensure no data loss and reprocess only the failed records?
4. Design a pipeline that supports both batch and streaming ingestion for real-time analytics.
5. How would you partition a table in a data warehouse to optimize query performance for time-series data?

Round 3: Python & Data Engineering Code

1. Write a Python script to read data from an S3 bucket and load it into a PostgreSQL database efficiently.
2. How would you handle schema evolution dynamically while processing JSON data from Kafka?
3. Given a PySpark DataFrame with billions of rows, how would you calculate running totals without introducing a shuffle?
4. Write a Python function to detect anomalies in streaming data and alert based on dynamic thresholds.
5. How would you implement deduplication logic in a PySpark streaming pipeline?

Round 4: Troubleshooting & Performance Optimization

1. Your Spark job is running out of memory. How do you diagnose and fix it?
2. You notice high consumer lag in a Kafka topic. How do you reduce lag without losing events?
3. A query that ran in seconds is now taking hours. What’s your step-by-step debugging approach?
4. How do you handle data skew in Spark? What are the best techniques for large joins?
5. If your ETL pipeline is processing duplicate records during a reprocessing event, how do you eliminate duplicates effectively?