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

Data engineer interviews test a specific combination of skills that no other engineering role requires: SQL fluency, distributed systems thinking, data modeling theory, cloud infrastructure knowledge, and the ability to debug production pipelines under pressure. This guide gives you the actual questions — and more importantly, what the interviewer is evaluating with each one.

Every answer is grounded in concepts from two foundational texts: Martin Kleppmann's Designing Data-Intensive Applications (DDIA) for distributed systems and pipeline design, and Ralph Kimball's The Data Warehouse Toolkit for dimensional modeling. If an interviewer asks you "how would you design a data warehouse," they're testing whether you understand Kimball's principles — even if they never name the book.

What to Expect in a Data Engineer Interview

Key insight: The SQL/coding round eliminates the most candidates. If you pass that, the system design round determines your level (mid vs senior vs staff). The behavioral round confirms cultural fit and communication skills.

SQL Interview Questions

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()

What the interviewer is evaluating: Whether you understand window functions beyond just using them — specifically how each handles ties, and when to use which.

Answer: All three are ranking window functions, but they handle duplicates differently:

• 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.

When to use each: ROW_NUMBER() for deduplication and pagination. RANK() when you need to show gaps (leaderboards where ties matter). DENSE_RANK() for "top N per group" queries where you want exactly N distinct values.

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

What the interviewer is evaluating: Window function application + partition logic.

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;

Why DENSE_RANK over ROW_NUMBER: If two employees share the highest salary, ROW_NUMBER would arbitrarily assign one as rank 2 — giving a wrong answer. DENSE_RANK correctly identifies the actual second-highest salary value.

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

What the interviewer is evaluating: Code readability practices and understanding of query execution.

Answer: A Common Table Expression (CTE) is a named temporary result set defined with WITH. CTEs are preferred when: (1) the same result set is referenced multiple times, (2) the query has multiple transformation stages (readability), or (3) you need recursive queries. Subqueries are fine for simple, one-off inline filters. Most query engines compile CTEs and subqueries to the same execution plan — the difference is readability and maintainability, not performance.

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

What the interviewer is evaluating: Systematic debugging approach, not just listing techniques.

Answer (structured approach):

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)

What the interviewer is evaluating: Kimball dimensional modeling knowledge — this is directly from The Data Warehouse Toolkit.

Answer:

• 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.

Production nuance: Type 2 is the most common in real data warehouses. The implementation challenge is the merge logic — you need to detect changes, expire old rows, and insert new ones atomically. In dbt, this is handled by snapshot strategies.

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

What the interviewer is evaluating: Understanding of DDL vs DML and transaction logging.

Answer:

• 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?

What the interviewer is evaluating: Practical large-scale data processing + window function application.

-- 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
);

Production approach: For very large tables, a DELETE with subquery may be too slow. Instead: CREATE TABLE ... AS SELECT the deduplicated result, then swap the tables. This is a common pattern in Redshift and BigQuery where DDL operations are cheap.

Q8: Explain star schema vs snowflake schema

What the interviewer is evaluating: Kimball modeling fundamentals.

Answer: Both are dimensional modeling approaches from Kimball:

• 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.

In practice: Star schema dominates modern cloud data warehouses (Snowflake, BigQuery, Redshift) because storage is cheap and query performance matters more than disk savings. Snowflake schema is used when dimension tables are very large and frequently updated.

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

What the interviewer is evaluating: Window functions with frame specifications.

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;

Key detail: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives exactly 7 rows (6 prior + current). Using RANGE instead of ROWS would handle gaps in dates differently — mention this distinction to show depth.

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

What the interviewer is evaluating: Core Kimball concepts.

Answer: From Kimball's dimensional modeling:

• 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).

Design principle: Facts answer "how much" or "how many." Dimensions answer "who," "what," "where," and "when."

Python Interview Questions

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?

What the interviewer is evaluating: Memory efficiency awareness — critical for data engineering.

Answer: A list stores all elements in memory. A generator produces elements one at a time, on demand. For data engineering, this matters when processing large files: reading a 10GB CSV into a list would crash the process, while a generator processes one line at a time with constant memory usage. Use generators for ETL pipelines where data flows through; use lists when you need random access or the dataset fits in memory.

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

What the interviewer is evaluating: Real-world data engineering problem-solving.

Answer:

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')

Alternatives to mention: (1) Use dask for parallel chunk processing, (2) use PySpark for distributed processing across a cluster, (3) convert to Parquet first (columnar format, much smaller), (4) use DuckDB for SQL on files without loading into memory.

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

What the interviewer is evaluating: Functional programming concepts used in PySpark.

Answer:

• 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

DE context: These concepts map directly to PySpark RDD operations: rdd.map(), rdd.filter(), and rdd.reduce(). Understanding them is fundamental to Spark programming.

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

What the interviewer is evaluating: Understanding of distributed vs single-machine processing.

Answer:

• 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.

Key difference for interviews: PySpark uses lazy evaluation — df.filter() and df.select() build an execution plan but don't process data until an action (collect(), count(), write()) triggers execution. This allows Spark to optimize the entire pipeline before running it. Kleppmann calls this "dataflow optimization" — the system can reorder and combine operations for efficiency.

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

What the interviewer is evaluating: Production pipeline resilience.

Answer:

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.

DDIA connection: Kleppmann's Chapter 4 on encoding and evolution covers forward and backward compatibility — the same principles apply to pipeline schema evolution.

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

What the interviewer is evaluating: PySpark window function + practical pipeline logic.

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?

What the interviewer is evaluating: Understanding of Python's concurrency model.

Answer: The Global Interpreter Lock (GIL) prevents multiple threads from executing Python bytecode simultaneously. For CPU-bound data processing, this means threading doesn't provide true parallelism in Python. Solutions: (1) use multiprocessing for CPU-bound work, (2) use PySpark/Dask which distribute work across separate processes/nodes, (3) use libraries like NumPy/Pandas that release the GIL during C-level operations. For I/O-bound work (API calls, file reads), threading still works fine because the GIL is released during I/O waits.

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

What the interviewer is evaluating: PySpark performance awareness.

Answer: UDFs are Python functions registered for use in Spark SQL:

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))

When to avoid: UDFs serialize data between JVM and Python for every row — this is slow. Always prefer built-in Spark functions (lower(), trim(), regexp_replace()) which execute natively in the JVM. Use UDFs only when no built-in function exists. For complex logic, consider Pandas UDFs (vectorized) which process batches of rows via Arrow — much faster than row-at-a-time UDFs.

Q19: Explain *args and **kwargs

What the interviewer is evaluating: Python fundamentals (quick screening question).

Answer: *args collects positional arguments into a tuple. **kwargs collects keyword arguments into a dictionary. Used to create flexible functions that accept variable numbers of arguments. In data engineering, you'll see these in pipeline factories and configuration-driven frameworks where the number of parameters varies by data source.

Q20: How do you test a data pipeline?

What the interviewer is evaluating: Production maturity.

Answer: Three layers of testing:

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.

Key principle: Test the business logic, not the framework. Don't test that Spark can read Parquet — test that your transformation produces the expected business result.

System Design Questions

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

What the interviewer is evaluating: End-to-end pipeline thinking + trade-off reasoning.

Framework for answering:

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:

Sample answer (batch): "For 10M events/day with daily reporting requirements, I'd use: Kafka for ingestion (decouples producers from consumers), write raw events to S3 in Parquet format (columnar, compressed), use Spark/dbt for daily batch transformations, and serve from a warehouse like Snowflake or BigQuery. I'd organize the storage as a medallion architecture: bronze (raw), silver (cleaned), gold (business-ready)."

DDIA connection: Kleppmann's Chapter 10 on batch processing and Chapter 11 on stream processing provide the theoretical framework for this answer. Reference reliability (what happens when the pipeline fails?), scalability (what if volume grows 10x?), and maintainability (can another engineer operate this?).

Q22: Design a real-time data processing system

What the interviewer is evaluating: Streaming architecture + consistency trade-offs.

Key points to cover:

• 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

Trade-off to mention: Latency vs completeness. Streaming gives low latency but you must handle late data (watermarks, reprocessing). Batch gives completeness but higher latency. Many production systems use Lambda or Kappa architecture to get both.

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

What the interviewer is evaluating: Kimball dimensional modeling applied to a real domain.

Answer structure:

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

Kimball's four-step process: (1) Select the business process, (2) declare the grain, (3) identify the dimensions, (4) identify the facts. Mention this explicitly — interviewers recognize the framework.

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

What the interviewer is evaluating: Production maturity + data governance thinking.

Answer:

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

What the interviewer is evaluating: Real-time database replication knowledge.

Answer:

• 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

DDIA connection: Kleppmann's Chapter 11 discusses CDC as a form of derived data — the change log is the source of truth, and downstream systems are derived views. This framing demonstrates theoretical depth.

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

What the interviewer is evaluating: Trade-off reasoning — the core of system design.

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

What the interviewer is evaluating: Operational maturity and incident response thinking.

Answer:

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

DDIA connection: Kleppmann's reliability chapter (Ch. 1) directly applies — faults are inevitable, the system must be fault-tolerant.

Q28: Design a data mesh for a large organization

What the interviewer is evaluating: Staff-level thinking — organizational architecture, not just technical.

Key concepts: Domain-owned data products, federated governance, self-serve data platform, data as a product. Explain that data mesh is an organizational paradigm, not a technology choice. Each domain (marketing, sales, engineering) owns its own data pipelines and serves curated data products to consumers. The platform team provides infrastructure (compute, storage, catalog) but doesn't own the data.

When to recommend it: Large organizations with multiple independent data teams. NOT recommended for small companies or teams that share a single data platform.

Cloud Platform Questions

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?

Answer:

• 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).

Decision framework: Start with Glue for scheduled ETL. Move to EMR when jobs are too complex or large for Glue. Use Athena for analysts who need direct access to the data lake.

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

Answer:

• 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?

Answer: Five strategies: (1) partition and cluster tables to reduce scan volume, (2) use auto-scaling — don't over-provision compute, (3) set lifecycle policies on storage (move old data to cheaper tiers), (4) monitor query costs (BigQuery slots, Snowflake credits, Redshift spectrum) and set alerts, (5) use reserved capacity for predictable workloads, on-demand for spiky ones.

Q32: Explain Parquet vs Avro vs ORC

Answer:

• 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.

Rule of thumb: Parquet for analytics, Avro for streaming, ORC only if you're in a Hive-heavy environment.

Behavioral Interview Questions

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

What they're evaluating: Incident response maturity, accountability, and learning from failure.

How to answer: Pick a real incident. Describe the detection (how did you find out?), triage (what was the root cause?), resolution (what did you fix?), and prevention (what did you change to prevent recurrence?). Don't blame others — own your part. The best answers include a specific metric: "We detected the failure within 15 minutes through our freshness SLA alert, identified the root cause (upstream schema change) in 30 minutes, and implemented a schema validation check that has caught 12 similar issues since."

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

What they're evaluating: Stakeholder management and data modeling maturity.

How to answer: Show that you facilitate resolution rather than pick a side. "When marketing and finance disagreed on the definition of 'active customer,' I scheduled a meeting with both teams, documented both definitions, and proposed implementing both as separate columns with clear naming (is_active_marketing, is_active_finance). We added a data dictionary entry explaining the distinction. This resolved the immediate conflict and prevented future confusion."

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

What they're evaluating: Self-awareness and growth mindset.

How to answer: Choose a real decision with real trade-offs. "I chose batch processing for a pipeline that eventually needed streaming. At the time, the latency requirement was 'daily,' so batch was the right call. Six months later, the business needed real-time updates. Migrating from batch to streaming was painful. I learned to ask about future latency requirements during design, not just current ones."

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

What they're evaluating: Organizational skills and communication.

How to answer: Show a framework, not just "I work hard." "I categorize by business impact: production pipeline failures first (revenue/compliance at risk), then SLA-bound deliverables, then stakeholder requests. I communicate timelines to everyone affected and escalate if capacity doesn't match demand. I learned to say 'I can do A by Tuesday or B by Thursday — which is more urgent?' rather than promising both by Monday."

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

How to answer: Quantify everything. "Our daily warehouse load was taking 6 hours, blocking the BI team until noon. I profiled the pipeline, found that 70% of the time was spent on a full table scan during the merge step. I implemented incremental loading using a CDC approach — only processing changed records. Load time dropped to 45 minutes. I also added partitioning by date, which reduced query costs by 40%."

Common Interview Mistakes

2-Week Preparation Plan

Prepared by

Careery Team

Editorial Policy→

Reviewed by

Bogdan Serebryakov

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