Differences between ETL and ELT, and when to choose each
Pipeline architecture tradeoffs.
ETL transforms before loading for structured data; ELT loads raw first and transforms in the warehouse for scale.
Calling one better without citing volume, structure, or compute.
What's really being asked
This question evaluates whether you understand modern data integration architecture beyond buzzwords. Interviewers want to see if you can distinguish between processing paradigms based on where transformation occurs, what hardware is used, and how schema flexibility impacts downstream analytics. The core theme is tradeoff analysis: cost, latency, compliance, and scalability.
The full answer
A strong response hits four things in order. First, location of transformation: ETL uses a secondary processing server to clean and structure data before it reaches the target database, while ELT lands raw data first and transforms it inside the warehouse or lake. Second, data structure and compatibility: ETL is traditionally suited for structured relational data with rigid schemas, whereas ELT handles structured, semi-structured, and unstructured data because the schema is applied after loading. Third, flexibility and speed: ELT enables faster ingestion since data is loaded immediately, and analysts can run ad-hoc transformations repeatedly without re-extracting from source systems. Fourth, historical context and cost: ETL rose with 1970s data warehouses requiring custom pipelines per source, while cloud elasticity made ELT the modern default by separating storage from compute and allowing unlimited raw retention.
The mistakes people make
Red flags include stating that ETL is always cheaper or faster, which ignores the overhead of maintaining separate transformation servers. Another mistake is recommending ELT for every use case without acknowledging that sensitive or regulated data often needs cleansing and masking before it enters the target system. Candidates also err by conflating the two acronyms or describing the steps without explaining why the order matters architecturally.
What usually comes next
Expect the interviewer to ask how you would handle schema evolution in an ELT pipeline, or when you would still choose ETL in a cloud-native environment. They may probe cost models, asking how compute charges differ between running transformations on dedicated ETL instances versus warehouse compute. Another common pivot is data quality: who owns validation when raw data lands first, and how do you prevent downstream contamination.
A concrete example
Suppose you are ingesting clickstream logs from a mobile application into Snowflake. The data is semi-structured JSON with evolving fields. Using ELT, you extract the JSON from Kafka, load it into a raw stage in Snowflake, then use dbt or SQL to parse nested fields, filter bots, and aggregate sessions. If you instead used ETL, you would need to define every possible JSON path upfront in a separate server like Apache Spark before loading, which slows onboarding of new event types and increases engineering toil every time the mobile team adds a telemetry field.
Interview question
When is ELT generally the preferred approach over ETL?
- a.When the pipeline goal is to reduce costs by avoiding all transformation workloads inside the data warehouse
- b.When semi-structured JSON clickstream data with evolving fields is loaded into a cloud warehouse for later transformationCorrect
- c.When sensitive data must be cleansed and masked before it enters the target system to meet compliance rules
- d.When structured relational data requires strict schema enforcement on a secondary processing server before loading
Why? this is the answer
ELT is ideal for semi-structured data like evolving JSON because the schema is applied after loading in the warehouse, whereas the compliance-focused option describes ETL, since sensitive data often requires cleansing and masking before it reaches the target system.
Just read this? Test yourself on what you have been reading.
Read the original → aws.amazon.com
- #data engineering
- #etl
- #elt
- #analytics
- #data pipelines
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data engineering — each one lists the topics its interview covers.
See open roles