For decades, the standard way to build a pipeline was ETL (Extract, Transform, Load).
- Extract: Pull data from sources.
- Transform: A dedicated, separate server (like an Informatica or Talend server) processes the data in-memory or on disk. It cleans, joins, and aggregates it.
- Load: The final, clean data is loaded into the Data Warehouse.
The problem with ETL: The transformation step was often a major bottleneck. Traditional data warehouses like Teradata or Oracle were expensive and couldn't handle heavy processing, so you had to do it before loading. This meant maintaining a separate fleet of transformation servers, writing complex procedural code, and discarding the raw data once transformation was complete — meaning if your transformation logic had a bug, the original data was gone.
With the rise of powerful, scalable cloud data warehouses like Snowflake, BigQuery, and Redshift, a new paradigm emerged: ELT (Extract, Load, Transform).
- Extract: Pull the raw data.
- Load: Dump the raw data directly into the Data Warehouse as fast as possible.
- Transform: Use the massive, scalable compute power of the Data Warehouse itself to run SQL queries that transform the raw data into clean, analytics-ready tables.
ELT is now the dominant paradigm for modern data stacks because:
- It's simpler to manage — no separate transformation cluster.
- You always retain a copy of the raw data, so you can re-transform it if your logic changes.
- Cloud warehouses can scale compute independently of storage, making large SQL transformations cheap and fast.
- Tools like dbt (data build tool) have emerged specifically to manage the "T" in ELT — letting analysts write SQL transformations as version-controlled, tested, documented models.
When ETL still makes sense: If your destination system is a legacy on-premises warehouse with limited compute, or if data privacy regulations require that sensitive fields be masked before they ever land in the warehouse, ETL remains the right choice.