Where the transform sits
A Socratic walk-through of where the transform sits — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #Why did loading raw data first and cleaning it later become the better order?
For roughly three decades the received wisdom in data warehousing was: extract, transform, load. Get the data out of the source, reshape it on a machine in the middle, and put the finished result into the warehouse. Then, over about a decade, the profession largely reversed the last two letters. Extract, load, transform. Dump it in raw; clean it inside the warehouse afterwards.
That is a strange thing to happen to a settled practice. The data did not change character. The business questions did not change. So either the old order was wrong all along and nobody noticed, or something underneath it moved. Which is it?
Reasoning it through
REASONING #Start with why the transform sat in the middle in the first place. What was the warehouse in 1998? A machine — one appliance, bought with a capital budget, sized for a peak, with storage and processing welded together in the same box. Adding capacity meant buying another box.
Given that, where would you put a heavy transformation? Not on the expensive appliance. Every cycle spent there is a cycle not available for the queries the appliance exists to serve, and you cannot add cycles without a purchase order. So you put a cheap commodity server in front of it and did the work there. And because storage was equally scarce, you loaded only the finished result — keeping the raw data in the warehouse would have been paying premium prices to store something nobody queries.
Notice that both decisions follow from one fact: warehouse capacity was scarce, indivisible and expensive. The ETL order was not a theory about data. It was an accommodation to a cost structure.
Now change that fact and see what happens. Cloud warehouses separated storage from compute — Amazon Redshift arrived in 2012, BigQuery's public launch the same year, Snowflake in 2014, with separation of storage and compute being Snowflake's central architectural claim. Storage becomes object storage at cents per gigabyte-month. Compute becomes something you start for four minutes and stop.
Ask the two questions again against that structure. Is it still expensive to keep the raw data? No — it is nearly free, and we saw earlier that keeping it is what makes mistakes recoverable. Is it still wasteful to transform inside the warehouse? Now the opposite: the warehouse's query engine is a massively parallel, heavily optimised piece of machinery, and the intermediate server is a single node. Moving the work into the warehouse makes it faster, not slower.
So the reversal was not a discovery about the right way to process data. It was the same optimisation problem re-solved after its constraints moved.
But there is a second consequence, and arguably the more important one. In the old order, a transformation was a program on a middle server, usually written in a specialised tool by a specialist team. In the new order, a transformation is a SQL statement running in the warehouse. That changed who could write one — an analyst who knows SQL could now define a model, and the tooling that grew up around this, dbt most prominently, brought version control, testing and dependency graphs to those definitions. The order of the letters is the visible change; the widening of who does the work is the one that reshaped teams.
The analogy
THE ANALOGY #Think of a restaurant kitchen and its supply chain. When the kitchen is tiny and the oven is the only heat in the building, you buy vegetables pre-chopped from a preparation house: paying someone else to do the knife work protects your one scarce resource. Move to a kitchen with a whole bank of ovens and a walk-in cold store, and the calculation inverts — you buy whole produce, store it, and prep in-house, because now you have the space and the hands, and because a whole carrot can become three different dishes while a pre-chopped julienne can only become the one it was cut for.
vegetables spoil, so the restaurant's cold store has a real limit that a cloud object store does not — raw data can be kept indefinitely, which is why the analogy understates how completely the storage constraint disappeared.
Clarifying the model
THE MODEL #Some corrections, because the reversal gets over-claimed.
First, ETL did not die and is not obsolete. Transformation still belongs before the load in at least two situations. One is when data must not land in the warehouse in its raw form at all — personal data that must be masked or tokenised before it crosses a boundary, where "load it then clean it" means the sensitive values existed in the warehouse, however briefly. The other is when the volume arriving is enormous and mostly irrelevant, where filtering early is simply cheaper. Streaming pipelines also transform in flight by nature.
Second, the choice is not really binary. Most real platforms do a little transformation in flight — format conversion, light filtering, masking — and the substantive modelling afterwards. The letters describe where the centre of gravity sits.
Third, the honest cost. Loading everything raw and transforming in the warehouse converts a storage bill into a compute bill, and compute is the expensive half. An ELT platform with careless incremental logic, re-scanning full histories nightly, can cost far more than the ETL setup it replaced. The pattern shifted the constraint; it did not remove it.
Finally, a caution about the language. "ELT is the modern way" is a claim about a cost structure, and cost structures move again. The transferable insight is the reasoning, not the conclusion: work migrates to wherever capacity is cheap and elastic, and the shape of a pipeline is largely a fossil of the prices in force when it was designed.
A picture of it
THE PICTURE #How to readRead left to right as real elapsed time, and read each row's second and third entries as cause and consequence rather than as a list. The turning point is not any single product launch but the separation of storage from compute, because that is what removed both reasons the transform had been placed before the load. Notice the last row: the constraint did not vanish, it moved from storage to compute — which is exactly why the final position should be read as current, not as settled.
What became clearer
WHAT CLEARED #The order of the letters was never a claim about the right way to treat data. It was an answer to the question of where capacity was cheap. When the warehouse was a fixed appliance, the answer was "somewhere else, and store only the result". When the warehouse became elastic storage plus rentable compute, the answer became "inside it, and store everything". The reasoning survives the technology: look at what is scarce, and expect the pipeline's shape to follow.
Where to go next
ONWARD #- How incremental models avoid the compute bill that a naive full rebuild incurs.
- Why reverse ETL emerged — pushing warehouse-modelled data back into operational tools — and what constraint that answers.
- Where lakehouse table formats sit in this story, and whether they collapse the distinction further.
Key terms
TERMS #| Term | What it means |
|---|---|
| ETL | extract, transform, load: reshaping data on intermediate infrastructure before it enters the warehouse. |
| ELT | extract, load, transform: landing raw data in the warehouse and modelling it there with the warehouse's own engine. |
| Separation of storage and compute | an architecture where data sits in cheap durable storage and processing capacity is provisioned independently. |
| Transformation model | a versioned, tested definition of a derived table, typically expressed as SQL and managed by tooling such as dbt. |
| Incremental model | a transformation that processes only new or changed partitions rather than rebuilding the full history. |
Every term the collection defines is gathered in the glossary.