ETL (extract, transform, load)
ETL is the process of extracting data from source systems, transforming it (cleaning, mapping, joining, aggregating) and loading it into a target such as a data warehouse. ELT is the variant where raw data is loaded first and transformed inside the target platform.
Roles that need it
What it is
ETL stands for extract, transform, load. Extraction pulls data from source systems such as a core banking platform, a trading system or a general ledger. Transformation applies rules: correcting formats, mapping codes to a common standard, joining tables, converting currencies and deriving new fields. Loading writes the result into a target, usually a data warehouse or a reporting mart.
In ELT (extract, load, transform), raw data lands in the target first, often a cloud data platform, and transformations run there. Both patterns are built with orchestration tools that schedule jobs, manage dependencies and log every run.
Why it matters
Most management, risk and regulatory reports depend on ETL pipelines. If a transformation rule is wrong, every report built on it is wrong in the same way, often without anyone noticing. Supervisors therefore expect transformations to be documented in data lineage, controlled and reconciled back to source. The ECB’s BIRD (Banks’ Integrated Reporting Dictionary) initiative publishes a reference data model and transformation rules to help banks produce regulatory data consistently.
Good ETL design also affects cost and speed: pipelines that reload everything every night are slow, while incremental loads process only what changed.
Worked example
Illustrative numbers. A loan extract contains 100,000 records. The transformation step rejects 300 records with a missing currency code and converts the rest to euro at the month-end rate. It then aggregates loans by country and sector. A control compares totals: the source shows 8,000 million euro equivalent and the warehouse shows 7,976 million. The 24 million gap matches the rejected records exactly, so the pipeline is complete apart from known rejections. The rejected records go to the data steward for correction before the next run.
Who uses it
Data analysts and BI (business intelligence) analysts build or modify pipelines and check that outputs match expectations. Data analysts in banking map source fields to reporting definitions. Regulatory reporting analysts reconcile loaded data to the ledger and investigate breaks. Data governance analysts make sure each transformation is documented in lineage and has an owner. Business analysts write the mapping specifications that developers implement.
Common confusions
ETL is a process; a data warehouse is a destination. The two are often mixed up because they are built together. Another confusion is between ETL and data migration: ETL usually runs repeatedly on a schedule, while a migration moves data once from an old system to a new one. Finally, adding manual spreadsheet steps between ETL stages breaks lineage and controls, even when the automated parts are well designed.