Data Warehouses: A Separate Store Built for Analysis
A data warehouse is a central store that collects historical data from an organisation's operational systems and keeps it so the organisation can analyse it.
The operational database that runs the business is tuned for many small, fast writes, while the warehouse is tuned for a few large reads that sweep across years of data.
Six features follow from that purpose, and each one is on the syllabus: append-only, subject-oriented, integrated, time-variant, non-volatile, and optimized for query performance.
Append-Only Data: New Records Are Added, Old Ones Stay
A warehouse only ever adds rows, so a change in a source system arrives as a new record rather than as an overwrite of the old one.
The full history of every value survives, which is what makes trend analysis over months or years possible.
An analyst can therefore ask what a figure was at any past date, not only what it is now.
Note
This is the opposite of a transactional database, where a customer's address row is updated in place and the previous address is gone.
That difference is why the two stores exist side by side rather than one replacing the other.
Subject-Oriented Data: Organised Around Business Subjects
Data in a warehouse is grouped around subjects such as sales, customers or products, rather than around the application that produced it.
An analyst working on one business area finds everything about it in one place, which makes the analysis focused.
A retail warehouse organises data into subjects such as "Sales", "Inventory" and "Customer Behaviour".
An analyst studying seasonal demand works inside the Sales subject instead of pulling from the till system, the website and the loyalty scheme separately.
Integrated Data: One Format for Data from Many Systems
A warehouse consolidates data from several source systems into a single consistent format.
Two systems may record a date as `01/03/25` and `2025-03-01`, or a customer as `J. Smith` and `Smith, John`, and the warehouse must settle on one form before the data can be compared.
The result is a comprehensive view of the organisation, free of duplicates and conflicting formats.
The ETL Process: Extract, Transform, Load
Extract: data is copied out of each source system.
Load: the transformed data is written into the warehouse, usually on a schedule such as nightly or weekly.
The transform stage is where normalisation of formats, cleansing of errors, filtering of irrelevant records, and validation against warehouse constraints happen.
Note
The same cleaning ideas are covered in detail for machine learning in A4.2.1, so they are not repeated here.
Time-Variant Data: Every Record Is Tied to a Point in Time
Each record in a warehouse carries a time stamp or a valid-from and valid-to period, so the same customer can appear many times, once per state they were in.
This is what append-only data buys: because nothing is overwritten, the warehouse can answer what was true at a given date, not just what is true now.
An analyst can then compare the same measure across periods, for example this March against last March.
Analogy
A data warehouse works like a shelf of time capsules rather than a single noticeboard.
A noticeboard shows only the current notice, while the shelf keeps every notice ever pinned up, each labelled with its date.
The analogy breaks down in one place: a warehouse is queried across all its capsules at once, which is the whole point of keeping them.
Non-Volatile Data: Loaded Once, Then Read Only
Users and reporting tools have read access only, so the warehouse changes solely during a scheduled load.
Two analysts running the same query on the same day therefore get the same answer, and a report produced last year can be reproduced exactly today.
Data integrity: the figures loaded from the source systems stay exactly as they were committed there.
Optimized for Query Performance: Built for Complex Reads
A warehouse is designed so that a query touching millions of historical rows still returns in a usable time.
Indexing lets the system find matching rows without scanning the whole table.
Partitioning splits large tables by a value such as year, so a query about 2025 never reads the 2024 partition.
Denormalization stores some data redundantly to remove joins from the most common queries, trading storage space for read speed.
Support for OLAP: the same design that keeps queries fast is what lets Online Analytical Processing (OLAP) and data mining run across the whole warehouse.
Tip
Denormalization in a warehouse is a deliberate step taken after normalization, not a failure to normalize.
An exam answer that treats it as sloppy design misses the read-versus-write trade-off that the mark is for.
What the Warehouse Gives Business Intelligence
Decision support: business intelligence tools query the warehouse instead of the live operational systems, so heavy analysis never slows down the systems running the business.
Trend analysis: the preserved history lets analysts see patterns that a snapshot of today cannot show.
Forecasting: past figures become the basis for predicting future demand, staffing or revenue.
Performance monitoring: key performance indicators are tracked over time against targets.
Example
A supermarket chain queries five years of warehouse data on customer purchases.
It finds that demand for a product line rises sharply in the four weeks before a public holiday.
Stock levels and marketing are then adjusted ahead of that window rather than in response to empty shelves.
Where This Connects to the Rest of the Course
A3.4.3 covers OLAP and data mining, the two techniques that turn warehouse data into business decisions.
A4.2.1 covers the data cleaning that the transform stage of ETL depends on.
A4.4.1 covers the ethics of holding and analysing large volumes of personal data, including privacy, bias and transparency.
Active recall
State the six core features of a data warehouse.
How does append-only data make time-variant analysis possible?
What does non-volatile mean for someone reproducing a report a year later?
Name the three stages of ETL and say what happens at each.
Give two techniques a warehouse uses to keep complex queries fast.
