Extract, transform, load (ETL)
Extract, transform, load (ETL) is a data integration process that combines data from multiple sources into a single, consistent data store. Data is extracted from source systems, transformed into a usable format, and loaded into a target system, most often a data warehouse built on analytical databases. ETL is a foundational process in big data pipelines, feeding data warehouses and data lakes that in turn supply management information systems and other analytical consumers.
The three phases
ETL is conventionally described as three sequential phases, though in practice they overlap and are often pipelined. Structurally, an ETL job is a pipe-and-filter pipeline. Extract, transform, and load are the filters, and the staging tables and queues between them are the pipes.
- Extract. Data is read from one or more source systems — transactional databases, logs, APIs, files, or third-party feeds — and staged in a working area. Sources rarely share a common schema or update cadence, so the extract phase is where heterogeneity first has to be absorbed.
- Transform. The staged data is cleaned and reshaped into the target schema. Typical transformations include normalization and denormalization, type conversion, deduplication, enrichment by joining against reference data, and aggregation. This is where data integrity is enforced: constraints are checked, missing values handled, and business rules applied so that the loaded data is trustworthy for downstream analytics.
- Load. The transformed data is written into the target store. Loads are either full loads, which replace the target dataset entirely, or incremental loads, which apply only the changes since the last run.
Batch ETL and streaming alternatives
ETL is conventionally a batch processing workload. A scheduled job extracts a bounded snapshot from each source, transforms it, and loads the result on a fixed cadence. Batch ETL favors throughput over latency, so the target store can lag behind the source systems by hours.
Where fresher data is needed, change data capture and stream processing offer streaming alternatives. CDC captures row-level changes from a source database’s transaction log and propagates them continuously, avoiding the periodic full-snapshot extraction of batch ETL. Stream processing systems transform in-flight records as they flow through a pipeline. Both approaches reduce the latency between a source change and its appearance in the target, at the cost of more complex operational machinery.