ETL vs ELT for Enterprise Data Pipelines

A Worked Example: Retail Order to Finance

The same fictional retailer appears throughout this article. Meridian Retail processes about 4 million orders a month across 12 marketplace regions. Four sources feed its warehouse: a Postgres order management system, a payments provider API, one nightly CSV drop per region, and web clickstream events.

The warehouse serves a finance mart and a merchandising dashboard. The nightly window runs from 01:00 to 03:00. Month-end close lands on working day three.

One source forces a decision. Payment records arrive with cardholder data attached, and that data must not be stored in the warehouse. Every other source can land unmodified.

Meridian therefore runs both patterns in one pipeline. The payments feed is tokenised before load, which is ETL. Orders, region files and clickstream land raw and are modelled in the warehouse, which is ELT.

1. Payments feed Tokenise card data in the ingestion layer. Only the token and the last four digits land.
2. Order management Land raw with a change-data-capture watermark. Model into orders and returns in the warehouse.
3. Region CSV drops Land raw, partitioned by region and load date. Normalise currency and tax codes downstream.
4. Clickstream Land raw at full fidelity. Sessionise only where an analyst has asked for it.

One pipeline, two patterns, chosen per source rather than per organisation.

ETL Explained

ETL extracts from source systems, transforms in a dedicated processing layer, and loads the finished result. The warehouse receives data that already matches the model it will serve.

Extract

Extraction reads from operational databases, applications, files and partner feeds. The design question is how change is detected. A full snapshot, an incremental watermark and change data capture each put a different load on the source. The choice also decides whether history can be reconstructed later.

Transform

Transformation runs in a separate engine before anything reaches the target. Typical work includes type casting, deduplication, surrogate keys, currency normalisation and referential checks.

This layer can enforce rules the warehouse never sees. Records that fail validation are quarantined instead of loaded. Fields that must not be stored are masked or dropped before they cross the boundary.

At Meridian this is where card numbers are exchanged for tokens. The warehouse has no path to the original value, which is the point of running the step there.

Load

The load step writes conformed output into target tables. Loads are usually scheduled. The target holds modelled data rather than raw source structures.

Reprocessing means re-running extraction and transformation. The raw input was never kept in the target, so correcting history depends on the source still holding it. Operational systems purge and archive on their own schedule, which sets the real correction window.

Where ETL fits

ETL suits pipelines where rules are stable and validation must happen before data lands. It also suits target platforms with limited or costly compute. Regulated reporting flows often qualify, because the transformation gate is also the control an auditor inspects.

ETL puts a controlled gate in front of the target and keeps no raw record inside it.

ELT Explained

ELT extracts, loads the raw result into the target, and transforms it there using the platform's own compute. The warehouse becomes the transformation engine, not only the destination.

Extract

Extraction is deliberately thin. The aim is to move data with as little reshaping as possible. Less logic in this step means fewer places where a schema change breaks the pipeline.

Load

Data lands in a raw zone, usually partitioned by source and load time. Keeping that zone intact is the point. It is what allows a transformation to be corrected and replayed without asking the source for history again.

Meridian partitions its region files by region and load date. When a tax rule changed retroactively, the affected 30 days were rebuilt from the landed files. No region was asked to resend.

Transform

Transformation runs as SQL against the landed data, usually in layers: raw, cleaned, conformed, then the models analysts consume. Each layer is rebuilt from the one beneath it. Changing a definition becomes a re-run rather than a re-ingest.

Transformation logic also becomes reviewable by the people who own the definitions. Version control, tests and documentation apply to it as they do to application code. Tools in this layer expose the rebuild explicitly, and dbt's incremental model documentation sets out how a full refresh differs from an incremental run.

Why cloud platforms favour ELT

Separating storage from compute removed the constraint that made pre-load transformation necessary. Storage is cheap enough to retain raw history. Compute can be scaled for a rebuild and released afterwards. Exposing SQL as the transformation interface also widens who can own a definition.

ELT keeps the raw record and moves transformation into the platform, so definitions stay cheap to change.

ETL vs ELT Comparison

The two approaches differ on more than sequence. The table sets out the dimensions that usually decide the choice.

How ETL and ELT differ across architecture, cost, governance and analytics readiness

Retained history is the consequential row. Without it, every future question is limited to what someone chose to model at load time. The constraint is invisible on the day the pipeline is built and expensive the first time a definition changes.

Ownership is the second. Under ETL, logic usually lives inside specialist tooling, which concentrates it in a small team. Under ELT, the same logic is SQL in a repository, so more people can read it and propose a change. That often decides how quickly a definition dispute gets resolved.

Sequence is the visible difference. Retained history and elastic compute are the ones that bite.

When to Use ETL

ETL is the right choice in specific, recognisable conditions rather than as a default.

• Regulated transformations where masking or tokenisation must happen before data lands.
• Target platforms with fixed compute, where pushing transformation into them is not viable.
• Pipelines needing strict pre-load validation, so invalid records never reach reporting tables.
• Legacy environments where existing transformation logic is stable and not worth relocating.
• Residency rules that prevent raw source data being stored in the target.

The common thread is that the gate does work the target cannot or must not do. Where that holds, moving transformation into the platform removes a control rather than an inefficiency.

Choose ETL when the pre-load gate is a requirement, not a habit.

When to Use ELT

ELT is the better default on platforms that separate storage from compute. The advantage grows with volume and with how often definitions change.

• Cloud warehouse and lakehouse platforms whose compute scales on demand for a rebuild.
• High-volume ingestion where a fixed processing tier becomes the bottleneck.
• Exploratory analytics, where the questions are not settled.
• AI pipelines that need raw history for feature engineering and retraining.
• Teams who want transformation logic in version control, tested and reviewed.

Change is usually the deciding factor. Where definitions still move, keeping the raw record means a correction costs a re-run instead of a re-ingest. The source is never asked to replay history it may no longer hold.

Choose ELT when definitions will keep changing and the platform can absorb the compute.

Hybrid ETL and ELT

Many enterprise estates run both. That is a reasonable end state, not a transitional compromise. The question is which pattern each pipeline warrants.

Meridian's split is the common one. A pre-load gate applies only where it is required. Card data is tokenised before load; everything else lands raw and is shaped in the platform. The pipeline is ETL for the fields that need a gate and ELT for the rest.

A second pattern appears around acquired or partner systems. Feeds arriving in an unusable shape get a normalisation step before load. Landing them raw would push the same parsing work into every downstream model. Once the feed stabilises, that step is often retired.

Treating the choice as binary causes real damage. Teams standardising on one approach either bolt gates onto pipelines that never needed them, or remove controls from the few that did.

A hybrid estate needs a written rule for which pattern applies where, and a register recording the decision per pipeline. Without it the split stops being deliberate and becomes an accident of who built what and when.

Decide per pipeline, and write the decision down.

Pipeline Architecture

Both approaches occupy the same shape. What differs is which stage owns transformation and whether staging is preserved.

1. Source systems Databases, applications, files, telemetry and partner feeds, each with its own change pattern and owner.
2. Ingestion Extraction and movement using snapshots, watermarks or change data capture.
3. Staging or raw The landed copy. Retained and partitioned under ELT; usually transient under ETL.
4. Transformation Typing, deduplication, conformance and business rules, before or after load.
5. Quality Validation, contract checks and reconciliation. A blocking gate under ETL, a tested layer under ELT.
6. Warehouse or lakehouse The governed serving layer where conformed models live.
7. Consumption Reporting, self-service analytics, applications, APIs and machine learning features.

Quality is where the two diverge most in operational terms. Under ETL it is a blocking gate with somewhere to put failures, so a quarantine table and an exception process are part of the design. Under ELT everything lands, so quality becomes assertions between model layers. The failure mode shifts from a record that never arrived to a model that refuses to rebuild.

Porting an ETL pipeline to ELT without rebuilding that stage is how teams discover the difference through a disputed report.

Where these stages sit relative to control planes and domain boundaries is an architecture decision. The reference layering is covered in modern enterprise data architecture.

The stages are common to both. Retained staging is what ELT adds and ETL gives up.

Batch and Real-Time

Transformation placement and data freshness are separate decisions. Both ETL and ELT are usually batch, and both can run continuously on change data capture.

Streaming changes where the work can happen. Event-by-event enrichment has to occur in flight, because waiting for a scheduled transformation defeats the purpose. That in-flight step resembles ETL even on a platform otherwise running ELT, with the stream also landing raw for later modelling.

Latency targets, ordering, replay and idempotency belong to streaming design. They are covered in real-time data architecture.

Set freshness from the decision window, then decide where transformation runs.

Warehouse, Lake and Lakehouse

The target platform constrains the choice. A warehouse with elastic compute and strong SQL makes ELT straightforward. A lake without table formats or a catalogue makes retained raw data hard to govern, which weakens ELT's main advantage. A lakehouse sits between, with open table formats providing the transactional guarantees in-place transformation needs.

Selecting between those patterns has its own criteria around workload, governance depth and operating cost. They are set out in data warehouse vs data lake vs data lakehouse.

Confirm the target can govern retained raw data before committing to ELT.

Orchestration, Quality and Observability

Data orchestration

Moving transformation into the platform does not remove the need to sequence work. ELT typically produces more interdependent steps, because each model layer depends on the one beneath it. Scheduling, retries and backfills belong to data orchestration, which owns that dependency graph.

Meridian's nightly run shows the shape. The finance mart cannot build until all 12 region files have landed and the FX rate feed has refreshed. One late region blocks the close, not the whole warehouse.

Data quality and observability

The two approaches fail differently. ETL fails early and loudly: a record is rejected at the gate and never lands. ELT fails late and quietly, because bad data lands successfully and surfaces when a downstream model produces a disputed number.

That difference is the argument for testing between model layers under ELT. Freshness, volume, schema and reconciliation checks replace the gate ETL provided, and belong to pipeline monitoring and observability.

ELT trades an early hard failure for a later silent one, so layer tests are not optional.

Migration Considerations

Moving from legacy ETL to ELT is rarely a rewrite. Treating it as one is the usual reason these programmes stall.

The first task is recovering the business rules. In mature ETL estates, logic accumulates inside proprietary tooling, and current behaviour is often the only specification that exists. That logic has to be expressed in the target platform before anything is switched over.

Coexistence is the normal intermediate state. Old and new pipelines run in parallel against the same inputs, and outputs are reconciled. A discrepancy is then found by comparison rather than by a user disputing a report.

Parallel running should cover at least one full reporting cycle, including a period-end close. Meridian ran both paths for six weeks so that two month-end closes were compared before the legacy job was retired.

Phased modernisation by pipeline works better than a platform-wide cutover. Sequencing, validation gates and rollback criteria are covered in legacy data pipeline modernization.

Recover the rules, run in parallel through a full cycle, retire the legacy flow last.

Choosing the Right Approach

Score each pipeline against the criteria below rather than adopting a house standard. Where answers conflict, the constraint that cannot be relaxed decides.

A per-pipeline decision framework for choosing between ETL and ELT

Two answers tend to override the others. If a field must not land raw, the gate stays. If the source cannot replay history, retaining the raw record is the only way to rebuild a definition later.

Meridian's payments feed answers yes to the first question, so it keeps its gate. Every other source answers no, and lands raw.

Record the decision alongside the pipeline, with the reasoning and the date. Constraints expire. A platform gains elastic compute, a residency rule changes, a source starts exposing change data capture. A pipeline whose rationale was never written down keeps its original shape long after the reason has gone.

Score per pipeline, and let the constraint that cannot be relaxed decide.

Frequently Asked Questions