16 Data Engineer Interview Questions: SQL, SCDs, Airflow, Kafka

Last updated: October 2026

Quick Answer

Data engineer interview questions test whether you can move data reliably from source to user: write correct SQL under messy conditions (duplicates, late rows, changing dimensions), model a warehouse at the right grain, design pipelines that survive retries and backfills, choose batch or streaming for a reason, prove data quality with tests, and explain an incident calmly. Rehearse the modeling and pipeline design answers aloud in OphyAI Interview Practice; Interview Copilot gives real-time answer suggestions in live video rounds.

#	Question	What the interviewer is testing
1	Keep only the latest version of each record	ROW_NUMBER, tie-breaking, dedup logic
2	Implement a Type 2 slowly changing dimension	History tracking, effective dates, merges
3	Design a star schema for orders	Grain, facts vs dimensions, keys
4	Normalize or denormalize the warehouse?	Trade-offs, not dogma
5	ETL or ELT?	Where transformation belongs and why
6	Batch or streaming for this use case?	Latency needs vs cost and complexity
7	How does Kafka give you exactly-once?	Delivery semantics and their limits
8	What makes an Airflow DAG idempotent?	Safe retries, partitioned writes
9	Backfill 90 days without breaking anything	Operational judgment
10	How do you test a data pipeline?	Data quality as code

What data engineers do and earn in the US

The US Bureau of Labor Statistics (BLS) does not publish a separate data engineer occupation; the closest Occupational Outlook Handbook profile is database administrators and architects. In May 2025, BLS reports a median of $139,500 a year for database architects and $104,620 for database administrators. The two jobs held about 144,500 positions in 2025, and BLS projects 4% combined growth from 2025 to 2035 (9% for architects, 0% for administrators), with about 7,300 openings a year. The typical entry-level education is a bachelor’s degree. Treat these as a proxy: titled data engineer roles at large tech companies are often paid differently, and those figures are candidate-reported.

What candidates report about the loop (checked October 2026):

Stage	What candidate guides commonly describe
Recruiter screen	Background, level, motivation; at Amazon, Leadership Principles from the first call
Online assessment or phone screen	Timed SQL plus easy-to-medium Python
Technical rounds	Data modeling (facts, dimensions, SCDs), pipeline and ETL design
System design	An end-to-end pipeline for the team’s domain
Behavioral	Incidents, stakeholders, ownership

A third-party Amazon data engineer guide (Aced, formerly Exponent) reports a 3-6 week process with an onsite of 3-5 interviews including a Bar Raiser; Amazon has not published a data-engineer-specific loop, so confirm with your recruiter.

The Questions, With Sample Answers

The sample answers are templates for a fictional candidate with three years of warehouse and pipeline experience; numbers inside them are illustrative, so swap in your own systems. For behavioral questions, use the STAR method. For every design question, state the requirements and assumptions before drawing boxes: interviewers grade the reasoning more than the tool names.

1. “Raw orders arrive with duplicates and updates. Write a query that keeps only the latest version of each order.”

Why they ask it: Deduplication is the most common real-world SQL task in data engineering. It tests window functions, and whether you think about ties and what “latest” means.

Sample answer: “I’d first ask what defines a version: the source’s updated_at, or our ingestion time? Sources can resend the same update, so I’ll order by updated_at and break ties with ingested_at.”

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC, ingested_at DESC
         ) AS rn
  FROM raw_orders
)
SELECT * FROM ranked WHERE rn = 1;

“I use ROW_NUMBER rather than RANK because I need exactly one row per order even when timestamps tie. In Snowflake, BigQuery or Databricks I could write QUALIFY rn = 1 instead of the CTE. In production I’d run this incrementally on recent partitions and merge into the clean table, rather than rescanning all history every day.”

What weak answers look like: SELECT DISTINCT (which only removes identical rows), GROUP BY with MAX on each column (which mixes values from different versions), or no tie-breaker.

Prepare with OphyAI: talk through the tie-breaker choice in Interview Practice; for more drills see our SQL interview questions.

2. “How would you implement a Type 2 slowly changing dimension for customers?”

Why they ask it: Analysts need to know what a customer’s plan or region was at the time of each order. SCD Type 2 is the standard way to keep that history, and interviewers check that you know the mechanics, not just the name.

Sample answer: “Type 1 overwrites the old value; Type 2 keeps every version as its own row. My customer dimension would have a surrogate key, the natural customer_id, the tracked attributes, valid_from, valid_to and an is_current flag. On each load I compare incoming rows with current rows on the tracked columns. For a changed customer I close the current row by setting valid_to to the change time and is_current to false, then insert a new row with valid_from at the change time and valid_to null. New customers just get inserted. Facts join on the surrogate key, so an order keeps pointing at the version that was true when it happened. In dbt, snapshots implement exactly this, adding dbt_valid_from and dbt_valid_to columns, with a timestamp or check strategy.”

What weak answers look like: Describing only overwrites, forgetting to close the old row (so two rows are current), or joining facts on the natural key so history is lost.

Prepare with OphyAI: Interview Practice’s technical format will ask follow-ups such as “what about deletes?”, so rehearse that edge case.

3. “Design a star schema for an e-commerce company’s orders.”

Why they ask it: Data modeling is where data engineers create or destroy analyst productivity. The interviewer listens for one word first: grain.

Sample answer: “I’d start by agreeing the grain: one row per order line, because revenue questions are asked by product. The fact table, fct_order_lines, holds foreign keys to the dimensions plus additive measures: quantity, unit price, discount and line revenue. Dimensions describe the context: dim_customer (Type 2, so we keep region history), dim_product with category and brand, dim_date for calendar logic such as fiscal weeks, and dim_store or channel. Facts are numeric and grow fast; dimensions are descriptive and change slowly. I’d keep order-level facts like shipping fees in a separate order-grain fact rather than repeating them on every line, which would double-count. Finally I’d partition the fact by order date and confirm the top ten business questions can be answered with simple joins.”

What weak answers look like: Listing tables before stating the grain, putting descriptive text in the fact table, or mixing order-level and line-level measures in one table.

Prepare with OphyAI: sketch this schema, then explain it in two minutes in an Interview Practice session and check the transcript for the word “grain” early.

4. “When would you normalize, and when would you denormalize, in a warehouse?”

Why they ask it: There is no single right answer, so this reveals whether you reason from workload or repeat a rule.

Sample answer: “Normalization removes redundancy, which is great for transactional systems where many small writes must stay consistent. Analytical warehouses flip the workload: few writes, large scans, many joins. Columnar engines compress repeated values well, so storing a category name on every row costs less than it would in a row store, and fewer joins make queries simpler and often faster. So I usually keep a normalized or source-shaped staging layer, model a star schema in the core layer, and build wide denormalized tables for specific dashboards. The cost of denormalizing is update complexity: if a product’s category changes, every wide table needs rebuilding. If an attribute changes often or many tables need it, I keep it in a dimension and join at query time.”

What weak answers look like: “Always normalize to third normal form,” or “joins are slow, flatten everything,” with no mention of the workload or update cost.

Prepare with OphyAI: practice giving the trade-off and then a clear recommendation in Interview Practice; interviewers want a decision at the end.

5. “What is the difference between ETL and ELT, and which would you choose?”

Why they ask it: Most modern stacks load raw data into a cloud warehouse and transform it there, but not every case fits. The interviewer checks you understand why.

Sample answer: “In ETL, you transform data before loading it into the target, usually in a separate processing engine. In ELT, you load raw data first and transform inside the warehouse or lakehouse, typically with SQL models in a tool like dbt. ELT became common because warehouse compute is elastic and keeping raw data lets you rebuild models when logic changes. I’d choose ELT for most analytics work: raw layer, staging, then modeled marts, all version-controlled and tested. I’d still transform before loading when data must be masked or dropped for privacy before it lands, when the volume of raw data would be too costly to store, or when the source format needs heavy parsing that SQL handles badly, such as binary logs.”

What weak answers look like: Defining the acronyms with no reason to choose, or claiming ETL is obsolete.

Prepare with OphyAI: answer this in Interview Practice using your own stack as the example; concrete tools make the answer credible.

6. “Should this use case be batch or streaming?”

Why they ask it: Streaming is expensive to build and operate. Good data engineers ask what latency the business actually needs before reaching for Kafka, Spark Structured Streaming or Flink.

Sample answer: “I’d ask who consumes the data and what decision changes with fresher data. A finance report reviewed each morning is a daily batch job: simpler, cheaper, easy to rerun. Fraud scoring at checkout or live inventory needs seconds, so it needs streaming. Many cases sit in between, where a micro-batch every 15 minutes is good enough. Streaming adds new problems: out-of-order events, state management and late data. In Spark Structured Streaming, for example, a watermark sets how late an event can arrive and still update a window; after that, state is dropped. Flink handles the same issues with event-time processing and watermarks. If I do stream, I keep a batch reconciliation job that recomputes the day from raw data and flags drift.”

What weak answers look like: “Streaming, because real time is better,” or no mention of late or out-of-order data.

Prepare with OphyAI: rehearse asking the latency question first in Interview Practice; it is the habit design interviewers listen for.

7. “How does Kafka achieve exactly-once processing, and what are its limits?”

Why they ask it: Delivery guarantees are widely misunderstood. The interviewer wants precise language about what Kafka guarantees and where your code takes over.

Sample answer: “Kafka’s documentation defines three semantics: at most once, where messages may be lost but never redelivered; at least once, where none are lost but some may be redelivered; and exactly once. By default Kafka gives at least once. Since version 0.11, the idempotent producer uses a producer ID and sequence numbers so retries don’t write duplicates to the log, and transactions let a producer write to several partitions atomically, with offsets committed in the same transaction. That gives exactly-once for Kafka-to-Kafka pipelines, such as Kafka Streams. It does not make an external side effect exactly once, like a database write or an API call. For those I make the sink idempotent, for example an upsert keyed on event ID. Ordering is only guaranteed within a partition, so I key by entity ID when order matters.”

What weak answers look like: “Kafka is exactly-once,” or claiming global ordering across a topic.

Prepare with OphyAI: let Interview Practice push you with “what if the consumer crashes after writing to the database?“

8. “What makes an Airflow DAG idempotent, and why does it matter?”

Why they ask it: Retries, reruns and backfills happen every week. A non-idempotent task turns each of them into duplicated or corrupted data.

Sample answer: “Idempotent means running a task twice for the same interval gives the same result as running it once. Airflow retries failed tasks, so its best-practices docs say tasks should produce the same outcome on every re-run. Three habits get me there. First, each run reads and writes one partition defined by the run’s data interval, using data_interval_start rather than datetime.now(), which changes on every run. Second, writes replace or upsert that partition instead of appending with INSERT, which duplicates rows on a rerun. Third, side effects are keyed, so a re-sent file or message is recognized. I also keep the DAG file light, with no database calls at the top level, because the scheduler parses it often.”

What weak answers look like: “Use retries,” or any design that appends rows and hopes a rerun never happens.

Prepare with OphyAI: explain one of your real DAGs in Interview Practice and ask for feedback on whether a rerun would be safe.

9. “A transformation bug corrupted 90 days of data. How do you backfill it?”

Why they ask it: This is an operational judgment question. They want a plan that fixes history without overloading the warehouse or confusing users.

Sample answer: “First I’d fix and test the logic, then announce the issue and the affected tables to consumers so nobody makes decisions on bad numbers. Because my tasks are idempotent and partitioned by date, a backfill is just rerunning those 90 intervals. Airflow can do that from the UI or with the backfill CLI over a date range, and I’d cap concurrent runs so we don’t starve daily jobs or hit warehouse limits. I’d run a few days first and compare row counts and key metrics against the old output and the source. I’d also check downstream models, since they need rebuilding too, in dependency order. Last, I’d write a short post-mortem and add the test that would have caught the bug.”

What weak answers look like: Truncating the table and reloading everything at once, or forgetting downstream tables and consumers.

Prepare with OphyAI: practice this as a step-by-step answer in Interview Practice; ordered steps sound like experience.

10. “How do you test a data pipeline and monitor data quality?”

Why they ask it: Broken data rarely throws an error. Interviewers want quality treated as code, not as hope.

Sample answer: “I test at three levels. First, assertions on every important model: primary keys unique and not null, foreign keys that match a dimension, and accepted values for status fields. dbt ships with exactly these four generic tests, unique, not_null, accepted_values and relationships, and a test fails when its query returns any rows. Second, freshness and volume checks: did today’s partition arrive, and is the row count within an expected range? Third, unit tests on transformation logic with small fixed inputs, run in CI before merge. I also agree data contracts with the teams producing source data, so a renamed column fails their deploy instead of my dashboard. Failures page the owning team, and critical tests block downstream models from publishing.”

What weak answers look like: “I check the dashboard looks right,” or tests with no owner or alerting.

Prepare with OphyAI: list the tests you would add to one of your own pipelines, then defend them in an Interview Practice technical session.

11. “How would you store and partition 5 TB of events a day in a lake?”

Why they ask it: Storage layout decides query cost. This tests file formats, partitioning and the small-files problem.

Sample answer: “I’d store events as Parquet, a column-oriented format with efficient compression and encoding, so queries read only the columns they need. CSV and JSON force full scans and lose types. I’d partition by event date because nearly every query filters on time, and avoid partitioning on high-cardinality columns like user_id, which creates millions of tiny files. Within partitions I’d aim for files in the hundreds of megabytes, compacting streaming output that lands as many small files. For a table format I’d use Apache Iceberg or Delta Lake to get ACID commits, schema evolution and time travel. Iceberg’s hidden partitioning derives partition values from a column such as event_time, and its partition layout can evolve without rewriting the table.”

What weak answers look like: Partitioning by user or by hour without checking query patterns, or not knowing why columnar formats help.

Prepare with OphyAI: rehearse explaining the small-files problem in plain language in Interview Practice.

12. “A Spark job that used to take 20 minutes now takes 3 hours. How do you debug it?”

Why they ask it: Performance tuning shows whether you understand how distributed engines actually run, especially shuffles and skew.

Sample answer: “First I’d check what changed: input volume, code, cluster size or the data distribution. Then I’d open the Spark UI and find the slow stage. If most tasks finish in seconds and a few run for hours, that’s skew: one key, often a null or a default value, has far more rows than others, so one task does most of the shuffle work. Fixes include filtering or handling the hot key separately, salting the key, or letting adaptive query execution split skewed partitions. If all tasks are slow, I’d look at spill to disk, too few partitions, or a join that should be a broadcast join because one side is small. I’d fix one thing at a time and compare stage timings.”

What weak answers look like: “Add more executors,” with no diagnosis.

Prepare with OphyAI: tell a real tuning story in Interview Practice; specifics such as the stage and the key that skewed make it convincing.

13. “In Python, read a file of JSON events, skip bad lines, and keep the latest version of each event.”

Why they ask it: Data engineering coding rounds favor practical data manipulation over puzzles. They check error handling and memory awareness.

Sample answer: “I’ll stream the file line by line so memory scales with unique events, not file size, and count bad rows instead of failing the whole job.”

import json

def latest_events(lines):
    latest, bad = {}, 0
    for line in lines:
        try:
            e = json.loads(line)
            key, ts = e["event_id"], e["updated_at"]
        except (json.JSONDecodeError, KeyError, TypeError):
            bad += 1
            continue
        cur = latest.get(key)
        if cur is None or ts > cur["updated_at"]:
            latest[key] = e
    return list(latest.values()), bad

“This assumes updated_at is an ISO 8601 string in one timezone, so string comparison orders correctly; otherwise I’d parse it. I’d log the bad count and alert above a threshold. If unique keys outgrew memory, I’d push this to Spark or the warehouse.”

What weak answers look like: Loading the whole file with one json.load, crashing on the first bad line, or never stating the timestamp assumption.

Prepare with OphyAI: practice narrating while you code in an Interview Practice technical session; see our Python interview questions for more.

14. “Design a pipeline that powers a clickstream analytics dashboard.”

Why they ask it: This is the data engineering system design round. They test requirements gathering, component choices and failure handling.

Sample answer: “I’d confirm requirements first: say 50,000 events a second at peak, dashboards fresh within 5 minutes, and two years of history. Clients send events to a collector that validates them against a schema and writes to Kafka, keyed by user ID. A streaming job in Spark or Flink deduplicates by event ID, applies a watermark for late events, and writes small aggregates to a fast serving store for the live dashboard. The raw stream also lands in the lake as Parquet in an Iceberg or Delta table, partitioned by date. A daily batch job rebuilds sessions and core facts from raw data, which corrects any streaming drift and supports backfills. Airflow orchestrates the batch side, dbt tests guard models, and alerts cover consumer lag, freshness and volume.”

What weak answers look like: Naming tools without requirements, no plan for late or duplicate events, or no way to reprocess history.

Prepare with OphyAI: run a system design mock in Interview Practice and practice drawing the raw path and the serving path separately; our system design interview guide covers the general method.

15. “Tell me about a time a pipeline broke in production.”

Why they ask it: Every data engineer has broken something. The interviewer tests ownership, calm triage and whether you fixed the system, not just the symptom.

Sample answer: “(Situation) Our daily revenue table showed a 40% drop one Monday morning, an hour before the leadership review. (Task) I was on call and owned that pipeline. (Action) I posted in the analytics channel that the number was under investigation so nobody presented it. Row counts showed one region’s orders were missing: the source team had renamed a column, and our job loaded nulls without failing. I patched the mapping, reran the affected partitions, and confirmed totals against the source system. (Result) Corrected numbers were out in 50 minutes. Afterwards I added a not-null test and a volume check on that source, and agreed a schema change notice with the source team. We had no repeat of that failure type that quarter.”

What weak answers look like: Blaming the other team, or a story with no prevention step.

Prepare with OphyAI: build two incident stories in Interview Practice and get per-answer feedback on whether your result is specific.

16. “Tell me about a time two stakeholders disagreed on a metric definition.”

Why they ask it: Data engineers sit between teams. Conflicting definitions of “active user” or “revenue” are common, and they want someone who resolves them instead of building both.

Sample answer: “(Situation) Marketing and finance each wanted a monthly active customers table, and their numbers differed by about 12%. (Task) I owned the model both would use. (Action) I wrote out both definitions side by side: marketing counted any login, finance counted customers with a paid transaction. Neither was wrong; they answered different questions. I met both leads together, showed sample customers counted by one and not the other, and proposed two clearly named metrics, active_users and paying_customers, documented in our catalog with an owner each. (Result) Both teams signed off, the conflicting dashboards were retired, and the definitions became the template for later metric requests.”

What weak answers look like: Picking a side without discussion, or quietly building two tables with the same name.

Prepare with OphyAI: rehearse this story in Interview Practice, focusing on how you got the two leads into one room.

How to Practice These

Split preparation by round: write dedup, SCD and window-function SQL from a blank editor, sketch two star schemas until stating the grain is automatic, prepare one pipeline you built and can defend for 20 minutes, practice one end-to-end system design aloud, and build four behavioral stories (an incident, a stakeholder conflict, a backfill or migration, a mistake). Our data scientist interview questions and data analyst interview questions cover the people who use your tables.

• OphyAI Interview Practice runs voice or text mock interviews in behavioral, technical and system design formats, with follow-up questions, a transcript and per-answer feedback, so you can rehearse modeling and pipeline answers before the loop.
• OphyAI Interview Copilot gives real-time answer suggestions during live Zoom, Microsoft Teams and Google Meet interviews.

Start practicing →

Frequently Asked Questions

What are the most common data engineer interview questions?

The most common are SQL deduplication and window function problems, slowly changing dimensions, star schema design, ETL versus ELT, batch versus streaming, idempotent orchestration and backfills, data quality testing, file formats and partitioning, a practical Python task, an end-to-end pipeline design, and behavioral questions about incidents and stakeholders.

How hard is the SQL in a data engineer interview?

Expect medium difficulty with an emphasis on correctness under messy data: window functions such as ROW_NUMBER and LAG, deduplication, joins that must not drop or double-count rows, and incremental logic. Interviewers often care more about edge cases, such as ties, NULLs and late rows, than about clever syntax. Practice without autocomplete.

Do data engineer interviews include LeetCode-style coding?

Some do, usually at easy-to-medium difficulty, but many data engineering coding rounds favor practical tasks: parsing files, transforming nested JSON, deduplicating records or aggregating with dictionaries. Large tech companies are more likely to include classic data structures questions. Ask the recruiter whether the coding round is general or data-focused.

What is the difference between a data engineer and a data scientist interview?

Data engineer interviews focus on pipelines, data modeling, SQL performance, orchestration and distributed systems. Data scientist interviews focus on statistics, experiment design and modeling. Both test SQL and communication. If the role title is analytics engineer, expect heavier dbt, modeling and SQL, with less infrastructure and streaming.

How much do data engineers make in the US?

BLS does not track data engineers as a separate occupation. Its closest profile reports May 2025 medians of $139,500 for database architects and $104,620 for database administrators. Pay for titled data engineer roles, especially at large tech companies with stock grants, varies widely by level and city, and those figures are candidate-reported.

Which tools should I know for a data engineer interview?

Know one cloud warehouse or lakehouse well, an orchestrator such as Airflow, a transformation tool such as dbt, Spark for distributed processing, and Kafka concepts for streaming. Interviewers rarely require a specific tool you have not used; they test whether you understand the concepts, such as idempotency, partitioning and delivery semantics, behind any tool.

How does OphyAI work in a data engineer interview?

Use Interview Practice to rehearse modeling and pipeline design answers aloud, with follow-up questions, a transcript and per-answer feedback. On live Zoom, Microsoft Teams or Google Meet rounds, Interview Copilot gives real-time answer suggestions and a live transcript. For live coding and system design rounds, Coding Interview (Coding Copilot in the desktop app) is on the Premium plan.

Related Guides

Company guides where data engineer interviews show up:

• Amazon interview guide and Amazon Leadership Principles interview guide
• Databricks interview guide
• Snowflake interview guide
• Capital One interview guide and Capital One Power Day case interview
• Meta interview guide
• Netflix interview guide
• Airbnb interview guide
• Uber interview guide and DoorDash interview guide: streaming and marketplace data
• Spotify interview guide and Instacart interview guide

Related question guides:

• SQL interview questions
• Python interview questions
• Data scientist interview questions
• Data analyst interview questions and answers
• System design interview guide
• Behavioral interview questions and answers

Sources and verification notes

Sources checked October 2026. Questions and sample answers are original templates, not published by any employer; systems and numbers in them belong to a fictional candidate. The SQL and Python were reviewed by hand, not run. Statements about Spark skew handling, broadcast joins, QUALIFY support, Delta Lake and table-format features such as ACID commits and time travel are standard practice, not from a page fetched for this guide.

• US Bureau of Labor Statistics, Occupational Outlook Handbook: Database Administrators and Architects: May 2025 median pay for each job, 2025 employment, 2025-2035 projected growth, annual openings and typical education (page modified August 27, 2026). The page does not mention data engineers by name.
• Apache Airflow, Best Practices: tasks should produce the same outcome on every re-run; avoid INSERT on reruns; read and write a specific partition using data_interval_start; never use datetime.now() for critical computation; keep top-level DAG code light.
• Apache Airflow, DAG Runs: catchup, backfill from the UI or the backfill CLI over a date range, and max active runs.
• Apache Kafka, Design: message delivery semantics: at-most-once, at-least-once and exactly-once definitions, at-least-once by default, the idempotent producer and transactions since 0.11.0.0.
• Apache Kafka, Introduction: events with the same key go to the same partition, and order is guaranteed within a partition.
• Apache Spark, Structured Streaming APIs on DataFrames and Datasets: watermarking with withWatermark, the late-data threshold and state cleanup.
• Apache Spark, Structured Streaming overview: end-to-end exactly-once fault tolerance through checkpointing and write-ahead logs.
• dbt, Add data tests to your DAG: the four built-in generic tests and that a test fails when its query returns rows.
• dbt, Snapshots: snapshots implement Type 2 slowly changing dimensions with dbt_valid_from and dbt_valid_to, using timestamp or check strategies.
• Apache Parquet, Overview: column-oriented file format with compression and encoding schemes.
• Apache Iceberg, Partitioning: hidden partitioning and partition evolution.
• Aced (formerly Exponent), Amazon data engineer interview guide: third-party and candidate-reported; the 3-6 week timeline, onsite of 3-5 interviews with a Bar Raiser, and topic areas. Amazon’s own data engineer prep page could not be found (the URL tried returned 404).