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.
See also
Frequently asked questions
What does ETL stand for?
ETL stands for Extract, Transform, Load. It is a data integration pattern that reads data from source systems such as databases, APIs or flat files, cleans and reshapes it to match the target schema and business rules, then writes it into a destination like a data warehouse or analytics database.
What is the difference between ETL and ELT?
The order of the last two steps changes. In ETL, data is transformed before being written to the destination; in ELT, 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.
Why not just query the source systems directly?
Because raw data is rarely analysis ready: formats, units and quality levels differ from one system to the next. ETL produces a single trusted version, standardizes currencies, dates and customer IDs, and moves heavy processing upstream so it happens once instead of in every query. It also makes lineage and auditing possible.
What happens during the Transform step exactly?
The data is cleaned, validated, deduplicated, joined, aggregated and reformatted so it conforms to the target schema and business rules. In practice this means things like converting all prices to a single currency, standardizing timestamps to UTC, removing duplicate returns and mapping several systems onto one product catalog.
How often does an ETL pipeline run, and who watches it?
ETL pipelines are usually scheduled, nightly being a common choice, or triggered by events. Orchestration tools coordinate the steps, handle retries and send alerts on failures. Loading itself can be a full refresh or a series of incremental updates, depending on data volume and how fresh the target needs to be.