Home / Learn / Data & AI / Data modelling / Lesson 1.1
All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

An OLTP database runs the business one tiny change at a time; an OLAP warehouse reads huge piles of history to answer questions, so the two are shaped differently.

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)
JobRun the business: take orders, take payments, keep stock rightUnderstand the business: trends, totals, comparisons
Typical questionWhat is in order 1038?Sales by department per year
Rows per queryA handful, found by keyThousands to billions, scanned and summed
WritesConstant small inserts and updates, many at onceLoaded in batches (nightly, hourly); then read-only
Table shapeMany narrow tables, no repeats (normalised)A few wide tables, repeats on purpose (star schema)
Who uses itTills, the website, appsAnalysts, dashboards, finance
HistoryCurrent state; old values are overwrittenKeeps history so last year can be compared
Two databases, two jobs. Northbay's tills write to an OLTP database all day; each night the rows are copied into an OLAP warehouse shaped for questions.

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):

The till database.
order_idproductqty
1038Eggs2
1038Kettle1
1039Bananas6
OLTP: one order by its key. WHERE order_id = 1038
order_idproductqty
1038Eggs2
1038Kettle1
2 of 3 rows touched.
OLAP: read everything. Every row is read, for every order ever placed:
rows readrows in table
33
Add it up. SUM(qty)
total_qty
9
Many rows in, one number out.
Split the jobs. The till keeps taking sales; a nightly load copies the rows into a warehouse, where the big reads cannot slow a checkout.

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?

  1. It needs every completed 2025 order line in a Home category, not one row found by key, so it is OLAP.
  2. The query above already answers it: Home, 2025, $23,566.55.
  3. Compare 2024: $18,605.36. Growth = 23,566.55 ÷ 18,605.36 − 1 ≈ 26.7%.
  4. 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

1Which question is a typical OLTP query?
2Why do most companies copy data out of the till database into a separate warehouse?
3How are OLAP tables usually shaped compared with OLTP tables?

Sources

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.