ETL vs ELT

Two acronyms appear constantly in data engineering: ETL and ELT. Both describe a pattern for moving data from source systems into a storage layer. The letters stand for the same three steps — Extract, Transform, Load — but the order of the last two steps creates entirely different architectures with different trade-offs.

What the Letters Mean

Extract

Pull raw data from source systems. This step connects to APIs, databases, files, or streaming platforms and retrieves data in whatever format the source provides.

Transform

Clean, reshape, filter, join, and enrich the raw data. This step converts data into the format that downstream consumers need.

Load

Write the processed data into a destination — a data warehouse, database, or data lake.

The ETL Pattern

In ETL, transformation happens before the data reaches the destination. Raw data extracts from the source, passes through a dedicated transformation engine, and only the cleaned result loads into storage.

ETL Flow:
[Source DB]  --> Extract --> [Staging Area]
                          --> Transform  --> [Data Warehouse]
                              (Python, Spark, dedicated ETL tool)

Example:
1. Extract: Pull 500,000 raw order records from MySQL
2. Transform: Filter cancelled orders, convert currencies,
              join with customer table, calculate order totals
3. Load: Write 480,000 valid, enriched records to Redshift

When ETL Works Best

ETL suits situations where the destination has limited compute power, where data is highly sensitive and should not land in storage in raw form, or where transformation logic existed before modern cloud warehouses made ELT practical. Legacy systems built before 2015 often use ETL because the warehouses of that era charged for storage and compute together.

The ELT Pattern

In ELT, raw data loads into the destination first. Transformation happens inside the destination using its own compute power. Modern cloud data warehouses — Snowflake, BigQuery, Redshift — can process enormous amounts of data with their own engines, making it practical to let them do the transformation work.

ELT Flow:
[Source DB]  --> Extract --> [Data Warehouse Raw Layer]
                          --> Load raw data immediately
                          --> Transform inside the warehouse (dbt, SQL)
                              --> [Data Warehouse Processed Layer]

Example:
1. Extract: Pull 500,000 raw order records from MySQL
2. Load: Write all 500,000 raw records to BigQuery raw schema
3. Transform: Run dbt SQL models inside BigQuery to filter,
              join, and aggregate into clean summary tables

When ELT Works Best

ELT suits modern cloud warehouses that offer cheap storage and powerful built-in compute. It simplifies pipeline architecture because raw data always exists in the warehouse for debugging and reprocessing. ELT also supports more agile development — when analysts want to explore raw data or change transformation logic, they query or modify SQL directly without rebuilding an external transformation engine.

ETL vs ELT Comparison

Feature               | ETL                            | ELT
----------------------|--------------------------------|--------------------------------
Transform location    | Outside destination            | Inside destination
Raw data preserved?   | No (only cleaned data loads)   | Yes (raw data always available)
Tooling               | Informatica, Talend, Spark     | dbt, warehouse-native SQL
Reprocessing ease     | Harder (need to re-extract)    | Easy (re-run SQL on raw data)
Compute used          | External servers               | Warehouse compute engine
Privacy               | Raw data never enters storage  | Raw data sits in warehouse
Cost model            | Pay for external compute       | Pay for warehouse compute
Best for              | Legacy systems, strict privacy | Modern cloud warehouses

A Side-by-Side Scenario

A healthcare company collects patient appointment records from 50 clinics. They want to build a report showing average wait times by clinic and specialty.

ETL Approach

A Spark job runs every night. It extracts appointment records from each clinic's database, removes records missing the wait time field, calculates average wait time per clinic and specialty, and loads only the aggregated summary table into the reporting database. Raw records never enter the reporting system.

ELT Approach

A pipeline extracts all raw appointment records from every clinic and loads them into a BigQuery raw table immediately. A dbt SQL model runs inside BigQuery, filters invalid records, joins with the clinic reference table, and aggregates wait times. Analysts can query both the raw table and the aggregated table directly in BigQuery.

The Rise of dbt in ELT

dbt (data build tool) became the dominant ELT transformation tool because it turns SQL SELECT statements into full transformation pipelines with dependency management, testing, and documentation. Data engineers write SQL models in dbt; dbt compiles them into CREATE TABLE AS SELECT statements that run inside the warehouse. This approach makes transformation logic transparent, version-controllable, and testable.

Hybrid Approaches

Many real-world pipelines mix both patterns. Sensitive personally identifiable information (PII) might get masked or removed in an ETL step before landing in the warehouse. Everything else loads raw (ELT style) and gets transformed inside the warehouse afterward. The right combination depends on privacy requirements, team skills, and the capabilities of the chosen destination system.

Summary

ETL transforms data before it reaches the destination; ELT loads raw data first and transforms it inside the destination. Modern cloud data warehouses with powerful compute engines have made ELT the dominant pattern. ETL remains relevant for legacy systems and use cases with strict raw data privacy requirements. Choosing between them depends on the destination platform, team expertise, and data sensitivity requirements.