ETL
Copy data out of its sources, clean it up, load it where it can be analyzed.
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.
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.
