Most warehouses fail in the same way. Someone wires a transformation directly to a BI tool, ships a dashboard, and a quarter later there are eleven definitions of “active customer,” none of which match the number the CFO quoted on the earnings call. The fix is not better governance, more documentation, or a metrics catalog grafted onto an existing mess. The fix is structural: three layers, each doing one job, with a hard contract between them.
Layer one: source models
The source layer’s only job is to make raw operational data queryable. It is the boundary between systems of record (Salesforce, Stripe, the application database, the event stream) and everything else. Models here are thin: rename columns to a consistent convention, cast types, deduplicate, snapshot slowly-changing dimensions, mark soft deletes. No business logic. No joins across systems. No “is this customer active” — that question doesn’t belong here yet.
The discipline is restraint. The temptation to start cleaning, enriching, and conforming at this layer is the single most common failure mode in analytics engineering, because every shortcut taken here metastasizes downstream. If a source model decides that a refunded order is “not really an order,” then every metric built on top of it inherits that decision invisibly, and the analyst who later asks “what’s our gross order count” has no clean answer.
Source models exist to be boring. They should mirror the source system’s grain, preserve every row, and surface the raw facts. When something upstream changes — a new field in Stripe, a renamed Salesforce object — the blast radius stops at this layer.
Layer two: metric definitions
This is where business logic lives, and only here. A metric definition is a contract: given these inputs, this is what “monthly recurring revenue” means, computed this way, with these exclusions, at this grain. One definition. One implementation. Versioned.
The metric layer joins across source models, applies business rules (the kind that get debated in revenue ops meetings), and produces conformed dimensions and measures. Customer status, revenue recognition windows, attribution rules, churn calculations, segment definitions — all of it lands here, in code, reviewed in pull requests, with tests that fail loudly when someone changes the math.
What this layer is not: a place to optimize for any specific dashboard. The metric definition for ARR should not know or care that the CFO’s dashboard wants it grouped by region and the sales VP’s dashboard wants it grouped by deal size. Both consumers see the same ARR; they just slice it differently downstream.
The reason this layer must exist as its own tier — rather than living inline in dashboards or being computed in the BI tool — is that the moment metric logic lives in two places, it diverges. Not might diverge. Will diverge. Someone will fix a bug in one place and not the other. Someone will add an exclusion in the dashboard for a one-off board meeting and forget to remove it. The contradictions in your dashboards are not a people problem; they are an architecture problem.
Layer three: consumption views
The consumption layer is purpose-built for a specific reader: an executive dashboard, a finance export, a customer-facing analytics feature, a reverse-ETL feed back into the CRM. Each view is shaped, denormalized, pre-aggregated, and named for its consumer. It joins metric outputs to whatever dimensions that consumer needs and nothing more.
This is where you optimize aggressively. Pre-aggregate to the day if the dashboard only ever shows daily numbers. Denormalize ruthlessly. Pin column order for an export contract. Add the cosmetic field names the business team actually uses. The whole point of this layer is that it is allowed to be opinionated about a single use case.
The discipline at this layer is the inverse of layer one: never reach back past the metric layer to compute something new. If a consumption view needs a number that does not exist in the metric layer, the answer is to add it to the metric layer first, not to recompute it inline. A consumption view that contains business logic is a consumption view that has just become a fourth, undocumented metric definition.
What breaks when you collapse the layers
Collapse source and metrics, and every system change becomes a metric change. A Salesforce admin renames a field and three months of “active customer” history quietly shifts.
Collapse metrics and consumption, and every dashboard becomes its own metric. The CRO and the CFO disagree about ARR, both pull receipts, both are technically correct, and the company can no longer trust its own numbers.
Collapse all three, and you have a warehouse full of bespoke SQL where every analyst is recomputing the same numbers slightly differently, governance is impossible, and the only way to answer a new question is to rewrite a query someone else already wrote — wrong.
The three layers are not a style preference. They are the minimum structure required for a warehouse to scale past the second analyst.