The data engineering interview is one of the most multi-disciplinary in tech: you'll be tested on SQL depth, data modeling, pipeline architecture, distributed processing, and the judgment to design systems that stay correct and cheap at scale.
This guide covers the 38 questions you're most likely to face across five areas — SQL, data modeling, pipelines/ETL, distributed processing, and data system design — with what a senior interviewer is grading for.
Practice data engineering interviews out loud at interview-prep.academy — AI voice mocks, free, no card.
How data engineering interviews are structured in 2026
| Round | What's tested | Format |
|---|---|---|
| SQL | Joins, windows, optimization | Live querying |
| Coding | Python/Scala data manipulation | 45–60 min |
| Data modeling | Schema design, normalization, dimensional | Whiteboard |
| Pipelines / system design | Build an ETL/streaming system | 45 min |
| Behavioral | Ownership, incidents, trade-offs | 45 min |
SQL and data system design carry the most weight. Expect at least one deep SQL round and one "design a data pipeline" round at every company.
Part 1: SQL (12 questions)
SQL is the single most important data engineering skill tested. Expect to write real queries, not just talk.
1. Explain the difference between INNER, LEFT, RIGHT, and FULL OUTER JOIN. Grading: clear mental model + when each matters. Be ready to predict row counts with duplicate keys.
2. What's the difference between WHERE and HAVING? WHERE filters rows before aggregation; HAVING filters after (on grouped results).
3. Write a query to find the second-highest salary. Classic. Multiple approaches: subquery with MAX, DENSE_RANK(), LIMIT/OFFSET. Know the trade-offs and how each handles ties.
4. Explain window functions and give a use case. ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, running totals with SUM() OVER. The most powerful SQL topic for data engineers — expect a question that requires one.
5. Find duplicate rows in a table. GROUP BY ... HAVING COUNT(*) > 1, and how to delete duplicates keeping one (using a window function + CTE).
6. How do you optimize a slow query? Read the execution plan, add/inspect indexes, avoid SELECT *, reduce scanned partitions, push down filters, avoid functions on indexed columns. Always look at the plan first.
7. Explain indexes — types and trade-offs. B-tree vs. hash, composite indexes, covering indexes; the write-cost vs. read-benefit trade-off.
8. Write a query for a running total / cumulative sum. SUM(x) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
9. What's the difference between UNION and UNION ALL? UNION dedupes (costly sort); UNION ALL doesn't. Use ALL when you know there are no duplicates.
10. Explain CTEs and when to use them over subqueries. Readability, recursion (hierarchies), reuse within a query. Note materialization differences across engines.
11. How would you compute month-over-month growth? LAG() over month-ordered aggregates, then the percentage change. A very common analytics-style question.
12. Explain a slowly changing dimension (SCD) and how to query Type 2. Type 1 (overwrite) vs. Type 2 (history with effective dates). How to get the "current" or "as-of" row.
Part 2: Data modeling (8 questions)
13. Explain normalization vs. denormalization and when to use each. Normalize for OLTP (write integrity); denormalize for OLAP/analytics (read performance). Trade-offs both ways.
14. Star schema vs. snowflake schema? Facts + dimensions; star denormalizes dimensions (faster reads, simpler), snowflake normalizes them (less redundancy). When each fits.
15. Design a data model for [e-commerce / ride-sharing / streaming]. Identify facts (orders, trips, plays) and dimensions (user, product, time, location). Grain matters — define the fact-table grain first.
16. What's the grain of a fact table, and why does it matter? The level of detail of one row. Getting the grain wrong breaks every downstream aggregation.
17. How do you handle slowly changing dimensions? SCD types 0–3; when Type 2 (full history) is worth the storage.
18. Wide table vs. star schema in a modern warehouse — trade-offs? Columnar warehouses (Snowflake/BigQuery) make wide denormalized tables viable; when that beats classic star.
19. How do you model many-to-many relationships? Bridge/junction tables; handling them in dimensional models.
20. How do you design for data quality and idempotency? Primary/natural keys, dedup strategy, constraints, and making loads re-runnable without double-counting.
Part 3: Pipelines & ETL/ELT (8 questions)
21. ETL vs. ELT — what's the difference and which do you prefer? Transform before vs. after load. ELT dominates with modern warehouses (load raw, transform in-warehouse with dbt). Know why.
22. How do you make a pipeline idempotent? Re-running produces the same result: deterministic keys, MERGE/upsert, partition overwrites, dedup. A top real-world concern.
23. Batch vs. streaming — when do you use each? Latency requirement drives it. Batch for periodic aggregates; streaming (Kafka/Flink) for real-time. Lambda vs. Kappa architectures.
24. How do you handle late-arriving data? Watermarks, windowing with allowed lateness, reprocessing strategies, backfills.
25. How do you handle schema evolution in a pipeline? Schema registry, backward/forward compatibility, additive changes, Avro/Parquet evolution rules.
26. Design an ETL pipeline for [daily sales / event logs]. Source → ingestion → staging (raw) → transform → serving. Add: orchestration (Airflow), data quality checks, monitoring, retries, alerting.
27. How do you orchestrate dependencies between jobs? DAGs (Airflow/Dagster), task dependencies, retries with backoff, SLAs, idempotent tasks so a retry is safe.
28. How do you monitor data pipelines and ensure data quality? Freshness, volume, schema, and distribution checks; tools like Great Expectations/dbt tests; alerting on anomalies; data contracts.
Part 4: Distributed processing (6 questions)
29. Explain how Spark works (driver, executors, partitions). Lazy evaluation, transformations vs. actions, the DAG scheduler, shuffles.
30. What is a shuffle, and why is it expensive? Data movement across the network between stages. Minimize shuffles; the #1 Spark performance lever.
31. How do you handle data skew? Salting keys, broadcast joins for small tables, repartitioning, AQE (Adaptive Query Execution).
32. Narrow vs. wide transformations? Narrow (map, filter — no shuffle) vs. wide (groupBy, join — shuffle). Why it matters for performance.
33. How do you optimize a slow Spark job? Reduce shuffles, broadcast small tables, right-size partitions, cache reused datasets, choose the right file format (Parquet) and partitioning, push down filters.
34. Parquet/ORC vs. row formats — why columnar for analytics? Column pruning, compression, predicate pushdown. Why columnar wins for OLAP scans.
Part 5: Data system design (4 questions)
35. Design a data warehouse for an analytics team. Ingestion (batch + CDC), raw/staging/marts layers, dimensional modeling, orchestration, governance, cost controls.
36. Design a real-time analytics pipeline (clickstream). Producers → Kafka → stream processor (Flink/Spark Streaming) → serving store (Druid/ClickHouse) → dashboards. Handle ordering, exactly-once, late data.
37. Design a system to ingest data from 100s of sources. Connector framework, schema registry, standardized landing zone, data contracts, observability, backfill strategy.
38. Design a metrics/feature store. Offline (batch) vs. online (low-latency) consistency, point-in-time correctness to avoid training/serving skew, freshness.
The biggest data engineering interview mistakes
- Weak SQL. Strong system-design talk can't compensate for fumbling window functions live. SQL is the floor.
- Skipping the fact-table grain. In modeling questions, not defining the grain first leads to a broken model.
- Ignoring idempotency. Real pipelines re-run. "How is this re-runnable without double-counting?" is a question you must answer proactively.
- Not addressing data quality. A pipeline design with no monitoring, no checks, and no alerting reads as junior.
- Over-indexing on tools. Naming ten technologies without explaining trade-offs loses to clear reasoning about latency, correctness, and cost.
FAQ
How important is SQL for a data engineering interview? It's the most important single skill. Expect at least one deep SQL round where you write real queries, including window functions and optimization. Weak SQL is the most common reason strong candidates fail.
Do I need to know Spark specifically? Know a distributed processing engine well — Spark is the most common. The concepts (partitioning, shuffles, skew, lazy evaluation) transfer to Flink and others, and that's what's actually graded.
What's the difference between a data engineer and an analytics engineer interview? Data engineering leans more on pipelines, distributed systems, and infrastructure. Analytics engineering leans more on SQL, dbt, modeling, and the warehouse layer. There's overlap, but the system-design depth differs.
How long should I prepare for a data engineering interview? With daily out-loud practice: 3–5 weeks for most candidates. Prioritize SQL (especially window functions), data modeling, and one solid pipeline/system-design framework.
What's the most common data engineering interview question? A SQL window-function problem (e.g., second-highest salary, running total, or month-over-month growth) and a "design an ETL pipeline" system-design question appear in nearly every loop.