50 Data Engineer Interview Questions (With Answers)

Data engineering interviews test your ability to design reliable data pipelines, model data efficiently, and work across the full stack — SQL, Python, distributed systems, and cloud platforms. This guide covers the 50 most important questions with concise, accurate answers.

Quick reference

Core data engineering concepts

1. What is data engineering?

Data engineering is the discipline of building and maintaining infrastructure and pipelines that collect, transform, and serve data for analysis and machine learning.

Key responsibilities:

• Design and build data pipelines (ETL/ELT)
• Model and warehouse data for analytical queries
• Ensure reliability, scalability, and data quality
• Collaborate with data scientists and analysts

2. What is the difference between ETL and ELT?

Modern preference: ELT has become dominant because cloud warehouses have cheap, elastic compute — you load raw data first and transform inside the warehouse using SQL or dbt.

3. What is a data pipeline?

A data pipeline is an automated workflow that moves data from source to destination through a series of processing steps:

Source → Ingest → Validate → Transform → Load → Serve

Types:

• Batch pipeline — processes data on a schedule (hourly, daily)
• Streaming pipeline — processes data in real-time as events arrive
• Micro-batch — near-real-time, batches every few seconds (Spark Structured Streaming default)

4. What is the difference between a data warehouse, data lake, and data lakehouse?

Lakehouse (coined by Databricks) combines the flexibility of a lake with the reliability and query performance of a warehouse — using open table formats like Delta Lake, Apache Iceberg, or Apache Hudi.

5. What is Change Data Capture (CDC)?

CDC is a technique that identifies and captures row-level changes (INSERT, UPDATE, DELETE) in a source database and propagates them downstream.

Methods:

• Log-based CDC — reads database transaction log (binlog in MySQL, WAL in PostgreSQL). Most reliable, low overhead.
• Query-based CDC — polls tables using a updated_at timestamp. Simple but misses deletes.
• Trigger-based CDC — database triggers write changes to a shadow table.

Tools: Debezium (open-source, Kafka-based), AWS DMS, Fivetran, Airbyte.

MySQL binlog → Debezium connector → Kafka topic → Spark consumer → Warehouse

6. What is the difference between batch and streaming data processing?

Lambda architecture combines both: a batch layer for accuracy and a speed layer for low latency.

Data modeling

7. What is a star schema?

A star schema organises data into a central fact table surrounded by dimension tables:

         DimDate
            |
DimProduct — FactSales — DimCustomer
            |
         DimStore

• Fact table — measurable business events (sales, clicks, orders). Contains foreign keys and numeric measures (revenue, quantity).
• Dimension table — descriptive attributes (product name, customer city, date).

Benefits: Simple joins, fast aggregations, easy for analysts to understand.

8. What is a snowflake schema?

A snowflake schema normalises dimension tables into sub-dimensions:

DimProduct → DimCategory → DimDepartment

Typical choice: Star schema for OLAP/reporting; snowflake if storage matters or dimensions are very large.

9. What are Slowly Changing Dimensions (SCD)?

SCDs handle how dimension records change over time.

Type 2 example:

-- New row added when customer city changes
customer_id | city       | valid_from | valid_to   | is_current
1001        | New York   | 2023-01-01 | 2024-06-30 | false
1001        | Boston     | 2024-07-01 | 9999-12-31 | true

10. What is a surrogate key vs a natural key?

Best practice: Use surrogate keys in the warehouse; keep natural key as business_key column.

11. What is data vault modeling?

Data Vault is a modeling methodology designed for auditability, scalability, and flexibility:

Pros: Audit trail, parallel loading, no DELETE/UPDATE (append-only).
Cons: Many tables, complex queries — usually materialised into a star schema for reporting (business vault / information mart).

SQL

12. Write a query to find the top 3 products by revenue per region.

WITH ranked AS (
  SELECT
    region,
    product_id,
    SUM(revenue) AS total_revenue,
    RANK() OVER (PARTITION BY region ORDER BY SUM(revenue) DESC) AS rnk
  FROM sales
  GROUP BY region, product_id
)
SELECT region, product_id, total_revenue
FROM ranked
WHERE rnk <= 3;

13. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

SELECT
  name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM results;
-- score 90,90,80 → row_num 1,2,3 | rank 1,1,3 | dense_rank 1,1,2

14. What is a CTE and when would you use a recursive CTE?

A Common Table Expression (CTE) creates a named temporary result set within a query:

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
),
growth AS (
  SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT month, revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM growth;

Recursive CTE for hierarchical data (org chart, category tree):

WITH RECURSIVE org_tree AS (
  -- Anchor: top-level employees
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: add direct reports
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.id
)
SELECT * FROM org_tree ORDER BY depth, name;

15. How do you identify and remove duplicate rows?

-- Identify duplicates
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- Delete duplicates, keep latest row (PostgreSQL)
DELETE FROM users
WHERE id NOT IN (
  SELECT MAX(id)
  FROM users
  GROUP BY email
);

-- Using ROW_NUMBER (works on all major databases)
WITH dupes AS (
  SELECT id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM dupes WHERE rn > 1);

16. What is query partitioning and how does it improve performance?

Partition pruning lets the query engine skip irrelevant partitions entirely:

-- Without partition: scans all 365 days of data
SELECT * FROM events WHERE event_date = '2024-01-15';

-- With partition on event_date: scans only 1/365 of data
CREATE TABLE events PARTITION BY RANGE (event_date);
CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

In BigQuery:

-- Only scans the partition for 2024-01-15 (~1 day of data)
SELECT * FROM `project.dataset.events`
WHERE DATE(_PARTITIONTIME) = '2024-01-15';

Python for data engineering

17. How do you read and process a large CSV file without loading it into memory?

import csv

def process_large_csv(filepath: str):
    with open(filepath, 'r') as f:
        reader = csv.DictReader(f)
        for row in reader:  # reads one row at a time
            yield row       # generator — memory-efficient

# Using pandas chunking
import pandas as pd

for chunk in pd.read_csv('large.csv', chunksize=100_000):
    process(chunk)  # each chunk is a DataFrame of 100k rows

18. What is the difference between map(), filter(), and list comprehensions in Python?

data = [1, 2, 3, 4, 5, 6]

# filter even numbers, square them
squares = [x**2 for x in data if x % 2 == 0]   # list comprehension — preferred
squares = list(map(lambda x: x**2, filter(lambda x: x % 2 == 0, data)))  # functional

# Generator expression (memory-efficient for large data)
total = sum(x**2 for x in data if x % 2 == 0)

For data engineering: prefer generator expressions and pandas vectorised operations over loops.

19. How do you handle missing values in pandas?

import pandas as pd

df = pd.read_csv('data.csv')

# Detect
print(df.isnull().sum())

# Drop rows where any column is null
df_clean = df.dropna()

# Drop only if ALL columns are null
df_clean = df.dropna(how='all')

# Fill with static value
df['age'] = df['age'].fillna(df['age'].median())

# Forward fill (time series)
df['price'] = df['price'].ffill()

# Flag instead of impute (preserve signal)
df['age_missing'] = df['age'].isnull().astype(int)
df['age'] = df['age'].fillna(-1)

20. How do you write unit tests for a data transformation function?

import pandas as pd
import pytest

def calculate_revenue(df: pd.DataFrame) -> pd.DataFrame:
    """Add revenue column: quantity * unit_price."""
    df = df.copy()  # immutable pattern
    df['revenue'] = df['quantity'] * df['unit_price']
    return df

# Test
def test_calculate_revenue():
    input_df = pd.DataFrame({
        'quantity': [2, 3],
        'unit_price': [10.0, 5.0]
    })
    result = calculate_revenue(input_df)
    assert list(result['revenue']) == [20.0, 15.0]

def test_calculate_revenue_zero_quantity():
    input_df = pd.DataFrame({'quantity': [0], 'unit_price': [10.0]})
    result = calculate_revenue(input_df)
    assert result['revenue'].iloc[0] == 0.0

def test_calculate_revenue_does_not_mutate_input():
    input_df = pd.DataFrame({'quantity': [1], 'unit_price': [5.0]})
    _ = calculate_revenue(input_df)
    assert 'revenue' not in input_df.columns  # input unchanged

Apache Spark / PySpark

21. What is the difference between a transformation and an action in Spark?

# Transformations (lazy — nothing runs yet)
df_filtered = df.filter(df.age > 25)
df_grouped  = df_filtered.groupBy('country').agg(count('*'))

# Action — triggers the full DAG
df_grouped.show()  # NOW Spark runs everything

22. What is the difference between repartition() and coalesce()?

# Writing to storage: coalesce to avoid many small files
df.coalesce(10).write.parquet('output/')

# Before a large join: repartition on join key to co-locate data
df.repartition(200, 'customer_id').join(other, 'customer_id')

23. What causes data skew in Spark and how do you fix it?

Skew occurs when some partitions are much larger than others (e.g., one customer has 80% of orders).

Detection:

df.groupBy(spark_partition_id()).count().show()  # one partition >> others

Fixes:

# Salting: spread hot key across N partitions
from pyspark.sql.functions import col, concat_ws, lit, monotonically_increasing_id

N = 20
# Add random salt to big table
big = df_big.withColumn('salt', (monotonically_increasing_id() % N).cast('string'))
big = big.withColumn('salted_key', concat_ws('_', col('customer_id'), col('salt')))

# Explode small table N times
from pyspark.sql.functions import array, explode
small = df_small.withColumn('salt_array', array([lit(str(i)) for i in range(N)]))
small = small.withColumn('salt', explode('salt_array'))
small = small.withColumn('salted_key', concat_ws('_', col('customer_id'), col('salt')))

result = big.join(small, 'salted_key')

24. What file formats are used in data engineering and when?

Rule of thumb: Use Parquet for static analytics data; use Delta Lake / Iceberg when you need ACID transactions, schema evolution, or time travel.

25. What is a broadcast join in Spark?

A broadcast join sends a small table to every executor, avoiding a shuffle of the large table:

from pyspark.sql.functions import broadcast

# Manual broadcast hint
result = orders.join(broadcast(dim_country), 'country_code')

# Auto-broadcast if table < spark.sql.autoBroadcastJoinThreshold (default 10MB)
spark.conf.set('spark.sql.autoBroadcastJoinThreshold', '50m')

When to use: dimension table < 50–200 MB (depending on executor memory).

Apache Kafka

26. What is a Kafka topic, partition, and consumer group?

Topic: "orders"
├── Partition 0: [msg1, msg2, msg5]  → Consumer A (Group 1)
├── Partition 1: [msg3, msg6]        → Consumer B (Group 1)
└── Partition 2: [msg4, msg7]        → Consumer C (Group 1)

• Topic — logical stream of messages (like a table)
• Partition — ordered, immutable log; unit of parallelism
• Consumer group — set of consumers that collectively read a topic; each partition assigned to one consumer
• Offset — message position within a partition; consumers commit offsets to track progress

27. How do you ensure exactly-once delivery in Kafka?

from confluent_kafka import Producer

producer = Producer({
    'bootstrap.servers': 'broker:9092',
    'enable.idempotence': True,          # exactly-once at producer level
    'acks': 'all',
    'max.in.flight.requests.per.connection': 5,
    'retries': 2147483647,
    'transactional.id': 'my-transactional-id',  # for cross-partition transactions
})

producer.init_transactions()
producer.begin_transaction()
try:
    producer.produce('orders', key='k1', value='v1')
    producer.commit_transaction()
except Exception:
    producer.abort_transaction()

28. What is Kafka lag and how do you monitor it?

Consumer lag = latest offset in partition − consumer's committed offset.

High lag → consumer is falling behind producers.

# Check lag with kafka-consumer-groups CLI
kafka-consumer-groups.sh \
  --bootstrap-server broker:9092 \
  --describe --group my-group
# Shows: TOPIC | PARTITION | CURRENT-OFFSET | LOG-END-OFFSET | LAG

Monitor with: Kafka JMX metrics, Prometheus + kafka_exporter, Confluent Control Center, Burrow (LinkedIn's lag monitor).

Apache Airflow

29. What is an Airflow DAG?

A DAG (Directed Acyclic Graph) defines a workflow: tasks and their dependencies.

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.operators.bash import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'data-team',
    'retries': 2,
    'retry_delay': timedelta(minutes=5),
    'email_on_failure': True,
}

with DAG(
    dag_id='daily_etl',
    default_args=default_args,
    start_date=datetime(2024, 1, 1),
    schedule='@daily',
    catchup=False,
    tags=['etl', 'production'],
) as dag:

    extract = BashOperator(task_id='extract', bash_command='python extract.py')
    transform = PythonOperator(task_id='transform', python_callable=transform_fn)
    load = PythonOperator(task_id='load', python_callable=load_fn)

    extract >> transform >> load  # dependency chain

30. What is the difference between Airflow sensors and operators?

from airflow.sensors.s3_key_sensor import S3KeySensor

wait_for_file = S3KeySensor(
    task_id='wait_for_s3_file',
    bucket_name='my-bucket',
    bucket_key='data/{{ ds }}/input.parquet',
    poke_interval=300,      # check every 5 minutes
    timeout=7200,           # fail after 2 hours
    mode='reschedule',      # free worker slot between checks
)

31. What is XCom in Airflow?

XCom (cross-communication) lets tasks share small values between each other:

def extract(**context):
    row_count = 42_000
    context['ti'].xcom_push(key='row_count', value=row_count)

def validate(**context):
    row_count = context['ti'].xcom_pull(task_ids='extract', key='row_count')
    assert row_count > 0, 'No rows extracted!'

extract_task = PythonOperator(task_id='extract', python_callable=extract)
validate_task = PythonOperator(task_id='validate', python_callable=validate)

Warning: XCom is stored in Airflow's metadata database — keep values small (< 1MB). For large data, pass paths/URIs instead.

32. How do you handle Airflow DAG backfilling and catchup?

# catchup=True: backfill all runs since start_date (default)
# catchup=False: only run for current/future intervals

with DAG('my_dag', start_date=datetime(2024, 1, 1), catchup=False):
    ...

# Manual backfill via CLI
airflow dags backfill --start-date 2024-01-01 --end-date 2024-01-31 my_dag

Best practice: Set catchup=False for new DAGs unless you explicitly need historical runs. Use max_active_runs=1 to prevent overlapping runs.

dbt (data build tool)

33. What is dbt and how does it fit in the data stack?

dbt transforms raw data inside the warehouse using SQL + version control + testing:

Raw layer (S3/SFTP)
    ↓ Fivetran/Airbyte
Staging layer (raw warehouse tables)
    ↓ dbt models (SQL SELECT statements)
Intermediate layer (cleaned, joined)
    ↓
Marts layer (star schema, served to BI)

34. What are dbt models, sources, and seeds?

-- models/marts/dim_customer.sql
WITH source AS (
  SELECT * FROM {{ source('raw', 'customers') }}
),
cleaned AS (
  SELECT
    customer_id,
    TRIM(LOWER(email)) AS email,
    COALESCE(country, 'Unknown') AS country,
    created_at::DATE AS created_date
  FROM source
  WHERE customer_id IS NOT NULL
)
SELECT * FROM cleaned

Data quality

35. How do you implement data quality checks?

Layers of quality checks:

# dbt schema.yml — built-in tests
models:
  - name: dim_customer
    columns:
      - name: customer_id
        tests:
          - not_null
          - unique
      - name: email
        tests:
          - not_null
          - unique
      - name: country
        tests:
          - accepted_values:
              values: ['US', 'UK', 'CA', 'DE']

36. What is data lineage?

Data lineage tracks the origin, movement, and transformation of data through pipelines:

CRM DB → Fivetran → raw.customers → dbt dim_customer → BI dashboard

Benefits: impact analysis (if raw table changes), debugging (where did this NULL come from?), compliance (GDPR data deletion).

Tools: dbt docs/lineage graph, OpenLineage, Apache Atlas, DataHub, Marquez.

Cloud data platforms

37. What is Snowflake's virtual warehouse and how does it scale?

A Snowflake virtual warehouse is a cluster of compute nodes independent of storage:

• Multi-cluster warehouses — automatically add clusters during peak load
• Auto-suspend — pauses after idle period (stops billing)
• Auto-resume — wakes on next query

-- Create a warehouse
CREATE WAREHOUSE etl_wh
  WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 300         -- suspend after 5 min idle
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3;     -- scale to 3 clusters under load

-- Use it
USE WAREHOUSE etl_wh;

38. What is BigQuery's partitioning and clustering?

Partitioning divides table data by a date/integer column — queries on that column scan only relevant partitions.

Clustering sorts data within each partition on up to 4 columns — reduces data scanned for filter/aggregate queries.

CREATE TABLE `project.dataset.events`
PARTITION BY DATE(event_ts)           -- partition by day
CLUSTER BY user_id, event_type        -- cluster within each partition
AS SELECT * FROM staging.events;

-- This query scans only 2024-01 partition and skips irrelevant user blocks
SELECT COUNT(*) FROM `project.dataset.events`
WHERE DATE(event_ts) = '2024-01-15'
AND user_id = '12345';

39. What is the difference between Redshift SORT KEY and DIST KEY?

CREATE TABLE fact_orders (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  revenue DECIMAL(10,2)
)
DISTKEY(customer_id)    -- join partner dim_customer also on customer_id
SORTKEY(order_date);    -- most queries filter by date

Architecture patterns

40. What is the Lambda architecture?

Lambda architecture processes data in two paths:

Source → Batch layer (accurate, hours-old)  ─┐
       → Speed layer (approximate, real-time) ─┼→ Serving layer → BI

Limitation: Duplicated code (batch + streaming logic). Led to Kappa architecture.

41. What is the Kappa architecture?

Kappa removes the batch layer — process everything as a stream, reprocess historical data by replaying the event log:

Source → Event log (Kafka) → Stream processor (Flink/Spark) → Serving
                    ↑ replay for reprocessing

Tradeoff: Simpler code but requires a long-retention event log and stream processor capable of high-throughput historical replay.

42. What is the Medallion architecture (Bronze/Silver/Gold)?

Used in Databricks/Delta Lake:

Benefits: data lineage tracing, easy rollback, incremental processing at each layer.

Performance and optimisation

43. How do you optimise a slow Spark job?

Checklist:

44. What is incremental loading and why does it matter?

Incremental loading only processes new/changed records since the last run, rather than reloading everything (full refresh).

# Incremental in dbt
{{ config(materialized='incremental', unique_key='event_id') }}

SELECT event_id, user_id, event_type, event_ts
FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
  WHERE event_ts > (SELECT MAX(event_ts) FROM {{ this }})
{% endif %}

Benefits: faster runs, less cost, lower DWH load.
Risk: misses late-arriving data unless you look back N days.

Practical scenarios

45. Design a pipeline to ingest 10 million rows/day from a REST API.

Architecture:

REST API → Python ingestion worker → Kafka topic (raw_api_events)
         → Spark Structured Streaming → Bronze Delta table
         → dbt Silver model (schedule: hourly)
         → dbt Gold model (schedule: daily)
         → BI (Tableau/Looker)

Key decisions:
1. Pagination: cursor-based (not offset) for large datasets
2. Rate limiting: exponential backoff, token bucket
3. Idempotency: de-duplicate on event_id in Silver
4. Monitoring: row count alert, API error rate alert
5. Schema evolution: Avro + Schema Registry for Kafka

46. How would you handle late-arriving data in a streaming pipeline?

# Spark Structured Streaming with watermark
from pyspark.sql.functions import window, col

stream_df = (
    spark.readStream.format('kafka')
    .load()
    .withWatermark('event_ts', '2 hours')  # accept late data up to 2 hours
    .groupBy(window(col('event_ts'), '1 hour'), col('country'))
    .count()
)

Watermark tells Spark to drop state older than X hours — balances completeness vs memory.

For batch: use a lookback window — daily job reprocesses last 3 days to catch late data.

47. How do you perform a zero-downtime migration of a large warehouse table?

Expand-contract pattern:

Phase 1 — Expand:
  Add new column(s) alongside old; write to both

Phase 2 — Migrate:
  Backfill new column for existing rows

Phase 3 — Switch:
  Update readers to use new column; update writers

Phase 4 — Contract:
  Drop old column once all readers migrated

For table rename / schema change in Delta Lake / Iceberg:

-- Iceberg supports safe schema evolution
ALTER TABLE orders ADD COLUMN discount_pct DOUBLE;
ALTER TABLE orders RENAME COLUMN old_name TO new_name;
-- No data rewrite required

48. How do you monitor a production data pipeline?

Tools: Airflow alerts, Datadog, PagerDuty, Monte Carlo, Soda Core.

Advanced topics

49. What is the difference between OLTP and OLAP databases?

50. What is Apache Iceberg and why is it gaining adoption?

Apache Iceberg is an open table format for huge analytic datasets on object storage:

# Spark + Iceberg
spark.sql("""
  CREATE TABLE catalog.db.orders
  USING ICEBERG
  PARTITIONED BY (months(order_ts))
  AS SELECT * FROM staging.orders
""")

# Time travel
spark.sql("""
  SELECT * FROM catalog.db.orders
  FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-01 00:00:00'
""")

Common mistakes

Data engineer vs related roles

Frequently asked questions

Q: What languages do data engineers use?
Python (primary), SQL (essential), Scala (Spark internals), sometimes Java. Shell scripting for ops tasks.

Q: Do I need to know Spark for a data engineering role?
At most companies: yes. Spark is the dominant distributed processing engine. At smaller companies, you may work with dbt + SQL only. Know Spark concepts even if you use managed tools like Databricks or AWS Glue.

Q: What is the difference between a data engineer and an analytics engineer?
An analytics engineer (popularised by dbt) focuses on the transformation layer inside the warehouse — writing dbt models, maintaining star schemas, and collaborating with analysts. A data engineer builds the broader infrastructure: ingestion, streaming, orchestration, and the raw storage layer.

Q: How do you choose between Airflow and Prefect/Dagster?
All three orchestrate Python workflows. Airflow has the largest ecosystem and is battle-tested. Prefect offers a simpler Python-native API. Dagster focuses on data assets (software-defined assets) with excellent observability. For large teams with complex DAGs: Airflow. For modern Python-first teams: Prefect or Dagster.

Q: What is the role of dbt in a modern data stack?
dbt handles the T in ELT — it runs inside the warehouse, version-controls SQL transformations, provides testing, documentation, and lineage. It doesn't ingest data (that's Fivetran/Airbyte) and doesn't orchestrate (that's Airflow/Prefect), but it integrates with both.

Q: What certifications are valuable for data engineers?

• Cloud: AWS Data Engineer Associate, GCP Professional Data Engineer, Azure Data Engineer Associate (DP-203)
• Databricks: Databricks Certified Data Engineer Professional
• dbt: dbt Analytics Engineering Certification
• Snowflake: SnowPro Core Certification