Data Engineering interview question · Most asked
What is the difference between ETL and ELT, and when would you choose each?
Summary
ETL transforms data in a separate process before loading it; ELT loads raw data into the warehouse first and transforms it there with SQL. ELT is the usual choice with modern cloud warehouses because it keeps raw data and uses scalable warehouse compute; ETL still fits when data must be cleaned, masked or reduced before it lands.
Longer Answer
Keeping raw data means new questions and bug fixes can be answered by recomputing from tables already in the warehouse, without re-extracting history from production. Transforming before loading remains right for removing personal data that must never reach the warehouse, decoding binary formats, or targets that can't run transformations (operational databases).
Key Points
- ETL: transform, then load.
- ELT: load raw, transform in the warehouse.
- ELT keeps history recomputable.
- Pre-load transforms for privacy and format decoding.
Deep dive: ETL and Data Pipelines: ETL vs ELT
Explanation
What interviewers look for
Understanding why ELT became standard (cheap storage, scalable warehouse compute, raw data retention) and when ETL still applies.
Follow-up questions
- How would you handle personal data in an ELT pipeline?
More most-asked Data Engineering questions
- Batch or streaming: how do you decide?
- What is change data capture (CDC), and why is log-based CDC preferred over polling?
- What does it mean for a data pipeline to be idempotent, and how do you achieve it?
- How do you implement incremental extraction, and what can go wrong?
- How do you deduplicate events, and what does 'exactly-once' really mean?
- What is a star schema, and why is it used in data warehouses?
- What is the grain of a table, and what is join fan-out?
- Explain slowly changing dimensions, especially Type 1 and Type 2.