ETL vs. ELT: Key differences and when to use each?
This tests your understanding of modern data architecture trade-offs. A good answer explains the T vs. L order, then links ETL to structured, legacy systems and ELT to flexible, cloud-native data lakes/warehouses.
What's really being asked
The interviewer is assessing your understanding of data pipeline architecture and its evolution. They want to see if you can articulate the trade-offs between processing data before or after loading it. This isn't just a definition question; it's about cost, scalability, flexibility, and compliance in a real-world context. They're looking for experience with modern cloud data stacks versus traditional on-premise data warehouses.
The full answer
A strong answer outlines four key points. First, define the core difference: ETL transforms data on a separate processing server before loading, while ELT loads raw data into the target system (like a data warehouse or lake) and transforms it in place. Second, explain the architectural drivers: ETL was dominant for on-premise, resource-constrained warehouses that required structured data. ELT is the modern standard for cloud data warehouses which have scalable, cheap storage and powerful compute. Third, discuss the use cases: Use ETL for systems with rigid, pre-defined schemas, sensitive data that must be cleaned/anonymized before loading, and smaller data volumes. Use ELT for large volumes of unstructured data, when you need to store raw data for future unknown analyses, and to leverage the power of the target cloud warehouse's compute engine. Fourth, mention the impact on teams: ETL often requires specialized data engineers, while ELT empowers analysts to perform their own transformations on raw data using SQL.
The mistakes people make
A major red flag is treating them as just a simple reordering of letters without discussing the "why". Candidates who can't explain the shift from on-premise to cloud as the driver for ELT's popularity are missing the key context. Another weak answer is saying "ELT is just better." It's not always better; ETL is still the right choice for specific compliance (e.g., GDPR, HIPAA) or legacy system integration scenarios where PII cannot be loaded into the raw data zone. Failing to mention the cost implications of running transformations inside an expensive data warehouse (ELT) versus on a separate ETL server is also a miss.
What usually comes next
Be prepared for "Describe a project where you chose one over the other and why." or "How does the choice of ETL vs. ELT affect data governance and security?" or "Let's say we have 100 TB of daily log data and need to provide dashboards for 5 different teams. How would you design the pipeline?"
A concrete example
Choose ETL when integrating data from a third-party CRM into a legacy on-premise financial reporting database. The data must be cleaned, specific fields selected, and PII masked before it lands in the highly structured, audited finance system. Choose ELT when ingesting clickstream event data from a mobile app into a cloud data warehouse like Snowflake. Load the raw JSON events directly into a staging area. One team can then transform it for a marketing dashboard, while a data science team can use the same raw data to build a new recommendation model, all using the warehouse's compute.
Interview question
For a new data ingestion pipeline into a cloud data warehouse, which scenario is the strongest argument for choosing an ELT approach?
- a.Raw, high-volume data needs to be retained for future, unpredictable analysis.Correct
- b.The target system has limited compute power for complex transformations.
- c.Data contains sensitive information that must be anonymized before storage.
- d.Data must conform to a rigid, pre-defined schema before loading.
Why? this is the answer
ELT is ideal for loading large volumes of raw data directly into a scalable cloud data warehouse, enabling flexible in-place transformations for diverse and evolving analytical needs. Masking sensitive data or conforming to rigid schemas before loading are typical use cases for ETL, which transforms data on a separate server.
Just read this? Test yourself on what you have been reading.
Read the original → aws.amazon.com
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