OLTP vs OLAP explained for beginners, with SQL examples

When you order food in an app, the app records one order: who, what, how much. When the restaurant owner opens a report called "monthly revenue by city", the system reads thousands of orders and adds them up. Both use the same orders, yet they ask for very different things. OLTP and OLAP are the names for those two kinds of work.

The two names

• OLTP means Online Transaction Processing. It runs the business as it happens: place an order, update an address, take a payment. Each action touches 1 record or a few, and it must be very fast and always correct.
• OLAP means Online Analytical Processing. It studies what happened: totals, trends, comparisons. Each question reads many records and summarises them.

See the difference in SQL

Here is one table of 8 orders. The queries ran in DuckDB, and each output is exactly what it printed.

CREATE TABLE orders (
  order_id   INTEGER,
  customer   VARCHAR,
  city       VARCHAR,
  order_date DATE,
  amount     INTEGER,
  status     VARCHAR
);

INSERT INTO orders VALUES
  (1, 'Asha',  'Pune',      '2026-03-01', 1200, 'paid'),
  (2, 'Ravi',  'Mumbai',    '2026-03-01',  800, 'paid'),
  (3, 'Meena', 'Chennai',   '2026-03-02',  450, 'refunded'),
  (4, 'Kiran', 'Pune',      '2026-03-02', 2500, 'paid'),
  (5, 'Asha',  'Pune',      '2026-03-03',  300, 'paid'),
  (6, 'Divya', 'Bengaluru', '2026-03-03', 1500, 'pending'),
  (7, 'Ravi',  'Mumbai',    '2026-03-04',  950, 'paid'),
  (8, 'Meena', 'Chennai',   '2026-03-05', 2200, 'paid');

OLTP-style work. Small actions about one order. Customer 6 pays, so you update that one order, and then you look it up:

UPDATE orders SET status = 'paid' WHERE order_id = 6;

SELECT order_id, customer, amount, status
FROM orders
WHERE order_id = 6;

order_id | customer | amount | status
---------+----------+--------+-------
6        | Divya    | 1500   | paid
(1 row)

1 row touched, 1 row read, and it must be instant and correct, because a customer is waiting. A real shop does thousands of these every minute, and each one must not interfere with the others.

OLAP-style work. One question about everything. How much did each city spend, and how many orders was that?

SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY city
ORDER BY revenue DESC;

city      | orders | revenue
----------+--------+--------
Pune      | 3      | 4000
Chennai   | 1      | 2200
Mumbai    | 2      | 1750
Bengaluru | 1      | 1500
(4 rows)

Every row was read and then summarised into 1 row per city. Over 8 rows that is instant. Over 800 million rows it is a heavy job, and it must not slow down the shop's order screen while it runs.

Why they need different systems

The two workloads pull a database in opposite directions.

How the data is stored. An OLTP database stores data row by row, so all the details of order 6 sit together, which is perfect for fetching one order. An OLAP system usually stores data column by column, so all the amount values sit together. A revenue report only needs the amount and city columns, so it can skip all the others, which saves a lot of reading. The file formats guide shows the same idea in files.

What is being protected. OLTP is about correctness for each transaction: if a payment and a stock update must both happen, they either both happen or neither does. OLAP is mostly read-only, so it cares more about reading huge amounts quickly.

Keeping them apart. If analysts ran a giant report on the same database the shop uses, they could slow the shop down. So companies copy data from the OLTP database into a separate OLAP system on a schedule, which is exactly what ETL and ELT pipelines do.

OLTP and OLAP side by side

The data shapes in the table connect to two other guides: OLTP tables are usually normalized, and OLAP tables often follow a star schema. Some newer systems called HTAP try to do both in one place, but the two ideas remain the vocabulary every data engineer uses.

A one-line answer for an interview: "OLTP records transactions quickly and correctly, with many small reads and writes. OLAP analyses data, with fewer large queries that scan and aggregate many rows." Now that you can tell them apart, read where OLAP data lives.