Data Engineer Interview Questions and Prep Plan for 2026

Data engineer interview questions in 2026 fall into five rounds: advanced SQL, Python data manipulation, data modeling, pipeline and streaming design, and behavioral. Most loops spend more time on SQL and design than on algorithm puzzles, so the fastest way to prepare is to practice each round on its own terms. This guide gives you the questions, worked answers, a full clickstream pipeline design, and a 4-week plan.

Key Takeaways

• SQL is the gate. Window functions, deduplication, gaps and islands, and sessionization show up in almost every data engineering screen.
• Python rounds test plain data wrangling. Expect to parse, group, and clean records with dictionaries and generators, often without pandas.
• Modeling is where seniors separate. Be ready to draw a star schema, pick the grain, and implement SCD Type 2 in SQL.
• Design answers need numbers. Estimate events per second and storage per day before you name Kafka or Spark.
• Data quality is a design topic, not an afterthought. Idempotency, late data, backfills, and tests belong in your main answer.
• Four focused weeks is enough for most working engineers who already write SQL and Python daily.

What Does a Data Engineer Interview Loop Look Like in 2026?

A data engineer interview loop is usually a recruiter call, one or two technical screens, and a virtual onsite of four to five rounds. The exact mix varies, but the table below reflects the structure most large tech companies and data-heavy startups use.

Some companies merge modeling and design into one "data architecture" round. Platform-focused roles at companies like those covered in our Databricks interview guide and Snowflake interview guide lean harder on distributed systems internals. Analytics engineering roles lean harder on SQL and modeling, and may skip the streaming round.

The technical screen is the first filter. If you have not done one recently, our technical phone screen guide covers pacing and how to talk through your approach.

How to Pass the Advanced SQL Round

The SQL round tests whether you can write correct, readable queries against messy data under time pressure. Interviewers care about correctness on edge cases (nulls, ties, duplicates) more than clever syntax.

These are the question types that repeat across companies:

1. Top-N per group. "Find the top 3 products by revenue in each category." Use ROW_NUMBER, RANK, or DENSE_RANK, and say out loud how you handle ties.
2. Deduplication. "Keep only the latest record per user." Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) and filter to 1.
3. Running totals and moving averages. Window frames like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
4. Gaps and islands. "Find each user's longest streak of consecutive login days."
5. Sessionization. "Group events into sessions with a 30-minute inactivity timeout."
6. Retention and funnels. Cohort tables with self-joins or conditional aggregation.

Sessionization is the one that trips people up most, and it is also directly relevant to the clickstream design later in this guide:

WITH ordered AS (
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM events
),
flagged AS (
  SELECT
    user_id,
    event_time,
    CASE
      WHEN prev_time IS NULL
        OR event_time - prev_time > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS new_session
  FROM ordered
)
SELECT
  user_id,
  event_time,
  SUM(new_session) OVER (
    PARTITION BY user_id ORDER BY event_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_number
FROM flagged;

The pattern is "flag the boundary, then take a running sum of the flags." The same trick solves most gaps-and-islands questions. Interval syntax differs between Postgres, Snowflake, BigQuery, and Spark SQL, so ask which dialect the interviewer expects before you start.

Expect follow-ups on performance: what an index or clustering key would help, why COUNT(DISTINCT) is expensive at scale, and when to pre-aggregate. For a larger question bank, work through our SQL interview questions and the focused SQL window functions guide.

What Python Questions Do Data Engineers Get?

The Python round for data engineers is usually a data wrangling task, not a graph algorithm. You get raw input (log lines, JSON records, CSV rows) and need to produce a clean, aggregated output with correct handling of bad records.

Typical prompts:

• Parse a web server log and return the top 10 endpoints by p95 latency.
• Merge two lists of user records, resolving conflicts by the latest timestamp.
• Flatten nested JSON events into rows with a fixed schema.
• Process a file too large for memory and compute per-key counts.

Here is a compact answer to a common one, "deduplicate events by event_id and count events per user per day, skipping malformed rows":

import json
from collections import defaultdict
from datetime import datetime, timezone

def daily_counts(lines):
    seen = set()
    counts = defaultdict(int)
    bad = 0
    for line in lines:
        try:
            rec = json.loads(line)
            event_id = rec["event_id"]
            user_id = rec["user_id"]
            ts = datetime.fromtimestamp(rec["ts"] / 1000, tz=timezone.utc)
        except (json.JSONDecodeError, KeyError, TypeError, ValueError):
            bad += 1
            continue
        if event_id in seen:
            continue
        seen.add(event_id)
        counts[(user_id, ts.date().isoformat())] += 1
    return dict(counts), bad

What earns points here is not the code length. It is that you process lines as a stream, count bad records instead of crashing, normalize to UTC, and then volunteer the scaling problem: the seen set grows without bound. At real volume you would bound it with a time window, push dedup into a keyed store, or let Spark do it with a watermark.

If interviewers allow pandas or PySpark, know groupby, merge, explode, and window functions in the DataFrame API. Our Python interview questions list covers generators, iterators, and the language details that come up as follow-ups.

Data Modeling: Star Schemas and Slowly Changing Dimensions

The data modeling round asks you to turn a business process into tables that analysts can query correctly and cheaply. The standard approach is Kimball-style dimensional modeling: fact tables for events or measurements, dimension tables for the context around them.

A star schema is a design where one central fact table holds measurable events at a declared grain, and it joins directly to several denormalized dimension tables such as date, customer, and product.

Walk through modeling questions in this order:

1. Name the business process. "Orders placed," not "the e-commerce data."
2. Declare the grain. "One row per order line item." Every other decision follows from this sentence.
3. Pick the dimensions. Date, customer, product, store, promotion.
4. Pick the facts. Quantity, unit price, discount, net amount. Note which are additive.
5. Handle change. Decide how each dimension attribute behaves when it changes.

That last step is where slowly changing dimensions come in.

Interviewers often ask you to implement Type 2. A two-step version that works in most warehouses:

UPDATE dim_customer d
SET valid_to = s.updated_at,
    is_current = FALSE
FROM staging_customer s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND (d.region <> s.region OR d.tier <> s.tier);

INSERT INTO dim_customer
  (customer_id, region, tier, valid_from, valid_to, is_current)
SELECT s.customer_id, s.region, s.tier, s.updated_at, NULL, TRUE
FROM staging_customer s
LEFT JOIN dim_customer d
  ON d.customer_id = s.customer_id AND d.is_current = TRUE
WHERE d.customer_id IS NULL;

The second statement inserts brand-new customers and the customers whose current row was just closed. In a real answer, mention a surrogate key (generated by the warehouse), null-safe comparisons for attributes that can be null, and that many teams express this as a single MERGE or as a dbt snapshot instead.

Pipeline and Streaming Design: A Sample Clickstream Answer

The pipeline design round is a system design interview with data-specific trade-offs. The general framework from our system design interview guide still applies, but the scoring centers on data correctness, latency, and cost. Here is a full sample answer for the most common prompt.

Prompt: "Design a system to ingest clickstream events from our web and mobile apps and make them available for real-time dashboards and daily analytics."

Step 1: Clarify requirements (3-5 minutes)

Ask, then state your assumptions:

• Volume: 50,000 events/second at peak, about 1 KB each.
• Freshness: dashboards within 1-2 minutes; analytics tables by 6 a.m. daily.
• Correctness: no double counting; late events (offline mobile) can arrive hours late.
• Retention: raw data for 1 year, aggregates indefinitely.
• Privacy: user IDs and IPs are personal data; deletion requests must be honored.

Step 2: Estimate

50,000 events/s x 1 KB is 50 MB/s, or about 4.3 TB of raw JSON per day. Columnar formats like Parquet compress this kind of data heavily, so stored size is several times smaller. This number drives your Kafka partition count, your storage cost, and whether you can afford to reprocess a full day.

Step 3: Architecture

Data flows through three layers, often called bronze, silver, and gold. Bronze is raw events landed as-is. Silver is deduplicated, validated, and enriched. Gold holds sessionized and aggregated tables for analysts.

Step 4: Go deep on the hard parts

This is where strong candidates win the round:

• Duplicates. Mobile SDKs retry, so every event carries a client-generated event_id. The streaming job deduplicates on event_id within a watermark window, and the silver table write uses MERGE on event_id so replays are idempotent.
• Late data. A watermark of, say, 2 hours bounds streaming state. Events later than that land in bronze and get picked up by a nightly batch job that recomputes the affected partitions. State this trade-off explicitly: real-time numbers are provisional, daily numbers are final.
• Exactly-once. Most Kafka consumer setups deliver at-least-once, so duplicates are possible after a retry or restart. Effectively-once output comes from checkpointing offsets together with idempotent or transactional sink writes.
• Skew. A bot or a huge customer can overload one partition. Detect it, filter known bots at the collector, and salt hot keys in aggregations.
• Small files. Frequent micro-batches create many small files. Schedule compaction on the table format.
• Privacy deletes. Keep personal data in columns you can delete or tokenize, and use the table format's row-level deletes to process requests.

Step 5: Operate it

Close with monitoring: consumer lag, events per second by source, dedup rate, late-event rate, and freshness SLAs per gold table. Mention the backfill path: because bronze is immutable and partitioned by event date, you can rerun any day's silver and gold jobs from Airflow.

A few current facts help you sound up to date. Apache Kafka 4.0 (released in 2025) runs only in KRaft mode, with ZooKeeper removed. Apache Airflow 3.0 (released in 2025) renamed datasets to "assets" for data-aware scheduling. Spark 4.0 turned on ANSI SQL mode by default. If the company runs on AWS, map each layer to managed services (MSK, Kinesis, Glue, EMR, S3); our AWS interview questions cover those trade-offs.

Spark and ETL Interview Questions You Should Answer Cold

Spark questions show up in the design round as follow-ups and sometimes as a dedicated round for platform roles. These are the ones to answer in under a minute each:

• What is the difference between a narrow and a wide transformation? Narrow transformations (map, filter) work within a partition. Wide transformations (groupBy, join, distinct) need a shuffle across the cluster, which is where most cost goes.
• How do you fix data skew? Broadcast the small side of a join, salt the hot key, or rely on Adaptive Query Execution's skew join handling.
• repartition versus coalesce? repartition does a full shuffle and can increase partitions. coalesce merges partitions without a full shuffle and only reduces them.
• ETL versus ELT? ETL transforms before loading. ELT loads raw data into the warehouse or lakehouse and transforms there with SQL, usually with dbt. ELT is the default for most modern stacks.
• How do you make a job idempotent? Overwrite by partition or MERGE on a natural key, so rerunning the same input produces the same output.

How Do Interviewers Test Data Quality and Orchestration?

Data quality questions check whether you treat data like production software. The usual prompt is "a dashboard showed wrong numbers yesterday; how would you prevent that?"

A strong answer covers four layers:

1. Contracts at the source. Schema registry and agreed schemas with producing teams.
2. Tests in the pipeline. Not-null, unique, accepted values, and referential integrity checks, written as dbt tests or with a tool such as Great Expectations or Soda.
3. Monitoring on outputs. Freshness, row counts against a trailing average, and distribution checks on key metrics.
4. Safe failure. A failed check blocks downstream tasks instead of publishing bad data, and someone is paged.

For orchestration, expect questions on DAG design, retries with backoff, task idempotency, sensors versus event-driven triggers, and backfills. Know why you should never use datetime.now() inside a task (reruns produce different results) and should instead use the run's logical date. Be ready to explain how you would backfill 90 days without overwhelming the warehouse: limit concurrency and process oldest to newest.

The Behavioral Round for Data Engineers

The behavioral round for data engineers focuses on incidents, ownership, and working with people who consume your data. Prepare five or six stories that you can adapt:

• A pipeline failure you debugged and what you changed so it would not recur.
• A time you found a data quality problem nobody else had noticed.
• A disagreement with an analyst or product manager about a metric definition.
• A migration you led (on-prem to cloud, batch to streaming, warehouse to lakehouse).
• A cost or performance win with a real number attached.

Use the STAR format and end each story with a measurable result. Our top 50 behavioral interview questions list has prompts to practice against. For Amazon data engineering roles, map each story to a Leadership Principle using our Amazon Leadership Principles guide.

A 4-Week Data Engineering Interview Prep Plan

This plan assumes 10-15 hours per week and that you already write SQL and Python at work. Adjust the weighting toward your weakest round.

Two tips make this plan work. First, time yourself from week one, because the hardest part of the SQL round is speed with correctness. Second, say your design answers out loud. Design rounds reward clear structure, and you only find the gaps in your explanation when you hear it.