The big idea: An operational database and a data warehouse hold the same data with opposite requirements.
Operational database
- Optimised for recording transactions
- Many small writes, one row at a time
Data warehouse
- Optimised for asking questions of history
- Few enormous reads scanning years of records
| Operational (OLTP) | Warehouse (OLAP) | |
|---|---|---|
| Optimised for | Writing — many small transactions | Reading — few huge queries |
| Holds | Current state | History, often years of it |
| Structure | Normalised — no duplication | Denormalised — deliberately duplicated |
| A typical query | One customer's balance | Sales by region by quarter since 2019 |
| Users | The application, constantly | Analysts, periodically |
The six properties the guide lists
| Property | Meaning | Supermarket example |
|---|---|---|
| Subject-oriented | Organised around business subjects, not around the applications that produced the data | Sales, customers, products |
| Integrated | Data from many sources made consistent: one date format, one product code | Tills, website and loyalty cards combined |
| Time-variant | Every record is tied to a point in time, so trends over years can be seen | Sales per week since 2020 |
| Non-volatile | Once loaded, data is not changed or deleted by users | Last year's sales stay exactly as loaded |
| Append-only | New data is added at each load; old data is never overwritten | Each night adds that day's sales |
| Optimised for queries | Denormalised and indexed for fast reads of huge amounts of data | A report over five years runs in seconds |
Together these serve business intelligence: managers asking questions of a complete, stable history to make decisions.
Free preview
This is the free notes preview
You're reading the free notes. Aimnova Pro unlocks the full study experience — and you can try it with your first topic free to keep:
- FlashcardsLock in vocabulary and key terms with spaced repetition.
- Practice questionsAnswer exam-style questions and get instant AI marking.
- Mock exams & past-paper vaultSit full mocks and see exactly how examiners award marks.
- Personalised study planA daily plan built around your exam date and weak areas.
Four reasons
- Load — a query scanning five years of sales would slow the system customers are using right now
- History — operational systems overwrite. Yesterday's price is simply gone
- Many sources — sales, web, stock and support each have their own database and their own formats
- Structure — normalised tables need many joins per analytical query; a warehouse denormalises so those joins are already done
ETL is where the work is: Extract from each source system · Transform into one consistent shape — the same date format, the same country codes, the same definition of "a sale" · Load into the warehouse.
The transform step is where most of the effort goes, because every source defines things slightly differently.
Denormalised on purpose: A warehouse breaks the normalisation rules deliberately. Update anomalies are the usual objection — but a warehouse is rebuilt in bulk, not edited by users, so the anomaly cannot arise.
That is what makes the trade safe here and unsafe in an operational system.
Feeling unprepared for exams?
Get a clear study plan, practice with real questions, and know exactly where you stand before exam day. No more guessing.
Not live
- Loaded on a schedule — nightly is common
- So it is out of date by design
- Never use it to answer "what is the stock right now?"
Not a backup
- It holds transformed data, not the originals
- You could not restore the business from it
- Different purpose entirely
Not free
- Storage for years of duplicated history
- The ETL pipeline must be built and maintained
- Every source change breaks it until it is updated
Name the latency: Saying "a warehouse is not real-time — it is loaded nightly, so it answers questions about the business up to yesterday" is frequently a mark. It shows you know what the design costs.
How this is tested — you must separate recording from analysing, and justify the duplication a warehouse involves. It comes up two ways:
Paper 1 Section A
- Explain the objectives of a data warehouse, 3-4 marks
- State one difference from an operational database
- Name the stages of ETL
Paper 1 Section B — case study
- Justify a warehouse for a scenario
- Explain why denormalisation is acceptable here
The classic trap: Describing a warehouse as "a copy of the database". It holds transformed, integrated history from several sources, deliberately denormalised — and it is only current to the last load.
A supermarket chain wants to analyse five years of sales across 200 stores alongside its loyalty and stock systems. Explain why a data warehouse is appropriate rather than querying the existing databases.
Model answer plan
See the answer broken into clear points, with guidance on how each point earns marks.