ETL vs ELT: Choosing Where to Transform Data
Compare ETL and ELT by transformation requirements, data-handling constraints, warehouse capabilities, and team skills.
ETL transforms data before loading it into a destination. ELT loads it first and transforms it there. The choice depends on where sensitive data may be stored, which transformations you need, and the compute available in the destination.
Where Transformation Happens
Both patterns move data from sources into a destination. The difference is when and where transformation happens.
ETL — Extract, Transform, Load
- Data is pulled from source systems.
- Transformation logic runs in a separate processing layer (custom code, Spark, a dedicated ETL tool).
- Cleaned, shaped data lands in the destination.
ELT — Extract, Load, Transform
- Data is pulled from source systems.
- Raw data lands directly in the destination (typically a cloud warehouse).
- Transformation runs inside the warehouse using SQL — usually orchestrated by dbt.
Cloud warehouses such as BigQuery, Snowflake, and Redshift can run transformations alongside stored data. That lets teams use warehouse SQL and tools such as dbt without maintaining a separate transformation cluster.
When ETL Still Makes Sense
Choose ETL when:
- Compliance or privacy rules prevent raw data from landing in the warehouse. If you must redact PII or mask fields before storage, transformation must happen before load.
- Source data needs non-SQL logic. Check whether your warehouse supports the required model inference, external calls, or parsing. If it does not, use an external processing layer.
- You are working with on-premise or legacy destinations that lack compute for in-warehouse transformation.
- Volume is extreme and you need to filter aggressively before storage to control warehouse costs.
In these cases, tools like Apache Spark, AWS Glue, or custom Python processors serve as the transformation layer.
When ELT Fits
ELT can simplify an analytics pipeline when the warehouse supports the required transformations:
- You land raw data fast. Ingestion and load are decoupled from transformation, so raw data is queryable immediately.
- Transformations are version-controlled SQL. dbt models live in git, are testable, and have clear lineage. The transformation logic is available for review.
- Iteration is faster. When business logic changes, you update a dbt model and re-run. You do not need to re-ingest data.
- The warehouse handles compute scaling. No separate transformation cluster to size, provision, or maintain.
A common modern ELT stack: Fivetran or Airbyte for extract and load, dbt for transformation, Airflow or Dagster for orchestration.
Combining ETL and ELT
A pipeline can combine both patterns:
- Pre-load filtering in ELT: Even in an ELT setup, you often apply lightweight filtering during extraction — dropping obviously malformed rows, normalizing timestamps — before loading. This is not full ETL, but it is pre-load transformation.
- Spark + dbt: Heavy, non-SQL transformations (ML feature engineering, large joins across terabyte-scale datasets) run in Spark. The output lands in the warehouse, then dbt handles downstream modeling.
- Streaming pipelines: Real-time ingestion via Kafka or Kinesis typically requires an ETL-style transformation layer before the data is useful to downstream consumers.
Practical Decision Checklist
Before picking a pattern, answer these:
- Must data be filtered or redacted before it reaches the warehouse? If yes, include a pre-load transformation.
- Does the warehouse lack the required transformation capabilities? If yes, use ETL or a hybrid.
- Is your destination a cloud warehouse with significant compute? If yes, ELT is simpler.
- Do you need raw data available for ad hoc exploration before transformations are finalized? If yes, ELT.
- Is your team stronger in SQL than in distributed systems engineering? If yes, ELT reduces operational burden significantly.
The dbt Factor
dbt adds testing, documentation, lineage graphs, and modular SQL to warehouse transformations. Evaluate it if you need a shared way to develop and maintain SQL models.