ETL
ETL (Extract, Transform, Load) is a data integration process that pulls data from source systems, transforms it into a clean, structured format outside the target system, and then loads it into a destination such as a data warehouse.
Definition
ETL (Extract, Transform, Load) is a data integration process that pulls data from source systems, transforms it into a clean, structured format outside the target system, and then loads it into a destination such as a data warehouse.
Overview
ETL breaks data movement into three distinct stages. Extract pulls raw data from one or more source systems — an operational OLTP database, an API, a CSV export, a third-party SaaS tool. Transform reshapes that raw data into the format the destination expects: cleaning invalid values, joining data from multiple sources, aggregating, deduplicating, and enforcing the target schema — this transformation step happens on a separate processing engine before the data ever reaches the destination. Load writes the finished, transformed data into its destination, most commonly a data warehouse. Traditional ETL tools — Informatica, Talend, Microsoft SSIS, and workflow orchestrators like Apache Airflow coordinating custom Spark or Python jobs — perform the transform step on dedicated compute separate from both the source and destination systems, which historically made sense when target databases had limited processing power and transformation logic needed to run somewhere else. This approach guarantees that only clean, validated data ever lands in the warehouse, at the cost of requiring separate transformation infrastructure and making it harder to reprocess historical raw data if transformation logic needs to change later. ETL is directly contrasted with ELT (Extract, Load, Transform), a newer pattern that loads raw data into the destination first and performs transformation afterward using the destination's own compute — a shift enabled by the elastic, decoupled storage-and-compute architecture of modern cloud warehouses like Snowflake and BigQuery. Tools like dbt are specifically built around this ELT pattern, running SQL transformations directly inside the warehouse after loading. Despite the industry's broader shift toward ELT, ETL remains the right choice in scenarios requiring data to be cleaned, masked, or validated before it ever touches the destination system — for example, when regulatory or privacy requirements mean sensitive data must be filtered out before loading — and is a foundational concept covered in Apache Airflow & Orchestration.
Key Concepts
- Three distinct stages: Extract, Transform, Load, in that order
- Transformation happens on separate compute before data reaches the destination
- Ensures only clean, validated data lands in the target warehouse
- Traditionally implemented with tools like Informatica, Talend, or SSIS
- Often orchestrated with workflow tools like Apache Airflow
- Well suited to scenarios requiring data masking or validation before loading
- Contrasted with the newer ELT pattern favored by modern cloud warehouses