The definitive question bank of 30 technical interview questions asked in Big Tech and Indian GCC data engineering rounds: data skew in PySpark, Kafka partition rebalancing, SCD Type 2 in SQL, and Airflow backfilling.
Technical interviews for Data Engineering positions at Amazon, Walmart Global Tech, Swiggy, Flipkart, and leading analytics consultancies in Gurugram and Bengaluru are notoriously rigorous. Interviewers focus heavily on system failure modes: What happens when partitions skew? How do you guarantee exactly-once processing? How do you update dimension history without locking tables? Here is the curated master question bank with battle-tested production solutions.
Q1: How do you identify and resolve Data Skew in PySpark?
Answer: Data skew occurs when an uneven key distribution causes one executor to process 90% of the data while other workers sit idle. Symptoms: 99% of tasks finish quickly while the last task hangs indefinitely. Solution: Key Salting (adding a random suffix like key_0, key_1 to the join key to evenly distribute rows across executors) or Broadcast Joins.
Q2: What is the difference between `repartition()` and `coalesce()`?
Answer: `repartition()` triggers a full network shuffle and can either increase or decrease partition count evenly. `coalesce()` avoids a full shuffle by merging existing adjacent partitions on the same worker, but can only decrease partition count.
# PySpark Key Salting Technique to Fix Severe Data Skew
from pyspark.sql.functions import concat, lit, rand, floor, col
# Salt the skewed large table with random integer (0 to 4)
skewed_large_df = large_df.withColumn("salt", floor(rand() * 5)) \
.withColumn("salted_key", concat(col("join_key"), lit("_"), col("salt")))
# Replicate the small lookup table 5 times with matching salts
# This distributes the join load evenly across all Spark executors without bottlenecks.Q: How do you implement Slowly Changing Dimension Type 2 (SCD2) to preserve historical record changes in SQL?
Answer: SCD2 retains the full history of dimensional changes by storing multiple rows per entity with `start_date`, `end_date`, and an `is_current` active flag.
-- SCD Type 2 Upsert Pattern using SQL MERGE
MERGE INTO dim_customers AS target
USING staging_customers AS source
ON target.customer_id = source.customer_id AND target.is_current = TRUE
WHEN MATCHED AND (target.address <> source.address OR target.city <> source.city) THEN
-- Expire previous active record
UPDATE SET target.end_date = CURRENT_TIMESTAMP, target.is_current = FALSE;
-- Followed by inserting the new updated version
INSERT INTO dim_customers (customer_id, address, city, start_date, end_date, is_current)
SELECT customer_id, address, city, CURRENT_TIMESTAMP, NULL, TRUE
FROM staging_customers;Q1: How does Kafka guarantee message ordering?
Answer: Kafka guarantees message ordering ONLY within a single partition, never across multiple partitions of a topic. To maintain ordering for an entity (e.g., all events for customer X), specify the entity ID as the message key during publishing.
Q2: What causes Kafka Consumer Group Rebalancing, and how do you prevent rebalance storms?
Answer: Rebalancing triggers when a consumer joins/leaves or fails to send heartbeats within `session.timeout.ms`, or when message processing exceeds `max.poll.interval.ms`. Prevent rebalances by keeping poll batches smaller (`max.poll.records`) and processing messages asynchronously.
Thorough preparation on real failure scenarios is the key to cracking Tier-1 Data Engineering interviews. Datadrix’s placement coaching team conducts 1-on-1 technical mock interviews with former hiring managers from top Indian engineering firms.
Join Datadrix’s live cohort with 1:1 mentor code reviews, capstone projects, and 400+ hiring partner referrals.
What to expect from a high-impact Data Engineering course in Delhi NCR: mastering PySpark, Kafka, Airflow, and AWS on real cloud clusters, with direct hiring connections in Gurugram Cyber City and Noida Sector 62/135.
Build a cloud-native serverless data analytics platform on Amazon Web Services: ingesting raw logs into S3, cataloging with AWS Glue Crawlers, querying ad-hoc with Athena, and loading into Amazon Redshift.
End-to-end tutorial assembling a streaming data pipeline: streaming financial transactions through Apache Kafka, processing with PySpark Structured Streaming, and sinking enriched records into PostgreSQL.