ETL is the traditional pattern for moving data from where it is created to where it is analysed.
It exists because source systems hold data in shapes built for running the business, not for answering questions.
Transforming before loading means only clean, agreed data reaches the warehouse.
Extraction can copy everything each time, or only what changed since the last run.
Copying only changes, called incremental extraction, is faster but needs a reliable way to spot what changed.
Transformation is where cleaning happens: fixing types, removing duplicates, mapping categories and checking rules.
Joining sources, such as orders with customers, and calculating new columns also happen here.
Loading can replace the target table or add new rows to it.
Pipelines should be idempotent, meaning a rerun of the same day gives the same result instead of doubling rows.
Failed runs need clear alerts, because a silent failure leaves yesterday's numbers looking current.
Because the raw data is not kept, changing a transformation rule needs a fresh extraction.
ETL runs on a separate system, which must be powerful enough for the heaviest transformations.
It suits sensitive data well, since personal details can be removed before anything reaches the warehouse.
In practice, ETL jobs often run nightly, after the business day ends and source systems are quiet.
The usual mistake is a pipeline that adds rows on every rerun, which quietly double counts whole days.