ETL vs ELT: Choosing by Constraint, Not by Fashion
Two orderings, six criteria. Where compute is available, how large raw is, whether you need history, what security forbids, how complex the logic is, and what the destination can actually do.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
For this specific source, feeding this specific destination, which ordering does the constraint actually permit — and which one is a preference dressed as a principle?
The engineer who will be paged when this pipeline breaks and will have to fix history afterwards. The ordering decides what tools they have that night: a re-run against retained data, or a request to another team for a historical re-extract that may not be possible.
The decision unit is one source feeding one destination, not the platform. A company routinely runs both orderings simultaneously and correctly: a clinical feed transformed before load because a field may not cross a boundary, and a product event stream loaded raw because nothing forbids it and reprocessing matters more. Deciding once for the whole platform is the mistake this lesson exists to prevent.
Pick one and standardise. It makes the platform consistent, it makes onboarding simpler, it makes the architecture diagram legible, and every source gets the same treatment so nobody has to argue about it twice.
The standard is ELT, and a source arrives whose payload contains data that is not permitted in the analytical environment. The pipeline is built to the standard, the field lands, and the remediation is a deletion project rather than a config change (PII in Pipelines).
- The standard is ELT, and a source arrives whose payload contains data that is not permitted in the analytical environment. The pipeline is built to the standard, the field lands, and the remediation is a deletion project rather than a config change (PII in Pipelines).
- The standard is ETL, and six months of a metric turn out to be wrong. The transform discarded the inputs, the source has mutated, and the honest answer to "can we fix history" is no (Keeping Raw History: The Recovery Position and the Liability).
- The standard is ELT, and a mainframe feed arrives as EBCDIC fixed-width records. The warehouse cannot parse it, so someone writes a transform outside the warehouse and calls it "a temporary exception", and the exception becomes a second undocumented platform (CSV, JSON and Their Limits).
- The standard is ETL because "the warehouse is expensive", but the transform cluster is provisioned for peak and idle most of the time, so the standard is optimising the cheaper line (Compute Waste).
- The standard was chosen when the destination was a fixed-capacity appliance. The destination was replaced two years ago and the standard was not revisited, so the platform is still paying for a constraint that no longer exists (Separating Storage from Compute).
- A team needs a transformation that calls an external service. Under a pure-ELT standard there is nowhere to put it, so it appears in a scheduled script nobody owns, outside every test and lineage the platform has (Data Lineage).
What is actually happening
- Both orderings do the same three things. The only variable is where the transform's compute runs, and everything else — cost shape, recoverability, governance exposure, freshness behaviour — follows from that one placement (Where the Transformation Actually Runs).
- The historical argument is settled and unhelpful. ETL was the default because destination compute was fixed and scarce; ELT became viable because destinations began separating storage from compute and scaling each independently. Neither fact tells you what to do with the source in front of you.
- The durable question is a pair. *Where is compute available and appropriate?* and *who holds the raw copy?* Those two are independent: you can transform outside the destination and still keep raw, which is the shape most mature platforms converge on and which neither acronym names (The Raw Landing Zone).
- Security constraints are the only criterion that can rule an ordering out entirely rather than merely making it expensive. Everything else trades cost against recoverability; a boundary that forbids a field from landing forbids ELT for that field, full stop (Data Classification).
- Transformation complexity decides which language you are allowed to think in. SQL is excellent at set-oriented work and poor at recursive parsing, external calls and stateful sequence logic; the moment the transformation needs one of those, the destination stops being a candidate for it (SQL Transformations).
- Destination capability is a moving target and the most common source of stale decisions. A warehouse that could not read semi-structured data, or could not scale transformation independently of serving, is a warehouse that made ETL correct — and that constraint has a habit of quietly disappearing between architecture reviews.
The decision, made on constraints rather than on preference
Work down this list in order and stop at the first entry that applies to the source in front of you. The order matters: the security constraint can rule an ordering out, and the rest merely make one expensive, so a criteria list that starts with cost will occasionally produce an answer that is illegal.
Notice that several branches lead to the same place — land raw, transform outside the destination — which is a shape neither acronym describes cleanly. That is not a flaw in the framework; it is evidence that the acronyms are a poor coordinate system for a decision with two independent axes.
The last option is the one worth defending. "Neither, yet" is a real answer for a small platform with one source and a read replica, and reaching for either ordering before there is a second source or a historical question is a way to spend a year building infrastructure for a problem you do not have (Choosing a Data Access Layer in Backend terms).
Take one source and one destination. What is the binding constraint?
when Classification, contract or jurisdiction says specific fields must not enter the analytical environment at all.
cost Those fields are permanently unavailable for future questions, and the filter becomes compliance-critical code needing review, tests and an audit trail. Prevention is the strongest control available and it is not free.
when A proprietary binary, fixed-width mainframe records, deeply nested documents, or a transform that must call an external service or run a model.
cost You operate a separate runtime and its dependencies. Land the original bytes next to the parsed output, so a parser bug is still a re-run.
when High-volume, low-value records where only an aggregate is ever read: per-frame telemetry, verbose debug logs, sensor streams sampled far above the analytical grain.
cost Aggregation is irreversible. The grain you choose is the finest question anyone can ever ask of that history (Grain: What Does One Row Represent?).
when Business logic is complex or contested, definitions change, and the expected failure is "the transformation was wrong for six months".
cost Destination storage for data you rarely query, plus every governance obligation that copy carries in a system built for broad access (Data Access Control).
when Data is tabular, the work is set-oriented joins and aggregates, and the warehouse scales transformation independently of serving.
cost Transformation competes with querying unless deliberately isolated, and models proliferate because they are cheap to add (Model Layering).
when One source carries a few classified columns and many harmless ones.
cost A pipeline with two shapes in it, which is harder to explain and needs the filter tested explicitly — and which is nonetheless the honest answer for most operational databases.
when One source, a read replica answers the questions, no historical requirement, production unaffected.
cost None today. Start retaining raw events now anyway, because that is the part you cannot recreate when the answer changes.
Six criteria, and what each one actually decides
Every one of these appears in architecture discussions as a slogan. Written out with its decision and its trap, each becomes checkable: you can go and find out whether the destination can parse the format, whether raw is genuinely large relative to the curated output, whether anyone has ever asked a historical question.
The column that ends most arguments is the last one. Almost every strongly-held position about ETL and ELT is a correct inference from a premise that stopped being true, and stating the trap out loud is usually enough to get the premise re-examined.
Two criteria are worth weighting above the rest. Security can eliminate an option, and nothing else can. History need is the one whose consequences are invisible for the longest and irreversible when they arrive.
| Criterion | Points to ETL when | Points to ELT when | The trap |
|---|---|---|---|
| Where compute is available | You already run a processing cluster, or the destination has fixed capacity you cannot grow. | The destination scales compute on demand and separately from storage. | Reasoning from a destination you replaced. The premise that made ETL right is a property of a specific system, and systems get migrated without the architecture being revisited. |
| Raw data volume | Raw is far larger than the useful output and the destination charges for what it holds and scans. | Raw is comparable to the output, or storage there is cheap relative to the value of keeping it. | Comparing raw volume to output volume and forgetting that the comparison changes as the business grows — the ratio is not a constant. |
| History and reprocessing need | The transformation is settled, the definitions are regulatory and stable, and nobody has ever needed to restate. | Definitions are contested, the logic is complex, and a six-month-old bug is a realistic expectation rather than a fear. | Assuming your transformation is settled. Almost every metric definition changes at least once, and the platform that cannot restate discovers this in a board meeting. |
| Security and classification | Specific fields must not exist in the analytical environment under any access model. | Nothing in the payload is classified beyond what the destination's controls already handle. | Treating "we will mask it after load" as equivalent to "it never landed". Masking is an access control; not loading is a boundary. |
| Transformation complexity | The work needs recursion, external calls, a model, or stateful sequence logic that SQL expresses badly. | The work is joins, filters, aggregates and window functions — set-oriented work SQL was built for. | Writing procedural code in SQL because the platform standard says SQL, producing something correct, unreadable and impossible to test. |
| Destination capability | The destination cannot read the format, cannot isolate transform from serving, or cannot delete rows in the raw layer. | It parses semi-structured data, isolates workloads, and supports row-level deletes for governance. | Checking capability once. This is the fastest-moving criterion of the six and the one most likely to be wrong in a document written two years ago. |
What mature platforms actually run
Ask a platform team which they do and the honest answer is usually "both, per source, and the raw landing is common to both". That shape is worth drawing, because it makes visible the thing the acronyms hide: the raw landing zone is the load-bearing component, and it is orthogonal to where the transform runs.
In the diagram below, two sources take two different paths and both pass through the same immutable raw layer. The clinical feed is filtered before it lands, so the raw layer never holds the forbidden fields — that is genuine ETL, and the filter is the enforcement point. The product events land untouched and are transformed inside the warehouse — that is ELT. Both are recoverable, because both have something upstream of the transform that was never modified.
This is also why the third lesson in this module is not "and the winner is". The interesting design question is not which acronym; it is where you put the immutable copy and what you are willing to let cross each boundary. Once those two are answered, the ordering follows and stops being an argument.
The architecture document says the platform is ELT. Every new source is built that way. Sources that do not fit become exceptions, built outside the standard by whoever needed them, with their own scheduling, no tests and no lineage.
Each pipeline records where its transform runs, what is retained upstream of it, and which of the six criteria decided it. The platform still has two shapes, but both are inside the same orchestration, testing and lineage, and the reason for each is auditable.
Standards do not remove variance, they relocate it. A source that violates the standard still needs a pipeline, and the pipeline it gets is the one built quickly and outside every control — which is precisely the pipeline that later cannot be re-run, cannot be explained, and cannot be handed over (Data Platform Anti-Patterns).
How to build it
Most important first.
- Decide per source and write the reason down next to the pipeline. "ELT, because nothing here is classified and reprocessing matters" is a decision; "ELT, because that is what we do" is a habit, and habits do not survive a source that violates them (Dataset Documentation).
- Whichever ordering you choose, land the extract immutably before transforming. This is the choice that actually determines recoverability, and it is available in both orderings — which is why arguing about the acronym misses the point.
- Split the decision by field, not just by source, where classification demands it: drop or hash the two forbidden columns before load and load the rest raw. The result is neither pure ETL nor pure ELT and is usually the right answer (Data Masking, Tokenisation & Encryption).
- Put the boundary-enforcing transform in the smallest, most reviewable place you have, with a test that asserts the field is absent from the destination. A control nobody verifies is a comment (Contract Enforcement).
- Revisit the decision when the destination changes, not on a calendar. A warehouse migration is the moment a platform's ETL-because-compute-is-scarce reasoning becomes obsolete, and it is almost never re-examined then (Choosing an Analytical Platform).
- Measure the two cost lines you are trading — provisioned transform compute versus destination storage and scan — before asserting which one is larger. This is one of the few architecture arguments with an available answer (Cost Attribution).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- ETL guarantees the destination never held the raw data. That is a *security* guarantee, it is the strongest thing either ordering offers, and it is unavailable under ELT by construction.
- ELT guarantees that whatever arrived is still available to reprocess. That is a *recovery* guarantee, and it is available under ETL only if you deliberately land raw as a separate step.
- Neither ordering guarantees correctness, completeness or freshness. Those are properties of the extract, the tests and the schedule, all of which are unchanged by the ordering (The Pipeline Succeeded. The Data Is Wrong.).
- Neither ordering guarantees cost. It is entirely possible to build an expensive version of either, and the expensive version of each is the one that reprocesses unchanged history on a schedule.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check that discriminates between the two is a reproducibility test: pick a closed historical period, re-run the pipeline from whatever you retained, and compare the output to what is published. If the numbers match, your recovery story is real. If you cannot run the test at all, you have your answer about which ordering you actually have (Reconciliation).
- It misses everything about periods you have already lost, and it will pass on a pipeline that is reproducibly wrong — determinism and correctness are separate properties (Determinism: Same Input, Same Output?).
- It also cannot detect a boundary violation. Testing that a forbidden column is absent from every destination table is a different check and belongs in the same suite (Data Tests).
- ELT usually makes raw available sooner, because the load has less work in front of it. That is freshness of a layer nobody should be querying, so it is worth less than it sounds (Raw, Staging, Curated: Layers by Purpose).
- ETL usually makes the *curated* result available in one step rather than two, which can be fresher end to end for a simple pipeline — there is no second scheduled build between load and usable data.
- At the point that matters — when a consumer can trust a number — the two are decided by the transform schedule and the transform's duration, not by the ordering. Any claim that one acronym is inherently fresher is a claim about a specific implementation (Cost vs Freshness).
- A new source field is captured for free under ELT and requires an extract change under ETL. Over years this compounds: the ELT platform can answer retroactive questions about fields nobody was interested in at the time, and the ETL platform cannot (Schema Evolution).
- A change in the *destination's* capability should reopen the decision, and almost never does. This is the single most common way a platform ends up with an architecture justified by a system it no longer runs.
- A change in classification — a field reclassified as sensitive after years of loading — is much harder to unwind under ELT, because remediation means finding and deleting it from raw and every derivative rather than changing a filter (Data Retention).
- Recovery capability is not a property of the acronym, it is a property of whether an immutable copy of the input exists upstream of the transform. Both orderings can have one; only ELT gets it by default (Reprocessing vs Retrying).
- Under ETL without a raw landing, recovery depends on the source still holding the affected period unmutated — which is true for append-only event tables and false for the mutable operational tables most metrics are built on.
- Under ELT, recovery depends on the raw layer's retention window exceeding the age of the bug. Bugs are routinely older than retention policies chosen for cost, and nobody discovers the mismatch until the incident (Backfills).
- In both cases the re-run must be bounded, written somewhere consumers are not reading, and validated before publish. The ordering changes what you can re-run; it does not change how to re-run safely (Planning a Backfill).
What can go wrong
- A platform-wide standard applied to a source that violates its precondition, producing either an unrecoverable dataset or a compliance incident.
- An "exception" pipeline built outside the standard, with no tests, no lineage and no owner, which becomes load-bearing.
- A decision made correctly against a destination that has since been replaced, and never revisited.
- The mitigation failing: a raw layer that exists but is incomplete — the connector was configured to mirror current state rather than capture changes — so the recovery story is believed and untrue (Snapshot and Stream: the Bootstrap Problem).
- Both orderings implemented for the same source by two teams, producing two versions of the same metric that differ in a way nobody can explain (Two Dashboards, Two Numbers).
- "ELT replaced ETL." It replaced ETL as the *default* for cloud warehouses fed by tabular sources. It replaced nothing where a boundary, a format or a destination limitation rules it out (ETL: Transform Before the Data Lands, ELT: Load First, Transform Where the Data Lives).
- "ETL is for on-premise, ELT is for cloud." The deciding property is whether destination compute is elastic and separately scalable, which is an architecture question rather than a hosting one.
- "Pick one for consistency." Consistency in *interface* — every pipeline lands raw, every pipeline declares its grain, every pipeline is tested — is valuable. Consistency in ordering is a constraint you impose on yourself for no benefit.
- "ELT means we skip data modelling." It means modelling happens later and in SQL. A warehouse full of raw tables and views is not a model; it is a source system with a different address (Analytical Data Modeling).
- "The decision is reversible, we can change it later." Changing ordering forward is easy; changing it backwards over history is not. Data you did not keep does not become available because you changed your mind.
- This is the decision point where privacy is cheapest to enforce. Before load, exclusion is prevention; after load, it is deletion, discovery and proof — three orders of effort apart (Data Minimization).
- Where a source is mixed — most sensitive fields plus a lot of harmless ones — the right answer is usually to split the field set rather than the source, which neither acronym describes and both permit (Row and Column Security).
- Whichever ordering, record the classification of every field at the boundary, because that record is what makes masking, access policy and deletion automatable downstream instead of manual.
Operating it
- Per pipeline, one recorded attribute: where the transform runs and what is retained upstream of it. This is metadata, it costs nothing, and during an incident it is the first thing anybody needs (Metadata: Technical, Operational and Business).
- A periodic automated attempt to reproduce a closed period from retained data, per pipeline. A recovery capability that is never exercised is a belief (Restore Testing is the infrastructure analogue).
- The two cost lines side by side — provisioned transform compute and destination scan — attributed per pipeline, so the ordering argument has evidence behind it.
- At 10x volume, ETL's transform compute becomes a capacity planning problem and ELT's becomes a query optimisation problem. Both are solvable; they need different skills and different people, and that is often the deciding factor in practice.
- At 100x, raw retention becomes a genuine cost conversation rather than a rounding line, and the honest answer is usually tiered retention — recent raw hot, older raw archived, oldest raw aggregated and dropped.
- At high source count, per-source decisions need a decision *record*, not a decision *meeting*. Ten sources can be reasoned about; a hundred cannot without written criteria (Data Ownership).
- ETL concentrates cost in compute you provision and hold; ELT concentrates it in destination storage and the scans that transformation performs. Which is larger is an empirical question about your data and your schedule, not a property of the pattern (What Actually Drives Data Platform Cost).
- The cost that dominates both is reprocessing unchanged history. A nightly full rebuild is expensive in either ordering and is the first thing to fix regardless of which acronym is on the diagram.
- Retaining raw is a small, permanent line in both. It is the line most often cut and the one that converts an incident into a re-run, which makes cutting it the worst-value saving available (Storage Lifecycle).
- The unpriced cost of a wrong choice is the question you cannot answer later — under ETL because the field was dropped, under ELT because a compliance review forced a deletion of the layer you were relying on.
- Deciding per source is correct and costs consistency: two shapes to operate, two sets of failure modes, and a platform that is harder to explain to a new engineer than one with a single rule.
- Choosing ETL for a boundary buys prevention and costs every future question about the filtered fields. That trade is right when the boundary is real and wasteful when it was assumed.
- Choosing ELT buys reprocessability and costs a permanent, broadly-accessible copy of source-shaped data that someone will eventually have to account for in an audit.
ETL or ELT — decide it from the constraints
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Constraint in play | Points toward |
|---|---|
| The warehouse can do the work and is already next to the data, so moving it out and back is pure cost. | Extract → Load → Transform |
| The source will not have this history later, so landing it untransformed is the only way to be able to fix a modelling mistake at all. | Extract → Load → Transform |
| The people who understand the business write SQL, and ELT puts the transformation in the language they already argue in. | Extract → Load → Transform |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe six criteria — compute location, raw volume, history need, security constraint, transformation complexity, destination capability — apply to any source and destination pair, including ones with no warehouse involved at all, such as a service writing to a search index.
- WAREHOUSE-SPECIFICThe destination-capability criterion resolves differently per warehouse: engines differ in whether they can parse semi-structured payloads natively, whether transformation compute can be isolated from serving compute, and whether row-level deletes are available in the raw layer.
- ORG-SPECIFICWhether per-source decisions are workable depends on team size and documentation discipline: with three pipelines the criteria can live in people's heads, while at a hundred the absence of a written decision record produces exactly the inconsistency that a platform-wide standard was meant to prevent.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why "we can just re-extract" is a weaker claim than it sounds when the source is a replicated system whose history is bounded by retention rather than by storage.
- — DevOps / Production Engineering owns the decision-record practice this lesson depends on: an architecture decision that is not written down next to the thing it decided will be re-litigated by people who do not know it was ever decided.