What is ETL? Extract, transform, load explained

Extract: getting data out of source systems

Extraction pulls data out of wherever it originates — operational databases, third-party APIs, log files, spreadsheets, software-as-a-service platforms. Source systems rarely store data in a form that is convenient for analysis; they store it in whatever structure suits the application generating it, which is often a different shape entirely from what a report or model needs later.

A good extraction process handles this variety without disrupting the systems it reads from — pulling only new or changed records where possible, rather than repeatedly hauling an entire dataset, and coping gracefully when a source is temporarily unavailable or its format shifts slightly.

Transform: cleaning, standardising, joining

Transformation is where raw, extracted data becomes usable. That typically means cleaning it — fixing malformed values, handling missing fields, removing duplicates — standardising it, so that a date, a currency or a customer identifier means the same thing everywhere it appears, and joining or aggregating it, combining data from multiple sources into the structure the destination expects.

This is usually the most labour-intensive step, because real-world data is inconsistent in ways that are hard to anticipate fully in advance. It is also the step most directly responsible for whether the data that eventually reaches an analyst or a model can be trusted.

Load: getting data into its destination

Loading writes the transformed data into its destination — commonly a data warehouse for structured analytics, though it might equally be a data lake, a search index or another downstream system (our companion explainer on data lakes versus data warehouses covers how those destinations differ). A well-designed load step is repeatable and recoverable: it can run again safely if it fails partway, without duplicating or corrupting what is already there.

ETL versus ELT

ETL transforms data before it reaches the destination — extract, then transform, then load. ELT reverses the last two steps: extract, load the raw data straight into the destination, then transform it there, using the destination’s own processing power. ELT has grown alongside cheap, scalable cloud storage and increasingly powerful cloud warehouses: when storing raw data is inexpensive and the destination itself can transform it efficiently at scale, there is less reason to do that work in a separate step beforehand.

Neither approach has replaced the other outright. ETL still suits situations where data must be cleaned, filtered or masked before it reaches a destination — for cost, compliance or sensitivity reasons — while ELT suits large-scale analytics where keeping the raw data around, and transforming it flexibly later, is valuable in its own right.

Batch versus streaming pipelines

Data pipelines also differ in timing. A batch pipeline processes data in scheduled chunks — every hour or every night, for example — which suits reporting and analytics where a short delay between an event happening and it appearing in a dashboard is perfectly acceptable. A streaming pipeline processes each event as it arrives, in near real time, which suits use cases such as fraud detection or live operational dashboards, where the value of the data depends on acting on it quickly rather than waiting for the next batch window.

The choice between batch and streaming is independent of the ETL-versus-ELT choice — either transformation order can run on a batch or a streaming schedule — and a mature data platform often runs both, batching some sources and streaming others, depending on how quickly each needs to be actioned.

Why it is the backbone of analytics and machine learning

Every dashboard, report and machine-learning model is only as trustworthy as the data pipeline feeding it — the familiar principle of garbage in, garbage out. Extraction that misses records, transformation that silently mangles values, or loading that duplicates rows will quietly poison every analysis built on top, often long before anyone notices. Getting ETL or ELT right is unglamorous, invisible-when-done-well work, and it is precisely why data-engineering roles exist as a discipline separate from data analysis or data science.

Cloud providers offer managed tools that handle much of this plumbing: AWS Glue, Microsoft’s Azure Data Factory and Fabric pipelines, and Google Cloud’s Dataflow, alongside a wide range of open-source tools outside any single vendor’s ecosystem. Each automates extraction, transformation and loading to varying degrees; the concepts in this article — not any one tool’s specific interface — are what carries across all of them.

ETL and data-engineering certifications

Building and operating pipelines like these is central to data-engineering work, and it shows up directly in data-engineering certification syllabuses. The AWS Certified Data Engineer – Associate (DEA-C01) has data ingestion and transformation as one of its core domains, and Microsoft’s Fabric Data Engineer Associate (DP-700) covers equivalent ground on the Fabric platform. Either exam expects candidates to reason about extraction sources, transformation logic, batch versus streaming design, and where the transformed data ultimately lands — exactly the process this article has walked through.