Data warehouse definition
A data warehouse is a central analytical database that collects copies of data from the systems a business runs on, such as billing, the product database, the CRM and advertising platforms, and reshapes them so questions spanning those systems can be answered consistently. It keeps history, so you can ask how a number has changed rather than only what it is now.
Three properties separate it from simply querying the source systems. It is separate, so heavy reporting queries never slow down the application customers use. It is integrated, so a customer in billing and the same customer in the CRM can be matched on agreed keys. And it is modelled, so revenue, active customers and other metrics are calculated once, in one place, instead of in every spreadsheet that needs them.
Data warehouse vs database vs data lake
An operational database, such as the Postgres or MySQL behind an application, is optimised for many small reads and writes of current records: create an order, update an address. A warehouse such as BigQuery, Snowflake or Redshift is optimised for scanning and aggregating large numbers of rows, usually stored by column, and is rarely written to row by row.
A data lake stores raw files, often in object storage, without requiring a schema up front. It is useful for large or unstructured data, but on its own it does not give you agreed definitions. Many modern platforms blur the line by querying lake files with warehouse engines. For a small team, the useful question is not which label applies but where the modelled, tested tables people report from will live.
How to design a data warehouse: six decisions before the first table
Most warehouse problems come from decisions nobody made explicitly, not from the choice of vendor. Write these six down before building, and revisit them when the business changes.
- 1. The questions: list the five to ten recurring decisions the warehouse must support, with the person who makes each one. Anything not on the list waits.
- 2. The sources: for each question, name the system that holds the truth and who can grant access to it. When two systems disagree, decide in advance which wins.
- 3. The layers: raw copies of the sources, staging models that clean and rename without removing records, and marts shaped for reporting. Keep raw data so you can rebuild when a rule changes.
- 4. The grain: for every reporting table, state what one row means, such as one row per order line, and which key is unique at that grain.
- 5. The definitions: write each core metric with its owner, formula, inclusions and exclusions before it is coded, so every report reads the same version.
- 6. Ownership and cost: who approves changes, who is told when a load fails, how often each source refreshes, and what spending limit triggers a review.
Creating a data warehouse in practice
Start with one question and the one or two sources it needs, build the layers end to end, add tests and reconcile the result against a number the business already trusts. Then add the next question. Building every source first and modelling later produces a large, expensive copy of the source systems that nobody reports from.
The step-by-step tutorial builds exactly that thin slice on BigQuery with dbt: synthetic orders loaded as raw data, a staging view, a fact and a daily mart, tests that catch a duplicated key, and a reconciliation you can do by hand.
When you do not need a data warehouse yet
If every question can be answered from one system, its own reporting is usually enough. If the answers are needed once a quarter, a careful export may cost less than a platform to maintain. And if nobody will own the definitions, a warehouse will only make disagreements faster.
You probably do need one when the same number is calculated differently in several places, when reports require joining exports from two or more systems by hand every week, when history is lost because sources only keep current state, or when reporting queries are slowing down the production database.