ETL: Extract, Transform, Load
In ETL, data is extracted from source systems, transformed on a separate compute engine (often a custom script or ETL tool), and then loaded into the destination. This made sense when storage and compute were expensive and databases were slow.
ELT: Extract, Load, Transform
In ELT, raw data is loaded directly into the cloud warehouse first, then transformed using the warehouse’s own compute engine (SQL). This pattern leverages the massive scalability of modern cloud warehouses like BigQuery and Snowflake.
When to Use Each
- ELT: Cloud warehouse destination, large data volumes, want SQL-based transformations (dbt).
- ETL: Loading into operational databases, complex transformations requiring non-SQL logic, data masking before storage.