Model Layering
Staging renames and types one source. Intermediate joins and reshapes. Marts face the business. The rule that makes it work is that consumers depend only on marts.
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.
Why split a transformation into staging, intermediate and mart layers when one query would produce the same table?
Two consumers with opposite needs. The analyst, who wants a stable table whose columns do not change when a source system does. The engineer, who wants to change a source system without negotiating with every dashboard in the company.
Each layer has a declared grain and the grain changes between them. Staging is one row per source record, renamed and typed. Intermediate is whatever the reshaping needs — often one row per order line. A mart is at the business grain: one order, one customer, one country-day (Grain: What Does One Row Represent?).
Write one model per output table, reading the source directly. fct_orders selects from the raw orders table, casts, joins customers, filters and aggregates. It is fewer files, fewer names and one place to look.
The source renames amount_cents to amount_minor. Six models read it directly, so six models break, and each one breaks in a slightly different place because each did its own casting (Schema Evolution).
- The source renames
amount_centstoamount_minor. Six models read it directly, so six models break, and each one breaks in a slightly different place because each did its own casting (Schema Evolution). - The same cleaning logic — trim the email, coerce the timezone, coalesce the status — is copied into every model that reads the source. Six copies drift, and two of them are subtly wrong, and nobody can tell which behaviour is intentional.
- A join changes the grain in the middle of a two-hundred-line model. There is nowhere to test the intermediate result because it exists only inside a CTE, so the fan-out is discovered downstream in an aggregate (Duplicate Rows).
- An analyst builds a dashboard directly on a staging table because it had the column they needed. Now a cleaning change is a breaking change for a dashboard nobody knew existed (Impact Analysis).
- A cleaning bug is found. Fixing it means re-running every model that inlined the cleaning, and there is no way to test the fix in isolation because cleaning is not a thing that exists on its own (Reprocessing vs Retrying).
- Two teams model the same source independently, each with its own casting and status handling, and produce two fact tables that disagree by a few percent in a way neither can explain (Two Dashboards, Two Numbers).
What is actually happening
- Staging is one model per source table, and it does exactly four things: rename to house conventions, cast to intended types, apply source-specific coercions, and deduplicate to one row per source record. It contains no business logic — no filters on status, no joins, no derived measures.
- That restriction is what makes staging a boundary. Because it has no business logic, a change to it is always a mechanical change, and because it is one model per source, the blast radius of a source change is exactly one model (Data Contracts).
- Intermediate models are where joins and reshaping happen, and therefore where the grain is at risk. Giving them names and materialisations means each reshaping step can be tested for the grain it claims, which is the only place a fan-out can be caught early (Grain: What Does One Row Represent?).
- Marts are the business-facing contract:
fct_orders,dim_customers,revenue_daily. They carry business definitions, they have owners, and they are the only layer consumers are allowed to depend on. - The rule that makes the whole structure pay for itself is the dependency rule, not the naming: consumers depend only on marts. A source shape change propagates to staging and stops, because everything downstream reads renamed, typed columns that staging is responsible for continuing to produce (Backward Compatibility).
- Layers are justified by two things and nothing else: they are a restart point, or they are a contract boundary. A layer that is neither is a copy with a name and a build cost (Raw, Staging, Curated: Layers by Purpose).
Three layers, and what each one promises
The layers are usually introduced as a naming convention, which undersells them. Each one is a different *kind* of guarantee, and the reason to have three is that these three guarantees have different owners and change for different reasons.
Staging is owned by whoever understands the source. Its promise is shape: these column names, these types, one row per record, regardless of what the source does. Marts are owned by whoever understands the business. Their promise is meaning: this is what revenue is, at this grain, tested. Intermediate models are owned by whoever is doing the modelling, and their promise is the narrowest and most important — that a join did not change the grain.
Read the failsBy column below as the argument for the boundary. Each layer fails in a characteristic way, and each of those failures is much easier to find when it is confined to a layer that does one thing.
- 1Source
Produces records in whatever shape the operational system finds convenient.
guarantees Nothing to you. The shape is theirs to change, and they will, usually without telling you (Data Contracts).
fails by Changing a column's meaning without changing its name or type, which no boundary below can detect.
- 2Raw landing
Stores what arrived, unmodified, including duplicates and fields nobody currently uses.
guarantees Reproducibility. Everything above can be rebuilt from here as long as it is retained (The Raw Landing Zone).
fails by Being "cleaned on the way in", which destroys the only copy that could prove what actually arrived.
- 3Staging
One model per source table: rename to house conventions, cast, coerce, deduplicate. No business logic.
guarantees A stable column shape and one row per source record, so a source rename changes exactly one file (Schema Evolution).
fails by Absorbing a removed column by emitting nulls instead of failing — a silent zero downstream rather than a loud break.
- 4Intermediate
Joins, reshaping, pivots. The place the grain is most at risk and therefore the place to test it.
guarantees A declared grain, asserted. That is the whole reason this layer has names and materialisations rather than being CTEs (Grain: What Does One Row Represent?).
fails by Having no declared grain, so a fan-out passes through and is discovered as inflated revenue three models later.
- 5Marts
Business-facing facts, dimensions and aggregates with owners and documented definitions.
guarantees A business grain, tested assertions, and a column shape that is stable under upstream change.
fails by Encoding a definition nobody wrote down, so it is defensible, undocumented and different from the one in the BI tool (The Metrics Layer).
- 6Consumers
Dashboards, extracts, reverse-ETL syncs, model training sets.
guarantees Nothing. A BI tool applies its own filters and joins downstream of every test you wrote (Who Actually Consumes This Data).
fails by Reading staging or intermediate models directly, which converts every mechanical upstream change into a breaking one.
The dependency rule in one line: everything to the left of Marts may change without consulting anyone; Marts may not. That is the trade the structure exists to make.
A source rename, and where it stops
Here is the concrete payoff, and it is worth walking through slowly because it is the only justification the layer needs. The application team renames amount_cents to amount_minor and changes it from an integer to a decimal. This is a correct, reasonable migration on their side and they have no reason to tell you.
In an unlayered project, every model reading that table breaks — or worse, some of them break and some absorb it. In a layered project, stg_orders breaks. One file. It is edited to map the new source column to the same house name it always produced, the cast is adjusted, and nothing above it changes at all.
Note the asymmetry in the impact table. The change is absorbed for every consumer *reading through the layer*, and it is a breaking change for the one team who went around it. That is not a flaw in the argument; it is the argument.
- order_id BIGINT
- customer_id BIGINT
- amount_cents BIGINT
- currency VARCHAR
- status VARCHAR
- created_at TIMESTAMP
- order_id BIGINT
- customer_id BIGINT
- amount_minor DECIMAL(18,4)
- currency VARCHAR
- status VARCHAR
- created_at TIMESTAMP
change amount_cents renamed to amount_minor and widened from BIGINT to DECIMAL(18,4) in the source system, with no notice.
| Consumer | Effect | How it shows up |
|---|---|---|
| `stg_orders` (staging model) | Fails at build: the referenced column no longer exists. One file to edit, mapping the new source name to the unchanged house name amount_minor_units and adjusting the cast. | Loudly — it raises |
| `int_orders_enriched`, `fct_orders`, `customer_metrics` | No change. They read the house column name that staging is responsible for continuing to produce, and they never saw the source name at all. | Loudly — it raises |
| Every dashboard reading `fct_orders` | No change, and nobody needs to be told. This is what the layer bought. | Loudly — it raises |
| A dashboard built directly on the source table by a team in a hurry | Breaks, or silently reports zero, depending on whether the BI tool errors on a missing column or coalesces it. Nobody knew this consumer existed. | Silently — no error, wrong result |
| An incremental model that stored `amount_cents` values already | Builds fine and now mixes integer minor units with decimal values across the incremental boundary, so history and new rows are on different scales. No error anywhere. | Silently — no error, wrong result |
When a layer is not worth it
Layering is one of the few pieces of advice in this domain that gets over-applied. A four-model project with three layers has more structure than problem, and the honest starting position for a small platform is two layers: a staging model per source and a mart per business concept.
The criterion is not model count and it is not team size directly. It is whether the layer is a restart point — somewhere you would genuinely want to begin a rebuild from — or a contract boundary — somewhere a change is allowed to stop. A layer that is neither costs a materialisation, a name and build time, and buys a diagram that looks more professional.
The most common unjustified layer is a thin pass-through between staging and marts that renames nothing and joins nothing, added because the reference architecture had three boxes. The most commonly *missing* layer is the intermediate one, skipped because the join felt simple, which is where the fan-outs get in (The Transformation DAG).
Is it a restart point, a contract boundary, or neither?
when Something upstream of it changes for reasons unrelated to the business, and you want that change to stop here. The staging layer is the archetype.
cost A model per source, maintained by someone who understands that source, plus the discipline to keep business logic out of it.
when You would want to rebuild from here after a bug, without redoing everything before it. Expensive deduplication and wide joins qualify.
cost A materialisation and storage for a copy. Pays for itself the first time a downstream bug does not require re-reading raw history (Reprocessing vs Retrying).
when A join or an aggregate changes what one row means. That transition needs a name and a uniqueness test, or the fan-out is caught downstream in an aggregate.
cost One more model and one more test. The cheapest layer on this list and the one most often skipped (Grain: What Does One Row Represent?).
when It renames nothing, joins nothing, is read by exactly one model, and you would never rebuild from it.
cost The consuming model gets longer. That is a readability cost, not a correctness one, and it is usually the right trade.
when You cannot say which of the three criteria above it satisfies. This is more common than it sounds and is the main reason layered projects feel bureaucratic.
cost Deleting it means rebuilding its consumers once. Keeping it means paying for it on every build forever (Compute Waste).
How to build it
Most important first.
- One staging model per source table, named after the source, containing no business logic whatsoever. The discipline is the value; the moment a status filter appears in staging, the boundary is gone.
- Deduplicate in staging, using the source's own sequence. Every model downstream is then entitled to assume one row per business key, and that assumption is testable in exactly one place (Deduplication).
- Give intermediate models a declared grain and a uniqueness test. If you cannot state the grain of an intermediate model, that is a sign the reshaping should be split into two.
- Publish only marts. If a consumer needs something that is only in staging, that is a request for a mart column, not a reason to grant access to staging (Data Marts).
- Keep marts stable under upstream change: renaming a column in a mart is a breaking change for consumers and should follow the same expand-then-contract discipline as a public API (Expand and Contract Migrations).
- Do not add a layer because a diagram had one. Three layers is a good default and two is right for many projects; the criterion is whether the layer is a restart point or a contract boundary (Medallion: One Naming Convention Among Several).
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.
- Staging guarantees a stable column shape and stable types regardless of what the source does, for as long as someone maintains it. That is a promise made by a person, enforced by tests, not a property of the tool.
- Staging guarantees one row per source record after deduplication — the single most useful guarantee in the whole structure, because every model above it depends on it and none of them re-checks it.
- Marts guarantee a business grain and a set of tested assertions. They guarantee nothing about the *definition* being the one the business currently means (The Metrics Layer).
- No layer guarantees atomicity across layers. A build that fails between staging and marts leaves them at different vintages, and a consumer joining across that boundary sees a state that never existed as a whole (Atomic Publish).
- The layering guarantees nothing at all if consumers read staging directly. The rule is the mechanism; without enforcement it is a naming convention (Data Access Control).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Test uniqueness and not-null on the grain key of every layer, not just the mart. The value is attribution: a failure in staging localises the problem to one source, and a failure in a mart localises it to nothing.
- Test the staging boundary itself — accepted values, expected column set, expected types — so an upstream change fails there rather than propagating as nulls (Contract Enforcement).
- These miss the thing layering was not designed to solve. A perfectly layered project with a wrong business definition in the mart is wrong in a well-organised way (The Metrics Layer).
- Each layer adds its build time to everything above it. Three thin layers where one would do costs every consumer that difference on every run (Topological Execution).
- Materialising staging as views keeps the layer free in build time and pays for it on every read of everything above. Materialising as tables does the opposite. Neither is right in general and both are common defaults (dbt Concepts).
- Freshness should be published per mart, because that is the layer consumers see. Publishing a platform-wide number hides the mart whose upstream test has been failing since Friday (Freshness Monitoring).
- A source column rename is absorbed by editing one staging model. Everything above continues to read the house name, and nothing downstream changes. This is the entire argument for the layer, stated as a single sentence.
- A source column *removal* cannot be absorbed. Staging can keep the name and produce nulls, which is worse than failing — the honest move is to fail at staging and have the conversation (Breaking Schema Changes).
- A mart column change is a consumer-facing breaking change and needs the same treatment as an API change: add the new column, migrate consumers, then remove the old one (Expand and Contract Migrations).
- A change in a source column's *meaning* passes through staging untouched, because staging checks names and types and has no view on meaning. This is the failure layering does not address and should not be assumed to (Semantic Changes).
- Layering is what makes partial recovery possible. A cleaning bug is a staging fix plus a rebuild of that model's descendant set; the business logic in the marts is untouched and does not need re-reviewing (Reprocessing vs Retrying).
- Without layers, a cleaning bug and a business-logic bug are fixed in the same file, so every fix requires re-validating everything the file does.
- Rebuilding a layer means rebuilding everything above it, in topological order, to the leaves. Stopping halfway leaves two layers at different vintages, which is a worse state than the original bug (Validating a Backfill Before You Publish).
What can go wrong
- Business logic leaking into staging — a status filter, a currency conversion — which silently makes staging a business model and removes the boundary.
- A consumer reading staging directly, so a mechanical cleaning change becomes a breaking change for a dashboard nobody knew about.
- Intermediate models with no declared grain, so a fan-out passes through and is discovered in an aggregate three layers later.
- Layers added for symmetry rather than for a restart point, costing build time and materialisation with no corresponding guarantee.
- The mitigation failing: staging that absorbs a removed source column by producing nulls, which propagates a silent zero instead of a loud failure (Nullability & Defaults).
- "Bronze, silver, gold is the required structure." It is one naming convention for the same idea, popular in lakehouse tooling. The layers are justified by restart points and contract boundaries, not by having three of them or by what they are called (Medallion: One Naming Convention Among Several).
- "Staging is where we clean the data." Staging is where representation is normalised. Cleaning that involves a judgement — which statuses count, what an outlier is — is business logic and belongs above it.
- "More layers means better separation." Each layer must be a restart point or a contract boundary. A layer that is neither adds build time and a name to remember (Raw, Staging, Curated: Layers by Purpose).
- "Analysts can read intermediate models, they are read-only." Read access creates a dependency whether or not anyone intended it, and the next change to that model is now a breaking change for a consumer nobody can enumerate (Data Ownership).
- Staging holds source data at full fidelity, which usually means it holds every PII column the source has. Marts can be built to exclude or mask them, and the layer boundary is a natural place to enforce that (PII in Pipelines).
- Granting analysts access to marts only is both a modelling rule and an access-control rule, and implementing it as the latter is the only version that holds (Data Access Control).
- A deletion request must reach every layer. Layering makes the set enumerable — it is the descendant set of the staging model — which is a meaningful improvement over searching a warehouse (Deletion Requests).
Operating it
- Which layer each consumer query reads, taken from warehouse query logs. Any read of staging by a BI tool is a boundary violation and worth knowing about on the day it starts (The Data Catalog).
- Row counts per layer per run, so a loss can be attributed to a layer rather than searched for across a whole model.
- The count of models in each layer over time. Staging growing faster than marts usually means sources are being added without anyone deciding what business question they serve (Data Discovery).
- At a handful of models, layering is overhead and two layers is plenty. The structure starts paying at the point where more than one person changes models and more than one source feeds a mart.
- At a hundred models, the naming convention *is* the navigation, and a project without one requires reading SQL to find anything (Data Discovery).
- Consumer count is what makes the dependency rule load-bearing. With four consumers you can email them; with eighty dashboards the mart boundary is the only thing making change possible at all (Impact Analysis).
- Each materialised layer costs a write and storage for a copy. Three layers of thin renames materialised as tables is a real cost with a small benefit; as views it is nearly free and moves the cost to read time (Scan Cost).
- Layering reduces cost during incidents, by making selective rebuild possible at a finer granularity than "everything" (Compute Waste).
- The largest hidden cost is a wide staging layer nobody reads — models built nightly for sources that no mart consumes (Cost Attribution).
- Layering costs more models, more names, more build time and a convention everyone must learn. It buys a blast radius that stops at staging and a fan-out that is caught at the intermediate rather than in a dashboard.
- A strict no-business-logic rule in staging occasionally forces an awkward extra model, and relaxing it once is how the boundary erodes. The rule is worth more than the exception.
- Publishing only marts means analysts sometimes wait for a column that already exists one layer down. That friction is the cost of the boundary, and removing it removes the boundary.
Layer boundary lab
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.
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.
- GENERALSeparating source-shaped cleaning from business modelling from consumer-facing contracts is a structural idea that predates every current tool and applies equally in a warehouse, a lakehouse or a hand-written pipeline. Layer names differ constantly; the boundaries do not.
- ORG-SPECIFICThe dependency rule solves a coordination problem that only exists once more than one team consumes the platform. With one team and four dashboards, direct reads of intermediate models cost nothing; at fifty consumers the rule is the only thing making upstream change possible.
- SCALE-SPECIFICBelow roughly a dozen models, three layers is more structure than the problem has. Two layers — a staging model per source and a mart per business concept — carries most of the benefit and is the right starting point for a small project.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the deployment discipline that makes a mart change safe — environment separation, CI on a pull request, and the expand-then-contract sequencing borrowed directly from database migrations.