Autocoder

Data Engineer Interview Questions and Answers

Last updated 2026-11-19

Data engineer interviews test whether you can move data reliably at scale. Expect SQL that goes beyond joins (gaps and islands, sessionization, deduplication), questions on pipeline design and failure handling, and tool knowledge such as Spark, Kafka and Airflow. Coding is usually Python and SQL.

The answers below give precise reasoning and code you can adapt. If your SQL is the weak spot, work through how to crack the SQL interview and the SQL window functions questions first, then return here.

Typical roundsSQL and Python test, pipeline design, tools round, behavioural
Core skillsSQL, Python, Spark, Kafka, Airflow, warehousing
Questions here13 worked questions

What interviewers listen for

A pipeline design answer in five steps

State the source, volume and freshness need. Choose batch or streaming from the freshness need, not from habit. Describe storage layout (raw, cleaned, modelled layers) and partitioning. Describe orchestration, retries and monitoring. Finish with how you would backfill and how you would detect bad data.

Data Engineer Interview Questions and Answers: questions with worked answers

  1. 1

    What is the difference between OLTP and OLAP systems?

    easy

    OLTP systems run the live application: many small reads and writes on a few rows, normalised tables, strict transactions, for example an orders database. OLAP systems serve analysis: few large read-heavy queries scanning many rows and aggregating a few columns, usually denormalised or star-schema tables in columnar storage. Data engineers move data from OLTP sources into OLAP stores, and running heavy analytics directly on the OLTP database risks slowing the application.

  2. 2

    ETL versus ELT: when would you choose each?

    easy

    ETL transforms data before loading it, which suits small volumes, strict masking requirements before data lands, or a target with limited compute. ELT loads raw data first and transforms inside the warehouse or lakehouse, which suits cloud warehouses with elastic compute, keeps the raw data for reprocessing and lets analysts own SQL transformations. Most modern stacks use ELT with tools like dbt, but sensitive fields may still be masked on the way in.

  3. 3

    What does it mean for a pipeline to be idempotent, and how do you achieve it?

    medium

    Running the job twice for the same input gives the same final state as running it once. Achieve it by writing to a partition with overwrite instead of append, by upserting on a natural or surrogate key with MERGE, or by deleting the target date range inside the same transaction before inserting. Avoid using the current timestamp inside the transformation logic, pass the run date in as a parameter. This is what makes retries and backfills safe.

    sql
    MERGE INTO dim_customer t
    USING staging_customer s
      ON t.customer_id = s.customer_id
    WHEN MATCHED THEN UPDATE SET email = s.email, updated_at = s.updated_at
    WHEN NOT MATCHED THEN INSERT (customer_id, email, updated_at)
      VALUES (s.customer_id, s.email, s.updated_at);
  4. 4

    Remove duplicate rows from an events table, keeping the latest row per event_id.

    medium

    Number the rows within each event_id by ingestion time descending and keep row 1. This works on any warehouse that supports window functions. Mention the tie-break: if two rows share the same timestamp, add another column to the ORDER BY so the result is deterministic.

    sql
    SELECT *
    FROM (
      SELECT e.*,
             ROW_NUMBER() OVER (PARTITION BY event_id
                                ORDER BY ingested_at DESC, batch_id DESC) AS rn
      FROM events e
    ) t
    WHERE rn = 1;
  5. 5

    Assign session ids to click events, where a gap of more than 30 minutes starts a new session.

    hard

    Compare each event with the previous event of the same user using LAG, flag a new session when the gap exceeds 30 minutes or there is no previous event, then take a running sum of the flags per user. The running sum is the session number. The syntax below is PostgreSQL; other databases use DATEDIFF or TIMESTAMPDIFF for the gap.

    sql
    WITH flagged AS (
      SELECT user_id, event_time,
             CASE WHEN LAG(event_time) OVER w IS NULL
                    OR event_time - LAG(event_time) OVER w > INTERVAL '30 minutes'
                  THEN 1 ELSE 0 END AS new_session
      FROM clicks
      WINDOW w AS (PARTITION BY user_id ORDER BY event_time)
    )
    SELECT user_id, event_time,
           SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_no
    FROM flagged;
  6. 6

    Find each user's longest streak of consecutive login days (gaps and islands).

    hard

    Take distinct login dates, number them per user with ROW_NUMBER and subtract that number of days from the date. Consecutive dates give the same result, so the result works as a group key for each streak. Group by it and count. For dates 1, 2, 3, 5, 6 the row numbers are 1 to 5 and the date minus row number is 0, 0, 0, 1, 1 days (day 1 minus 1, and so on), giving two groups of size 3 and 2. The SQL is PostgreSQL style.

    sql
    SELECT user_id, MIN(login_date) AS start_day, MAX(login_date) AS end_day, COUNT(*) AS days
    FROM (
      SELECT user_id, login_date,
             login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
      FROM (SELECT DISTINCT user_id, login_date FROM logins) d
    ) g
    GROUP BY user_id, grp
    ORDER BY days DESC;
  7. 7

    Why is Parquet preferred to CSV for analytics?

    easy

    Parquet stores data by column, with compression and per-column statistics. A query that reads three columns out of fifty scans only those columns, and row groups can be skipped using min and max statistics (predicate pushdown). It also stores a schema and types, so there is no ambiguity about dates or numbers. CSV is row based, has no types, no built-in compression and must be fully parsed. Parquet is slower to write and not human readable, which is acceptable for analytical storage.

  8. 8

    How should you choose partition columns, and what is the small-files problem?

    medium

    Partition by a column that queries filter on and that has moderate cardinality, usually a date. Too many partitions, for example one per user id, create millions of tiny files. Each file adds metadata and task overhead, and reads become slow. As a guide, aim for files of roughly 128 MB to 1 GB. A 500 GB dataset at 128 MB per file is about 500 x 1024 / 128 = 4,000 files. Fix small files by compaction jobs or by repartitioning before the write.

  9. 9

    How does Kafka preserve ordering and scale consumers?

    medium

    A topic is split into partitions, and ordering is guaranteed only within a partition. Messages with the same key go to the same partition, so all events of one user stay in order. Within a consumer group, each partition is read by exactly one consumer, so parallelism is capped by the partition count: with 6 partitions and 8 consumers, 2 consumers sit idle. Each consumer tracks an offset per partition; committing it after processing gives at-least-once delivery, so consumers must be idempotent.

  10. 10

    Explain at-least-once, at-most-once and exactly-once delivery.

    medium

    At-most-once never retries, so messages can be lost. At-least-once retries until acknowledged, so messages are never lost but may be duplicated. Exactly-once means each message affects the result once. In practice you get it by at-least-once delivery plus idempotent writes or transactional writes, such as Kafka transactions with an idempotent producer, or a sink that upserts by key. Claiming exactly-once without naming the mechanism is a red flag in an interview.

  11. 11

    Write a minimal Airflow DAG that runs a daily extract, transform and load.

    medium

    Define the DAG with a daily schedule, a start date and catchup behaviour, create one task per step and chain them. Use the logical date passed by the scheduler so a rerun for a past day processes that day, not today. Set retries in default arguments. Keep the heavy work in the external system and keep tasks small and idempotent.

    python
    from datetime import datetime, timedelta
    from airflow import DAG
    from airflow.operators.python import PythonOperator
    
    def extract(ds, **_): print('extract', ds)
    def transform(ds, **_): print('transform', ds)
    def load(ds, **_): print('load', ds)
    
    with DAG('daily_sales', start_date=datetime(2026, 1, 1), schedule='@daily',
             catchup=False, default_args={'retries': 2, 'retry_delay': timedelta(minutes=5)}) as dag:
        t1 = PythonOperator(task_id='extract', python_callable=extract)
        t2 = PythonOperator(task_id='transform', python_callable=transform)
        t3 = PythonOperator(task_id='load', python_callable=load)
        t1 >> t2 >> t3
  12. 12

    How do you handle late-arriving data in a streaming job?

    hard

    Use event time, not processing time, and a watermark that says how long to wait for stragglers, for example 10 minutes. Events later than the watermark are either dropped, routed to a side output, or corrected by a periodic batch reprocess of the affected partitions. The choice depends on the accuracy requirement. State a trade-off: a larger watermark gives more accurate windows but higher latency and more state to hold.

  13. 13

    What data quality checks would you add to a pipeline?

    medium

    Check at each layer boundary: row counts against the source within a tolerance, not-null and uniqueness on keys, referential integrity between facts and dimensions, accepted value ranges, freshness (latest timestamp not older than the SLA) and schema checks for added or dropped columns. Fail loudly on critical checks and only warn on soft ones. Store check results so trends are visible, because a slow decline in volume is often the first sign of a broken source.

Frequently asked questions

Which language should I prepare for data engineering interviews?

Python and SQL are near universal. Scala or Java appear for Spark-heavy roles. Be able to write clean SQL by hand.

Do data engineers face DSA rounds?

Often a light round with arrays, hash maps, strings and sorting, plus SQL. Heavy graph or dynamic programming problems are uncommon but possible at product companies.

How do I prepare for system design for pipelines?

Practise designing a clickstream pipeline and a daily warehouse load end to end, naming storage, partitioning, orchestration, failure handling and monitoring.

Is Spark knowledge compulsory?

For most data engineering roles in India, yes at a basic to intermediate level. See the PySpark questions page for joins, shuffles and skew.

Related guides

More in Role-wise Interview Questions by Job and Tech Stack

See all in this section