Ingestion is not the hard part

Most warehouse projects start with pipelines: get the CRM, the ERP, the webshop and the accounting system into one place. Modern tools make that part quick. Within days, every table is available in one database.

And yet the first dashboard built on top of it often shows the same disagreements the company had before. Sales counts an active customer as anyone with an open invoice. Finance counts anyone who paid in the last 90 days. Both definitions are reasonable. Put them side by side under the same label and nobody trusts either.

A warehouse without a modelling layer moves the argument to a new place. It does not settle it.

What a definition looks like

Take “active customer”. A usable definition answers every question someone could reasonably ask about it:

  • What counts as activity? A paid order, not a login, a quote or an open invoice.
  • Over what window? The last 90 days, counted back from the end of the reporting period.
  • Who is excluded? Internal accounts, test customers and resellers buying on behalf of others.
  • Which system decides? Orders from the ERP, matched to customers in the CRM by tax number.

Written this way, sales and finance can disagree with the definition, but not about what the number means. That is a far more useful argument to have, and it only needs to happen once.

Three layers, each with one job

A practical warehouse separates the work into layers. The exact tooling varies, but the separation is what keeps the logic understandable as the platform grows.

  1. Staging: clean, but close to the source. Each source table is renamed consistently, given proper data types and checked for duplicates and missing keys. Nothing is combined yet, so any issue can be traced to the system it came from.
  2. Business models: where definitions live. Sources are joined into business entities, such as one customer record spanning the CRM, the webshop and billing. Metrics like gross margin or churn are defined here once, in reviewed and tested code.
  3. Reporting models: built for the question. A small set of tables shaped for dashboards and briefs. Analysts and business users work from this layer and never need to rebuild logic from raw tables.

Tests turn definitions into guarantees

Definitions only hold if something checks them. Each model carries automated tests: keys are unique, every order belongs to a customer, revenue is never negative, the latest data arrived on time. When a source system changes a field or a sync fails, the test fails before the number reaches a report.

This is the difference between a warehouse that is correct on the day it is delivered and one that stays correct.

Where to start

Do not try to define everything. Pick the five to ten measures leadership actually uses to run the business, usually some version of revenue, margin, cash, customers and stock. Define those, model them, test them and build one report on top. Everything else can follow the same pattern later.

For each model, keep three things next to the code: a one-line description of what a row represents, the definition of every metric it exposes, and the name of the person who owns it. Documentation that lives elsewhere goes out of date within a quarter.