Getting data out of operational systems and into an analytical store is the job of a data pipeline. The two patterns differ in one respect: where the transformation happens.
Extract, transform, load (ETL) extracts data from the sources, transforms it according to business rules in a separate, specialised engine (often using staging tables to hold data while it is processed), and then loads the result into the destination. Transformations typically include filtering, sorting, aggregating, joining, cleaning, deduplicating and validating data.
Extract, load, transform (ELT) extracts the data and loads it into the target store first, then transforms it there, using the target store’s own processing power. Learn notes that ELT is common in modern lakehouses. Its advantages are a simpler architecture (no separate transformation engine) and performance that scales with the target store. It only works well, though, when the target system is powerful enough to transform the data efficiently. Figure 2.2 compares where the transformation runs in each pattern.
| Aspect | ETL | ELT |
|---|---|---|
| Order of steps | Extract, transform, then load | Extract, load, then transform |
| Where transformation runs | A separate transformation engine before the target | Inside the target data store |
| Raw data in the target | No, only transformed data arrives | Yes, raw data is loaded and kept |
| Typical fit | Constrained target systems, complex business rules, curated staging audits before loading | Modern warehouses and lakehouses with elastic compute; keeping raw data for exploration or future schema changes |
On Azure, pipelines are built with Data Factory in Microsoft Fabric for work inside Fabric, or with the standalone Azure Data Factory service. Both are described in Chapter 7, Large-scale analytics: Microsoft Fabric and Azure Databricks.
Common trap: Thinking ELT skips transformation - ELT still transforms the data; the difference from ETL is only where and when, with the transformation running inside the target store after loading.