ETL, ELT and where a data lake sits
This page is about the general shape of an analytics pipeline. It is not about the AWS service formerly named Data Pipeline, which is covered under Amazon Data Pipeline.
The common flow
Section titled “The common flow”- Operational database — the source system
- ETL or ELT — extract, transform, load, in one order or the other
- Data warehouse and/or data lake — storage and processing
- BI tools — analysis and visualisation
Where transformation happens
Section titled “Where transformation happens”Transformation happens on the way into the lake or warehouse, not on the way out. A data lake is a destination, not an intermediate staging area, and running a second ETL pass after the lake is usually a sign that the lake has become a landing zone by accident.
Three patterns cover most cases:
| Pattern | Typical use |
|---|---|
| ETL → data warehouse → BI tools | Modelled, governed reporting on known questions |
| ELT → data lake → BI tools | Exploratory analytics where the questions are not fixed yet |
| ELT → data lake → data warehouse → BI tools | Raw data retained for exploration, curated subsets promoted for reporting |
ETL or ELT
Section titled “ETL or ELT”ETL transforms before loading. It is the traditional approach, and it keeps the destination clean at the cost of deciding the schema up front — anything the transformation discards is gone.
ELT loads raw data first and transforms later. It is the modern default alongside data lakes, because storage is cheap enough to keep the raw form and defer the modelling until the question is known. The cost is governance: raw data accumulates, and without a catalogue and access controls nobody can tell what is safe to use. That is the gap Lake Formation fills.