Data engineer interview questions: SQL, pipelines and data modeling
Data engineer interviews test four things: SQL beyond the basics, how you build reliable pipelines, how you model data, and how you reason about scale. Strong answers say what breaks (late data, duplicates, schema change, skew) and how you would make the job safe to re-run. This guide gives twenty questions with short answers, built on the public Airflow, dbt, Spark and Kimball documentation.
How the interview is usually structured
Expect a SQL screen (window functions, joins, deduplication), a pipeline design discussion ("design a daily ingestion of orders"), a modelling question, and questions on a tool you list. General SQL basics are in our SQL interview questions; this guide goes to the data-engineering layer. Say your assumptions aloud and ask about volume, latency and who consumes the data. See also the system design interview framework.
SQL questions for data engineers
1. How do you remove duplicates and keep the latest row?
Rank rows per key with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) and keep rank 1. Mention that ties need a deterministic tie-breaker.
2. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
ROW_NUMBER gives unique numbers. RANK gives equal rows the same number and skips the next ones. DENSE_RANK gives equal rows the same number without gaps.
3. How do you find the running total or a 7-day moving average?
Use a window aggregate with a frame, for example SUM(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Note the difference between ROWS and RANGE when days are missing.
4. Why can a LEFT JOIN produce more rows than the left table?
If the right side has several matches per key, each match yields a row. Check key uniqueness on both sides and aggregate or deduplicate the right side first.
5. How do you read a slow query plan?
Look for full scans, large sorts, shuffles and join order. Check filters are applied early, statistics are fresh and the partition or clustering key is used.
Pipeline and orchestration questions
6. ETL or ELT, and when?
ETL transforms before loading, useful when data must be filtered or masked before it lands. ELT loads raw data first and transforms inside the warehouse, which is common with cheap storage and SQL tooling. Say which constraints decide it: privacy, volume, team skills.
7. What is an idempotent pipeline and why does it matter?
Re-running a task gives the same result. Airflow's guidance says tasks "should produce the same outcome on every re-run" and suggests upserts rather than plain inserts. Overwrite a partition or merge on a key so retries do not duplicate rows.
8. How do you handle late-arriving data?
Keep event time separate from load time, reprocess a lookback window, and make loads idempotent. In streaming, define watermarks. Decide with the business how late is too late.
9. How do you design incremental loads?
Track a high-water mark such as updated_at or an ID, or use change data capture. In dbt, incremental models transform only new rows and a unique_key lets existing rows be updated; without it most adapters just append, which can duplicate rows.
10. How do you test and monitor a pipeline?
Test DAG import and task logic, validate row counts, null rates, uniqueness and freshness, and alert on failure and on silent drift. Airflow says DAGs should be treated as production code.
11. What would you avoid in an Airflow DAG file?
Heavy top-level code, because it runs on every parse, and reading "the latest" data or using the current time inside tasks, which breaks reruns. Use fixed partitions or the run's logical date.
Data modelling questions
12. Star schema versus snowflake schema?
A star has a central fact table joined to denormalised dimensions: simple and fast to query. A snowflake normalises dimensions into sub-tables, saving some storage but adding joins.
13. What is the grain of a fact table?
The exact meaning of one row, such as one order line. Decide it first; mixing grains causes wrong totals.
14. What is a slowly changing dimension Type 2?
When an attribute changes you add a new row, with effective and expiry dates and a current-row flag, and a new surrogate key. Facts then point at the version that was true at the time.
15. Why use surrogate keys?
They are stable and independent of source systems, and Type 2 history needs several rows per business key.
16. Normalised or denormalised for analytics?
Normalised models suit transactional writes; analytics usually prefers denormalised, read-optimised tables. State the trade-off in terms of query patterns and cost.
Scale and big-data questions
17. What is data skew and how do you fix it?
One key holds far more data, so one task runs long. Options: salting the key, broadcasting the small side, filtering hot keys separately or letting Spark's adaptive execution split skewed partitions, which is on by default since Spark 3.2.
18. When do you use a broadcast join?
When one side is small enough to send to every executor, avoiding a shuffle. The default size threshold is 10 MB and hints are not guaranteed to be honoured.
19. How do you choose partitions and file sizes?
Partition by a column used in filters with moderate cardinality, usually date. Avoid many tiny files. In Spark, spark.sql.shuffle.partitions defaults to 200 and should be tuned or left to adaptive execution.
20. Batch versus streaming?
Choose by the freshness the consumer needs. Streaming costs more in complexity: ordering, exactly-once semantics, state and replay. Many "real-time" requests are fine with micro-batches.
A pipeline design answer in five steps
- Clarify source, volume, latency and consumers.
- Land raw data unchanged with load timestamps.
- Transform into clean, typed, deduplicated tables with tests.
- Model for the queries: facts, dimensions, aggregates.
- Orchestrate with retries, idempotent tasks, alerts and backfill support.
Cover cost and failure modes at the end; interviewers listen for them.
Practising honestly
Practise out loud with a timer. Interview Copilot has a free plan without a card, useful for mock interviews, and its coding assistant can explain a SQL problem from a screenshot. For a real interview or assessment, ask the recruiter what tools are allowed first and follow the answer. More: Python interview questions and technical interview preparation.
Sources
- Apache Airflow: best practices
- dbt: incremental models
- Apache Spark: SQL performance tuning
- Kimball Group: Type 2 slowly changing dimension
Checked 13 November 2026.
FAQ
What are the most common data engineer interview questions?
SQL window functions and deduplication, ETL versus ELT, idempotent and incremental pipelines, late-arriving data, star schema and slowly changing dimensions, and Spark topics such as skew, joins and partitioning.
How is a data engineer interview different from a SQL interview?
SQL is one part. You are also asked to design reliable pipelines, model data for analytics, handle failures and reruns, and reason about scale and cost, often on a whiteboard or shared document.
What is an idempotent pipeline?
A pipeline whose reruns give the same result. Airflow's guidance says tasks should produce the same outcome on every re-run. Use upserts or partition overwrites instead of plain inserts so retries do not create duplicates.
What is a Type 2 slowly changing dimension?
Instead of overwriting a changed attribute, you add a new row with effective and expiry dates, a current flag and a new surrogate key, so history is preserved. Kimball describes it as adding a new row with the updated values.
How do you fix data skew in Spark?
Broadcast the small side of a join, salt hot keys, process them separately, or rely on adaptive query execution, which can split skewed partitions and has been on by default since Spark 3.2.
Do I need Spark and Airflow for the interview?
Only if the job asks for them. Read the posting. SQL, data modelling and pipeline thinking transfer across tools, so prepare those first and add the stack named in the role.
Can I use an AI assistant in the interview?
Only if the interviewer allows it. Ask first. Use AI tools for practice and mock interviews at any time.