In this post we are going to cover the difference between OLTP and OLAP. It is usually the very first data warehouse interview question, and everything else in the series builds on it.
OLTP stands for Online Transaction Processing. These are the systems that run the business, like the database behind an online shop that records orders, payments and sign-ups as they happen. OLAP stands for Online Analytical Processing. These systems analyze the business: they hold lots of history and answer questions like total sales by country or top products by month.
We will look at where each one sits in a company, then run a real OLTP query, a real OLTP write and a real OLAP query on a tiny shop called TB Shop, so you can see the difference in the SQL itself.
Question 1 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
OLTP (Online Transaction Processing) systems run the business: many small, fast reads and writes on current data. OLAP (Online Analytical Processing) systems analyze the business: fewer, large queries that scan and aggregate historical data.
In one line: OLTP handles many small, fast transactions on current data, and OLAP handles fewer, big analytical queries on historical data.
Where each system sits
Here is the path data takes in a typical company:
- Customers sign up, browse and buy on the TB Shop website or mobile app.
- Each of those actions is a small transaction, written straight into the OLTP database, for example PostgreSQL, MySQL, SQL Server or Oracle.
- Once a day, or more often, an ETL pipeline copies the data out: extract, transform and load.
- The data lands in the data warehouse, the OLAP side, for example Snowflake, BigQuery, Redshift or Databricks.
- Analysts and dashboards query the warehouse, and the business makes decisions from it.
The important part is the last point: reports hit the warehouse, not the shop's database, so the website never feels the load of those reports.
Let's create some sample data
We create both sides of TB Shop. The OLTP side is a normalized application database with customers, products, orders and order_items. The OLAP side is the same data reshaped into a tiny star schema: a dim_customer dimension and a fact_sales fact table, loaded from the OLTP tables at the end of the script.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
-- OLTP side: the TB Shop application database (normalized)
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
status VARCHAR(20)
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'Canada'),
(2, 'Omar Ali', 'Dallas', 'USA'),
(3, 'Lina Park', 'Seattle', 'USA'),
(4, 'Mei Chen', 'Vancouver', 'Canada');
INSERT INTO products VALUES
(101, 'Laptop Stand', 'Accessories', 35.00),
(102, 'USB-C Cable', 'Accessories', 9.00),
(103, 'Wireless Mouse', 'Electronics', 25.00),
(104, 'Desk Lamp', 'Home', 30.00);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered'),
(1002, 2, '2026-01-09', 'Delivered'),
(1003, 1, '2026-02-02', 'Shipped'),
(1004, 3, '2026-02-14', 'Delivered'),
(1005, 4, '2026-03-03', 'Placed');
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);
-- OLAP side: the same data reshaped for analysis (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,
order_date DATE,
customer_key INT,
product_key INT,
quantity INT,
amount DECIMAL(10,2)
);
INSERT INTO dim_customer
SELECT customer_id, name, city, country FROM customers;
INSERT INTO fact_sales
SELECT ROW_NUMBER() OVER (ORDER BY oi.order_id, oi.product_id),
o.order_id, o.order_date, o.customer_id, oi.product_id,
oi.quantity, oi.quantity * oi.price
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id;Let's look at the orders in the OLTP database.
SELECT * FROM orders ORDER BY 1;| order_id | customer_id | order_date | status |
|---|---|---|---|
| 1001 | 1 | 2026-01-05 | Delivered |
| 1002 | 2 | 2026-01-09 | Delivered |
| 1003 | 1 | 2026-02-02 | Shipped |
| 1004 | 3 | 2026-02-14 | Delivered |
| 1005 | 4 | 2026-03-03 | Placed |
Five orders from four customers. Notice that an order only points to its customer by customer_id. The customer's city lives only in the customers table, which is what normalized means: each fact is stored once.
Now the warehouse side.
SELECT * FROM fact_sales ORDER BY 1;| sale_id | order_id | order_date | customer_key | product_key | quantity | amount |
|---|---|---|---|---|---|---|
| 1 | 1001 | 2026-01-05 | 1 | 101 | 1 | 35.00 |
| 2 | 1001 | 2026-01-05 | 1 | 102 | 2 | 18.00 |
| 3 | 1002 | 2026-01-09 | 2 | 103 | 1 | 25.00 |
| 4 | 1003 | 2026-02-02 | 1 | 104 | 1 | 30.00 |
| 5 | 1004 | 2026-02-14 | 3 | 101 | 2 | 70.00 |
| 6 | 1004 | 2026-02-14 | 3 | 103 | 1 | 25.00 |
| 7 | 1005 | 2026-03-03 | 4 | 102 | 3 | 27.00 |
The fact table has one row per order line, seven rows in total, with the amount already worked out as quantity times price. For example, sale 2 is two USB-C cables at 9.00, so the amount is 18.00.
Step 1: An OLTP read - one customer opens "My Orders"
Sara Khan is customer 1. When she opens "My Orders" on the website, the app runs a query like this one: look up one customer by key and get back a handful of rows.
SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
ORDER BY order_date;| order_id | order_date | status |
|---|---|---|
| 1001 | 2026-01-05 | Delivered |
| 1003 | 2026-02-02 | Shipped |
Only Sara's two orders come back: 1001, which is Delivered, and 1003, which is Shipped. That is the OLTP pattern. A real shop runs thousands of these tiny lookups every second, each touching a few rows and finishing in milliseconds.
Step 2: An OLTP write - Sara places a new order
Now Sara buys something. A new order is one row in orders plus one row per product in order_items. Small, fast writes.
INSERT INTO orders VALUES (1006, 1, '2026-03-10', 'Placed');
INSERT INTO order_items VALUES (1006, 103, 1, 25.00);Now let's check the result.
SELECT * FROM orders ORDER BY order_id;| order_id | customer_id | order_date | status |
|---|---|---|---|
| 1001 | 1 | 2026-01-05 | Delivered |
| 1002 | 2 | 2026-01-09 | Delivered |
| 1003 | 1 | 2026-02-02 | Shipped |
| 1004 | 3 | 2026-02-14 | Delivered |
| 1005 | 4 | 2026-03-03 | Placed |
| 1006 | 1 | 2026-03-10 | Placed |
Order 1006 is now in the OLTP database with status Placed, so the table has six orders. Notice that we did not touch fact_sales. The warehouse does not change with every click; it picks up order 1006 on its next load.
Step 3: An OLAP query - total sales by country
A manager does not ask about one order. She asks: what are our total sales by country? To answer that, the query has to read every sale, join it to the customer dimension, group by country and add up the amounts.
SELECT c.country,
SUM(f.amount) AS total_sales
FROM fact_sales f
JOIN dim_customer c
ON c.customer_key = f.customer_key
GROUP BY c.country
ORDER BY total_sales DESC;| country | total_sales |
|---|---|
| USA | 120.00 |
| Canada | 110.00 |
USA has 120.00 and Canada has 110.00. Here the query read only seven sales, but in a real warehouse it would scan millions of rows and still return a tiny summary of two rows. That is the OLAP pattern: few queries, huge scans, small answers. The new order 1006 from Step 2 is not in this total, because it has not been loaded into the warehouse yet.
OLAP systems are built for exactly this kind of query. They store data by column, compress it, and spread the work across many machines, so big scans stay fast.
OLTP vs OLAP side by side
Here is the comparison you can walk through in the interview.
| OLTP | OLAP | |
|---|---|---|
| PURPOSE | Run the business | Analyze the business |
| QUERIES | Read / write a few rows by key | Scan and aggregate millions of rows |
| DATA | Current state, up to the second | History: months or years |
| DESIGN | Normalized (3NF) | Star schema (denormalized) |
| USERS | Customers, applications | Analysts, dashboards, data science |
| EXAMPLES | PostgreSQL, MySQL, SQL Server, Oracle | Snowflake, BigQuery, Redshift, Databricks |
OLTP tables are normalized to avoid duplicates and keep writes fast. OLAP tables are usually shaped into a star schema to keep reads simple and fast.
Don't say "OLAP is just a bigger database." The difference is the workload and the design, not the size.
Follow-up questions interviewers ask
Can we run reports on the OLTP database?
For small data yes, but heavy analytical queries compete with the application for CPU, memory and locks, and can slow it down.
Is a data warehouse the same as OLAP?
OLAP is the workload (analytical queries). A data warehouse is the system built to serve it.
How does data move from OLTP to OLAP?
Through ETL or ELT pipelines, usually on a schedule (batch) or continuously (streaming / CDC).
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 #1 - OLTP vs OLAP
-- 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_q01; SET search_path TO dwh_q01;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_customer;
-- OLTP side: the TB Shop application database (normalized)
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
status VARCHAR(20)
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'Canada'),
(2, 'Omar Ali', 'Dallas', 'USA'),
(3, 'Lina Park', 'Seattle', 'USA'),
(4, 'Mei Chen', 'Vancouver', 'Canada');
INSERT INTO products VALUES
(101, 'Laptop Stand', 'Accessories', 35.00),
(102, 'USB-C Cable', 'Accessories', 9.00),
(103, 'Wireless Mouse', 'Electronics', 25.00),
(104, 'Desk Lamp', 'Home', 30.00);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered'),
(1002, 2, '2026-01-09', 'Delivered'),
(1003, 1, '2026-02-02', 'Shipped'),
(1004, 3, '2026-02-14', 'Delivered'),
(1005, 4, '2026-03-03', 'Placed');
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);
-- OLAP side: the same data reshaped for analysis (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,
order_date DATE,
customer_key INT,
product_key INT,
quantity INT,
amount DECIMAL(10,2)
);
INSERT INTO dim_customer
SELECT customer_id, name, city, country FROM customers;
INSERT INTO fact_sales
SELECT ROW_NUMBER() OVER (ORDER BY oi.order_id, oi.product_id),
o.order_id, o.order_date, o.customer_id, oi.product_id,
oi.quantity, oi.quantity * oi.price
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id;
-- Step 1: An OLTP query: one customer opens "My Orders"
SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
ORDER BY order_date;
-- Step 2: An OLTP write: Sara places a new order
INSERT INTO orders VALUES (1006, 1, '2026-03-10', 'Placed');
INSERT INTO order_items VALUES (1006, 103, 1, 25.00);
-- check the result
SELECT * FROM orders ORDER BY order_id;
-- Step 3: An OLAP query: total sales by country
SELECT c.country,
SUM(f.amount) AS total_sales
FROM fact_sales f
JOIN dim_customer c
ON c.customer_key = f.customer_key
GROUP BY c.country
ORDER BY total_sales DESC;
Key points
- OLTP = Online Transaction Processing: runs the business
- OLAP = Online Analytical Processing: analyzes the business
- OLTP: small reads and writes by key, current data, normalized
- OLAP: big scans and aggregates over history, star schema
- Data moves from OLTP to OLAP through ETL or ELT
Question 2 — Why not run reports on OLTP? Why can't we just run our reports on the OLTP database?
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.