Data engineer interview questions

What is the difference between ETL and ELT, and why did ELT win?

ETL transforms data before loading it into the warehouse, which made sense when storage and warehouse compute were expensive and rigid. ELT loads raw data first and transforms inside the warehouse using its own engine. ELT won for analytics because cloud warehouses made compute elastic and separated it from storage, so keeping raw data lets you re-derive models when requirements change instead of re-ingesting from source systems you may no longer control.

What is a star schema and when would you not use one?

A star schema has a central fact table of events with numeric measures, surrounded by denormalised dimension tables describing the context. It is optimised for analytical queries: few joins, predictable aggregation, easy for BI tools. You would skip it for a pure event-streaming use case, for genuinely exploratory data science on raw events, or when the warehouse is columnar enough that a wide denormalised table performs better and costs less to maintain.

Explain slowly changing dimensions, particularly Type 2.

An SCD handles attributes that change over time, like a customer moving city. Type 1 overwrites the old value, losing history. Type 2 inserts a new row with validity dates and a current flag, so a fact joined on the surrogate key reflects the attribute as it was at event time. That is what lets you answer "what were sales by region last year" correctly rather than reattributing old sales to the customer's new region.

What are window functions and where do they beat a GROUP BY?

A window function computes over a set of rows related to the current row without collapsing them, so you keep row-level detail alongside the aggregate. That makes them the right tool for running totals, ranking within a partition, comparing a row to the group average, and lag/lead comparisons between consecutive events. A GROUP BY would force you to aggregate and then join back to the detail — more code, more shuffling, and easier to get wrong.

What does it mean for a pipeline to be idempotent, and why does it matter?

Idempotent means running the same task for the same period twice produces the same result rather than duplicating data. It matters because retries are inevitable — a task fails halfway, someone reruns yesterday, a backfill overlaps a scheduled run. The usual implementations are deleting and rewriting the target partition, or a merge keyed on a business key rather than a blind insert. Pipelines that only work when run exactly once break the first time they are retried.

How do you design an Airflow DAG that is safe to backfill?

Each task should be parameterised by the logical execution date rather than "now", so a run for an old date reads and writes that date's data. Tasks must be idempotent, so a rerun overwrites its own partition. Set sensible retries with backoff, and use max_active_runs and pools so a backfill of two years does not saturate the warehouse and starve production runs. Dependencies between DAGs are safer expressed as data availability checks than as guessed schedules.

What is a shuffle in Spark and why is it expensive?

A narrow transformation like map or filter works within a partition, so it needs no data movement. A wide transformation like groupBy, join or repartition requires rows with the same key to end up on the same executor, which means writing intermediate data to disk and moving it across the network — that is a shuffle. It dominates job runtime, so tuning means reducing shuffles: broadcasting a small side of a join, filtering before joining, and choosing partitioning that matches how you query.

What is data skew and how do you deal with it?

Skew is when one key holds a disproportionate share of rows, so a single task processes most of the data while the rest of the cluster idles — the job looks 99% done for an hour. You spot it in the Spark UI as one task with far larger input than its peers. Remedies include salting the hot key to split it across partitions, broadcasting the smaller table to avoid the shuffle join entirely, and handling known hot keys separately from the long tail.

Why Parquet rather than CSV or JSON, and what does partitioning add?

Parquet is columnar and compressed, so a query reading three of forty columns reads only those columns, and it carries a schema and column statistics that let engines skip row groups entirely. CSV and JSON force a full scan and re-parse every time. Partitioning by a column you filter on, usually date, lets the engine skip whole directories, but over-partitioning creates millions of tiny files and the metadata overhead then costs more than it saves.

What does exactly-once processing really mean in streaming?

True exactly-once delivery is not achievable end to end; what systems provide is effectively-once processing, meaning duplicates may be delivered but the observable result is as if each message counted once. That is achieved with idempotent writes, deduplication on a message key, or transactional sinks that commit offsets and output atomically. Claiming exactly-once without saying which mechanism makes it true is a common interview red flag.

How do you handle late-arriving data?

First decide the semantics: is the record assigned to the time the event happened or the time you received it? Event time is usually correct for analytics but requires the pipeline to reopen and rewrite an already-published partition. Watermarks define how long you wait before treating a window as closed, and anything later goes to a correction path or a dedicated late-arrivals table. The design mistake is silently dropping late data, because the numbers then differ from source and nobody knows why.

How do you test data quality, and what do you check?

Tests run as pipeline steps that fail loudly rather than as dashboards nobody reads. The standard checks are freshness, row-count volume against expectations, uniqueness of keys, not-null on required columns, referential integrity between fact and dimension, and accepted ranges or enumerations. Tools like dbt tests or Great Expectations formalise this. The important design decision is which failures block downstream tasks and which only alert, because blocking on every anomaly trains people to ignore the alerts.