# ETL

> Copy data out of its sources, clean it up, load it where it can be analyzed.

- Level: Intermediate
- Topics: Data
- URL: https://tidbit.legios.com.ar/etl/
- In Spanish: [ETL](https://tidbit.legios.com.ar/es/etl/)

**ETL** stands for extract, transform, load: the three steps of moving data from the systems where it is born to the place where it is analyzed. **Extract** reads it from the sources: the app's database, a CRM, a payments provider's API, spreadsheets. **Transform** cleans and reshapes it. **Load** writes the result into a data warehouse or a data lake.

The transform step is where most of the work hides. Dates come in three formats, the same customer appears as 'ANA PÉREZ' in one system and 'Ana Perez' in another, prices are in different currencies, some rows are duplicated and others are missing a field. The pipeline fixes all of that the same way every time, so the numbers in the dashboard can be trusted and compared month to month.

Pipelines run on a schedule (every night, every hour) or continuously, and a broken one shows up as a dashboard that quietly stops updating. Many modern teams flip the order to **ELT**: load the raw data first, then transform it inside the warehouse with SQL, which makes sense now that warehouses are powerful and storage is cheap. Tools like AWS Glue, Apache Airflow, dbt and Fivetran exist to build, schedule and monitor these pipelines.

## An analogy

A restaurant's prep kitchen. Ingredients arrive from different suppliers (extract), get washed, peeled and cut the same way every morning (transform), and go into labeled containers on the line (load), so the cooks never deal with a muddy potato.

## Keep in mind

- Extract from the sources, transform (clean, join, standardize), load into the destination.
- Most of the effort, and most of the bugs, live in the transform step.
- ELT loads the raw data first and transforms it inside the warehouse.
- Pipelines run on a schedule or continuously, and need monitoring for when they break.

## Go deeper

- [What is ETL? (AWS)](https://aws.amazon.com/what-is/etl/)
- [Extract, transform, load on Wikipedia](https://en.wikipedia.org/wiki/Extract,_transform,_load)

## Related terms

- [Database](https://tidbit.legios.com.ar/database/): An organized store of data that programs can search and update safely.
- [API](https://tidbit.legios.com.ar/api/): The menu of things one program lets another program ask for.
- [Spreadsheet vs database](https://tidbit.legios.com.ar/spreadsheet-vs-database/): Why a company that runs on Excel eventually needs a real database.
- [Data warehouse](https://tidbit.legios.com.ar/data-warehouse/): A database built for analysis: years of history, from many sources, in one place.
- [Data lake](https://tidbit.legios.com.ar/data-lake/): Cheap storage where all your data is kept as raw files, to be sorted out later.
- [SQL](https://tidbit.legios.com.ar/sql/): The language you use to ask a database questions.
- [AWS Glue](https://tidbit.legios.com.ar/aws-glue/): AWS's service for finding, describing and cleaning up data, with no servers to run.
- [dbt](https://tidbit.legios.com.ar/dbt/): A tool for writing data transformations as SQL files, with tests and docs, like software.