Refinement stages

Why copy the same data three times instead of cleaning it once on the way in?

Here is a design that looks wasteful. Data arrives from a payments system. Rather than cleaning it and storing the clean result, a warehouse team lands it untouched in one place, writes a cleaned copy into a second place, and then writes a third copy shaped for the finance dashboard. Three copies of the same facts, three sets of storage bills, three things to keep in step.

The obvious objection is the right place to start: why not clean once, on the way in, and keep only the good version? Storage is not free, and every duplicate is a chance for two numbers to disagree. So what does the extra copying buy that is worth all that?

Try the single-copy design honestly and see what it costs you. You clean on ingest, and a month later you discover the cleaning rule was wrong — a currency field was assumed to be in cents and was actually in whole units for one merchant. What do you re-run against? The bad rule consumed the only copy of the truth. You can ask the payments system to send it again, but it may only retain thirty days, or it may have mutated the underlying rows since. The unrefined copy is not a duplicate at all; it is the only thing that makes a mistake recoverable.

That is the first argument, and it is about time rather than space. Notice its shape: the raw layer is not there to be queried. It is there so that every later layer is derivable rather than precious.

Now push on the second copy. Why keep a cleaned-but-general layer between the raw landing and the dashboard table? Suppose you skip it. Finance builds its table directly off raw; so does the fraud team; so does the marketing analyst. Each writes its own deduplication, its own timestamp parsing, its own decision about what to do with a null merchant ID. Three teams, three interpretations, and the day someone asks why fraud counts 14,802 transactions and finance counts 14,796, nobody can answer, because the divergence was created independently in three places.

So what is the middle layer really doing? It is where the interpretation everyone shares gets made exactly once — what a row means, which rows are duplicates, what the types are. And what is the third layer doing that the middle one cannot? It is where interpretations that are not shared get made: finance's fiscal calendar, its revenue recognition rule, its particular grain of one row per day per region. Those are not general truths. Baking them into the middle layer would force the fraud team to live with finance's definition of a month.

Does that suggest a principle? Each stage is defined by whose agreement it encodes — nobody's, everybody's, one team's — and stages exist so that a change at one level does not force a change at the others.

The industry's common name for this three-stage version is the medallion architecture, popularised by Databricks, with the layers labelled bronze, silver and gold. The names are branding; the idea long predates them, and plenty of shops call the same thing landing, staging and marts. Three is a convention, not a law — some pipelines honestly need two, and some need four.

Two refinements are worth making. The first is that "copy" overstates the cost. The layers are usually not equal in size: raw is the largest, the shared layer often smaller after deduplication, and the presentation tables smaller again because they are aggregated. And because every layer below raw is reproducible, its storage is a cache, not an asset — you can drop and recompute it.

The second correction is about what really goes wrong. The usual failure is not too many layers; it is layers that do not keep their jobs separate. A raw layer that quietly renames columns is no longer a recoverable record. A shared layer that encodes finance's fiscal calendar has become a mart wearing the wrong badge, and the next team will build a fourth layer beside it rather than fight. When people complain that the medallion architecture is bureaucratic, what they have usually met is a pipeline where the boundaries were drawn by habit rather than by whose agreement each transformation needs.

It is also worth saying plainly that this is a convention, not a proven optimum. There is no controlled study showing three layers beat two. The argument for it is the one we reasoned out — recoverability at the bottom, shared meaning in the middle, local meaning at the top — and if your situation does not have all three of those needs, the extra stage is genuine waste.

• How incremental processing works between layers, so a refresh reprocesses one partition instead of the whole history.
• Where the streaming equivalent sits, when the raw layer is a retained log rather than a table.
• Whether a lakehouse table format's time travel makes a separate raw layer redundant, or only cheaper.

Every term the collection defines is gathered in the glossary.