ETL

Extract, Transform, Load

Updated September 30, 20262 min read

In short

ETL is a data integration process that extracts data from source systems, transforms it into a clean, consistent shape, and loads it into a target store.

What is ETL?

ETL stands for extract, transform, load, the three steps of moving data from where it is created to where it is analyzed. Extract copies data out of sources such as application databases, third-party APIs, CSV exports, and logs. Transform cleans and reshapes it: fixing types and formats, removing duplicates, validating values, joining sources, and computing totals. Load writes the result into a target such as a data warehouse, a data lake, or another database.

ETL pipelines typically run as scheduled batch jobs, for example every night or every hour, and are coordinated by workflow orchestration tools that track dependencies between steps and retry failures. Instead of copying everything each time, incremental loads process only rows that changed since the last run, found with timestamps or change data capture (CDC), which reads a database's change log. A well-built pipeline is idempotent, so rerunning it after a failure does not create duplicate data.

Many teams now use ELT instead: load the raw data into the warehouse first, then transform it there with SQL, because modern warehouses have plenty of computing power and keeping the raw copy makes it easy to rebuild results later. A kitchen is a good analogy: ingredients arrive from different suppliers (extract), are washed, chopped, and measured (transform), and are put on the serving line (load); in ELT, everything goes into the pantry first and is prepared when needed.

ETL is often confused with the data warehouse itself. ETL is the process that moves and prepares the data, while the warehouse is the destination where it is stored and queried. ETL also differs from event streaming, which processes each event as it happens rather than in batches, although streaming ETL pipelines exist, and from a database migration, which changes a database's structure rather than moving its data somewhere else.

Key takeaways

Extract pulls data from sources, transform cleans and reshapes it, and load writes it to a target.
Pipelines usually run as scheduled batch jobs managed by an orchestrator.
Incremental loads and change data capture avoid copying everything every time.
ELT loads raw data first and transforms it inside the warehouse.
Idempotent steps make it safe to rerun a pipeline after a failure.

Example

A tiny ETL job in Pythonpython

import csv, sqlite3
# Extract: read raw rows from a CSV export
with open("orders.csv", newline="") as f:
    rows = list(csv.DictReader(f))

# Transform: skip incomplete rows and normalize formats
clean = [
    (r["id"], r["email"].strip().lower(), round(float(r["total"]), 2))
    for r in rows if r["total"]
]

# Load: upsert into the analytics database (safe to rerun)
db = sqlite3.connect("warehouse.db")
db.executemany("INSERT OR REPLACE INTO orders VALUES (?, ?, ?)", clean)
db.commit()

Readers ask

What is the difference between ETL and ELT?

In ETL, data is transformed before it reaches the target system. In ELT, raw data is loaded first and transformed inside the target, usually a cloud data warehouse, using SQL.

What is a data pipeline?

A data pipeline is any automated series of steps that moves data from one system to another. ETL and ELT are common kinds of data pipelines, alongside streaming pipelines that process events continuously.

Is ETL still used?

Yes. The ELT variant has become very common with cloud warehouses, but transforming data before loading is still standard when data must be cleaned, filtered, or stripped of sensitive fields before it reaches the target.

ETL