What you will learn
- How staging, intermediate, and mart layers divide warehouse work
- Why dimensional design still matters for analyst speed
- The build order from raw tables to trusted marts
- How tests and docs keep a growing warehouse usable
What this project is
A warehouse is a promise: that the table called orders means the same thing to every analyst, every report, and every executive dashboard. Modelling is how that promise is kept, through layered transforms, consistent definitions, and tests that fail loudly when source data changes shape.
Most warehouses decay through small, reasonable shortcuts: a report that queries raw tables, a metric defined twice with different filters, a column renamed without warning. The platform's job is to make the disciplined path the easy path, with shared models, reviewed changes, and documentation generated from the models themselves.
Core features
- Layered models: staging for raw cleanup, intermediate for shared logic, marts for analyst-ready tables.
- Metric definitions in one place, so revenue or active users cannot drift between reports.
- Tests for uniqueness, referential integrity, freshness, and accepted value ranges on every mart.
- Column-level documentation generated from model definitions and kept beside the code.
- Change review with impact previews showing which downstream models and reports a change touches.
- Versioned releases of models, so a breaking change ships deliberately, not by accident.
How it is built
- Staging first. Clean, typed copies of source tables with freshness checks.
- Core intermediate models second, covering the entities every team needs: customers, orders, products.
- Marts third, built for specific analyst groups on top of shared intermediates.
- Tests and docs fourth, enforced on every model before it is called production-ready.
- Governance last. Ownership, deprecation rules, and change review for the full estate.
Technology considerations
Warehouse modelling lives close to the warehouse itself, so transforms should run inside it where possible, keeping data movement simple. Model code belongs in version control with review, and tests run on every change before it reaches production tables. Documentation generated from code stays current; documentation written separately goes stale within weeks. Costs depend on warehouse size and refresh frequency; confirm with a quote.
- In-warehouse transforms with version-controlled model code.
- A test runner checking models on every change.
- Generated documentation from model definitions and tests.
- Impact previews showing downstream effects of each change.
Challenges to plan for
- Metric drift. The same metric defined in two places diverges, so definitions must live once and be referenced everywhere.
- Source changes. A renamed column or new status value breaks models silently unless tests catch it.
- Mart sprawl. A mart per report request multiplies maintenance, so shared intermediates must come first.
- Stale documentation. Docs that live apart from code rot, so generation from code is the practical answer.
A worked example
A subscription company discovers its finance and growth teams report different monthly revenue figures, and the investigation finds two definitions: finance counts revenue on invoice date, growth on payment date, and neither team knew the other existed. The warehouse team builds a shared intermediate model for subscriptions and invoices, defines revenue once in the modelling layer with its date logic stated, and rebuilds both teams' marts on that single definition.
Tests enforce uniqueness of invoice keys, referential integrity to customers, and freshness of the source tables, so a source change that renames a status value fails the staging tests before it reaches any mart. Documentation generated from the models shows every analyst the definition, the owner, and the downstream reports affected by a change. The next time finance proposes a definition change, the impact preview lists the growth reports it touches, and the change ships with both teams informed. Revenue still has legitimate variants, such as recognised versus billed, but now each variant is a named, defined metric instead of a silent disagreement.
Who it is for, and its limits
This fits analytics teams whose warehouse serves several groups, or any team where two reports have ever disagreed about the same number.
It does not fit a single analyst querying one source, where direct queries plus a few views suffice. It also does not fix source data quality; it makes source problems visible and contained.
Frequently asked questions
What are staging, intermediate, and mart layers?
Staging cleans raw sources. Intermediate holds shared business logic. Marts present analyst-ready tables for specific teams or questions.
Why dimensional modelling still matters?
Because star-style dimensions and facts make common analyst queries fast to write and easy to understand, even on modern warehouses.
How are metrics kept consistent?
By defining each metric once in the modelling layer and having every report reference that definition, never re-implementing it.
What tests matter most?
Uniqueness, referential integrity, freshness, and accepted ranges on marts, plus source-shape tests on staging tables.
When should a model be deprecated?
When no report or downstream model uses it, after a notice period and a check of actual query history, not guesses.
Why do two teams report different revenue from the same warehouse?
Because the metric was defined separately in each report with different filters or date logic. Defining it once in the modelling layer and referencing it everywhere ends the drift.