A3.4.2 · Alternative databases and data warehouses
Data warehouses
By the end you can
Explain why organisations build data warehouses, what makes one different, and what ETL does.
A supermarket's tills record every sale, its stock system records every delivery, and its loyalty-card system records who buys what. Each system is built for its own everyday job. But the managers want questions answered across all of them: which products are losing popularity, and which customers are changing their habits? That is what a is for: one central store of data from many sources, kept for analysis and decision-making (business intelligence).
What makes it a warehouse#
- Subject-oriented. Organised around topics the business cares about (customers, products, sales) instead of around individual transactions. Analysts can study one subject without joining a dozen operational tables.
- Integrated. Data from many sources is combined into one consistent format, so “customer” means the same thing everywhere.
- Time-variant. It keeps history, so you can see how things change over months and years.
- Non-volatile (append-only). Data is added but not changed or deleted. A new version of a record is added as a new row, so you can still see what it used to be.
The warehouse is kept separate from the operational databases, so a huge analysis query does not slow down the tills.
Getting data in: ETL#
Data from different systems is messy and inconsistent, so it goes through : Extract (collect it from the sources), Transform (clean it: correct errors, filter out what is not needed, validate it against the warehouse's rules, and convert it to a common format) and Load (add it to the warehouse). Without the transform step, mixed formats and errors would make the analysis wrong.
- Source systemssales, stock, web logs, loyalty cards
- Extractgather the data
- Transformclean, filter, validate, standardise
- Loadappend to the warehouse
- Data warehouseintegrated, historical
- AnalystsOLAP, mining, reports
Step 1 of 6 · Source systems
During the day the tills, the stock system and the loyalty app each record their own data, in their own formats. One calls the customer “CustID”, another “member_no”.
Records each ticket the moment it is sold, and updates seat availability.
Holds ten years of sales so analysts can compare each December.
Combines data from the web shop, the stores and the call centre into one consistent form.
Rows are frequently updated and deleted as things change.
0 of 4 matched
Check yourself
Data warehouses and ETL
Quick questions, made fresh every time and marked here. Do a few until they feel easy.
Exam practice
Paper 1-style questions
5 questions · 14 marks
Write your answer as you would in the exam, then show the mark points and tick each one your answer made. These are our own questions in the style of the exam, not past-paper questions.
1.Define the term “data warehouse”.
[2]Define: Give the precise meaning of the term.
2.Describe the three stages of the ETL process.
[3]Describe: A detailed account: say what happens, step by step if it is a process.
3.Outline four characteristics of a data warehouse.
[4]Outline: A brief account: the main points, without much detail.
4.Distinguish between a data warehouse and an operational (transactional) database.
[3]Distinguish: Make the differences between two things clear.
5.Explain why data must be cleaned and transformed before it enters a data warehouse.
[2]Explain: Give reasons: say what happens AND why, or how one thing causes another.
Past papers
Old exam questions on this topic
From HL Paper 1, May 2014 to November 2025. These papers were set on the previous syllabus, so the wording may differ from today's exam, but the topic is the same. Find the questions and markschemes in your school's past-paper pack.
| Paper | Question | Marks | About |
|---|---|---|---|
| May 2025 TZ2 | 4(c) | 4 | Paper 2 Option A: data warehouses and ETL (same in TZ3) |
| November 2024 | 4(c) | 4 | Paper 2 Option A: data warehouses and ETL |
| November 2018 | 4(a) | 4 | Paper 2 Option A: data warehouses and ETL |
| November 2018 | 4(b) | 4 | Paper 2 Option A: data warehouses and ETL |
| May 2017 | 4(b) | 4 | Paper 2 Option A: data warehouses and ETL |
| May 2016 | 4(d) | 4 | Paper 2 Option A: data warehouses and ETL |
| November 2015 | 4(b) | 4 | Paper 2 Option A: data warehouses and ETL |
| November 2015 | 4(d) | 4 | Paper 2 Option A: data warehouses and ETL |
In a nutshell
- A data warehouse is a central store of integrated, historical data from many sources, built for analysis and business intelligence, not for day-to-day transactions.
- Characteristics: subject-oriented, integrated, time-variant (historical), non-volatile (append-only).
- ETL: Extract data from sources, Transform it (clean, filter, validate, standardise), Load it into the warehouse.
- Objectives: bring data together, keep history, support reports and analysis, and let managers see trends and decide.
- It is separate from the operational databases, so heavy analysis does not slow down everyday transactions.