ETL, short for extract, transform, load, is a data integration process that copies data out of source systems, cleans and reshapes it in a separate processing layer, and then writes the finished result into a target store, most often a data warehouse. In data engineering, ETL is the classic pattern for turning scattered operational records into consistent, query-ready tables for reporting and analytics.
The defining feature of ETL is the order of the steps: transformation happens before loading, on a dedicated engine or server, so the target only ever receives validated, conformed data. This distinguishes it from ELT, where raw data is loaded first and transformed inside the target platform.
How the three ETL stages work
Each ETL stage has a distinct responsibility, and a well-designed job keeps those responsibilities separate so that failures can be isolated and rerun. The extract stage reads from databases, APIs, files or message queues; the transform stage applies business rules; the load stage writes to the target with the correct write semantics.
Extraction is either full, which rereads an entire table, or incremental, which reads only rows changed since the last run. Incremental extraction relies on a high-water mark such as an updated-at timestamp or an increasing ID, or on change data capture (CDC), which reads the database transaction log and also captures deletes that timestamp queries miss.
The table below summarizes what each stage does and what typically goes wrong in it.
| Stage | Main tasks | Typical failure |
|---|---|---|
| Extract | Connect to sources, read full or changed rows, stage raw copies | Source timeouts, missed deletes, schema drift |
| Transform | Cleanse, deduplicate, join, convert types, apply business rules, aggregate | Wrong join keys, silent row loss, unhandled nulls |
| Load | Insert, upsert or merge into target tables, update indexes and statistics | Duplicates on rerun, partial loads, lock contention |
Most production jobs also write extracted data to a staging area before transforming it. Staging decouples the job from the source, allows a rerun without hitting the source again, and keeps an audit copy of exactly what was received.
Common transformations in ETL
Transformation is where business meaning is added, and it usually accounts for most of the logic and most of the defects in an ETL pipeline. The operations below are the building blocks that appear in almost every warehouse load.
| Operation | What it does | Example |
|---|---|---|
| Cleansing | Fixes or rejects invalid values | Trims spaces, standardizes country codes to ISO 3166 |
| Deduplication | Removes repeated records | Keeps the latest version of each customer ID |
| Type conversion | Casts values to target types | Parses text dates into DATE columns in UTC |
| Conforming | Aligns codes across sources | Maps two CRM status lists to one shared list |
| Surrogate key lookup | Replaces natural keys with warehouse keys | Joins orders to the customer dimension key |
| Aggregation | Summarizes detail rows | Totals daily sales per store |
In dimensional models, transformation also manages slowly changing dimensions (SCD). Type 1 overwrites an attribute and loses history, while Type 2 closes the old row and inserts a new version with validity dates, which preserves history at the cost of more rows and more complex joins.
ETL compared with ELT and streaming
ETL is one of several integration patterns, and the right choice depends on where compute is cheapest, how fresh the data must be and who owns the transformation logic. Cloud warehouses with elastic compute made ELT the default for many teams, but ETL remains the better fit in several situations.
| Aspect | ETL | ELT | Stream processing |
|---|---|---|---|
| Where transformation runs | Separate engine or server | Inside the target warehouse or lakehouse | Stream processor on events in flight |
| Data in the target | Only cleaned, conformed data | Raw plus transformed layers | Continuously updated results |
| Typical latency | Minutes to a day (batch) | Minutes to hours | Seconds or less |
| Strength | Sensitive data filtered before landing | Raw history kept, models rebuilt easily | Near-real-time reaction |
| Weakness | Reprocessing needs a new extract | Raw sensitive data lands in the target | Harder state, ordering and replay |
ETL is the stronger choice when personal data must be masked or removed before it reaches the target, when the target has limited compute, or when the source data needs heavy procedural processing that SQL handles poorly. ELT usually wins when the warehouse scales cheaply and analysts want to iterate on models over retained raw data.
Designing reliable ETL jobs
A reliable ETL job produces the same result no matter how many times it runs for the same input window. This property, called idempotency, matters because jobs fail halfway and orchestrators retry them automatically.
- Make loads idempotent with merge or upsert on a business key, or by replacing a whole date partition instead of appending rows.
- Reconcile counts and totals between source and target after each run, for example row counts and the sum of order amounts.
- Quarantine bad rows in a reject table with the reason, rather than failing the whole job or silently dropping them.
- Version the transformation code and track lineage, so that every target column can be traced back to its source fields.
- Schedule by dependency, not by clock, using an orchestrator that starts a job only when its upstream inputs have completed.
Batch windows are a practical constraint. A nightly job that must finish before business hours begin forces incremental extraction and parallel loads once volumes grow, because a full reload that took 20 minutes at launch can take hours a few years later.
Common ETL mistakes and how to fix them
Most ETL incidents trace back to a small set of design gaps rather than to infrastructure failures. The table below maps frequent symptoms to their usual causes and fixes.
| Symptom | Likely cause | Fix |
|---|---|---|
| Totals double after a retry | Append-only load without a key | Use merge on a business key or partition overwrite |
| Deleted source rows still appear | Timestamp-based incremental extract | Use CDC or periodic full reconciliation |
| Job fails after a source release | Unannounced schema change | Schema checks at extract and data contracts with owners |
| Revenue differs from the source system | Time zone or currency handled inconsistently | Convert to UTC and one currency in a single shared step |
| Reports show yesterday’s data at noon | Batch window overruns | Incremental loads, parallelism, earlier dependencies |
A further mistake is hiding business rules inside opaque scripts. When the definition of an active customer lives only in a stored procedure, analysts cannot see it and different pipelines drift apart. Documenting rules and testing them with known input and expected output closes that gap.
Put simply
ETL is how a company gathers information from many different systems and turns it into one tidy, trustworthy collection. Extract means collecting the data, transform means cleaning and organizing it, and load means putting the finished result where people can use it.
Think of a restaurant kitchen. Ingredients arrive from several suppliers, the cooks wash, cut and combine them, and only the finished dish goes out to the dining room. The guests never see the raw ingredients, only the prepared result, and that is exactly the idea behind ETL.
In practice
A regional retailer with 40 stores runs its point-of-sale system on one database, its online shop on another and its inventory in a third application. Every night at 1:00 a.m., an ETL job extracts the previous day’s changes from all three, about 300,000 sales lines in total, into a staging area.
The transform step converts all timestamps to UTC, maps the two different product code lists to a single product dimension, removes duplicate online orders created by payment retries and masks customer email addresses before anything reaches the warehouse. The load step then merges the rows into the sales fact table and replaces the matching date partition.
After loading, the job compares row counts and revenue totals with each source and stops the run if they differ by more than 0.1%. Store managers open a sales dashboard at 7:00 a.m. with one consistent revenue figure for the previous day, instead of three conflicting numbers from three systems.