ETL, ELT, and Incremental Loading

Same three steps, different order

ETL and ELT share extraction, transformation, and loading; their defining difference is order and location. Both are sequences that prepare data for analysis, so the argument between them is never about which one skips a step.

Extraction is the first move in either case: collecting raw data from databases, files, applications, sensors, or application events, structured or not. Transformation is the work of turning that raw shape into the shape a consumer needs — converting types and formats, dropping inconsistent or inaccurate records, removing duplicates. Loading is the write into the target where analysis will happen.

What changes is where transformation sits relative to that write.

ETL transforms extracted data on a secondary processing system before loading it into the analytical target.

ELT loads extracted data in its natural state and transforms it inside the target warehouse or lake as needed.

The consequence is what each approach leaves you holding.

ETL delivers target-ready data at load time, whereas ELT retains raw inputs for later transformations. In ETL, whatever arrives in the target has already passed through the rules; in ELT, the raw material is still there, and you can transform it again as often as a new question requires.

Both patterns extract from sources and prepare data for analysis; neither name describes extraction alone, because the distinction concerns transformation relative to loading, and either pattern can use full or incremental loads.

What transforming first buys you

Doing the work before the load is not merely the older habit. It buys two things that are difficult to obtain any other way.

ETL suits predetermined structured targets and can filter or mask sensitive data before it reaches the target. When the target schema is fixed and known in advance, pre-load transformation is what guarantees every row fits it. And because the rules run before the write, they can enforce a boundary: values that must never be stored in the target simply never travel there.

That second point is a control, not a preference. Masking a personal identifier after loading means the raw identifier was in the target, however briefly, and appeared in whatever the target keeps of it. Masking before loading means it never crossed the line at all.

What loading first buys you

The modern default runs the other way, for reasons that are just as concrete.

ELT uses the target platform's processing power and can retain structured, semi-structured, and unstructured inputs. A cloud warehouse or lake can scale its own compute and transform in parallel, so the transformation step stops being a separate bottleneck that has to grow with the data. Inputs that would not fit a fixed tabular schema — documents, events, varied formats — can land first and be interpreted later.

ELT can simplify the stack by performing transformations in the target instead of maintaining a separate transformation server. One fewer system to size, patch, monitor, and pay for. The target also brings its own access controls to the raw data it now holds, so governance is configured where the data lives rather than rebuilt in a middle tier.

Nothing is removed from the pipeline by this choice: extraction and loading still happen, the target still holds untransformed data by design, and that raw data still has to be governed.

Choosing under real constraints

The decision should follow constraints rather than fashion. Two claims settle most arguments:

Legacy formats, edge filtering, or a rule that raw sensitive fields must never enter the target can favor ETL.

Flexible exploration at scale and repeated transformations over retained raw data can favor ELT.

The two also coexist. A complex analytics estate can prepare some sources ahead of the load and land others raw, choosing per source rather than per organization.

Work a case through. A regulated target must never receive unmasked personal identifiers, its schema is fixed, a separate processing tier already exists, and analysts do not need the raw values.

Every constraint points the same way. The security rule requires the masking to happen before the write. The fixed schema means the shape of the load is known in advance, which is exactly the situation pre-load rules handle well. The processing tier is already built and staffed, so its cost is not an argument against using it. And the one thing that would pull toward retaining raw data — analysts wanting to re-derive from it — has been explicitly ruled out.

So: transform and mask before loading the fixed target. Loading the identifiers first and masking them later breaks the rule at the moment of the write. Retaining every raw identifier for exploration keeps precisely the values that must not be kept.

When the constraints point the other way

Change the facts and the answer flips cleanly. A cloud warehouse can scale its compute, incoming data arrives in varied formats, analysts must derive new models repeatedly without re-extracting the sources, and raw access is already governed inside the target.

Load the raw inputs and transform them in the target as needed. Scalable target compute makes transformation there cheap; varied formats are easier to land than to force into a fixed shape up front; repeated modeling needs the raw data to still be present; and the governance objection has been answered by the target's own controls.

The alternatives each fight a stated fact. Transforming every use case on a fixed external server means a new question demands new upstream work. Discarding the raw inputs after one model removes the material the next model needs. Re-extracting from the sources for every analytical question is the cost that retaining the raw data exists to avoid.

Loading scope is a separate decision

Here is the distinction most often collapsed by mistake. Loading scope is a separate decision from ETL versus ELT: how much data a run moves has nothing to do with where the transformation happens. Either pattern can use full or incremental loads.

A full refresh replaces the target from a complete source extract rather than relying on prior load state. Every run reads everything and rebuilds the target. It is simple and self-correcting — no saved progress can drift, because none is kept — and it costs a full pass every time.

An append-only load adds new source records without revising previously loaded target rows. It fits data that is genuinely additive, such as a log of events that happened. It is the wrong choice when earlier records get corrected or deleted, because those revisions never reach the rows already written.

Loading only what changed

A change-based incremental load applies records that are new or modified since the last successful load. Unlike append-only, it carries revisions; unlike a full refresh, it does not re-read everything to find them.

That "since" has to be stored somewhere. A watermark records the last processed value so the next incremental run can select a later range. The watermark column is usually a value that rises as rows are added or updated — a last-modified timestamp, or an increasing identifier — and its highest processed value is what gets saved.

A run then follows the same shape every time:

1. Read the saved watermark: where the previous run stopped.
2. Read the current highest value in the source watermark column: where this run will stop.
3. Copy the rows that fall between those two boundaries.
4. Save the new watermark, so the next run starts where this one ended.

Change data capture can limit processing to data that changed since the prior run, reading the source's own record of changes instead of comparing values.

The saving is real and so is the obligation. Incremental loading reduces repeated work but requires reliable progress state and correct handling of updates.

Two things can go wrong, and both are consequences of relying on saved state. If the watermark is lost, corrupted, or written before the copy actually succeeded, the next run selects the wrong range and rows are silently skipped — which is why the watermark is saved after the copy, not before it. And if the pattern only inserts, a row that was modified after it was first loaded arrives as a second copy or not at all, depending on the target's keys.

A full refresh has neither problem, because it keeps no state and rebuilds from scratch. That is the trade being made: repeated work in exchange for not having to be right about progress.

Designing one from the numbers

Constraints usually decide this outright. Take a source of 500 million rows in which roughly 0.2 percent change on any given day, where updates to old keys genuinely matter, and where the pipeline can store a reliable last-modified watermark.

Three of those facts each eliminate an option:

• 0.2 percent change daily. A full refresh would extract 500 million rows to carry about a million meaningful ones. It works, but it repeats nearly all of its work every day.
• Updates to old keys matter. Append-only is out. Loading only newly created keys leaves every corrected record stale in the target.
• A reliable watermark exists. The one requirement of change-based loading is satisfied, so its saving is available without gambling on unreliable progress state.

Select and apply the rows beyond the saved change watermark, treating new and modified records alike. That reads about a million rows instead of 500 million, and still carries the revisions that append-only would have dropped.

Key takeaways

• ETL transforms extracted data on a secondary processing system before loading it into the analytical target. ELT loads extracted data in its natural state and transforms it inside the target warehouse or lake as needed.
• ETL suits predetermined structured targets and can filter or mask sensitive data before it reaches the target; ELT uses the target's processing power, keeps structured, semi-structured, and unstructured inputs, and can simplify the stack by dropping a separate transformation server.
• Let constraints choose: legacy formats, edge filtering, and a hard rule against raw sensitive fields in the target favour ETL; flexible exploration at scale and repeated transformation over retained raw data favour ELT.
• Loading scope is its own decision. A full refresh replaces the target from a complete source extract rather than relying on prior load state. An append-only load adds new source records without revising previously loaded target rows. A change-based incremental load applies records that are new or modified since the last successful load.
• Incremental loading reduces repeated work but requires reliable progress state and correct handling of updates.