11 Data Engineering ETL and Pipelines Interview Questions and Answers

Short answer: ETL transforms data before loading (on a separate engine), while ELT first loads raw data into the warehouse and runs transformations inside it in SQL. ELT became the default because cloud MPP warehouses (Snowflake, BigQuery, Redshift) scale compute cheaply and separate storage from compute.

In depth:

1. ETL — transform on an intermediate engine (historically pricey ETL servers). Only clean, ready data lands in the warehouse. Downside: logic is locked in the tool and the raw data is lost.
2. ELT — load the raw layer as-is, transform via dbt/SQL. Upside: raw is preserved, transformations are version-controlled in Git, and the warehouse handles scaling.
3. Why ELT won — separating storage/compute made in-warehouse compute cheap and elastic; analysts prefer keeping logic in SQL.

When ETL still fits: heavy preprocessing before the warehouse, masking PII/compliance (raw cannot be loaded), or a source easier to coerce to schema on the fly.

⚠️ Common mistake: thinking ELT means "no transformations." The transformations didn't disappear — they just moved into the warehouse and run after loading.

Short answer: Batch processes data in chunks on a schedule (latency of minutes to hours, simple and cheap), while streaming processes events as they arrive (seconds of latency, but more expensive and harder to operate). The freshness requirement drives the choice, not fashion.

In depth:

1. Batch — read the accumulated volume per interval, run it through Airflow/dbt/Spark. Easier to debug, easy to backfill, cheaper. Latency = window size.
2. Streaming — Kafka + Flink/Spark Structured Streaming process events continuously. Needed for fraud, alerts, real-time dashboards. They require managing state, watermarks for late events, exactly-once.
3. Micro-batch — a compromise: process in tiny windows (seconds), simpler than streaming with nearly the same freshness.

How to choose: start from "how fresh must the data be for the decision?" If "an hour is fine" — batch. If seconds matter and money is on the line — streaming.

⚠️ Common mistake: building streaming because "it's modern" when the business is fine with hourly freshness — you pay in complexity and cost with no added value.

Short answer: An idempotent load produces the same result when re-run on the same data — a second run creates no duplicates. This is critical because tasks fail and retry, and without idempotency a retry doubles the data.

In depth:

1. Overwrite the partition — instead of a blanket INSERT, overwrite a day's partition: INSERT OVERWRITE PARTITION. A rerun simply replaces the old data with the same chunk.
2. MERGE / upsert by key — match on the business key and update rather than inserting blindly. A rerun updates the same rows.
3. Deduplicate by key — if the source emits duplicates, keep the latest version via ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC).

-- Idempotent: overwrite the partition for a specific date
INSERT OVERWRITE TABLE sales PARTITION (dt = '2026-06-30')
SELECT * FROM staging_sales WHERE dt = '2026-06-30';

-- Idempotent: upsert by business key
MERGE INTO dim_customer t
USING staging_customer s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, name, updated_at)
  VALUES (s.customer_id, s.name, s.updated_at);

⚠️ Common mistake: a blind INSERT INTO ... SELECT with no key or partition overwrite. When a failed task retries, rows are inserted again — you get silent duplicates that surface in reports as doubled revenue.

Short answer: A full load re-reads the entire source each time — simple but doesn't scale. Incremental pulls only what's new/changed since the last run using a watermark (high-water mark), while CDC captures the changes themselves (insert/update/delete) from the database log.

In depth:

1. Full load — TRUNCATE + reload. Fine for small tables, honest about deletes, but expensive and slow on large ones.
2. Incremental by watermark — store the previous run's max updated_at/id and take rows strictly greater than it. Cheap, but blind to physical DELETEs.
3. CDC — read the redo/WAL log (Debezium, Fivetran) and apply the change stream, including deletes. Closer to real-time, but harder to operate.

Watermark (high-water mark):

  last run: max(updated_at) = 2026-06-29 23:00
         │
         ▼
  SELECT * FROM src
  WHERE updated_at > '2026-06-29 23:00'   ← delta only
         │
         ▼
  MERGE into target table  →  new watermark = 2026-06-30 23:00

⚠️ Common mistake: incrementing on updated_at when the source doesn't stamp it on every change (or using a non-strict >=, catching boundary duplicates). Physical deletes are also lost this way — they need CDC or a periodic full reconcile.

Short answer: A backfill reprocesses historical periods when logic changes or gaps need filling. Do it safely only through idempotent per-partition overwrites: each day is recomputed independently and overwrites its own partition instead of appending rows.

In depth:

1. Partition by date — the task should take the date as a parameter and touch only that day's partition (INSERT OVERWRITE PARTITION (dt=...)). Then a backfill = running the same task for past dates.
2. Idempotency is mandatory — overwrite, not INSERT. Otherwise re-running an already-computed day doubles the data.
3. Control the load — cap parallelism (Airflow max_active_runs) so a 2-year backfill doesn't crush the cluster and the source.

Partition-based backfill (reprocess a date range):

  for dt in 2026-01-01 .. 2026-06-30:
      OVERWRITE partition(dt)  ← each day is independent and idempotent

  ┌────────┬────────┬────────┬────────┐
  │ dt=1   │ dt=2   │ dt=3   │  ...   │   overwrite, not append
  └────────┴────────┴────────┴────────┘

⚠️ Common mistake: double-counting on backfill — running a recompute on top of already-loaded data with INSERT instead of OVERWRITE, or aggregates that sum old and new rows. Always overwrite the whole partition.

Short answer: Airflow describes a pipeline as a DAG — a graph of tasks with dependencies; each task is created by an operator and runs on a schedule. The key principle is that tasks must be idempotent and retryable, because Airflow re-runs them on failure.

In depth:

1. DAG — a directed acyclic graph: nodes are tasks, edges are dependencies (a >> b). The scheduler runs a task once its upstream dependencies are done.
2. Operators — task templates: PythonOperator, BashOperator, sensors, service-specific ones. Each run is tied to a logical_date (the interval), which is what makes backfill possible.
3. Idempotency and retries — you set retries; a task must re-run safely, so we write via partition overwrite.

from airflow import DAG
from airflow.operators.python import PythonOperator
import pendulum

with DAG(
    dag_id="daily_sales",
    schedule="@daily",
    start_date=pendulum.datetime(2026, 1, 1, tz="UTC"),
    catchup=True,          # enables backfill of past intervals
    default_args={"retries": 2},
) as dag:
    extract = PythonOperator(task_id="extract", python_callable=do_extract)
    load = PythonOperator(task_id="load", python_callable=do_load)
    extract >> load        # dependency: load after extract

Airflow vs Dagster/Prefect: Airflow is the mature standard, task- and schedule-oriented. Dagster thinks in data assets with typing/tests; Prefect emphasizes dynamic flows and an ergonomic Python API. New projects often pick Dagster for data assets, but Airflow remains the default by ecosystem.

⚠️ Common mistake: a task that can't be re-run (e.g., it appends to a file or sends an email on every run). On the first retry you get duplicates or spam.

Short answer: dbt is the T in ELT: you write transformations as SQL SELECT models, and dbt builds a dependency graph from them via ref(), materializes the result in the warehouse, and runs tests. People love it because analytics becomes version-controlled, testable, documented code.

In depth:

1. Models and ref — each model is a SELECT; references via {{ ref('stg_orders') }} give dbt the DAG and run order. No manual CREATE TABLE.
2. Materializations — view (light, always fresh), table (fast reads), incremental (computes only what's new by watermark), ephemeral (CTE). The choice balances recompute cost against read speed.
3. Tests and docs — not_null, unique, relationships right in YAML; plus auto-generated docs and lineage.

# models/marts/orders.yml
models:
  - name: orders
    config:
      materialized: incremental
      unique_key: order_id      # upsert by key on incremental runs
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: customer_id

⚠️ Common mistake: using table where a view suffices, and conversely running incremental without a correct unique_key — then the incremental run duplicates rows on reprocessing.

Short answer: A good DAG is split into small, atomic, idempotent tasks with explicit dependencies, each with retries and alerts. A monolithic task is an anti-pattern: when it fails at 90%, you re-run everything from scratch and can't see where it broke.

In depth:

1. Split into atomic tasks — extract, load, transform, quality-check separately. A transform failure doesn't force re-pulling extract; retries are targeted.
2. Explicit dependencies — the graph's edges reflect the real data order; no hidden coupling via "it'll run by the clock."
3. Atomic writes — a task either fully applies its result (partition overwrite) or nothing. No half-written states.
4. Retries + alerting — exponential backoff for transient failures; alert to Slack/PagerDuty on a final fail and on SLA miss.

Bad (monolith)           Good (atomic tasks)
┌──────────────┐         extract ─► load ─► transform ─► test ─► publish
│ do_everything│              │        │         │          │
│  (fails at   │           retry    retry     retry      alert on fail
│   90% → all  │
│   over again)│         a transform failure ⇒ only transform retries
└──────────────┘

⚠️ Common mistake: one 500-line "do-everything" task with no intermediate states. Any failure = a full restart, you can't tell where it broke, and you can't reuse the steps.

Short answer: A pipeline is triggered either on a schedule (cron/interval — simple and predictable) or by an event (a sensor waits for a file/message/upstream readiness — more precise but more complex). Late-arriving data is handled by re-running its period's partition or by windows with a watermark.

In depth:

1. Cron / interval — "every day at 03:00." Pro: predictable and simple. Con: it may start before the data is actually ready.
2. Event / sensor-driven — the task waits for a trigger: a file lands in S3, a message hits the queue, an upstream DAG finishes. More precise on readiness, but you must manage timeouts and "hangs forever."
3. Late-arriving data — yesterday's data shows up today. Options: an idempotent re-run of yesterday's partition, a rolling recompute window (last N days), or a watermark in streaming that tolerates lateness.

⚠️ Common mistake: a rigid cron with no upstream-readiness check — the task starts on time, reads half the data, and silently emits an incomplete result. Or a sensor with no timeout that hangs and holds a scheduler slot.

Short answer: At-least-once guarantees an event isn't lost but allows duplicates on retries; exactly-once means exactly one processing, but it's expensive and not always achievable in a distributed system. In practice you aim for effectively-once: at-least-once delivery plus an idempotent write that yields the effect of a single processing.

In depth:

1. At-most-once — at most one attempt; on failure the event is lost. Fine only if loss isn't critical.
2. At-least-once — retry until success, no loss, but duplicates are possible. The default of most queues (Kafka, SQS).
3. Exactly-once — exactly one, end-to-end. Expensive: needs transactions, idempotent producers, coordination. Rarely needed in pure form.
4. Effectively-once — accept duplicate delivery but kill it at the write: upsert/MERGE by key, dedup by event_id, partition overwrite. The result is indistinguishable from exactly-once.

⚠️ Common mistake: promising "exactly-once" while actually running at-least-once with a non-idempotent write — duplicates from the queue happily reach the mart and inflate your metrics.

Short answer: Data flows through layers: raw/bronze — untouched source data, staging/silver — cleaned and typed, curated/gold — business marts for consumers. You keep an immutable raw layer so you can replay any transformation from scratch without hitting the source again.

In depth:

1. Bronze (raw) — data as-is, append-only, unmodified. This is your source of truth and safety net.
2. Silver (staging) — cleaning, type coercion, deduplication, key standardization. Quality is fixed here.
3. Gold (curated) — aggregates and marts for specific metrics/dashboards. What the business sees.

  Source
     │
     ▼
  ┌─────────┐   cleaning,   ┌─────────┐  aggregation, ┌────────┐
  │ BRONZE  │─ typing ─────►│ SILVER  │─ business ───►│  GOLD  │──► BI / ML
  │  (raw,  │   dedup       │(staging,│   logic       │(metric │
  │ immutab)│               │ clean)  │               │ marts) │
  └─────────┘               └─────────┘               └────────┘
   you can replay everything from bronze without touching the source

Why immutable raw: the source may change or become unavailable; when a new logic bug appears, you recompute silver/gold from bronze. Plus audit and reproducibility.

⚠️ Common mistake: loading straight into curated with no raw layer. Find a transformation bug — there's nothing to replay, the historical source data is gone, and you're stuck living with corrupted marts.