How it works
ETL is a data pipeline pattern made up of three steps: extract data from sources, transform it into a suitable format, and load it into a destination. The process starts with extraction from sources such as production databases, open APIs, or log files generated during website traffic. Then transformation standardizes formats, applies business rules, removes duplicates and enriches records. Finally, load writes the processed data to the destination, typically a data warehouse or an analytical database. Each step can be re-run, and the pipeline should log what was moved, so analysts know if a metric is wrong because of the data or the code. Extraction can be done from diverse sources, transformation converts data into a common format, which is needed to join information coming from different sources.
Practical example
A retailer extracts sales from multiple channels, converts currencies, removes duplicates, aggregates by day and loads the results into a data warehouse. The dashboard queries the data warehouse and shows revenue evolution by channel. If one of the channels sends a file with a column name different from expected, the pipeline can alert before loading, avoiding the report mixing tables with different formats.
What it's for
ETL standardizes data before it enters the analysis environment, which helps with consistent reporting and models that consume structured data. It allows you to control data quality at the destination boundary, applying business rules before anything is consumed. Standardization prevents a format change in a source from breaking production dashboards and makes period over period comparison harder. In a marketing environment, the pipeline joins navigation events, purchases and customer information, and delivers a consolidated view for the analysis team.
Difference between X and Y
ELT is the younger sibling: data is loaded first and transformed at the destination. ETL transforms before loading. The choice depends on processing capacity, data quality and the type of consumption.