ETL (Extract, Transform, Load)

What Is ETL (Extract, Transform, Load)?

ETL: Extract, Transform, Load, is the standard three-stage pipeline for moving data from a source system into a target system, database, or archive. Extraction pulls the data and its structure out of the source; transformation converts and reshapes it according to a mapping specification; loading writes the transformed data into its final destination. It’s the underlying mechanical process behind nearly every archiving, migration, and analytics data movement project.

In an archiving context specifically, the “load” stage has a distinctive requirement compared to a typical analytics ETL pipeline: the target isn’t a data warehouse optimized for aggregate queries, but a compliant, long-term repository that needs to support both bulk ad hoc reporting and individual business-object retrieval, potentially for a decade or more, with an audit trail proving the data’s provenance. That changes how the loading stage is designed, preserving original field-level detail and relationships matters more than optimizing for a specific analytical query pattern the way a typical business intelligence ETL pipeline might.

ETL has a close variant, ELT (extract, load, transform), where raw data is loaded into the target first and transformed afterward, taking advantage of modern data platforms that can transform data efficiently in place. Archiving projects still lean toward traditional ETL more often than ELT, because transformation before loading, particularly reconstructing business objects from multiple related source tables, is usually a prerequisite for the archive to be usable at all, rather than an optimization that can be deferred.

Key Points

• Three distinct stages, each with a different job. Extraction gets data out of the source, transformation reshapes it according to a mapping specification, and loading writes it into the target, conflating the stages is a common source of project confusion.
• The standard mechanism behind migration and archiving projects. Whether the goal is a new application, a data warehouse, or a compliance archive, ETL is the underlying process most of these projects use to actually move the data.
• Archival ETL optimizes differently than analytics ETL. A compliance archive needs to preserve full relational detail for both bulk reporting and individual record retrieval over many years, rather than being shaped around a specific recurring analytical query the way a typical BI pipeline is.
• ELT is a related but distinct approach. Extract-Load-Transform loads raw data first and transforms it afterward inside the target platform, archiving projects generally still favor transforming before loading, since usable business object reconstruction is usually a prerequisite, not an optimization.
• Each stage carries its own risk of error. A missed table during extraction, a mis-specified mapping during transformation, or a failed write during loading are three distinct failure points, each requiring its own validation rather than a single end-to-end check.

Common Use Cases

🔄 Legacy application archiving pipeline

A legacy ERP’s data is extracted from its source database, transformed into reconstructed business objects according to a documented mapping, and loaded into the target archive, with validation performed after each stage.

🏥 Clinical data ETL for a retiring EHR

Patient and encounter data is extracted from a legacy electronic health record system, transformed to standardize coded clinical values, and loaded into a compliant clinical archive that preserves the full patient record structure.

📊 Combining ETL output with ad hoc reporting

Once ETL has loaded data into the archive with its structure intact, finance and audit teams run ad hoc reports directly against the loaded data — a capability that depends entirely on the transformation stage having preserved relationships correctly rather than flattening the data.

ETL (Extract, Transform, Load) vs. Related Concepts

ETL is the umbrella pipeline; its individual stages are each their own glossary term.

DATA EXTRACTION

The first stage of ETL, focused specifically on pulling data and structure out of the source system. Extraction alone doesn’t transform or load anything.

DATA TRANSFORMATION

The middle stage, converting and reshaping extracted data according to a mapping specification. Transformation happens after extraction and before loading.

DATA INGESTION

A broader, sometimes less structured term for bringing data into a system, which can include ETL but also covers simpler cases like streaming data or bulk file imports without a formal transform stage.