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 suggest: ETL transforms data in a separate processing layer before loading it into the target system, while ELT loads raw data into the target first and runs transformations there. That one reordering has significant consequences for architecture, performance, cost, compliance, and the day-to-day workflow of data teams.
For most of the history of data warehousing, ETL was the only game in town. Data warehouses were expensive, storage was limited, and compute was scarce -- you cleaned and shaped data before it touched the warehouse because you couldn't afford to waste warehouse resources on raw, unstructured dumps. The rise of cloud data warehouses like Snowflake, BigQuery, and Redshift changed the economics. When compute scales elastically and storage is cheap, it makes sense to load everything first and transform on demand. That shift is why ELT has become the default for most modern data stacks, though ETL is far from dead.
How ETL Works
In a traditional ETL pipeline, data moves through three distinct 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, change data capture (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, and enforcing schema. This happens outside the warehouse, typically on dedicated ETL infrastructure. Transformations are defined in the ETL tool's own language or visual interface.
Load. Cleaned, structured data is written into the target warehouse or database. Because transformations have already happened, the loaded data is ready for querying immediately.
The critical characteristic of ETL is that transformations are coupled to the ingestion pipeline. If a business requirement changes -- say, a new calculated metric or a different join strategy -- the ETL pipeline itself needs to be modified and redeployed.
How ELT Works
ELT flips the order of the middle two steps and changes where computation happens.
Extract. Same as ETL -- data is pulled from sources.
Load. Raw data is loaded directly into the target system, usually a cloud data warehouse or data lake. No transformations, no filtering, no schema enforcement. The data lands as-is, often into a raw or staging schema.
Transform. Once the data is in the warehouse, transformations run inside the warehouse's own compute engine, typically as SQL queries or orchestrated by tools like dbt. This is where cleaning, modeling, joining, and aggregation happen -- using the warehouse's distributed processing power rather than a separate ETL server.
The key advantage is decoupling ingestion from transformation. Data engineers can ingest a new source without knowing exactly how it will be used, and analysts or analytics engineers can iterate on transformation logic without touching the pipeline. If a transformation needs to change, you update a SQL model and re-run it against the already-loaded raw data.
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. If you operate under HIPAA, PCI DSS, GDPR, or similar regulations, ETL lets you anonymize, mask, or encrypt sensitive data before it ever enters the warehouse. In healthcare, for example, patient records from EHR systems are typically transformed and de-identified during the ETL process to ensure compliance before the data reaches any analytical system.
On-premise or legacy warehouses. Traditional data warehouses like Teradata, Oracle, or older SQL Server installations don't have the elastic compute to handle transformation workloads efficiently. Offloading transformations to a dedicated ETL engine makes better use of limited warehouse resources.
High data quality requirements upfront. When downstream consumers need data to be clean and validated on arrival -- no nulls, no duplicates, no schema drift -- ETL enforces that at ingestion time rather than trusting every analyst to handle it at query time.
Well-defined, stable data models. If your schema and business logic change infrequently, the overhead of 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 data architectures:
Cloud data warehouses. Snowflake, BigQuery, Redshift, and Databricks are built to handle large-scale transformations efficiently. Loading raw data and transforming in-place leverages their distributed compute, auto-scaling, and columnar storage optimizations.
Iterative analytics and data science. When analysts and data scientists need to experiment with different transformations, aggregations, or feature engineering approaches, having the full raw dataset available in the warehouse eliminates pipeline bottlenecks. No need to re-extract from source systems.
Multiple use cases from the same source. Raw data can serve different transformation pipelines for different teams -- finance, marketing, product, ML -- without duplicating extraction work.
The medallion architecture. A widely adopted ELT pattern organizes data into three layers: Bronze (raw ingested data), Silver (cleaned and validated), and Gold (aggregated, business-ready). Tools like dbt manage 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 following Qlik's 2023 acquisition), IBM DataStage, and Microsoft SSIS. These are mature, enterprise-grade platforms with visual pipeline designers, extensive connector libraries, and strong governance features. They tend to be expensive and operationally heavy.
ELT ingestion tools handle the Extract and Load steps. Fivetran is the most established SaaS option, offering fully managed connectors with minimal configuration. Airbyte provides an open-source alternative with 600+ connectors and the option to self-host. Stitch (part of Talend) offers a simpler, lightweight SaaS ingestion layer aimed at smaller teams.
In-warehouse transformation tools handle the T in ELT. dbt (data build tool) is the clear market leader -- it lets analytics engineers write transformations as SQL SELECT statements, organized into models with dependency management, testing, and documentation built in. dbt runs against the warehouse directly, compiling models into SQL and executing them using the warehouse's compute. Matillion and Coalesce offer similar warehouse-native transformation capabilities with visual interfaces.
Hybrid and full-stack platforms bridge both worlds. Apache Airflow and Dagster orchestrate complex pipelines that can include both ETL and ELT steps. Meltano combines Singer-based extraction with dbt-based transformation in an open-source framework.
Performance Considerations
ETL performance is bounded by the ETL server's resources. Scaling means provisioning larger or additional servers, which adds cost and operational complexity. The upside is predictable resource consumption -- 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 can be scaled up or out independently, BigQuery's serverless model scales automatically, and Redshift's RA3 nodes separate compute from storage. The tradeoff is that heavy transformation jobs can consume significant warehouse compute credits, and poorly written transformations can get expensive fast. Separating transformation workloads into dedicated warehouse clusters or scheduling them during off-peak hours is standard practice.
For latency-sensitive pipelines, both approaches can support near-real-time processing. ETL tools like Informatica and Talend offer streaming modes, while ELT pipelines can use CDC-based ingestion tools (Fivetran, Airbyte, Debezium) combined with micro-batch transformations in the warehouse.
The Modern Reality: Hybrid Approaches
In practice, most organizations don't choose purely ETL or purely ELT. They use both, matched to the characteristics of each data source and use case.
A common pattern: ETL for sensitive customer data that requires 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 lower. The ingestion layer (Fivetran, Airbyte) handles extraction and loading, dbt handles in-warehouse transformations, and a targeted ETL process handles the subset of data that needs 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 is not going away. It remains essential for data governance, compliance-sensitive workflows, and any environment where raw data should not land in the analytical layer unprocessed. The right answer depends on your warehouse platform, your compliance requirements, your team's skills, and how much flexibility you need in your transformation logic.