In this post we are going to cover a question that usually comes right after OLTP vs OLAP: why can't we just run our reports on the OLTP database? It already has all the data, so why build and pay for a separate warehouse?
The short answer is that the OLTP database was built for a different job. It is designed for many small, fast transactions from customers and applications. Heavy reports get in the way of that job, and the data is also in the wrong shape for analysis.
We will go through four reasons, and prove two of them with SQL on a small TB Shop database: one about joins and one about history. Then we will look at what teams actually do instead.
Question 2 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- Reason 1: Reports slow down the shop
- Let's create some sample data
- Step 1: Sales by country on the OLTP database
- Step 2: The same question on the warehouse
- Step 3: Sales by city, before Lina moves
- Step 4: Lina moves, and the application overwrites her city
- Step 5: The same report, run again
- Reason 4: One database sees only one system
- What teams do instead
- Follow-up questions interviewers ask
- The full script
The interview answer
Because OLTP databases are built for many small, fast transactions. Heavy reports compete with customers for CPU, memory and locks. The data is normalized, so reports need many joins. It usually keeps only the current state, not history. And it holds data from just one system. So we copy data into a warehouse built for analytics.
Four reasons to remember: load, joins, history and sources.
Reason 1: Reports slow down the shop
The main reason is that both workloads would share one database.
At lunch time, hundreds of customers are browsing and checking out on TB Shop. Every click is a tiny query, and the database answers each one in milliseconds. Now an analyst runs a big report on the same database: total sales by country and month for the last three years.
- That report reads millions of rows.
- It uses CPU, memory and disk, and it can hold locks while it runs.
- Meanwhile every checkout waits for the same resources, so pages slow down and some orders may even time out.
Customers don't care about our reports. They just see a slow shop. That is why heavy analytics stays away from the production database.
Let's create some sample data
We create two versions of the same TB Shop data. The OLTP side is fully normalized: countries, cities, customers, orders and order_items, where each customer only points to a city by ID. The warehouse side is a tiny star schema: dim_customer, which already has the city and country on it, and fact_sales.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS cities;
DROP TABLE IF EXISTS countries;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
-- OLTP: the TB Shop database, fully normalized
CREATE TABLE countries (
country_id INT PRIMARY KEY,
country VARCHAR(50)
);
CREATE TABLE cities (
city_id INT PRIMARY KEY,
city VARCHAR(50),
country_id INT REFERENCES countries(country_id)
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city_id INT REFERENCES cities(city_id)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT,
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO countries VALUES (1, 'Canada'), (2, 'USA');
INSERT INTO cities VALUES
(1, 'Toronto', 1), (2, 'Dallas', 2), (3, 'Seattle', 2), (4, 'Vancouver', 1);
INSERT INTO customers VALUES
(1, 'Sara Khan', 1), (2, 'Omar Ali', 2), (3, 'Lina Park', 3), (4, 'Mei Chen', 4);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05'), (1002, 2, '2026-01-09'), (1003, 1, '2026-02-02'),
(1004, 3, '2026-02-14'), (1005, 4, '2026-03-03');
INSERT INTO order_items VALUES
(1001, 101, 1, 35.00), (1001, 102, 2, 9.00), (1002, 103, 1, 25.00),
(1003, 104, 1, 30.00), (1004, 101, 2, 35.00), (1004, 103, 1, 25.00),
(1005, 102, 3, 9.00);
-- Warehouse: the same data as a tiny star schema
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50)
);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_id INT,
customer_key INT,
amount DECIMAL(10,2)
);
INSERT INTO dim_customer
SELECT c.customer_id, c.name, ci.city, co.country
FROM customers c
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id;
INSERT INTO fact_sales
SELECT ROW_NUMBER() OVER (ORDER BY oi.order_id, oi.product_id),
oi.order_id, o.customer_id, oi.quantity * oi.price
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id;Let's look at the customers in the OLTP database.
SELECT * FROM customers ORDER BY 1;| customer_id | name | city_id |
|---|---|---|
| 1 | Sara Khan | 1 |
| 2 | Omar Ali | 2 |
| 3 | Lina Park | 3 |
| 4 | Mei Chen | 4 |
Each customer has a city_id, not a city name. To find out which country a customer is in, we have to go through cities and then countries.
Now the same customers in the warehouse.
SELECT * FROM dim_customer ORDER BY 1;| customer_key | name | city | country |
|---|---|---|---|
| 1 | Sara Khan | Toronto | Canada |
| 2 | Omar Ali | Dallas | USA |
| 3 | Lina Park | Seattle | USA |
| 4 | Mei Chen | Vancouver | Canada |
Here the city and country sit right on the customer row. Lina Park, customer 3, lives in Seattle, USA. Keep her in mind, because she moves later in this post.
Step 1: Sales by country on the OLTP database
This is reason 2: normalized tables mean many joins. Normalized tables are great for writes, but a simple business question like "sales by country" has to walk the whole chain of tables: order items, orders, customers, cities and countries.
SELECT co.country,
SUM(oi.quantity * oi.price) AS sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id
GROUP BY co.country
ORDER BY sales DESC;| country | sales |
|---|---|
| USA | 120.00 |
| Canada | 110.00 |
USA has 120.00 and Canada has 110.00, and it took four joins to get there. On big tables every extra join costs time, and every join is another place to make a mistake.
Step 2: The same question on the warehouse
Now let's ask the same question on the star schema. The country already sits on the customer dimension, so we only need to join the fact table to it.
SELECT c.country,
SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer c
ON c.customer_key = f.customer_key
GROUP BY c.country
ORDER BY sales DESC;| country | sales |
|---|---|
| USA | 120.00 |
| Canada | 110.00 |
Same answer, USA 120.00 and Canada 110.00, with one join. Much simpler query, and on big tables fewer joins usually means faster queries.
Step 3: Sales by city, before Lina moves
This is reason 3: an OLTP database usually keeps only the current state. Let's first run a sales by city report. Lina lives in Seattle, so her February order counts as Seattle sales.
SELECT ci.city,
SUM(oi.quantity * oi.price) AS sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
GROUP BY ci.city
ORDER BY ci.city;| city | sales |
|---|---|
| Dallas | 25.00 |
| Seattle | 95.00 |
| Toronto | 83.00 |
| Vancouver | 27.00 |
Seattle shows 95.00, which is Lina's order 1004. Toronto shows 83.00, which is Sara Khan's two orders.
Step 4: Lina moves, and the application overwrites her city
Now Lina moves to Toronto and updates her address on the website. The application does what OLTP applications do: it replaces the old value.
UPDATE customers
SET city_id = 1 -- Toronto
WHERE customer_id = 3;The statement just sets her city_id to 1, which is Toronto. No trace is left that Lina ever lived in Seattle.
Step 5: The same report, run again
Nothing about February changed. Let's run exactly the same sales by city report again.
SELECT ci.city,
SUM(oi.quantity * oi.price) AS sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
GROUP BY ci.city
ORDER BY ci.city;| city | sales |
|---|---|
| Dallas | 25.00 |
| Toronto | 178.00 |
| Vancouver | 27.00 |
Seattle has disappeared from the report, and Toronto jumped from 83.00 to 178.00, because Lina's old order now counts as Toronto sales. The history was rewritten, and nobody can tell what February really looked like. A data warehouse keeps that history with slowly changing dimensions, so it can remember that Lina was in Seattle when she placed the order, and old reports stay true.
Reason 4: One database sees only one system
The last reason is that one OLTP database only sees one part of the business. Orders live in the shop database, payments come from a payment provider, marketing data sits in a CRM and stock levels in the ERP system.
A question like "which marketing campaign brought in our most profitable customers?" needs all of them together. Querying each system separately is slow and error prone. A data warehouse brings them into one place, cleaned and matched, so one query can answer it.
What teams do instead
Small companies often start with a read replica: a copy of the OLTP database that only serves reports. It protects the shop from the load. But a replica still has all the joins, still has no history, and still sees only one system.
| Protects the app | Simple queries | Keeps history | All sources | |
|---|---|---|---|---|
| Reports on OLTP | ✗ | ✗ | ✗ | ✗ |
| Read replica | ✓ | ✗ | ✗ | ✗ |
| Data warehouse | ✓ | ✓ | ✓ | ✓ |
The full answer is a data warehouse, or a lakehouse: copy the data out with ETL or ELT, reshape it into facts and dimensions, keep history, and combine every source.
Don't say "never run reports on OLTP". Small operational reports, like today's open orders, are fine. Heavy, historical, cross-system analytics belongs in the warehouse.
Follow-up questions interviewers ask
Is a read replica enough?
It protects the application from load, but it keeps the OLTP design: many joins, no history, one source.
Are operational reports on OLTP always wrong?
No. Small, current-state reports (today's open orders) are fine. Heavy historical and cross-system analytics belong in the warehouse.
How does a warehouse keep history?
With slowly changing dimensions (for example Type 2 rows with start and end dates) and append-only fact tables.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #2 - Why not run analytics on OLTP?
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q02; SET search_path TO dwh_q02;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS cities;
DROP TABLE IF EXISTS countries;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
-- OLTP: the TB Shop database, fully normalized
CREATE TABLE countries (
country_id INT PRIMARY KEY,
country VARCHAR(50)
);
CREATE TABLE cities (
city_id INT PRIMARY KEY,
city VARCHAR(50),
country_id INT REFERENCES countries(country_id)
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city_id INT REFERENCES cities(city_id)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT,
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO countries VALUES (1, 'Canada'), (2, 'USA');
INSERT INTO cities VALUES
(1, 'Toronto', 1), (2, 'Dallas', 2), (3, 'Seattle', 2), (4, 'Vancouver', 1);
INSERT INTO customers VALUES
(1, 'Sara Khan', 1), (2, 'Omar Ali', 2), (3, 'Lina Park', 3), (4, 'Mei Chen', 4);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05'), (1002, 2, '2026-01-09'), (1003, 1, '2026-02-02'),
(1004, 3, '2026-02-14'), (1005, 4, '2026-03-03');
INSERT INTO order_items VALUES
(1001, 101, 1, 35.00), (1001, 102, 2, 9.00), (1002, 103, 1, 25.00),
(1003, 104, 1, 30.00), (1004, 101, 2, 35.00), (1004, 103, 1, 25.00),
(1005, 102, 3, 9.00);
-- Warehouse: the same data as a tiny star schema
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50)
);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_id INT,
customer_key INT,
amount DECIMAL(10,2)
);
INSERT INTO dim_customer
SELECT c.customer_id, c.name, ci.city, co.country
FROM customers c
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id;
INSERT INTO fact_sales
SELECT ROW_NUMBER() OVER (ORDER BY oi.order_id, oi.product_id),
oi.order_id, o.customer_id, oi.quantity * oi.price
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id;
-- Step 1: Sales by country on the OLTP database (4 joins)
SELECT co.country,
SUM(oi.quantity * oi.price) AS sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
JOIN countries co ON co.country_id = ci.country_id
GROUP BY co.country
ORDER BY sales DESC;
-- Step 2: The same question on the warehouse (1 join)
SELECT c.country,
SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_customer c
ON c.customer_key = f.customer_key
GROUP BY c.country
ORDER BY sales DESC;
-- Step 3: Sales by city - before Lina moves
SELECT ci.city,
SUM(oi.quantity * oi.price) AS sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
GROUP BY ci.city
ORDER BY ci.city;
-- Step 4: Lina moves: the application overwrites her city
UPDATE customers
SET city_id = 1 -- Toronto
WHERE customer_id = 3;
-- Step 5: Sales by city - the same report, run again
SELECT ci.city,
SUM(oi.quantity * oi.price) AS sales
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN cities ci ON ci.city_id = c.city_id
GROUP BY ci.city
ORDER BY ci.city;
Key points
- Heavy reports compete with customers and slow the app down
- Normalized OLTP tables mean many joins for simple questions
- OLTP overwrites history; old reports change
- One OLTP database sees only one system
- A read replica helps with load; a warehouse solves all four
Question 3 — What is a data warehouse? What is a data warehouse?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.