Design an ETL pipeline for a data warehouse
PremiumData pipelineEasy
- ETL
- Incremental extract
- Star schema
Quick answer
This data pipeline interview question asks you to design an ETL pipeline for a data warehouse. Aim for a design that moves data from sources to consumers reliably and on time, then explain ingestion, processing, storage and trade-offs. Interviewers want to see how you handle volume, late or bad data and failures along the way.
The problem
Design an ETL pipeline for a data warehouse: a nightly job that extracts orders, payments and product data from three sources, cleans them and loads a star schema that business dashboards read each morning. The hard parts are pulling only what changed, loads that are safe to rerun, and finishing before 6 AM.
The company is a mid-size online retailer. Sales, finance and marketing trust these dashboards for revenue, so a half-loaded or double-loaded night is worse than a late one.
What must an ETL pipeline for a data warehouse do?
- Daily load: Load yesterday's new and changed orders, payments and products into the warehouse every night.
- Star schema: Provide fact_orders and fact_payments with customer, product and date dimensions that analysts join on keys.
How well must it do it?
- Deadline: Dashboards show complete data for yesterday by 06:00 local time.
- Correctness: Daily order counts and revenue match the orders database to the cent; any gap must be explained by rows in quarantine.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- ETL
- Incremental extract
- Star schema
- Slowly changing dimensions
- Idempotent loads
- MERGE upserts
How you’ll practise
- Requirements
- Data volume
- Ingestion
- Storage and processing
- Serving and freshness
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any data pipeline design round.
- Clarifies sources, consumers and freshness needs
- Chooses batch, streaming or both with reasons
- Handles late, duplicate and malformed records
- Makes each stage safe to retry
- Explains storage formats and partitioning
- Plans monitoring, backfills and data quality checks
Questions people ask about this interview question
- Which design type is this question?
- It is a data pipeline question. You design an ETL pipeline for a data warehouse and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Easy, so it suits engineers who are new to data pipeline interviews. It is also a good warm-up before harder questions.
- What should I cover first?
- Start with the data sources, the consumers and how fresh the data must be. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team