Every analytical system starts with the same problem: the data lives in a dozen operational systems, a shop platform, a CRM, a payment provider, a support desk, each with its own identifiers, formats and definitions of a customer. ETL is the discipline of combining them. Extract reads the records out, by query, by API or by file. Transform makes them agree: one customer key across systems, one currency, one definition of an order, bad rows quarantined. Load writes the result into the data warehouse that analysts query. Kimball and Caserta's book on the subject made the point two decades ago that this stage absorbs most of the effort in any warehouse project, and nothing since has moved that share.
ELT reverses the last two steps for a reason that is economic rather than technical. Transformation used to run on a separate server because the warehouse was expensive and slow; cloud warehouses charge by the query and scale on demand, so loading the raw records first and transforming them in SQL inside the warehouse became cheaper and simpler, and the AWS overview now describes ELT as the norm. The raw layer stays available for a question nobody anticipated, the transformations become SQL models that an analyst can read, and tools built around the pattern treat those models as versioned, tested code. The pattern suits high-volume and semi-structured sources; ETL survives where data must be cleaned or anonymised before it may land anywhere.
| Aspect | ETL | ELT |
|---|---|---|
| Where transformation runs | A separate engine before loading | Inside the warehouse after loading |
| Raw data | Discarded or kept outside the warehouse | Kept in the warehouse as a landing layer |
| Language of the logic | A tool's own, or Python | Mostly SQL, as versioned models |
| Suits | Regulated data that must be cleaned first; fixed reports | Cloud warehouses; evolving questions; large and semi-structured sources |
Four properties of a trusted pipeline
Idempotent: re-running a load does not duplicate records. Incremental: only changed records are moved. Observable: a source that has stopped sending is noticed before the reporting run. Documented at column level: an analyst can trace each figure to its source system.
The pipeline is where data engineering and analysis meet, and where most quality problems are born or caught. The four properties listed above are what distinguish a pipeline that can be trusted from one that runs without checks. The data engineering courses teach the tools; the AWS Data Engineer Associate and Databricks Data Engineer Associate exams test the design of ingestion and transformation on their platforms, and Microsoft's Fabric Data Engineer exam does the same for its own.