ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) are two approaches to moving data from source systems into analytical destinations. The difference is exactly what the acronyms say: ETL transforms data in a separate processing layer before loading it into the target, while ELT loads raw data first and runs transformations there. That one reordering has real consequences for architecture, performance, cost, compliance, and how data teams work day to day.
For most of data warehousing's history, ETL was the only option. Warehouses were expensive, storage limited, compute scarce -- you cleaned and shaped data before it touched the warehouse because you couldn't afford to waste resources on raw dumps. Cloud warehouses like Snowflake, BigQuery, and Redshift changed the economics. When compute scales elastically and storage is cheap, loading everything first and transforming on demand makes sense. That shift is why ELT has become the default for most modern data stacks. But ETL is far from dead.
How ETL Works
In a traditional ETL pipeline, data moves through three stages on a dedicated processing server or cluster -- often called a staging area or ETL engine.
Extract. Connectors pull data from source systems: databases, APIs, flat files, SaaS applications, message queues. The extract phase handles connection management, incremental reads, CDC, and error handling.
Transform. Before data reaches the warehouse, a processing engine applies business logic: filtering, deduplication, type casting, joining reference tables, masking PII, computing derived fields, enforcing schema. This happens outside the warehouse on dedicated ETL infrastructure. Transformations are defined in the ETL tool's own language or visual interface.
Load. Cleaned, structured data goes into the target warehouse or database. Because transformations already happened, the loaded data is immediately queryable.
The defining characteristic: transformations are coupled to the ingestion pipeline. If a business requirement changes -- a new metric, a different join -- the ETL pipeline needs modification and redeployment.
How ELT Works
ELT flips the middle steps and changes where computation happens.
Extract. Same as ETL -- data is pulled from sources.
Load. Raw data lands directly in the target system, usually a cloud warehouse or data lake. No transformations, no filtering, no schema enforcement. Data arrives as-is, typically into a raw or staging schema.
Transform. Once in the warehouse, transformations run using the warehouse's own compute -- typically SQL queries orchestrated by tools like dbt. Cleaning, modeling, joining, aggregation all happen using the warehouse's distributed processing power rather than a separate ETL server.
The key advantage: ingestion and transformation are decoupled. Data engineers ingest a new source without knowing how it'll be used. Analysts iterate on transformation logic without touching the pipeline. When a transformation changes, you update a SQL model and re-run it against raw data that's already loaded.
Key Differences
| ETL | ELT | |
|---|---|---|
| Transformation location | Separate processing engine (outside warehouse) | Inside the target warehouse |
| Data loaded into warehouse | Cleaned, structured, transformed | Raw, as extracted from source |
| Raw data availability | Not stored in warehouse (only transformed output) | Full raw data preserved in warehouse |
| Compute requirements | Dedicated ETL server or cluster | Warehouse compute (scales elastically in cloud) |
| Iteration speed | Slow -- pipeline changes require redeploy | Fast -- update SQL models, re-run against raw data |
| Schema flexibility | Schema-on-write (defined before load) | Schema-on-read (defined at query/transform time) |
| Compliance and PII handling | Strong -- data masked/encrypted before entering warehouse | Requires additional governance (raw PII lands in warehouse) |
| Cost model | Fixed ETL infrastructure + warehouse | Warehouse compute (pay per query/transformation) |
| Best fit | Regulated industries, on-premise warehouses, legacy systems | Cloud-native stacks, analytics-heavy teams, iterative development |
When to Use ETL
ETL remains the right choice in specific scenarios:
Regulated industries with strict compliance. Under HIPAA, PCI DSS, GDPR, or similar regulations, ETL lets you anonymize, mask, or encrypt sensitive data before it enters the warehouse. In healthcare, patient records from EHR systems are typically transformed and de-identified during ETL to ensure compliance before data reaches any analytical system.
On-premise or legacy warehouses. Traditional warehouses like Teradata, Oracle, or older SQL Server installations lack elastic compute for handling transformation workloads efficiently. Offloading to a dedicated ETL engine makes better use of limited warehouse resources.
High data quality requirements upfront. When downstream consumers need clean, validated data on arrival -- no nulls, no duplicates, no schema drift -- ETL enforces that at ingestion time rather than relying on every analyst to handle it at query time.
Well-defined, stable data models. If schema and business logic rarely change, maintaining an ETL pipeline is manageable and the upfront transformation guarantees consistency.
When to Use ELT
ELT is the default for most modern cloud-native architectures:
Cloud data warehouses. Snowflake, BigQuery, Redshift, and Databricks handle large-scale transformations efficiently. Loading raw data and transforming in-place leverages their distributed compute, auto-scaling, and columnar storage.
Iterative analytics and data science. When analysts and data scientists need to experiment with different transformations, aggregations, or feature engineering, having the full raw dataset in the warehouse eliminates pipeline bottlenecks. No re-extraction from source systems.
Multiple use cases from the same source. Raw data serves different transformation pipelines for different teams -- finance, marketing, product, ML -- without duplicating extraction work.
The medallion architecture. A popular ELT pattern: Bronze (raw ingested data), Silver (cleaned and validated), Gold (aggregated, business-ready). dbt manages the transformations between layers with full lineage tracking and version control.
Tooling Landscape
The ETL and ELT ecosystems have distinct but overlapping tool categories.
Traditional ETL platforms include Informatica PowerCenter, Talend (now Qlik Talend Cloud), IBM DataStage, and Microsoft SSIS. Mature, enterprise-grade platforms with visual designers, extensive connectors, and strong governance. Tend to be expensive and operationally heavy.
ELT ingestion tools handle Extract and Load. Fivetran is the most established SaaS option with fully managed connectors. Airbyte provides an open-source alternative with 600+ connectors and self-hosting. Stitch (part of Talend) offers a lighter SaaS ingestion layer for smaller teams.
In-warehouse transformation tools handle the T in ELT. dbt is the clear market leader -- transformations as SQL SELECT statements, organized into models with dependency management, testing, and documentation. Matillion and Coalesce offer similar warehouse-native transformation with visual interfaces.
Hybrid and full-stack platforms bridge both worlds. Apache Airflow and Dagster orchestrate complex pipelines spanning ETL and ELT steps. Meltano combines Singer-based extraction with dbt transformation in an open-source framework.
Performance Considerations
ETL performance is bounded by the ETL server's resources. Scaling means provisioning bigger or more servers -- more cost, more operational complexity. The upside: predictable resource consumption, since transformations don't compete with analytical queries for warehouse compute.
ELT performance depends on the warehouse. Cloud warehouses handle this well: Snowflake's virtual warehouses scale independently, BigQuery's serverless model scales automatically, Redshift's RA3 nodes separate compute from storage. The tradeoff: heavy transformations consume warehouse credits, and poorly written transformations get expensive fast. Separating transformation workloads into dedicated warehouse clusters or scheduling off-peak is standard practice.
For latency-sensitive pipelines, both approaches support near-real-time. ETL tools like Informatica and Talend offer streaming modes; ELT pipelines can use CDC-based ingestion (Fivetran, Airbyte, Debezium) combined with micro-batch warehouse transformations.
The Modern Reality: Hybrid Approaches
Most organizations don't choose purely ETL or ELT. They use both, matched to each data source and use case.
A common pattern: ETL for sensitive customer data requiring PII masking before it touches any analytical system, combined with ELT for high-volume operational data like event logs, clickstreams, and application metrics where raw access is valuable and compliance constraints are lighter. The ingestion layer (Fivetran, Airbyte) handles extraction and loading, dbt handles in-warehouse transformations, and a targeted ETL process covers the subset of data needing pre-load treatment.
The shift toward ELT is real and accelerating -- driven by cloud warehouse economics and the productivity gains of SQL-based transformation tools like dbt. But ETL isn't going away. It remains essential for data governance, compliance-sensitive workflows, and environments where raw data shouldn't land in the analytical layer unprocessed. The right answer depends on your warehouse platform, compliance requirements, team skills, and how much flexibility you need in your transformation logic.