Module 1: Why model data at all · Lesson 1.1 · about 20 min
Two kinds of database: running the shop and understanding it
An OLTP database runs the business one small transaction at a time; an OLAP warehouse answers big questions across years of history. See both kinds of query on Northbay and why they need differently shaped tables.
Module 1 · about 20 minutes · read first: SQL for analysts 4.2. You’ll be able to tell an operational (OLTP) query from an analytical (OLAP) one, say why they live in different databases, and explain why that changes how tables are designed.
In everyday terms: a shop till and the year-end stocktake. The till rings up one sale in a second. The stocktake counts everything in the shop and adds it up. You would not do the stocktake at the till during the lunch rush.
1. Why this matters
Every BI job starts with the same surprise: the data you need lives in a database built for something else. The tills, the website and the stock system are built to take thousands of tiny updates safely. Dashboards need to read millions of rows and add them up. A data model is the bridge between those two worlds, and this course is about building it. Interviewers often open with “what is the difference between OLTP and OLAP?”
2. Two jobs, two databases
OLTP (online transaction processing) is the database that runs the business. Each checkout at a Northbay till writes one row to orders and a few rows to order_items, and it must never lose or double one.
OLAP (online analytical processing) is the database that helps people understand the business: sales per month, best products, returns by region. It reads lots of rows and writes almost none, apart from a nightly load. That database is usually called a data warehouse.
| OLTP (operational) | OLAP (analytical) | |
|---|---|---|
| Job | Run the business: take orders, take payments, keep stock right | Understand the business: trends, totals, comparisons |
| Typical question | What is in order 1038? | Sales by department per year |
| Rows per query | A handful, found by key | Thousands to billions, scanned and summed |
| Writes | Constant small inserts and updates, many at once | Loaded in batches (nightly, hourly); then read-only |
| Table shape | Many narrow tables, no repeats (normalised) | A few wide tables, repeats on purpose (star schema) |
| Who uses it | Tills, the website, apps | Analysts, dashboards, finance |
| History | Current state; old values are overwritten | Keeps history so last year can be compared |
3. An OLTP query: one order, found by its key
When a customer asks “what did I buy?”, the till looks up one order by its key. Only four rows of order_items are touched.
-- try
SELECT o.order_id, o.order_date, p.product_name, i.quantity, i.unit_price, i.discount
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.order_id = 1038
Order 1038 was placed at store 2 on 10 January 2024, during the Winter warm-up sale, so every line has a 10% discount. Press Show how it runs and notice that WHERE leaves just four rows: OLTP work is small and precise.
4. An OLAP query: everything, added up
A manager asks how each department sold, year by year. Now every order line in two years is read, joined and summed.
-- try
SELECT c.department,
strftime('%Y', o.order_date) AS year,
ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount)), 2) AS sales
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
JOIN products p ON p.product_id = i.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.department, year
ORDER BY c.department, year
Count what each query had to read:
-- try
SELECT 'OLTP: order 1038' AS query, COUNT(*) AS order_lines_read FROM order_items WHERE order_id = 1038
UNION ALL
SELECT 'OLAP: departments by year', COUNT(*) FROM order_items
The department query reads all 7,310 order lines to return 8 rows. On Northbay that takes a blink. On a real chain with a billion lines, running it on the till database at lunchtime would slow down every checkout. That is the main reason the two jobs get separate databases.
5. Why the shapes differ
The OLTP tables above are normalised: each fact is stored once. The department “Home” is written in one row of categories, not on every order line. That is perfect for writing, because a renamed department is one update. But to read it, the OLAP query had to walk through three joins.
A warehouse flips the trade-off. It is loaded in batches, then only read, so it can afford to repeat “Home” on many rows if that makes questions shorter and faster. The next two lessons show both sides: normal forms and why analytics breaks the rules.
One more difference: history. The till database stores a customer’s current loyalty tier. If Uma moves from Bronze to Gold, the old value is overwritten. A warehouse is expected to remember that she was Bronze last year, so last year’s report doesn’t change. Module 5 is all about that.
- OLTP
- Online transaction processing: the database that takes each sale, payment or sign-up as it happens.
- OLAP
- Online analytical processing: reading many rows and adding them up to answer a question.
- Data warehouse
- A separate database, loaded in batches, that keeps history and is shaped for reading.
- Selectivity
- Rows a query touches divided by all rows in the table. Near 0 is OLTP work; near 1 is OLAP work.
- Physical meaning
- An OLTP query finds a handful of rows by their key and changes or shows them. An OLAP query scans nearly every row, joins it to its descriptions and squeezes it into a few totals.
“A warehouse is just a copy of the live database.” A copy would still need many joins for every question, and it would still forget old values. A warehouse is reshaped for reading and keeps history that the live system overwrites.
Watch it happen, one step at a time (and press Show how it runs on any live SQL box in this lesson to animate the real query):
| order_id | product | qty |
|---|---|---|
| 1038 | Eggs | 2 |
| 1038 | Kettle | 1 |
| 1039 | Bananas | 6 |
WHERE order_id = 1038 | order_id | product | qty |
|---|---|---|
| 1038 | Eggs | 2 |
| 1038 | Kettle | 1 |
| rows read | rows in table |
|---|---|
| 3 | 3 |
SUM(qty) | total_qty |
|---|
| 9 |
Try one with fresh numbers:
Maths and principle
Principle: the two workloads touch very different fractions of the data. OLTP reads a tiny slice found by key; OLAP reads nearly all of it and squeezes it into a few numbers.
Equation: for a query, the selectivity is s = rows touched ÷ rows in the table. The order lookup has s = 4 ÷ 7,310 ≈ 0.0005. The department report has s = 7,310 ÷ 7,310 = 1, and an output of 8 rows: a reduction of about 900 to 1.
Analogy: a librarian fetching one book by its shelf number (OLTP) versus a researcher counting every book published each year (OLAP). The first needs a good catalogue; the second needs the books arranged by year, or a ready-made tally.
Try it: a query that lists the 4 orders of customer 17 out of 4,064 orders. Is s close to 0 or close to 1? (Close to 0: about 0.001, so it is OLTP-style work.)
6. Worked example
Question: is “Home sales in 2025” an OLTP or an OLAP question, and what is the answer?
- It needs every completed 2025 order line in a Home category, not one row found by key, so it is OLAP.
- The query above already answers it: Home, 2025, $23,566.55.
- Compare 2024: $18,605.36. Growth = 23,566.55 ÷ 18,605.36 − 1 ≈ 26.7%.
- To get there the database joined four tables and read 7,310 lines. In a warehouse we would design a table where the same answer needs one join or none. That design is what the rest of this course builds.
7. Practice
-- exercise: OLTP-style lookup: list order_id, order_date, store_id and status for every order placed by customer 17.
-- hint: Filter orders with WHERE customer_id = 17.
SELECT order_id, order_date, store_id, status
FROM orders
-- answer:
SELECT order_id, order_date, store_id, status
FROM orders
WHERE customer_id = 17
-- exercise: OLAP-style summary: show completed sales per year and channel (store or online), rounded to 2 decimals, ordered by year then channel.
-- hint: Join order_items to orders, keep status = 'completed', and GROUP BY strftime('%Y', order_date) and channel.
SELECT strftime('%Y', o.order_date) AS year, o.channel, COUNT(*) AS lines
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
GROUP BY year, o.channel
ORDER BY year, o.channel
-- answer:
SELECT strftime('%Y', o.order_date) AS year, o.channel,
ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount)), 2) AS sales
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.status = 'completed'
GROUP BY year, o.channel
ORDER BY year, o.channel
-- exercise: How many order lines does the department report have to read for 2025 only? Count the order_items rows whose order is dated in 2025.
-- hint: COUNT(*) after joining to orders, with WHERE order_date LIKE '2025%'.
SELECT COUNT(*) AS lines
FROM order_items
-- answer:
SELECT COUNT(*) AS lines
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.order_date LIKE '2025%'
- OLTP runs the business: many tiny writes and lookups by key.
- OLAP understands the business: big reads that add up many rows.
- Big scans on the till database slow down checkouts, so analysis moves to a warehouse.
- OLTP tables avoid repetition; warehouse tables repeat some values on purpose to need fewer joins.
- A warehouse also keeps history that the live system overwrites.
New words: OLTP, OLAP, data warehouse, normalised, selectivity.
Check yourself
Sources
- Microsoft Learn, Azure Architecture Center: Online transaction processing (OLTP) https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-transaction-processing
- Microsoft Learn, Azure Architecture Center: Online analytical processing (OLAP) https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-analytical-processing
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley, Chapter 1: Data Warehousing, Business Intelligence and Dimensional Modeling Primer
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.