Data Engineer Interview Prep: 50+ Questions, Answers & Study Plan (Full Guide)

The interviewer pulls up a whiteboard and says: "Design a real-time pipeline that handles 10 million events per second with exactly-once delivery guarantees."

You know Kafka. You know Spark. You've built pipelines. But your brain goes blank because you've been studying LeetCode arrays and strings for the last two weeks — and this interview isn't about algorithms at all.

Data engineer interviews are unlike any other technical interview. They test systems thinking, not syntax. They care about trade-offs, not textbook answers. And most candidates prepare for the wrong thing because they follow software engineering interview advice that doesn't apply.

Brought to you by Careery

This article was researched and written by the Careery team — that helps land higher-paying jobs faster than ever! Learn more about Careery →

SQL is the most heavily tested skill in data engineer interviews. You'll face questions ranging from basic aggregations to complex window functions and query optimization.

Q1: Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()

• ROW_NUMBER() assigns a unique sequential integer to each row — no ties, no gaps. Use for deduplication (keeping the latest record per key).
• RANK() assigns the same rank to tied values but skips the next rank(s). If two rows tie at rank 2, the next row gets rank 4.
• DENSE_RANK() assigns the same rank to tied values but does NOT skip ranks. If two rows tie at rank 2, the next row gets rank 3.

Q2: Write a query to find the second highest salary in each department

SELECT department_id, employee_id, salary
FROM (
  SELECT
    department_id,
    employee_id,
    salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

Q3: What's a CTE and when would you use one over a subquery?

Q4: How would you optimize a slow-running query?

1. Read the execution plan — identify full table scans, sort operations, and joins with high row estimates
2. Check indexing — are filter columns and join keys indexed? Are indexes being used (watch for implicit type casts that bypass indexes)?
3. Reduce data early — push filters before joins, use WHERE before HAVING, limit the columns selected
4. Evaluate join strategy — nested loop vs hash join vs merge join. For large-to-large table joins, hash joins are usually optimal
5. Consider partitioning — if filtering on date ranges, is the table partitioned by date?
6. Materialize intermediate results — if a complex subquery runs multiple times, materialize it as a temp table

Q5: Explain slowly changing dimensions (SCD Type 1, 2, and 3)

• Type 1 (Overwrite): Update the row in place. Simple, no history. Use when historical accuracy doesn't matter (fixing a typo in a customer name).
• Type 2 (Add Row): Insert a new row with a new surrogate key, mark the old row as expired (using effective_date / expiration_date / is_current flag). Preserves full history. Use for business-critical attributes (customer address, product pricing).
• Type 3 (Add Column): Add a previous_value column alongside the current value. Tracks one level of change. Rarely used — Type 2 is almost always preferred in practice.

Q6: What is the difference between DELETE, TRUNCATE, and DROP?

• DELETE removes rows matching a condition. Fully logged, can be rolled back, triggers fire. Slowest for large deletes.
• TRUNCATE removes ALL rows from a table. Minimally logged, cannot target specific rows, resets identity counters. Much faster than DELETE for full clears.
• DROP removes the entire table object (data + schema + indexes). The table no longer exists.

Q7: How would you deduplicate a table with millions of rows?

-- Keep the most recent record per entity
DELETE FROM staging.events
WHERE id NOT IN (
  SELECT id FROM (
    SELECT
      id,
      ROW_NUMBER() OVER (
        PARTITION BY entity_id
        ORDER BY updated_at DESC
      ) AS rn
    FROM staging.events
  ) deduped
  WHERE rn = 1
);

Q8: Explain star schema vs snowflake schema

• Star schema: Fact table at the center surrounded by denormalized dimension tables. One join to reach any dimension. Simpler queries, better query performance, more storage.
• Snowflake schema: Dimension tables are normalized into sub-dimension tables. Multiple joins required. Less storage redundancy, more complex queries.

Q9: Write a query to calculate a 7-day rolling average of daily revenue

SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_revenue_summary
ORDER BY order_date;

Q10: What is a fact table vs a dimension table?

• Fact tables store measurable business events (orders, clicks, transactions). Contain foreign keys to dimension tables and numeric measures (amount, quantity, duration). Typically very large (millions–billions of rows).
• Dimension tables store descriptive context about the entities involved in facts (customers, products, dates, locations). Contain attributes used for filtering, grouping, and labeling. Typically smaller (thousands–millions of rows).

Python questions for data engineers differ from general Python interviews. They focus on data manipulation, PySpark, and practical pipeline coding — not algorithms and data structures.

Q11: What's the difference between a list and a generator in Python?

Q12: How would you process a 50GB CSV file that doesn't fit in memory?

import pandas as pd

# Chunked reading — process in batches
for chunk in pd.read_csv('large_file.csv', chunksize=100_000):
    # Transform each chunk
    transformed = chunk[chunk['status'] == 'active']
    part_id = chunk.index[0]
    transformed.to_parquet(f'output/part_{part_id}.parquet')

Q13: Explain the difference between map(), filter(), and reduce()

• map() — applies a function to each element, returns transformed elements. map(lambda x: x * 2, [1, 2, 3]) → [2, 4, 6]
• filter() — applies a boolean function, returns only elements where True. filter(lambda x: x > 1, [1, 2, 3]) → [2, 3]
• reduce() — applies a function cumulatively to reduce a sequence to a single value. reduce(lambda a, b: a + b, [1, 2, 3]) → 6

Q14: What's the difference between PySpark DataFrames and pandas DataFrames?

• pandas: Single-machine, in-memory. Data must fit in RAM. Eager execution (operations run immediately). Great for exploration and datasets up to ~10GB.
• PySpark: Distributed across a cluster. Data is partitioned across nodes. Lazy execution (transformations are planned, only executed on action). Handles TB–PB scale.

Q15: How do you handle schema evolution in a pipeline?

1. Detection: Compare incoming schema against expected schema before processing. Use schema registries (Confluent Schema Registry for Kafka, Glue Schema Registry for AWS).
2. Strategy: Define what to do for each change type:
   • New column added → accept it, default to NULL for downstream
   • Column removed → alert, don't fail if column is optional
   • Type changed → fail fast, require manual review
3. Implementation: In Spark, use mergeSchema option for Parquet reads. In dbt, use on_schema_change: 'append_new_columns'. Always version your schemas.

Q16: Write a PySpark job to deduplicate a dataset by keeping the latest record per user

from pyspark.sql import Window
from pyspark.sql.functions import row_number, col

window_spec = Window.partitionBy("user_id").orderBy(col("updated_at").desc())

deduped = (
    df.withColumn("rn", row_number().over(window_spec))
    .filter(col("rn") == 1)
    .drop("rn")
)

Q17: What is the GIL and why does it matter for data engineering?

Q18: How would you write a custom PySpark UDF and when should you avoid them?

from pyspark.sql.functions import udf
from pyspark.sql.types import StringType

@udf(returnType=StringType())
def clean_email(email):
    return email.strip().lower() if email else None

df = df.withColumn("email_clean", clean_email(df.email))

Q19: Explain *args and **kwargs

Q20: How do you test a data pipeline?

1. Unit tests — test individual transformation functions with known input/output. Use pytest with small sample DataFrames.
2. Integration tests — test that pipeline stages connect correctly (source → transform → sink). Use test databases or mock external services.
3. Data quality tests — validate output data: row counts, null percentages, value distributions, schema conformance. In production, use Great Expectations or dbt tests.

System design rounds are the highest-signal interview stage. They test your ability to reason about trade-offs in distributed systems — concepts directly from Kleppmann's DDIA.

Q21: Design a data pipeline that processes 10 million events per day

1. Clarify requirements: What's the latency requirement? (Batch is fine for daily reporting; streaming needed for real-time dashboards.) What's the source? (API, Kafka, database CDC?) What are the downstream consumers?
2. Propose architecture: Source → Ingestion → Storage → Transformation → Serving
3. Make explicit trade-offs:

Q22: Design a real-time data processing system

• Ingestion: Kafka (partitioned by key for ordering guarantees) or Kinesis
• Processing: Flink or Spark Structured Streaming (Flink for true event-time processing; Spark SS for teams already on Spark)
• State management: How to handle late-arriving data? Use event-time watermarks (Kleppmann Ch. 11)
• Exactly-once semantics: Idempotent writes + consumer offset management. Explain that "exactly-once" is really "effectively-once" — achieved through idempotent consumers, not through preventing duplicate delivery
• Serving: Write processed results to a low-latency store (Redis, DynamoDB) for real-time queries, and to the warehouse for historical analysis

Q23: Design a data warehouse for an e-commerce company

1. Identify business processes: Orders, page views, inventory, customer interactions
2. Design fact tables: fact_orders (grain: one row per order line item), fact_page_views (grain: one row per page view)
3. Design dimension tables: dim_customer (SCD Type 2 for address changes), dim_product (Type 2 for price changes), dim_date, dim_geography
4. Choose the grain explicitly — this is the most important Kimball concept: "What does one row represent?"
5. Address performance: Partition fact tables by date, cluster by frequently filtered dimensions

Q24: How would you handle data quality in a production pipeline?

1. Define expectations: Row count ranges, null percentage thresholds, value distributions, referential integrity
2. Implement checks: Great Expectations framework, dbt tests (not_null, unique, accepted_values, custom SQL tests)
3. Decide on failure strategy: Hard fail (stop pipeline) vs soft fail (alert + continue). Critical metrics hard-fail; informational metrics soft-fail
4. Monitor trends: Track data quality metrics over time. A gradual increase in nulls may indicate an upstream problem before it becomes critical
5. Data contracts: Define expectations with upstream teams. Schema registries + SLAs for data freshness and quality

Q25: Design a change data capture (CDC) pipeline

• Source: Use Debezium to capture change events from the database WAL (Write-Ahead Log) — this is non-intrusive, doesn't add load to the source database
• Transport: Stream change events to Kafka (one topic per table, or per database)
• Processing: Apply changes to target tables maintaining order per key. Handle schema evolution (new columns, type changes)
• Sink: Write to data warehouse using merge/upsert operations

Q26: How do you choose between batch and streaming processing?

Q27: What happens when your pipeline fails at 3 AM?

1. Detection: Monitoring + alerting (pipeline SLAs, data freshness checks, failure notifications via PagerDuty/Opsgenie)
2. Triage: Is this a data issue (bad upstream data) or infrastructure issue (cluster OOM, network timeout)? Check logs, check upstream status
3. Recovery: Idempotent pipelines are critical — you should be able to rerun any failed stage without duplicating data. This means: (a) use INSERT OVERWRITE not INSERT, (b) write to partitions atomically, (c) maintain processing checkpoints
4. Prevention: After resolution, add the failure mode to monitoring and create a runbook. The best on-call engineers reduce future incidents, not just resolve current ones

Q28: Design a data mesh for a large organization

These questions test whether you've built real infrastructure or just studied for certifications.

Q29: Compare AWS Glue, EMR, and Athena — when would you use each?

• Glue: Managed ETL service. Use for scheduled transformations when you want serverless (no cluster management). Good for: small-to-medium jobs, catalog/schema management, crawlers.
• EMR: Managed Spark/Hadoop clusters. Use for heavy processing (TBs of data), complex Spark jobs, ML workloads. More control, more operational overhead.
• Athena: Serverless SQL on S3 data. Use for ad-hoc queries and exploratory analysis — pay per query. Not for repeated ETL transformations (cost adds up).

Q30: What is the difference between a data lake and a data warehouse?

• Data lake: Stores raw data in any format (Parquet, JSON, CSV, Avro) on cheap object storage (S3, GCS, ADLS). Schema-on-read. Flexible but requires discipline to avoid becoming a "data swamp."
• Data warehouse: Stores structured, modeled data optimized for queries (Snowflake, BigQuery, Redshift). Schema-on-write. Performant for BI and analytics.
• Data lakehouse: Combines both — open formats on object storage with warehouse-like query performance (Delta Lake, Iceberg, Hudi). This is where the industry is converging.

Q31: How do you manage costs in a cloud data platform?

Q32: Explain Parquet vs Avro vs ORC

• Parquet: Columnar format. Best for analytical queries that read specific columns. Default choice for data lakes and warehouses.
• Avro: Row-based format with rich schema evolution support. Best for streaming/Kafka messages where you read entire records and schemas change frequently.
• ORC: Columnar format optimized for Hive. Similar to Parquet but less widely supported outside the Hadoop ecosystem.

Behavioral questions test whether you can communicate about technical work. The STAR method (Situation, Task, Action, Result) provides a reliable structure.

**Situation:** "In [role] at [company], we had [specific problem]..."

**Task:** "I was responsible for [your specific responsibility]..."

**Action:** "I [specific technical actions you took]..."
- What tools/technologies did you use?
- What trade-offs did you consider?
- Who did you collaborate with?

**Result:** "As a result, [quantifiable outcome]..."
- Pipeline processing time reduced from X to Y
- Data freshness improved from hours to minutes
- Saved X dollars/month in cloud costs
- Reduced on-call incidents by X%

Q33: Tell me about a time a pipeline failed in production

Q34: How do you handle conflicting data requirements from different teams?

Q35: Describe a technical decision you made that you'd change in hindsight

Q36: How do you prioritize when you have multiple urgent requests?

Q37: Tell me about a time you improved a system's performance significantly

Prepared by Careery Team

Editorial Policy →

Reviewed byBogdan Serebryakov

Researching Job Market & Building AI Tools for careerists · since December 2020