OLTP vs OLAP: Differences, Examples and How Data Moves Between Them

OLTP (online transaction processing) systems run the business: they record each order, payment and login as it happens. OLAP (online analytical processing) systems help people understand the business: they answer questions such as “how much did we sell per month and category?” across years of history. Most companies need both, and a large part of a data engineer’s job is moving data from the first kind to the second.

A quick way to remember it: OLTP answers “what is happening to this record now?”, OLAP answers “what happened across all records over time?”.

The difference in one table

	OLTP	OLAP
Purpose	Run daily operations	Analyse and report
Typical query	Read or change a few rows by key	Scan and aggregate millions of rows
Writes	Many small inserts and updates, all day	Bulk loads, often hourly or daily
Users	The application and its customers	Analysts, dashboards, data scientists
Schema	Normalised (3NF): each fact stored once	Denormalised: star schemas, wide tables
Storage	Row-oriented	Usually column-oriented
History	Current state, old rows often overwritten	Years of history, kept on purpose
What matters most	Fast, correct transactions (ACID)	Fast scans, cheap storage, consistent definitions
Examples	PostgreSQL, MySQL, SQL Server, Oracle behind an app	Snowflake, BigQuery, Redshift, Databricks SQL, ClickHouse

Data warehouse vs database

People often ask “what is the difference between a database and a data warehouse?”. A data warehouse is a database, but one built for OLAP. When people say “database” in this comparison, they usually mean an operational OLTP database such as PostgreSQL or MySQL behind an application:

	Operational database	Data warehouse
Holds	Current data for one application	History from many sources, integrated
Designed for	Many small reads and writes	Large scans and aggregations
Updated	Continuously, by the application	In loads, hourly or daily, or by streaming
Model	Normalised tables	Star schemas, wide tables, marts
Used by	The application	Analysts, dashboards, data science

The rest of this lesson shows why they are built so differently.

Sample data: one small shop

Kestrel Market, the fictional shop used throughout this course, keeps its orders in a normalised PostgreSQL database. Customers, products, orders and order lines each have their own table:

CREATE TABLE customers (customer_id int PRIMARY KEY, name text NOT NULL, city text NOT NULL);
CREATE TABLE products  (product_id int PRIMARY KEY, name text NOT NULL, category text NOT NULL,
                        price numeric(8,2) NOT NULL, stock int NOT NULL CHECK (stock >= 0));
CREATE TABLE orders    (order_id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers,
                        ordered_at timestamp NOT NULL);
CREATE TABLE order_items (order_id int REFERENCES orders, product_id int REFERENCES products,
                          quantity int NOT NULL CHECK (quantity > 0), unit_price numeric(8,2) NOT NULL,
                          PRIMARY KEY (order_id, product_id));

INSERT INTO customers VALUES (1, 'Asha', 'Pune'), (2, 'Tom', 'Leeds'), (3, 'Mei', 'Pune');
INSERT INTO products VALUES (10, 'Trail mug', 'Kitchen', 12.00, 40), (11, 'Desk lamp', 'Home', 30.00, 5),
                            (12, 'Tea towel', 'Kitchen', 6.50, 100);
INSERT INTO orders VALUES (100, 1, '2026-09-03 10:15'), (101, 2, '2026-09-18 19:40'), (102, 3, '2026-10-02 08:05');
INSERT INTO order_items VALUES (100, 10, 2, 12.00), (100, 12, 3, 6.50), (101, 11, 1, 30.00),
                               (102, 10, 1, 12.00), (102, 11, 2, 30.00);

An OLTP workload: one order

When Tom buys a desk lamp, the application writes a new order, adds its line and lowers the stock. These three changes must succeed or fail together, so they run in one transaction:

-- OLTP: one customer places one order. Touch a few rows, by key, inside a transaction.
BEGIN;
INSERT INTO orders VALUES (103, 2, '2026-10-05 12:30');
INSERT INTO order_items VALUES (103, 11, 1, 30.00);
UPDATE products SET stock = stock - 1 WHERE product_id = 11;
COMMIT;

SELECT product_id, name, stock FROM products WHERE product_id = 11;

 product_id |   name    | stock
------------+-----------+-------
         11 | Desk lamp |     4

Notice the shape of the work: a handful of rows, each found by its primary key, and a guarantee that the stock never goes below zero (the CHECK constraint). A busy shop runs thousands of these per second. Indexes on keys, row storage and short transactions make that fast.

An OLAP workload: revenue by month

The finance team asks a different kind of question. They want totals over every order, grouped by time and category:

-- OLAP: revenue by month and category. Read many rows, a few columns, and aggregate.
SELECT date_trunc('month', o.ordered_at)::date AS month,
       p.category,
       SUM(i.quantity * i.unit_price)           AS revenue,
       COUNT(DISTINCT o.order_id)               AS orders
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p    ON p.product_id = i.product_id
GROUP BY 1, 2
ORDER BY 1, 2;

   month    | category | revenue | orders
------------+----------+---------+--------
 2026-09-01 | Home     |   30.00 |      1
 2026-09-01 | Kitchen  |   43.50 |      1
 2026-10-01 | Home     |   90.00 |      2
 2026-10-01 | Kitchen  |   12.00 |      1

On five orders this is instant. On five hundred million order lines, the same query reads every row of three tables, joins them and sorts the result. It needs only four columns, but a row store reads whole rows from disk. This is the work OLAP systems are built for.

The same question in a warehouse shape

A warehouse usually stores the data already joined, at a declared grain (here, one row per order line), with the descriptive columns it needs:

-- The same question on a denormalised fact table, as a warehouse would store it.
CREATE TABLE fact_sales AS
SELECT o.order_id, o.ordered_at::date AS order_date, c.city, p.category,
       i.quantity, i.quantity * i.unit_price AS revenue
FROM orders o
JOIN customers c   ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p    ON p.product_id = i.product_id;

SELECT city, category, SUM(revenue) AS revenue
FROM fact_sales
GROUP BY city, category
ORDER BY revenue DESC, city;

 city  | category | revenue
-------+----------+---------
 Leeds | Home     |   60.00
 Pune  | Home     |   60.00
 Pune  | Kitchen  |   55.50

The query has no joins left. In a real warehouse, the city and category would usually live in dimension tables of a star schema, and the fact table would hold keys and measures. Either way, the model is designed for reading, not for safe updates. That is the trade-off explained in normalisation and denormalisation.

Why storage differs: rows vs columns

• A row store keeps all columns of a row together. Reading or updating one whole order is one small read, which is ideal for OLTP.
• A column store keeps each column together. A query that sums revenue reads only that column, and similar values next to each other compress very well. This is ideal for OLAP, but changing single rows is slow, so column stores prefer bulk loads.

This is why the same SQL can take seconds on a warehouse and much longer on the production database, or the other way round for a single-row update.

Why not run analytics on the production database?

Small companies often start this way, and it works for a while. It breaks down for four reasons:

1. Load. Large scans compete with customer transactions for CPU, memory and disk. A heavy report can slow down checkout.
2. History. OLTP tables keep the current state. If a customer moves city, the old value is overwritten, so last year’s report changes. A warehouse keeps history, for example with slowly changing dimensions.
3. Many sources. Analysts need orders, payments, marketing and support data together. They live in different systems.
4. Shape. A normalised schema needs many joins for every question, and analysts have to know how every table relates.

A read replica solves the first problem only. It is a reasonable first step, not a warehouse.

How data moves from OLTP to OLAP

1. Extract changes from the source, either with incremental queries on an updated_at column or with log-based change data capture, which reads the database’s transaction log without loading its tables.
2. Load the raw changes into the lake or warehouse.
3. Transform them into clean, modelled tables (facts, dimensions, wide tables), usually with SQL and a tool such as dbt. Whether you transform before or after loading is the ETL vs ELT choice.
4. Serve the results to dashboards, notebooks and machine learning features.

What about HTAP?

HTAP (hybrid transactional and analytical processing) databases try to run both workloads in one system, usually by keeping a row copy for transactions and a column copy for analysis. They reduce delay for real-time analytics on operational data. Most companies still keep a separate warehouse or lakehouse, because they need history and data from many sources, not just one application’s tables.

Common mistakes

1. Saying OLAP means “cubes”. Old OLAP tools stored pre-computed cubes. Today the term mostly means analytical databases and warehouses that aggregate on the fly.
2. Copying the OLTP schema into the warehouse unchanged and calling it a model. Analysts then rebuild the same joins in every report.
3. Forgetting history. Loading only the current state from the source loses the changes analysts need.
4. Running heavy scans on the primary database “just this once”.

What interviewers look for

• A clear one-line definition of each, with an example system.
• At least three concrete differences: workload, schema, storage and history.
• Why you separate them, and how data gets from one to the other (CDC, ELT, a modelled layer).
• Bonus: row vs column storage, and when HTAP or a read replica is enough.

You can practise this in the data engineering interview questions.

Key takeaway

OLTP systems handle many small, safe transactions on current data with a normalised, row-based design. OLAP systems handle large scans and aggregations over history with denormalised, usually columnar storage. Keep them separate, move data between them with incremental loads or CDC, and model the analytical side for the questions people ask. Next: conceptual, logical and physical data models.