ETL stands for Extract, Transform, Load: pulling data out of source systems (extract), reshaping it into a usable structure (transform) and writing it to a target system such as a database or dashboard (load). It is the standard pattern behind reports and dashboards that combine data from multiple systems.
ETL in practice
The centre of gravity almost always sits in the T of transform: aligning amounts in different currencies, de-duplicating customer names, normalising dates and relating sources to one another. Anyone who skips that step and loads raw data straight into a dashboard ends up with figures that are just slightly off — and with that, arguments about the data instead of about the result.
In the Microsoft world, Power Query is the best-known ETL tool: it is built into Excel and Power BI. For recurring reports, an automated ETL flow is the answer to cut-and-paste work; see Automating Excel reporting.
Related terms
- Power Query — Power Query is the tool in Excel and Power BI with which you retrieve, clean and reshape data from sources — without programming.
- Data model — A data model is the structure in which data and its mutual relationships are captured: which tables there are (customers, orders, products), which fields they contain and how they are linked together.
- BI — BI (business intelligence) is the collecting, combining and visualising of business data to base decisions on — usually in the form of dashboards and reports.
- Middleware — Middleware is software that sits between two or more systems and manages the traffic between them: retrieving data, translating it into the right format and delivering it to the target system.