What Is ETL? Extract, Transform, Load Explained

ETL stands for Extract, Transform, Load: pull data from sources, transform it into the desired shape, then load it into a target system.

ETL is the classic pattern for moving data from operational systems into analytical ones. For decades it was the default way to populate a data warehouse: extract from sources, transform the data in a dedicated step, and load the clean result into the destination.

What ETL is

ETL is a three-stage process. Extract reads data from sources such as databases, APIs, or files. Transform cleans, joins, and reshapes it - filtering bad rows, standardizing formats, deriving columns. Load writes the finished data into the target, usually a warehouse.

Why transform before loading

In classic ETL, transformation happens outside the warehouse, often on a dedicated processing engine, before the data is loaded. This made sense when warehouse compute was expensive and you wanted only clean, modeled data to land. It also enforces a schema up front.

ETL versus ELT

The modern alternative, ELT, loads raw data first and transforms it inside the warehouse afterward, taking advantage of cheap, scalable cloud compute. ETL is still preferred when data must be cleaned or masked before it can land for compliance or volume reasons.

ETL in CI

ETL code is code: unit-test transformations against fixtures and run the job end to end on sample data before it touches production.

steps:
  - run: pytest tests/transform     # test transform logic
  - run: python etl.py --source sample.csv --dry-run

Latchkey note

ETL test jobs pull sample data and may spin up a database service container. On Latchkey, caching those fixtures and dependencies speeds runs, and auto-retry covers a source API or database that blips during extraction.

Key takeaways

• ETL extracts data from sources, transforms it outside the target, then loads the clean result.
• Transforming before loading enforces a schema and suits cases where data must be cleaned or masked first.
• ELT is the modern alternative that loads raw data and transforms it inside the warehouse.

Related guides