Differences between ETL and ELT, and when to choose each

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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.

ONE 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.

Source: aws.amazon.com