What Is a Data Warehouse? – Definition and How It Works
Learn what a data warehouse is, how columnar storage and modelling serve analytics, what governance it adds, and where it fits after collection.
A data warehouse is a database built for analysis rather than transactions: structured, modelled, governed, and optimised for queries that scan many rows and few columns.
1. What Distinguishes It
- Schema-on-write. Structure is enforced when data is loaded.
- Columnar storage, matching how analytical queries read.
- Modelled for consumption, not for how the source stored it.
- Governed — permissions, cataloguing, and lineage are first-class.
- Multi-user, serving many analysts and tools concurrently.
2. Warehouse Versus Transactional Database
| Aspect | Transactional | Warehouse |
|---|---|---|
| Workload | Many small reads and writes | Few large scans |
| Storage | Row-oriented | Column-oriented |
| Modelling | Normalised | Often denormalised for query speed |
| Concurrency | Very high, short transactions | Moderate, long queries |
| History | Current state | Full history over time |
That last row matters most for collected data: warehouses are designed to keep the past, which is what makes trend analysis possible.
3. Modelling for Analysis
The common approach separates facts from dimensions: measurements in fact tables, descriptive attributes in dimension tables joined to them.
For collected data this typically means a fact table of observations — one row per item per collection time — and dimensions describing the item, the source, and the period. That shape answers "how did this change" naturally, which is usually the question.
4. Slowly Changing Dimensions
Descriptive attributes change: a product is renamed, a business moves category. A warehouse can either overwrite or keep history with validity ranges.
Keeping history matters when analysing the past honestly — overwriting makes it look as though an item always had its current attributes, which quietly rewrites history.
5. Where It Fits After Collection
Collection lands raw payloads. Transformation cleans and types them. The warehouse is where the cleaned, modelled result goes so analysts and dashboards can query it.
Loading raw HTML into a warehouse is a common mistake — it belongs in a lake, with only the structured result promoted.
6. What It Adds Beyond Storage
- Access control by role and by column.
- A catalogue so datasets are discoverable and documented.
- Lineage, showing which sources produced which tables.
- Consistent semantics, so two dashboards computing the same metric agree.
- Performance management, so one heavy query does not starve everyone.
7. Cost Characteristics
Modern warehouses separate storage from compute, so idle data is cheap and queries cost what they consume.
The practical consequences are that scanning everything is expensive, partitioning and column pruning matter, and materialising frequently used aggregates is usually cheaper than recomputing them.
8. Summary
A data warehouse is a governed, columnar, schema-on-write store modelled for analysis and designed to retain history.
In a collection stack it sits after extraction and cleaning: the lake holds raw payloads and the warehouse holds the modelled result that people actually query.