Data engineering is the fastest-growing role in the data ecosystem. Companies need engineers who can build reliable, scalable pipelines that deliver clean, timely data to analysts and ML models. Data engineering interviews test distributed computing knowledge, SQL expertise, system design ability, and hands-on tool proficiency. This guide covers the 40 most frequently asked data engineering interview questions with complete answers for 2026.
Data Warehouse and Data Lake Questions
Q1. What is the difference between a data warehouse, data lake, and data lakehouse?
A data warehouse stores structured, processed data in a schema-enforced relational format, optimised for analytical SQL queries. Data is transformed before loading (ETL). Examples: Snowflake, BigQuery, Amazon Redshift. Fast query performance, strong governance, but expensive and inflexible — changing schema requires re-engineering. A data lake stores raw data in any format (structured, semi-structured, unstructured) at low cost (object storage: S3, GCS). Data is loaded as-is (ELT) and processed on read. Highly flexible and cheap but lacks ACID transactions, governance, and can become a “data swamp.” A data lakehouse combines both — open table formats (Delta Lake, Apache Iceberg, Apache Hudi) layer ACID transactions, schema enforcement, time travel, and efficient query optimisation on top of cheap object storage. You get data lake economics with data warehouse reliability. Databricks Delta Lake, Apache Iceberg on S3, and Google BigLake exemplify this pattern.
Q2. What is OLTP vs OLAP?
OLTP (Online Transaction Processing) systems handle high-volume, low-latency, real-time transactions: bank transfers, e-commerce orders, user signups. Optimised for INSERT, UPDATE, DELETE. Row-oriented storage (read the full row quickly). Many small transactions, concurrent users, strict ACID compliance. Examples: PostgreSQL, MySQL, MongoDB. OLAP (Online Analytical Processing) systems handle complex, aggregation-heavy queries on historical data. Optimised for SELECT with GROUP BY, SUM, AVG across millions of rows. Column-oriented storage (read only the columns needed, compress similar values). Few complex queries, batch-oriented. Examples: Snowflake, BigQuery, ClickHouse. The fundamental difference: OLTP is about capturing data accurately; OLAP is about querying data efficiently for decisions.
Q3. What is a star schema and snowflake schema? Which do you prefer for analytics?
A star schema has a central fact table (transactions, events, measurements) surrounded by denormalised dimension tables (dates, customers, products). Simple joins between fact and dimensions — one level of joins, fast query performance, easy to understand. A snowflake schema normalises dimension tables into additional sub-dimension tables. Example: the product dimension links to a separate category table. Reduces data redundancy and storage, but requires more joins and is harder to query. For analytics, prefer star schema — query performance is critical for exploratory analysis, and modern data warehouses (Snowflake, BigQuery) use columnar storage that handles the denormalisation storage cost efficiently. Snowflake schema is better when dimensions update frequently and you want to avoid updating many rows.
Q4. What is slowly changing dimension (SCD) and what are the types?
A slowly changing dimension tracks how dimension attributes change over time. Type 0: No changes allowed — historical attributes are kept as first captured. Type 1: Overwrite — update the attribute, no history kept. “Customer city changed from Mumbai to Pune” → just update the record. Simplest but loses history. Type 2: Add a new row — keep the old row and add a new row with the new attribute value, with effective_from and effective_to dates and a current_flag. Full history preserved, but dimension table grows. Most common for true historical analysis. Type 3: Add a new column — add a “previous_city” column. Only two versions of history. Type 6: Combines types 1, 2, and 3 — add a new row AND update the current value column in all rows for that entity.
Q5. What is data partitioning and clustering in BigQuery/Snowflake?
Partitioning divides a table into segments based on a column value (typically a date). When you query with a partition filter (WHERE date = ‘2026-09-01’), the database scans only that partition instead of the full table. Dramatically reduces query cost and latency. In BigQuery, partition by event_date. In Snowflake, use CLUSTER BY on the partition key. Clustering (BigQuery) / Micro-partitioning (Snowflake): further organises data within partitions by additional columns. A query filtering on clustered columns only reads the relevant micro-partitions. Best practice: partition on date (most common filter), cluster on the next most common filter columns. Proper partitioning on a 10TB table can reduce query cost from $50 to $0.05.
Apache Spark and Distributed Computing
Q6. What is Apache Spark and how does it differ from Hadoop MapReduce?
Apache Spark is a distributed in-memory processing engine for large-scale data. Hadoop MapReduce persists intermediate results to disk after every map and reduce step — safe but extremely slow due to disk I/O. Spark keeps intermediate data in memory (RDDs and DataFrames), reducing disk I/O by 10-100x for iterative workloads. Spark is also more expressive — it supports SQL, streaming, ML (MLlib), and graph processing (GraphX) in a unified API. Spark can read from and write to HDFS, S3, Kafka, databases, and many other sources. MapReduce is largely obsolete for new development; Spark has become the dominant big data processing engine. Spark on Kubernetes or managed platforms (Databricks, AWS EMR, GCP Dataproc) is the modern standard.
Q7. What is a Spark DataFrame and how is it different from an RDD?
RDD (Resilient Distributed Dataset) is Spark’s low-level API — an immutable distributed collection of objects. No schema, no optimiser, type-safe in Scala/Java but verbose in Python. DataFrame is Spark’s high-level API — a distributed table with a schema (column names and types), similar to pandas. Query plan is optimised by the Catalyst optimiser — it reorders operations, pushes down filters, and chooses join strategies automatically. Tungsten execution engine handles memory management and code generation for performance. DataFrames are 5-100x faster than equivalent RDD code for the same operations because of these optimisations. Use DataFrames (or Spark SQL) for all new Spark development. RDDs are only needed for unstructured data or when you need fine-grained control unavailable in the DataFrame API.
Q8. What is a shuffle in Spark and why is it expensive?
A shuffle is the redistribution of data across partitions, typically triggered by wide transformations: groupBy, join, distinct, repartition. Before the shuffle, data is written to disk in each executor. After, data matching the same key is read from disk on potentially different executors across the network. Shuffle is expensive because it involves disk I/O (write, then read), network I/O (data moves between machines), and serialisation/deserialisation overhead. The most common cause of Spark job slowness. Optimisation strategies: use broadcast joins when one side is small (avoids shuffle entirely); use partition keys in joins that already have the right partitioning; cache DataFrames that are used multiple times; reduce data size before shuffling by filtering and projecting early.
from pyspark.sql import SparkSession
from pyspark.sql import functions as F
spark = SparkSession.builder.appName('DataEngineering').getOrCreate()
# Read from S3 with partition pruning
df = spark.read.parquet('s3://bucket/events/date=2026-09-*')
# Broadcast join — avoids shuffle for small lookup table
small_df = spark.read.parquet('s3://bucket/products') # < 200MB
result = df.join(F.broadcast(small_df), 'product_id', 'left')
# Avoid wide transformations where possible
# Bad: groupBy on a high-cardinality column without prior filter
# Good: filter first, then aggregate
result = (df
.filter(F.col('event_type') == 'purchase')
.groupBy('user_id', F.date_trunc('day', F.col('timestamp')).alias('date'))
.agg(F.sum('amount').alias('daily_spend'),
F.count('*').alias('n_purchases'))
.write.mode('overwrite')
.partitionBy('date')
.parquet('s3://bucket/user_daily_spend/'))
Q9. What is Apache Kafka and what problems does it solve?
Apache Kafka is a distributed event streaming platform — a durable, high-throughput, low-latency publish-subscribe messaging system. Problems it solves: decoupling of data producers (web servers, mobile apps, IoT sensors) from data consumers (data warehouses, ML models, notification services); buffering traffic spikes without dropping data; enabling real-time data pipelines; and allowing multiple consumers to independently read the same events at their own pace. Core concepts: Topic — a named stream of events. Partition — a topic is split into ordered, immutable partitions for parallelism and scalability. Producer — writes events to topics. Consumer Group — reads events; each partition is consumed by exactly one consumer in a group, enabling horizontal scaling. Offset — position within a partition; consumers commit offsets to track progress. Retention — events are retained on disk for a configurable period (not deleted after consumption), allowing replay.
Q10. What is the difference between batch processing and stream processing?
Batch processing collects data over a period, then processes the entire batch at once — daily, hourly, or weekly. Simple to implement, efficient for large volumes, tolerates latency. Examples: nightly ETL jobs, monthly reporting, weekly model retraining. Stream processing handles data continuously as it arrives, typically within milliseconds to seconds. Enables real-time decisions: fraud detection, live dashboards, real-time recommendations. More complex: must handle late-arriving data, out-of-order events, stateful processing, and fault tolerance. Stream processing frameworks: Apache Kafka Streams, Apache Flink (most powerful, used for complex stateful streaming), Spark Structured Streaming. The modern trend is the Lambda architecture (batch layer + speed layer) being replaced by Kappa architecture (streaming only, with the ability to replay from Kafka for historical processing).
SQL and Data Modelling
Q11. What is query optimisation and what are the most impactful techniques?
Query optimisation reduces query execution time and resource usage. Most impactful techniques in order: (1) Partitioning — add WHERE clause on the partition key so only relevant partitions are scanned. A WHERE date = '2026-09-01' on a date-partitioned table reduces scan from the full table to one day's data. (2) Indexing — create indexes on columns used in WHERE, JOIN ON, and ORDER BY. (3) Reduce data early — SELECT only needed columns (avoid SELECT *), filter with WHERE before joining, push predicates down. (4) Join order — join the smaller filtered result first. (5) Avoid functions on indexed columns in WHERE — WHERE YEAR(date) = 2026 prevents index use; WHERE date BETWEEN '2026-01-01' AND '2026-12-31' uses it. (6) Use EXPLAIN/EXPLAIN ANALYZE to understand the execution plan and identify sequential scans on large tables.
Q12. What is dbt and how does it fit in the modern data stack?
dbt (data build tool) handles the T in ELT — transformation of data already loaded into the warehouse. You write transformations in SQL SELECT statements; dbt handles dependency management, running them in the right order, testing, and documentation. Key features: DAG — models form a directed acyclic graph; dbt resolves dependencies and runs them in order. Tests — built-in tests for not_null, unique, referential integrity, accepted_values. Documentation — auto-generates a data catalog from your schema. Materialisation strategies — table (recreate on every run), view (just a saved query), incremental (only process new/changed records), ephemeral (only used as CTE). dbt sits between data loading (Fivetran, Airbyte) and BI tools (Looker, Tableau) in the modern data stack: Extract → Load → Transform (with dbt) → Visualise.
Q13–20 (Common data engineering questions):
Q13. What is idempotency and why is it critical in ETL pipelines? An idempotent operation produces the same result when run multiple times. Critical in pipelines because failures require retries — a non-idempotent INSERT would duplicate data on retry. Solutions: upsert (INSERT OR REPLACE / MERGE), write-then-atomically-rename, partition overwrite instead of append.
Q14. What is data lineage? Tracking the origin, movement, and transformation of data across systems. Enables debugging ("where did this wrong value come from?"), impact analysis ("if I change table X, which downstream models break?"), and regulatory compliance. Tools: Apache Atlas, OpenLineage, dbt's built-in lineage graph.
Q15. What is the difference between Parquet and CSV? Parquet is columnar binary format with compression and encoding. CSV is row-oriented plain text. Parquet is 5-10x smaller (better compression), 5-100x faster to query (columnar reads only needed columns, predicate pushdown), and schema-enforced. Always use Parquet (or ORC) for analytical workloads; CSV only for data interchange.
Q16. What is a medallion architecture? A data quality pattern: Bronze (raw ingested data, as-is), Silver (cleaned, deduped, validated), Gold (business-level aggregates ready for analytics). Data flows bronze → silver → gold, improving quality at each stage. Common in Delta Lake/Databricks environments.
Q17. What is change data capture (CDC)? Tracking changes (INSERT, UPDATE, DELETE) in a source database and propagating them to downstream systems in near-real-time. Methods: database transaction log reading (Debezium reads PostgreSQL/MySQL WAL), triggers, timestamp-based (poll for updated_at > last_run). Enables real-time syncing of operational databases to data warehouses.
Q18. What is data normalisation vs denormalisation in data engineering? Normalisation (OLTP): reduce redundancy, ensure data integrity. Denormalisation (OLAP): pre-join tables, store redundant data. Analytical queries on denormalised tables (star schema) avoid expensive joins, improving query speed. The choice depends on read vs write optimisation requirements.
Q19. What is Apache Airflow and what problem does it solve? Airflow is a workflow orchestration platform — it schedules, executes, and monitors complex data pipelines (DAGs). It solves the dependency management problem: run task B only after task A succeeds; retry failed tasks; send alerts on failure; backfill historical runs. Alternatives: Prefect, Dagster (modern, Python-native), Mage.
Q20. What is the difference between Spark and Flink? Spark is primarily batch-oriented, with Structured Streaming added later — micro-batch by default (latency: seconds). Flink is natively stream-first with true record-at-a-time processing (latency: milliseconds). Flink has more sophisticated stateful stream processing and event-time handling. Choose Spark for batch + light streaming; choose Flink for complex, low-latency, stateful streaming.
System Design Questions
Q21. Design a real-time fraud detection pipeline.
Components: (1) Event stream — transaction events published to Kafka topic "transactions" as they occur. (2) Stream processor — Apache Flink or Kafka Streams consumes the topic, enriches each transaction with user history (fetched from a Redis feature store), applies real-time features (transaction velocity, device fingerprint, location anomaly), and runs the ML model inference. (3) ML model — a gradient boosting model (XGBoost or LightGBM) or neural network pretrained offline, served via a feature store + model server (Feast + MLflow). (4) Decision — if fraud score > threshold, publish to "fraud-alerts" Kafka topic. Downstream consumers block the transaction, notify the user, and log to the fraud investigation database. (5) Model retraining — daily batch job using confirmed fraud labels from human review, with automatic A/B testing of new model versions.
Q22. How would you design a data pipeline for 1TB of daily log data?
Ingestion: ship logs from application servers to an S3 landing zone using Fluentd or Logstash. Store as gzipped JSON, partitioned by date and hour. Transformation: daily Spark job on EMR or Databricks reads raw JSON, parses structured fields, handles malformed records, deduplicates, and writes Parquet partitioned by date and event_type to the "processed" S3 zone. Aggregation: dbt models in Snowflake or BigQuery compute daily/weekly aggregate tables (user activity summaries, funnel metrics, error rates). Orchestration: Apache Airflow DAG with sensors waiting for log files to arrive, triggering Spark job → dbt run → quality checks → notification. Quality checks: Great Expectations validates row counts, null rates, and value ranges before data reaches analysts.
Conclusion
Data engineering interviews in 2026 test three things: SQL mastery (window functions, query optimisation, data modelling), distributed systems knowledge (Spark internals, Kafka, streaming vs batch), and system design ability (designing end-to-end pipelines under constraints). The best preparation is building projects: set up a Spark environment locally or on Databricks Community Edition, ingest data from Kafka into Delta Lake, orchestrate it with Airflow, and model it with dbt. Hands-on experience with the modern data stack — even in a personal project — is the most convincing credential in a data engineering interview.
