Data Engineering Interview Questions and Answers

Data engineering interview questions test one underlying skill: moving data reliably, at the scale the problem actually has. The rounds approach it from different directions, through SQL, code, modelling, pipeline design and operations. This guide maps the rounds with model answers, then works the opening of a design round with real numbers, because that is where many candidates go wrong before they have drawn a single box.

The rounds and model answers

SQL. The most consistent round. Expect joins, window functions and aggregation with traps such as NULLs and duplicate keys. A model answer states the grain of the result and checks row counts. See SQL interview questions: top 50 with answers.

Coding. Usually Python: parse a file, deduplicate records, merge intervals, or implement a small transformation with tests. Clear, correct code beats clever code.

ETL and pipelines. "Two extracts overlap; how do you load them?" Expect watermarks, idempotent reloads, keys and reconciliation, worked on real overlapping data in ETL interview questions. Orchestration questions follow in Airflow interview questions.

Modelling and warehouses. "Design the tables for an order system's analytics." Declare the grain, choose facts and dimensions, handle changing attributes and unknown members; see data modelling interview questions. Then storage and loading behaviour, from columnar files to partition pruning, in data warehouse interview questions.

Operations. Shell, scheduling and debugging a failed job; see Linux interview questions for data engineers.

Behavioural. "Tell me about a pipeline that broke in production." Describe how you noticed, how you limited the damage, the root cause, and what you changed so it could not recur. Interviewers listen for monitoring and ownership, not blame.

System design. "Design a pipeline for taxi trip analytics." The rest of this guide works the first five minutes of that question, and the full method is in system design interview questions for data engineers.

A design round, sized from real data

The strongest first move in a design round is not a diagram; it is a size estimate. Here it comes from New York City's published yellow taxi trips for January 2024 and January 2025 (NYC TLC: trip record data).

import os

import duckdb

db = duckdb.connect()
files = {"2024": "yellow_tripdata_2024-01.parquet", "2025": "yellow_tripdata_2025-01.parquet"}   # NYC TLC, January
stats = {}
for year, f in files.items():
    rows, peak, low, cols = db.execute(f"""
        WITH d AS (SELECT CAST(tpep_pickup_datetime AS DATE) AS day, COUNT(*) AS n FROM '{f}'
                   WHERE MONTH(tpep_pickup_datetime) = 1 AND YEAR(tpep_pickup_datetime) = {year} GROUP BY day)
        SELECT SUM(n), MAX(n), MIN(n), (SELECT COUNT(*) FROM (DESCRIBE SELECT * FROM '{f}')) FROM d""").fetchone()
    stats[year] = dict(rows=rows, peak=peak, low=low, cols=cols, mb=os.path.getsize(f) / 1e6)
    print(f"January {year}: {rows:,} trips, {rows / 31:,.0f} a day on average, busiest day {peak:,}, quietest {low:,}, "
          f"{cols} columns, {stats[year]['mb']:.0f} MB as Parquet ({stats[year]['mb'] * 1e6 / rows:.1f} bytes per trip)")

growth = stats["2025"]["rows"] / stats["2024"]["rows"] - 1
s = stats["2025"]
year_rows, year_gb = s["rows"] * 12, s["mb"] * 12 / 1000
print(f"\nGrowth January to January: {growth:+.1%}")
print(f"One year at January 2025's rate: about {year_rows / 1e6:.0f} million trips and {year_gb:.1f} GB of Parquet")
print(f"Daily partitions would average {s['mb'] / 31:.1f} MB each; monthly ones {s['mb']:.0f} MB")

uncompressed = lambda columns: year_rows * columns * 8 / 2**40             # assume 8 bytes per numeric or timestamp value
for label, columns in (("daily revenue (2 columns)", 2), ("SELECT * (all columns)", s["cols"])):
    tib = uncompressed(columns)
    print(f"Scanning a year for {label}: about {tib:.3f} TiB uncompressed, {tib * 6.25:.2f} USD at 6.25 USD per TiB")

January 2024: 2,964,606 trips, 95,632 a day on average, busiest day 110,515, quietest 67,543, 19 columns, 50 MB as Parquet (16.9 bytes per trip)
January 2025: 3,475,204 trips, 112,103 a day on average, busiest day 138,711, quietest 79,624, 20 columns, 59 MB as Parquet (17.0 bytes per trip)

Growth January to January: +17.2%
One year at January 2025's rate: about 42 million trips and 0.7 GB of Parquet
Daily partitions would average 1.9 MB each; monthly ones 59 MB
Scanning a year for daily revenue (2 columns): about 0.001 TiB uncompressed, 0.00 USD at 6.25 USD per TiB
Scanning a year for SELECT * (all columns): about 0.006 TiB uncompressed, 0.04 USD at 6.25 USD per TiB

What the numbers change

The volume is small. January 2025 had 3,475,204 trips, about 112,000 a day, with a busiest day of 138,711. At that rate a year is about 42 million trips and 0.7 GB of Parquet, roughly 17 bytes a trip. That fits on a laptop. A design that opens with a streaming cluster and a distributed file system is answering a different question.

Growth matters more than today's size. Trips grew 17.2% from one January to the next, and the schema grew too, from 19 to 20 columns, when the congestion fee arrived. A good answer plans for both: a few years of growth at that rate is still small, but a new column each year is a certainty.

Partition for the data you have. Daily partitions would average 1.9 MB. BigQuery's own guidance is to consider clustering instead of partitioning when partitions would hold less than about 10 GB, because many small partitions add metadata overhead (BigQuery: partitioned tables). Monthly partitions, or one table clustered by pickup date, fit this volume better.

Cost follows columns read. BigQuery's on-demand price is 6.25 USD per TiB processed, with the first TiB each month free, and data size depends on column types (BigQuery: pricing). Assuming 8 bytes per value, a year of daily revenue reads about 0.001 TiB, and even SELECT * about 0.006 TiB: effectively free. At this scale, the design should optimise for simplicity, correctness and freshness, not for query cost.

Freshness is set by the source. The TLC publishes trip data monthly, typically with a two-month delay to allow for complete submissions from vendors, as its data page explains. No pipeline design can make this data fresher than that, so a real-time architecture would add cost and complexity for nothing. Ask about freshness early: it often rules out half the options at once.

Then draw the boxes. Land each monthly file unchanged, validate and load it idempotently into a partitioned or clustered table, build a daily summary, and reconcile row counts against the source. Every choice now has a number behind it.

How to prepare

Practise SQL daily on real data, build one small pipeline end to end with scheduling, tests and reconciliation, and rehearse two design questions with the sizing step first. Interviewers remember candidates who ask "how much data, how fast, how fresh?" before proposing tools. Keep the numbers from your own practice projects, such as rows per day and bytes per row, ready to quote.

Mistakes that cost offers

• Naming tools before sizing the problem.
• Designing loads that cannot be rerun safely.
• Ignoring schema change and late data.
• Optimising cost or speed for a scale the data does not have.

These skills are taught end to end in the Data Engineering course.

Sources

• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• BigQuery pricing (Google Cloud) — Google Cloud
• Introduction to partitioned tables (BigQuery documentation) — Google Cloud