What each layer protects
| Dimension | Purpose | What it forces you to decide |
|---|---|---|
| Raw | Preserve source fidelity for replay and audit. | Retention window, source keys and how corrections are applied. |
| Validated | Apply quality gates before data is used. | Which tests block, which quarantine, and who reviews rejects. |
| Modelled | Structure data for analysis at an agreed grain. | Fact and dimension design, keys, and history handling. |
| Served | Expose governed measures to consumers. | Which measures are certified, and who owns each definition. |
Start with grain
Before naming layers, state what one row represents in every table: one order line, one customer per day, one shipment event. Most reconciliation arguments are grain disagreements wearing a different hat. Writing the grain down makes joins, aggregations and duplicate checks verifiable rather than interpretive.
Take an orders table. At order grain, one row per order, shipping cost sits naturally on the row. At order-line grain, one row per product, shipping cost has to be allocated across the lines or kept in a separate fact, otherwise every sum of shipping by product double-counts it. The grain decides which questions a table can answer without error.
- One row = one thing, stated for every table
- Facts separated from dimensions and from snapshots
- Time zone and currency conventions recorded once
Layers are a convention, not a goal
A common arrangement is raw, validated, modelled and served, and the medallion pattern is the lakehouse expression of the same idea: raw preserves source fidelity, validated applies quality gates, modelled restructures into facts and dimensions, and served exposes marts or a semantic model. The pattern is useful because it separates concerns; the failure is copying the diagram and never defining what each layer protects.
Facts, dimensions and the questions they serve
A star-schema-shaped model keeps measurable events in fact tables and descriptive context in dimensions, which makes reporting predictable and query patterns efficient. Not every model needs this shape, but every model needs a stated structure, agreed keys and defined relationships rather than joins discovered at query time.
Where business measures live
A measure such as active customer, net revenue or on-time delivery must be defined once, with its filters, exclusions and time basis recorded. Whether that lives in a semantic layer, a mart or dbt models matters less than there being one definition with a named owner. Two definitions is the defect that produces a meeting about numbers.
- Definition, grain, filters and time basis written down
- Owner named for each governed measure
- Deprecated measures retired, not left available
History and change
Slowly changing attributes — a customer’s segment, a product’s category, an employee’s team — change how history should be read. Decide what you need: the value now, the value at the time of the transaction, or both. Recording that decision prevents the common defect where last year’s report silently changes when an attribute is updated today.
The standard technique is a type 2 slowly changing dimension: when a tracked attribute changes, the current row is closed with an end date and a new row opened, so each fact joins to the version that was true on its transaction date. Type 1 overwrites the value and keeps no history. Choose per attribute, not per table.
Illustrative example · hypothetical organisation
Worked example: modelling a sales mart
Consider a hypothetical distributor that wants revenue by region, product and customer segment.
The fact table is sales at order-line grain, with keys to date, product, customer and region dimensions, and measures for quantity, net amount and cost. The customer dimension is type 2 on segment, because the business wants last year's revenue reported under the segment each customer held at the time.
Net revenue is defined once: invoiced amount minus credit notes, excluding tax, in the reporting currency at the invoice-date rate. That definition lives in the served layer with a named owner, and every report reads it from there.
Cost, performance and refresh
Design choices show up as cost and latency: how often full refreshes run, how large the models are, which queries scan the most data, and how much history is retained. Make refresh windows and cost per workload visible early, because retrofitting them after the estate grows is far more expensive.
Questions and trade-offs
Question
Do we need a separate staging layer?
Usually, because it separates source fidelity from business modelling and makes replay possible without touching logic that consumers depend on. It does not need to be elaborate: the requirement is that raw data is preserved and validated before it is modelled, and that someone owns each step.
Question
Should the semantic layer live in the warehouse or the BI tool?
Wherever it can be owned and reviewed. If the data platform defines measures, every consumer inherits the same definition and the BI tool becomes a presentation choice. If only the BI tool defines them, that tool becomes the semantic layer by accident and migrating it later becomes a governance project.
Question
Should we use Kimball or Data Vault?
They solve different problems. Kimball's dimensional modelling produces star schemas that are easy to query and suit the served layer. Data Vault models hubs, links and satellites for auditability and for integrating many changing sources, and sits in the integration layer with dimensional marts built on top. Small and mid-sized estates seldom need Data Vault.
