ETL
Also: Extract Transform Load, Extract, Transform, Load, data pipeline
ETL (Extract, Transform, Load) is a data integration process that pulls data from sources, reshapes it into a consistent format, and writes it into a target system.
What ETL is
ETL stands for Extract, Transform, Load. It is a data integration pattern used to move data from one or more source systems into a target system, typically a data warehouse, data mart, or analytics database. The three stages run in sequence:
- Extract: Read raw data from sources such as databases, APIs, flat files (CSV, JSON), SaaS applications, or event streams.
- Transform: Clean, validate, deduplicate, join, aggregate, and reformat the data so it conforms to the target schema and business rules.
- Load: Write the transformed data into the destination, either as a full refresh or as incremental updates.
Why it matters
Raw data is rarely analysis ready. It arrives in different formats, units, and quality levels, often spread across many systems. ETL creates a single, trusted, consistent version of the data so teams can report and analyze with confidence.
- Data quality: Bad records are caught and fixed before they reach dashboards.
- Consistency: Currencies, dates, and customer IDs are standardized.
- Performance: Heavy processing happens once, upstream, instead of in every query.
- Governance: A controlled pipeline makes lineage and auditing possible.
How it is used in practice
ETL pipelines are usually scheduled (for example, nightly) or triggered by events. Orchestration tools coordinate the steps, handle retries, and alert on failures. A common modern variant is ELT, where raw data is loaded first and transformed inside a powerful cloud warehouse. ETL is often preferred when transformations are complex or when sensitive data must be cleaned before it lands.
Concrete Example
A retailer wants a daily sales report combining online and in store data.
1. Extract: Pull orders from a PostgreSQL store database and a Shopify API.
2. Transform: Convert all prices to euros, standardize timestamps to UTC, deduplicate returns, and map both systems to one product catalog.
3. Load: Insert the clean records into a `fact_sales` table in the warehouse.
Analysts then query one clean table instead of reconciling two messy sources by hand, saving hours and reducing errors.