How Would You Design an ETL Pipeline?
Learn how to design a production-grade ETL pipeline: extract, transform, load, idempotency, data quality, and ETL vs ELT trade-offs.
Expected Interview Answer
An ETL pipeline extracts data from source systems, transforms it into a clean, validated, and correctly shaped form, and loads it into a target store like a warehouse, and a good design makes each stage idempotent, monitored, and independently scalable so failures can be retried safely without corrupting downstream data.
The extract stage pulls data from sources — databases via change data capture, APIs, files, or event streams — ideally incrementally rather than re-reading everything each run. The transform stage cleans, deduplicates, validates against a schema, applies business rules, and reshapes data (joins, aggregations, type conversions), which can happen before loading (traditional ETL) or after loading raw data into the target (modern ELT, pushing transformation compute onto the warehouse). The load stage writes the transformed data into the target, typically using upserts or partition overwrites so reruns are idempotent rather than duplicating rows. A production-grade design adds orchestration (e.g., Airflow) to sequence and retry tasks, data quality checks that halt the pipeline or quarantine bad records rather than silently propagating errors, monitoring and alerting on freshness and volume anomalies, and schema evolution handling so upstream changes do not silently break downstream consumers.
- Idempotent stages let failed runs be safely retried without duplicating or corrupting data
- Incremental extraction keeps pipelines fast and cheap as source data grows
- Data quality checks catch bad records before they reach dashboards or ML models
- Orchestration and monitoring give visibility into freshness, failures, and volume anomalies
AI Mentor Explanation
An ETL pipeline is like a broadcaster’s production chain: raw camera and stat feeds are pulled from every ground (extract), edited into clean, labelled highlight clips with correct player names and timestamps (transform), and finally slotted into the evening broadcast schedule (load). If a producer needs to rerun the edit because a stat was wrong, the pipeline must be able to redo that step without duplicating the clip in the schedule — idempotent rerun, not double airtime. Quality checks before broadcast, like verifying player names against the official roster, catch errors before millions of viewers see them, exactly as data quality checks catch bad records in an ETL pipeline.
Step-by-Step Explanation
Step 1
Extract incrementally from sources
Pull only new or changed data (via CDC, watermarks, or event streams) from databases, APIs, or files rather than full re-reads.
Step 2
Validate and transform
Clean, deduplicate, type-check, and reshape the data, quarantining or halting on records that fail data quality rules.
Step 3
Load idempotently
Write to the target using upserts or partition overwrites so a rerun after failure never duplicates or corrupts data.
Step 4
Orchestrate, monitor, and alert
Sequence stages with a scheduler like Airflow, track freshness and volume metrics, and alert on anomalies or failures.
What Interviewer Expects
- Covers all three stages clearly and explains what happens at each
- Explicitly discusses idempotency and safe retries as a design requirement, not an afterthought
- Mentions incremental extraction (CDC or watermarks) instead of full reloads
- Discusses data quality validation, orchestration, and monitoring as part of a production design, and can contrast ETL with ELT
Common Mistakes
- Designing a pipeline that is not idempotent, so failed retries create duplicate data
- Doing full-table extraction on every run instead of incremental extraction
- Skipping data quality checks, letting bad records silently corrupt downstream dashboards
- Forgetting orchestration, retries, and monitoring, treating the pipeline as a single unmonitored script
Best Answer (HR Friendly)
“An ETL pipeline pulls data out of source systems, cleans and reshapes it into a consistent, trustworthy format, and then loads it into wherever it needs to live, like a data warehouse. Good design means each step can be safely rerun without creating duplicates or breaking things, and the pipeline has monitoring in place so the team knows quickly if something goes wrong.”
Code Example
def extract_incremental(source_db, last_watermark):
"""Pull only rows changed since the last successful run."""
return source_db.query(
"SELECT * FROM orders WHERE updated_at > %s",
[last_watermark],
)
def transform(rows):
"""Clean, validate, and reshape; quarantine bad records."""
clean, quarantined = [], []
for row in rows:
if row["amount"] is None or row["amount"] < 0:
quarantined.append(row)
continue
row["amount_usd"] = to_usd(row["amount"], row["currency"])
clean.append(row)
return clean, quarantined
def load_upsert(warehouse, clean_rows):
"""Idempotent load: upsert by primary key so reruns never duplicate."""
warehouse.upsert(
table="fact_orders",
rows=clean_rows,
conflict_key="order_id",
)Follow-up Questions
- How would you design extraction to be incremental instead of pulling the full source table every run?
- What makes a pipeline stage idempotent, and why does that matter for retries?
- How does ELT differ from ETL, and when would you push transformation into the warehouse instead?
- How would you detect and handle a schema change in an upstream source without breaking the pipeline?
MCQ Practice
1. Why is idempotency important in an ETL pipeline load stage?
An idempotent load (e.g., upsert by key) means rerunning after a failure produces the same correct result, not duplicate rows.
2. What is the main advantage of incremental extraction over full-table extraction?
Incremental extraction (via CDC or watermarks) avoids re-reading the entire source dataset on every run, saving time and load.
3. How does ELT differ from traditional ETL?
ELT reorders the pipeline to load first and transform afterward inside the target system, leveraging the warehouse’s compute power.
Flash Cards
What are the three ETL stages? — Extract (pull from sources), Transform (clean/validate/reshape), Load (write to target).
Why must ETL stages be idempotent? — So a retried or rerun stage after failure does not duplicate or corrupt downstream data.
What is incremental extraction? — Pulling only new or changed data since the last run, via CDC or watermarks, instead of a full reload.
ETL vs ELT? — ETL transforms before loading; ELT loads raw data first and transforms it inside the target system.