Five rounds in two weeks
TiffinGo has opened the full-time data engineer role, and Kabir is one of 14 candidates shortlisted. The recruiter emails the loop: an online test, two technical rounds, a manager round and HR, spread over two weeks. Kabir knows the company’s pipelines better than any outsider, but he has never sat a real interview.
Sir, I’ve worked on these pipelines for months. Why am I nervous about the questions?
Because doing and explaining are different skills, Kabir. Interviews test whether you can reason out loud: clarify the question, state assumptions, solve step by step, and discuss trade-offs.
Are the questions very different at each company?
The wording changes, Riya, but the core is stable: SQL with joins and window functions, Python basics, big data concepts, Spark internals, Kafka, data modelling, pipeline design and your own project. This lesson collects the most common ones with model answers.
Should I memorise the answers?
Understand them. Interviewers ask follow-ups: “why?”, “what if the data is ten times bigger?”, “what breaks first?” Today you’ll practise SQL questions on real data, watch how an interviewer scores an answer, handle a “why is this Spark job slow?” question and design a pipeline in 45 minutes.
The data engineering interview
A technical interview for data engineering evaluates a candidate’s ability to query and transform data (SQL), write correct code (usually Python), explain distributed data processing and storage concepts, design reliable pipelines and platforms under constraints, and communicate decisions. A typical process includes an online assessment, one or more technical rounds, a design round, a project or managerial discussion and a behavioural/HR round, each assessed against a rubric of correctness, depth, trade-off reasoning and communication.
Interviews check whether you can write SQL and Python correctly, explain how big data tools work and why, design a pipeline that won’t break, and talk clearly about your own work. Each round looks at one of these.
Words you’ll meet in this lesson
Interview preparation at three levels
Beginner Fresher: the must-knows
- SQL: joins (inner, left, full, self), GROUP BY with HAVING, subqueries, window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals), deduplication.
- Python: lists, dicts, sets, string handling, file reading, functions, exceptions, comprehensions, simple algorithms (counting, grouping, sorting).
- Concepts: 5 Vs, batch vs streaming, OLTP vs OLAP, ETL vs ELT, data lake vs warehouse vs lakehouse, row vs columnar formats.
- Your project: explain it in 2 minutes with architecture, scale, one hard problem and results.
Intermediate 1–3 years: depth and trade-offs
- Spark: driver and executors, lazy evaluation, transformations vs actions, narrow vs wide, shuffles, partitions, broadcast joins, skew, caching, AQE.
- Kafka and streaming: topics, partitions, offsets, consumer groups, delivery semantics, watermarks and late data.
- Modelling and warehousing: star vs snowflake schema, facts and dimensions, slowly changing dimensions (SCD type 1 and 2), partitioning and clustering.
- Pipelines: Airflow DAGs, idempotency, backfills, incremental loads, data quality checks, CDC.
Advanced Senior: design and ownership
- System design: requirements, estimates, architecture, storage and processing choices, failure handling, cost and governance, in a structured 45 minutes.
- Distributed systems: CAP, consistency, replication, exactly-once end to end, schema evolution, small-files problems.
- Operations: SLAs, observability, on-call, incident post-mortems, cost optimisation.
- Leadership: design reviews, mentoring, disagreeing constructively, data contracts across teams.
Exam tip: For any interview question: clarify (inputs, scale, edge cases), state assumptions, answer step by step, give a small example, then add trade-offs or limits. This structure also earns full marks in long-answer exam questions.
Interview rounds and what they test
| Round | What is tested | How to prepare |
|---|---|---|
| Online assessment | SQL queries, Python problems, MCQs on concepts | Daily SQL practice; timed Python problems on lists, dicts, strings |
| Technical: SQL and coding | Joins, window functions, deduplication; Python data handling | Solve aloud; test with edge cases (NULLs, ties, empty input) |
| Technical: big data concepts | Spark internals, Kafka, file formats, partitioning, warehouses | Explain each concept with a picture and an example from your project |
| Design round | An end-to-end pipeline or platform for a scenario | Practise a fixed structure: requirements → estimates → design → failures → cost |
| Project / manager round | Ownership, depth, decisions, collaboration | Prepare a 2-minute project story and 5 STAR stories |
| HR round | Motivation, expectations, notice period, culture fit | Research the company; be honest and specific |
What interviewers score
Correctness
Does the query or code give the right answer, including edge cases such as NULLs, ties and duplicates?
RightDepth
Can you explain why it works and what happens underneath (shuffles, partitions, offsets)?
WhyTrade-offs
Do you compare options and pick one for stated reasons (cost, latency, simplicity)?
JudgementCommunication
Do you clarify, think aloud, structure the answer and accept hints well?
ClarityWhen stuck, say what you know and reason towards the rest: “I haven’t used Iceberg, but it is a table format like Delta, so I’d expect snapshots and ACID commits…”. Interviewers reward honest reasoning far more than confident guessing.
How to answer any technical question
Clarify the question
“Top orders per city: should ties share a rank? Should duplicate rows count once? Any date range?”
Two or three clarifying questions show care and often reveal the edge case the interviewer is waiting for.State assumptions and scale
“I’ll assume order_id is unique after deduplication, and the table has around 10 crore rows partitioned by date.”
Assumptions make your answer checkable and set up good follow-ups.Solve simply first
Get a correct, simple solution, then improve it. A working brute-force answer beats an unfinished clever one.rows = [("Pune", 900), ("Pune", 450), ("Pune", 320), ("Thane", 610)] best = {} for city, amt in rows: best[city] = max(best.get(city, 0), amt) print(best){'Pune': 900, 'Thane': 610}Test with edge cases
Walk through NULL amounts, ties, a city with one order and an empty table. Fix anything that breaks.
Interviewers note whether you test without being asked.Discuss scale and trade-offs
“On 10 crore rows I’d filter by date partition first; in Spark, this window needs a shuffle by city, and a hot city could cause skew.”
Connecting a small answer to big data behaviour is what separates data engineers from general developers.Summarise
End with one sentence: what you did, its complexity or cost, and what you’d do with more time.
A clean summary leaves a strong final impression.
One answer: what Kabir says, and how it is scored
Step through the six frames. Left: Kabir’s answer. Right: the interviewer’s rubric.
- The interviewer asks a classic: what is a shuffle in Spark, and why care?
- Kabir asks to explain it through a familiar example.
- He defines a shuffle using GROUP BY city.
- He explains the cost, with a real figure from his internship.
- He lists ways to reduce shuffles.
- He ends with a one-line summary.
- The interviewer has a rubric with four areas, each scored on a scale.
- A short clarifying or framing question already shows good communication.
- A correct definition with an example earns most of the correctness score.
- Explaining that shuffles hit disk and network, and quoting 70% of runtime from real work, shows depth.
- Practical remedies (filter early, broadcast joins, skew handling, partition tuning, adaptive query execution) show judgement.
- The summary ties it together. Scores are illustrative, but this is how many structured interviews are assessed.
Real-time scenarios and a question bank
Three live scenarios: a SQL round with real query output (from the program below), a “why is this Spark job slow?” question, and an advanced 45-minute design round. Then a question bank with model answers.
Real-time scenario 1The SQL round: top 2 orders per city
The results come from the program in “Run it on your PC” below, which runs five classic SQL questions on a small orders table. Watch the most common window-function question solved step by step.
- Before writing SQL, Kabir clarifies two edge cases: how ties should be treated and whether duplicates exist.
- The table has the order O2 twice (a retry loaded it twice), so he deduplicates with SELECT DISTINCT. 8 rows become 7.
- DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) ranks orders within each city, giving equal amounts the same rank.
- Filtering rnk ≤ 2 returns the answer. Thane’s two 610 orders are both rank 1, so Thane returns two rows for rank 1.
- A common follow-up: ROW_NUMBER returns exactly two rows per city (breaking ties arbitrarily), RANK leaves gaps (1, 1, 3) and DENSE_RANK doesn’t (1, 1, 2). Knowing the difference is a frequent screening point.
“Why is this Spark job slow?”
This is one of the most common data engineering interview questions. Strong candidates don’t guess; they describe how they would find evidence. Watch the reasoning.
- Kabir doesn’t start with guesses like “add more memory”. He says he would open the Spark UI and find the slowest stage.
- In that stage, seven tasks take about 40 seconds and one takes 2,400 seconds. One straggler task like this is the classic sign of data skew.
- He would check row counts per join or group key and find that a single test merchant ID holds 30% of rows, so one partition does almost all the work.
- Fixes: filter out test data, salt the hot key (split it across several partitions), broadcast the smaller table, or enable adaptive query execution’s skew-join handling.
- The stage drops to about a minute. The structure is what interviewers want: symptom, evidence, cause, fix, and how to prevent it (a skew alert, data quality checks on test IDs). (Numbers are illustrative.)
A 45-minute pipeline design round
Design rounds reward structure more than buzzwords. Watch how Kabir spends 45 minutes on a real-time order analytics design.
- He spends the first five minutes on requirements: dashboard latency under a minute, three years of history, exact revenue for finance, six cities.
- Back-of-envelope estimates: 50 lakh orders a day is about 58 per second on average and roughly 300 per second at the lunch peak; about 5 GB of events a day. This rules out over-engineered designs.
- The architecture: order events into Kafka, Spark Structured Streaming into Delta Lake bronze, silver and gold tables, with BI dashboards on gold. He explains why each piece is there.
- The deep dive covers failures: checkpoints and idempotent writes for exactly-once results, watermarks for late events, and a nightly batch job reconciling totals with the orders database for finance.
- He finishes with cost (partitioning, autoscaling, retention), governance (PII masking, owners, quality checks) and a one-minute summary, leaving time for the interviewer’s questions.
Question bank with model answers
Open each question only after answering it aloud yourself. Model answers are short; in an interview, add an example from your own work.
SQL
Difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after aggregation, so it can use aggregates like COUNT(*) > 1.
ROW_NUMBER vs RANK vs DENSE_RANK?
For ties on 610, 610, 450: ROW_NUMBER gives 1, 2, 3 (arbitrary order for ties); RANK gives 1, 1, 3 (gaps); DENSE_RANK gives 1, 1, 2 (no gaps).
How do you remove duplicate rows but keep one?
Use ROW_NUMBER() OVER (PARTITION BY the key columns ORDER BY a tiebreaker such as load time DESC) and keep rows where the number is 1; or SELECT DISTINCT when entire rows are identical.
INNER vs LEFT vs FULL OUTER JOIN?
INNER keeps matching rows only; LEFT keeps all rows from the left table with NULLs where there is no match; FULL OUTER keeps all rows from both sides.
What happens to NULLs in COUNT, SUM and joins?
COUNT(column) and SUM ignore NULLs, COUNT(*) counts rows; NULL never equals NULL, so NULL keys don’t match in joins unless handled explicitly.
How would you find the second highest salary?
SELECT MAX(salary) FROM t WHERE salary < (SELECT MAX(salary) FROM t), or DENSE_RANK() and filter rank = 2. Ask how ties should be handled.
Python
List vs tuple vs set vs dict?
List: ordered, mutable. Tuple: ordered, immutable (usable as a dict key). Set: unordered unique items with fast membership. Dict: key-value pairs with fast lookup by key.
How do you read a 20 GB file that doesn’t fit in memory?
Stream it line by line or in chunks (a for loop over the file, pandas read_csv with chunksize, or pyarrow batches), process each chunk and aggregate; or use Spark or DuckDB.
What is a generator and why use it in data pipelines?
A function that yields values one at a time with yield, keeping memory low because items are produced lazily, ideal for streaming through large files or API pages.
How do you handle errors in a pipeline script?
Catch specific exceptions, log context (file, row, ID), retry transient errors with backoff, send bad records to a quarantine location and fail loudly on unexpected errors rather than silently continuing.
Count word frequency in a text file.
Use collections.Counter over words from each line: Counter(w for line in f for w in line.lower().split()).most_common(10).
Big data concepts
Explain the 5 Vs.
Volume (size), Velocity (speed of arrival), Variety (structured, semi-structured, unstructured), Veracity (quality and trust) and Value (business benefit). Give an example for each from a real system.
ETL vs ELT?
ETL transforms data before loading into the target; ELT loads raw data first and transforms inside a scalable warehouse or lakehouse, which is common in the cloud because storage is cheap and engines are powerful.
Data lake vs data warehouse vs lakehouse?
A lake stores raw files of any type cheaply; a warehouse stores structured, modelled data for fast SQL; a lakehouse adds table formats (Delta, Iceberg, Hudi) to lake storage for ACID transactions, schema enforcement and warehouse-like performance.
Why are columnar formats like Parquet faster for analytics?
Queries read only the needed columns, similar values compress well, and min/max statistics let engines skip row groups, so far less data is read.
What is partitioning and what is the small-files problem?
Partitioning splits data by a column (often date) so queries skip irrelevant folders. Too many tiny files, from over-partitioning or frequent small writes, slow down listing and reading; fix with compaction and sensible partition choices.
Explain the CAP theorem.
During a network partition a distributed system must choose between consistency (every read sees the latest write) and availability (every request gets a response). Payments favour consistency; feeds and counters often favour availability.
Spark
Driver vs executor?
The driver plans the job and schedules tasks; executors on worker nodes run tasks on partitions and hold cached data.
Transformations vs actions; what is lazy evaluation?
Transformations (select, filter, join) build a plan; actions (count, write, collect) trigger execution. Laziness lets Spark optimise the whole plan, for example pushing filters down.
Narrow vs wide transformations?
Narrow ones (map, filter) work within a partition; wide ones (groupBy, join, distinct) need a shuffle to bring rows with the same key together.
repartition vs coalesce?
repartition does a full shuffle to any number of partitions with even sizes; coalesce reduces partitions by merging without a full shuffle, cheaper but possibly uneven.
What is a broadcast join?
Sending a small table to every executor so the big table can be joined without shuffling it. Useful when one side fits comfortably in memory.
How do you handle data skew?
Find hot keys in the Spark UI and key counts, then filter bad keys, salt hot keys, broadcast the small side, or enable adaptive query execution’s skew handling.
cache vs persist?
cache stores a DataFrame in memory (default storage level); persist lets you choose levels such as memory and disk. Use it only when the result is reused several times, and unpersist after.
Kafka and streaming
Topic, partition, offset, consumer group?
A topic is a named stream; partitions split it for parallelism and ordering within a partition; an offset is a message’s position; consumers in a group share partitions, so each message is processed by one consumer in the group.
At-most-once, at-least-once, exactly-once?
At-most-once may lose messages; at-least-once may duplicate them; exactly-once means each message affects results once, achieved with idempotent producers, transactions or idempotent sinks plus checkpoints.
What are watermarks?
A threshold for how late event-time data may arrive; events older than the watermark are dropped or handled separately so windowed state can be finalised and freed.
How does Kafka keep messages in order?
Order is guaranteed only within a partition, so events that must stay ordered (for example, for one order ID) use the same key, which maps to the same partition.
Batch or streaming for a daily finance report?
Batch: it needs complete, reconciled data once a day. Streaming fits fraud alerts or live dashboards where seconds matter.
Data modelling and pipelines
Star vs snowflake schema?
A star schema has a central fact table joined directly to denormalised dimension tables; a snowflake normalises dimensions into more tables. Star is simpler and faster for BI.
What is a slowly changing dimension type 2?
A dimension that keeps history by inserting a new row with valid_from/valid_to (or is_current) when an attribute such as a customer’s city changes, instead of overwriting it (type 1).
What makes a pipeline idempotent?
Running it twice for the same input gives the same result: overwrite a date partition or MERGE on keys instead of blindly appending.
Full load vs incremental load vs CDC?
Full reloads everything; incremental loads only new or changed rows using a watermark column; CDC reads the database’s change log to capture inserts, updates and deletes reliably.
What is a backfill?
Re-running a pipeline for past dates, for example after a bug fix or a new column. Airflow supports it when tasks are parameterised by date and idempotent.
Behavioural and HR
Tell me about a time something broke in production.
Use STAR: the situation (dashboard 100× wrong), your task, the actions (traced lineage, found a unit change, fixed and backfilled, added a check and a contract) and the result, plus what you learnt.
Tell me about a disagreement with a teammate.
Show that you listened, used data or a small experiment to decide, and kept the relationship good. Avoid blaming.
Why data engineering?
Connect a real experience (building a pipeline others relied on) to what you enjoy: reliable systems, scale and enabling decisions.
Where do you see yourself in a few years?
Be honest and role-related: deeper expertise in streaming or platforms, owning systems end to end, mentoring others.
Practise SQL interview questions in your browser
Python’s built-in sqlite3 runs real SQL. The program creates a small orders table with a duplicate row and answers a classic question. Then try the challenges: deduplication, ranking functions, running totals, LAG and a Python counting question.
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (order_id TEXT, city TEXT, amount INT);
INSERT INTO orders VALUES ('O1','Pune',320), ('O2','Pune',450), ('O3','Thane',610),
('O4','Pune',150), ('O5','Thane',610), ('O6','Pune',900), ('O2','Pune',450);
""")
# Q: total revenue per city, counting each order once
sql = """SELECT city, SUM(amount) FROM (SELECT DISTINCT * FROM orders)
GROUP BY city ORDER BY city"""
for row in db.execute(sql):
print(row)sql = """SELECT city, SUM(amount) FROM (SELECT DISTINCT * FROM orders)Deduplicate first: O2 was loaded twice and would inflate Pune’s total.db = sqlite3.connect(":memory:")An in-memory database: nothing to install, perfect for practice.
Run it on your PC: five classic SQL interview questions
This program builds an orders table (with a duplicate row, as real data often has) and runs five classic questions: second highest amount, duplicates, top 2 per city with ties, a running total and customers active on several days. Try writing each query yourself before reading the solution. Save it as sql_interview.py.
Second highest, duplicates, top-N, running total, repeat customers
"""Five classic data engineering SQL interview questions, solved and run
on a small orders table with SQLite (built into Python, nothing to install)."""
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (order_id TEXT, customer TEXT, city TEXT, amount INTEGER, order_date TEXT);
INSERT INTO orders VALUES
('O1','Asha','Pune',320,'2026-10-01'), ('O2','Ravi','Pune',450,'2026-10-01'),
('O3','Meera','Thane',610,'2026-10-02'), ('O4','Asha','Pune',150,'2026-10-02'),
('O5','Kiran','Thane',610,'2026-10-03'), ('O6','Ravi','Pune',900,'2026-10-03'),
('O7','Zoya','Nashik',200,'2026-10-03'), ('O2','Ravi','Pune',450,'2026-10-01');
""")
questions = {
"Q1. Second highest order amount (handles ties)":
"SELECT MAX(amount) FROM orders WHERE amount < (SELECT MAX(amount) FROM orders)",
"Q2. Find duplicate order_ids":
"SELECT order_id, COUNT(*) AS n FROM orders GROUP BY order_id HAVING COUNT(*) > 1",
"Q3. Top 2 orders per city (window function, ties share a rank)":
"""SELECT city, order_id, amount, rnk FROM (
SELECT DISTINCT order_id, city, amount,
DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS rnk
FROM orders) WHERE rnk <= 2 ORDER BY city, rnk""",
"Q4. Running total of revenue by day":
"""SELECT order_date, SUM(amount) AS day_total,
SUM(SUM(amount)) OVER (ORDER BY order_date) AS running_total
FROM (SELECT DISTINCT * FROM orders) GROUP BY order_date ORDER BY order_date""",
"Q5. Customers who ordered on 2 or more different days":
"""SELECT customer, COUNT(DISTINCT order_date) AS days FROM orders
GROUP BY customer HAVING COUNT(DISTINCT order_date) >= 2""",
}
for title, sql in questions.items():
print(title)
for row in db.execute(sql):
print(" ", row)
# In a terminal: python sql_interview.pyRecorded from a real run with Python 3.13 and its built-in SQLite (window functions need SQLite 3.25 or newer, included in all recent Python versions). The same SQL works in PostgreSQL, Spark SQL, BigQuery and most warehouses with small changes.
Deduplicating records, step by step
A favourite interview task in Python: keep the latest record per order ID. Watch the dictionary keep only the newest version of each order.
Worked examples: real problems, solved and traced in memory
Four interview problems across SQL, Python and Spark thinking. Each runs live and is traced.
Is it a palindrome? (ignore case and spaces)
Problem statement
A common warm-up in online tests. Check whether a phrase reads the same forwards and backwards, ignoring case and spaces.
"Nitin", "Data Engineer"
Nitin -> True Data Engineer -> False
Approach
- Lower-case and remove spaces.
- Compare the string with its reverse.
- Test both examples.
Solution: run it live
What happens in memory, step by step
Recorded from a real Python run: press Next to execute one line at a time. Watch variables change in each frame, and lists and dictionaries (numbered #1, #2 …) fill up as objects.
Customers who ordered on consecutive days
Problem statement
Using LAG, find customers who placed orders on two consecutive days.
(customer, day number)
[('Asha',), ('Zoya',)]Approach
- Deduplicate to one row per customer per day.
- LAG(day) over each customer’s days gives the previous day.
- Keep rows where day − previous = 1.
Solution: run it live
What happens in memory, step by step
Recorded from a real Python run: press Next to execute one line at a time. Watch variables change in each frame, and lists and dictionaries (numbered #1, #2 …) fill up as objects.
Average order value per city without pandas
Problem statement
Given (city, amount) pairs, compute the average per city using only the standard library, as many interviews require.
5 (city, amount) pairs
{'Pune': 556.7, 'Thane': 405.0}Approach
- Keep a running sum and count per city in dictionaries.
- Divide at the end.
- Round for display.
Solution: run it live
What happens in memory, step by step
Recorded from a real Python run: press Next to execute one line at a time. Watch variables change in each frame, and lists and dictionaries (numbered #1, #2 …) fill up as objects.
Detect a skewed key before the job runs
Problem statement
Before a big join, sample the keys. Flag any key holding more than 10% of rows, and estimate how many salt buckets would bring it to about the average partition size.
key counts, 50 partitions
M001: 30% of rows -> salt into 15 buckets TEST: 60% of rows -> salt into 30 buckets
Approach
- Total rows and each key’s share.
- Flag keys above 10%.
- Salt buckets ≈ key rows ÷ average rows per partition.
Solution: run it live
What happens in memory, step by step
Recorded from a real Python run: press Next to execute one line at a time. Watch variables change in each frame, and lists and dictionaries (numbered #1, #2 …) fill up as objects.
Check yourself
Quick revision
Lesson 6.3 in one screen
- Answer structure: clarify → assume → solve simply → test edge cases → scale and trade-offs → summarise.
- SQL must-knows: joins, WHERE vs HAVING, dedup with ROW_NUMBER, ROW_NUMBER/RANK/DENSE_RANK, LAG/LEAD, running totals.
- Python: dicts/sets/Counter, chunked file reading, generators, error handling, no-pandas grouping.
- Spark: driver/executors, lazy evaluation, narrow vs wide, shuffles, broadcast joins, skew, repartition vs coalesce.
- Kafka: partitions, offsets, consumer groups, ordering per key, delivery semantics, watermarks.
- Modelling: star schema, facts/dimensions, SCD 1 vs 2; pipelines: idempotent, incremental, CDC, backfills.
- Design rounds: requirements, estimates, architecture, failures, cost and governance; STAR for behavioural.
Interview and viva questions
What are the most common data engineering interview questions?
SQL problems with joins, aggregation and window functions (top-N per group, duplicates, running totals); Python data-handling questions; concepts such as ETL vs ELT, data lake vs warehouse, Parquet and partitioning; Spark questions on shuffles, partitions, joins and skew; Kafka basics; data modelling with star schemas and slowly changing dimensions; a pipeline design scenario; and questions about your projects and behaviour.
How should I prepare for a data engineering interview in 30 days?
Practise SQL daily (window functions especially), revise Python data structures and file handling, review Spark and Kafka concepts with diagrams, practise two or three design scenarios with a fixed structure, prepare a two-minute project story and five STAR stories, and do mock interviews where you explain your thinking aloud.
What Spark questions are asked in interviews?
Typical questions cover the driver and executors, lazy evaluation, transformations and actions, narrow and wide dependencies, shuffles, partitioning and repartition versus coalesce, broadcast joins, handling data skew, caching, adaptive query execution, file formats and debugging slow or failing jobs using the Spark UI.
How do I answer a system design question for data pipelines?
Clarify requirements (latency, volume, history, correctness, consumers), estimate scale with simple arithmetic, propose an architecture from ingestion to storage, processing and serving, explain failure handling (retries, idempotency, late data, reconciliation), and discuss cost, security and governance, ending with a short summary and possible improvements.
What if I don’t know the answer to an interview question?
Say so honestly, then reason from what you do know, relate it to a similar tool or concept, and describe how you would find out. Interviewers value clear reasoning and honesty over confident guessing.
Practice problems
Interview-style problems in Python and SQL. The last two are business-flavoured design questions.
Reverse words
Print the words of "big data is fun" in reverse order.
"big data is fun"
fun is data big
Show solution and explanation
print(" ".join("big data is fun".split()[::-1]))How it works: Split, reverse the list, join.
Second largest
Print the second largest distinct number in [5, 9, 9, 3, 7].
list
7
Show solution and explanation
print(sorted(set([5, 9, 9, 3, 7]))[-2])How it works: Remove duplicates first so a tie for the maximum doesn’t count twice.
Count with HAVING
With SQLite, print the cities with more than 1 order from [Pune, Pune, Thane].
3 rows
[('Pune',)]Show solution and explanation
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("CREATE TABLE o (c TEXT); INSERT INTO o VALUES ('Pune'),('Pune'),('Thane');")
print(db.execute("SELECT c FROM o GROUP BY c HAVING COUNT(*) > 1").fetchall())How it works: HAVING filters after grouping.
First non-repeating character
Print the first character that appears only once in "aabbcdc".
"aabbcdc"
d
Show solution and explanation
from collections import Counter
s = "aabbcdc"
c = Counter(s)
print(next(ch for ch in s if c[ch] == 1))How it works: Count first, then scan in order.
Merge two sorted lists
Merge [1, 4, 7] and [2, 3, 8] into one sorted list without calling sort.
two sorted lists
[1, 2, 3, 4, 7, 8]
Show solution and explanation
a, b, out, i, j = [1, 4, 7], [2, 3, 8], [], 0, 0
while i < len(a) and j < len(b):
if a[i] <= b[j]:
out.append(a[i]); i += 1
else:
out.append(b[j]); j += 1
print(out + a[i:] + b[j:])How it works: The merge step of merge sort; external sorting in big data works the same way.
Latest status per order
From [("O1",1,"placed"),("O1",3,"delivered"),("O1",2,"shipped")], print the status with the highest version.
versioned records
delivered
Show solution and explanation
r = [("O1", 1, "placed"), ("O1", 3, "delivered"), ("O1", 2, "shipped")]
print(max(r, key=lambda x: x[1])[2])How it works: The same logic as ROW_NUMBER() ... ORDER BY version DESC = 1, or a MERGE in a lakehouse.
Sessionise clicks
Click times (minutes) [0, 5, 12, 50, 52, 120]. A new session starts after a gap over 30. Print the number of sessions.
times
3
Show solution and explanation
t = [0, 5, 12, 50, 52, 120]
print(1 + sum(1 for a, b in zip(t, t[1:]) if b - a > 30))How it works: Sessionisation is a common streaming and SQL interview problem (LAG + gap rule).
Estimate for a design round
50 lakh orders a day, 2 KB each, kept 3 years, 3 copies. Print storage in TB.
as given
32.9 TB
Show solution and explanation
print(round(50e5 * 2e3 * 365 * 3 * 3 / 1e12, 1), "TB")How it works: Quick estimates like this anchor every design discussion.
Peak throughput
Lunch (12–2 pm) carries 40% of 50 lakh daily orders. Print average orders per second in that window.
40% in 2 hours
278
Show solution and explanation
print(round(50e5 * 0.40 / (2 * 3600)))How it works: Peak windows, not daily averages, decide capacity.
Real-world scenario questions (asked in company interviews)
More interview situations to practise. Say your answer aloud first, then open the model answer.
“Find customers who ordered on consecutive days.”
An interviewer gives an orders table (customer, order_date) and asks for customers with orders on two consecutive days.
The interviewer is checking: can you use window functions beyond ranking?
Show a strong answer
Deduplicate to one row per customer per day, then use LAG(order_date) OVER (PARTITION BY customer ORDER BY order_date) and keep rows where the difference from the previous date is exactly one day. Mention edge cases: several orders on the same day, time zones, and performance (partition by date range on large tables).
Avoid: self-joining every row with every other row without explaining the cost.
“Your Spark job fails with OutOfMemory.”
An executor fails with an out-of-memory error during a join.
The interviewer is checking: can you reason from symptoms to causes?
Show a strong answer
- Check the Spark UI: which stage, how large are partitions, is one task much bigger (skew)?
- Look for
collect()ortoPandas()pulling data to the driver. - Fixes: more partitions, broadcast only genuinely small tables, salt skewed keys, select fewer columns, avoid caching large unused data, increase executor memory as a last resort.
Avoid: immediately doubling memory without finding the cause.
“Design a daily sales pipeline from 200 stores.”
Each store uploads a CSV of sales every night. Head office needs a dashboard by 7 am.
The interviewer is checking: can you structure a batch design with failure handling?
Show a strong answer
- Requirements: ~200 files a night, sizes, 7 am SLA, late or missing files.
- Land raw files in object storage by date and store (bronze).
- Airflow DAG: sensor for files with a deadline, validation and quality checks, Spark or dbt into silver and gold, idempotent overwrite per date.
- Alerts for missing stores, quarantine for bad files, backfill support.
- Serve gold tables to the dashboard; document owners and definitions.
Avoid: jumping to Kafka and streaming for a once-a-day requirement.
“What is your biggest weakness?”
Asked in many HR and manager rounds.
The interviewer is checking: can you be honest and show growth?
Show a strong answer
Pick a real, non-critical weakness and show what you are doing about it: “I used to jump into coding before clarifying requirements. After a pipeline I built had to be redone, I now write a one-page design note and confirm it before starting.” Keep it short and specific.
Avoid: a disguised strength like “I work too hard”.