On this page
The short answer
A data warehouse is a central database that pulls data out of several systems, cleans and reshapes it, and holds the result so it can be analysed together. Throughout this article a consumer means anyone or anything that reads that data: a dashboard, a finance model, a board pack, an operations report.
You need one when three things are true at once. You must combine several sources. You must model them into definitions you can trust. And many consumers must use those definitions, repeatedly, without re-deriving them each time.
Until all three hold, simpler options usually win on both cost and speed: better SQL against the source you already have; a single managed connector (a hosted integration that syncs a source into a database for you); a controlled, well-governed spreadsheet; a semantic model (one place where each metric is defined once and reused everywhere); or a smaller database that consolidates only what you need. This article is about telling the two situations apart honestly, because building early is expensive and quietly hard to undo.
What this usually looks like
The question rarely arrives as a technical decision. It arrives as friction. Dashboards have multiplied, and two of them disagree about the same number. Someone keeps a private spreadsheet because they trust it more than the reporting tool. Every month, several people export CSV files from the CRM, the billing system and the finance tool, then stitch them together by hand. A new question takes days, because answering it means chasing the same exports again.
At some point somebody says: “we should get a data warehouse.” It sounds like the obvious next step, and sometimes it is. But “get a warehouse” is a solution looking for its problem. The friction above has several possible causes, and only some of them are cured by a warehouse. The rest are cured by tidying one query, governing one definition, or connecting one tool properly.
So the useful first move is not to price up a warehouse. It is to name the problem precisely enough to know whether a warehouse is even the right shape of answer.
Before you build anything
Run this readiness checklist before you provision anything. Answer each question with a number or a name, not a feeling. If most answers come back small, you are looking at a task, not a system.
What your answers tell you
Read your answers together, not one at a time. The pattern matters more than any single number.
One or two sources, a handful of consumers, monthly refresh, no shared definition in dispute. A warehouse is almost certainly premature. The friction is more likely a single slow or wrong query, or a tool connected to the wrong source. Better SQL, or a managed connector into the tool you already report from, will move faster and cost a fraction as much.
Several sources, but only one contested definition. You may not have a warehouse problem at all. You have a definitions problem. A semantic model can settle it without moving any data: define the metric once, and point every report at that definition instead of forty private copies of it.
Several sources, many consumers, a daily rhythm, and definitions that must be governed centrally. Now the answers line up behind a warehouse. The cost of stitching exports by hand, and of everyone re-deriving the same numbers, has overtaken the cost of building and running the thing.
And if nobody will own it, the honest answer is “not yet”, whatever the other answers say. An unowned warehouse decays into exactly the mess it was meant to replace — only now it is a mess with a monthly bill.
What a warehouse actually does
Strip away the branding and a warehouse is four stages in a line: sources, ingestion, transformation and modelling, then serving.
Ingestion is moving raw data from each source into the warehouse. Transformation and modelling is turning those raw tables into clean, tested datasets with a clear, agreed meaning. Serving is exposing the result to the tools people use. The value lives almost entirely in the middle stage; the two ends are largely commodity.
There are two orders for the first two stages. ETL (extract, transform, load) reshapes the data before it lands. ELT (extract, load, transform) lands the raw data first, then transforms it inside the warehouse using SQL. Modern cloud warehouses are cheap and fast enough that ELT has become the default: you keep the raw data, and your transformations are version-controlled SQL you can test, review and rerun.
The assembled toolkit for this has a name, the modern data stack: managed connectors for ingestion, a cloud warehouse to hold the data, a transformation tool to model it, and a BI (business intelligence) tool to serve it. None of it is exotic, and most of it you rent rather than build.
The one idea worth understanding in depth is the incremental model: processing only new or changed rows on each run, instead of rebuilding every table from scratch. It is what keeps cost roughly flat as data grows. A simple incremental pattern uses a high-water mark: find the newest row you have already loaded, then load only the rows newer than that.
-- Append only the rows that changed since the last run INSERT INTO analytics.orders -- the modelled table SELECT order_id, customer_id, amount, updated_at FROM raw.orders -- raw, freshly ingested source WHERE updated_at > (SELECT max(updated_at) FROM analytics.orders);
On the first run the modelled table is empty, so every row loads. On every run afterwards, the subquery finds the latest updated_at already stored, and the WHERE clause lets through only rows newer than that. You process a day of changes, not the whole of history. In production you would usually merge rather than append, so an update to an existing order replaces the old row instead of duplicating it, but the high-water-mark idea is the same. This is the difference between a warehouse that costs about the same each month and one whose bill climbs with every row you have ever collected.
What good looks like
Right-sizing matters more than any tool choice. The correct architecture is the smallest one that answers your recurring questions reliably. Here is what that looks like at three scales.
Early: one or two systems
Connect your BI tool directly to the source, or sync it with a single managed connector into a small database. Keep your definitions in a short, written, shared document. No warehouse. This is not a compromise; it is the right size for the problem.
Growing: several systems, daily decisions
A boring, sound shape. Managed connectors land raw data in a cloud warehouse. A transformation layer turns raw tables into staging models close to the source, then business-level models with a clearly stated grain: the one thing each row represents, such as one order or one order line. A semantic layer defines each metric once, and a BI tool sits on top. Incremental models keep the cost flat. This is the sweet spot the modern data stack was built for.
Larger: many teams, strict governance
The same shape, hardened: tests that run on every change, documented lineage so any figure traces back to its source row, access controls, and a named team that operates it. Nothing exotic: the growing-company stack, owned and defended.
At every scale the rule holds: resist designing for a load you do not have. An over-built platform costs money, but its real cost is the ongoing burden of running something more complicated than the problem it serves.
Common ways this goes wrong
A few failures show up again and again. All are expensive, and all are avoidable.
- Building a warehouse to fix one report. If a single dashboard is wrong, fix that query or that definition. A warehouse is a large answer to a small, local problem, and it will not make a bad definition correct.
- No owner. A warehouse launched without someone responsible for running it does not stay reliable. Connectors break, sources change shape, tests go stale. Within months it is just another source people quietly stop trusting.
- Premature complexity. Real-time streaming, a lakehouse, orchestration for pipelines you do not have yet. Each piece is defensible alone and indefensible together when the business only needs yesterday’s numbers, reliably.
- Moving the mess in unchanged. Loading messy source data into a warehouse and pointing dashboards at it just relocates the mess and adds a bill. The value is in the modelling: resolving identity, fixing grain, agreeing definitions. Skip that and you have paid for storage, not clarity.
When you genuinely need one
There is a real line, and it is worth stating plainly. A warehouse becomes the right answer, not a premature one, when the work has genuinely become a system rather than a task. The clearest example is when you must combine several systems and resolve them into one trustworthy result, repeatedly. Building a single customer view across CRM, billing and product data is exactly this kind of problem: it does not stay solved without a modelled system underneath it. When the conditions below all hold, building is the cheaper choice, and delaying only prolongs the manual stitching.
A readiness decision guide
Match your situation to the closest row. The recommended move is deliberately the smallest thing that resolves the real problem, not the most impressive.
| Your situation | What it usually is | Start with |
|---|---|---|
| One slow or wrong report, from a single source | A query or logic problem | Better SQL: fix the query and pin down its definition |
| The data exists in one tool, but not in the tool that reports on it | An ingestion gap | A managed connector into your existing BI tool or database |
| Numbers reconcile in the data, but one metric is defined differently everywhere | A definitions problem | A semantic model: define it once, reuse it everywhere |
| A few sources, modest volume, growing cross-system questions | Early consolidation | A small database with a light managed pipeline |
| Several sources, many consumers, daily governed decisions, a clear owner | A system problem | A data warehouse, scoped tightly to the decisions it must support |
If you can place yourself in one row with confidence, act on that row. If you cannot — if the honest answer to “what breaks today?” is still vague — that uncertainty is itself the finding. Pinning the requirements down before you provision anything is exactly what a Data Diagnostic is for. The cheapest warehouse is the one you did not need to build; the second cheapest is the one you scoped precisely before you started.