Data modeling · Foundations

OLTP vs OLAP

EasyOLTPOLAPworkloadsarchitecture

Interview question

What is the difference between OLTP and OLAP?

How to answer

The short answer

(Online Transaction Processing) systems run the business day to day: place an order, update inventory, charge a card. They optimize for many small, fast reads and writes with strong consistency.

(Online Analytical Processing) systems analyze the business: revenue by region, funnel conversion, cohort retention. They optimize for large scans and aggregations.

Output
OLTP:  UPDATE orders SET status = 'paid' WHERE id = 123;   -- milliseconds
OLAP:  SELECT region, SUM(amount)
       FROM fct_orders
       WHERE order_date >= '2026-01-01'
       GROUP BY region;                                   -- heavy aggregate

Mental model

Think of the checkout app as OLTP (many cashiers ringing up one sale each). Think of the BI dashboard as OLAP (one analyst asking "how did all cashiers do this quarter?").

OLTPOLAP
PurposeRun the businessAnalyze the business
WorkloadPoint lookups, short writesScans, group-bys
ModelingOften normalized (3NF)Often dimensional / denormalized
ExamplesPostgres app DB, MySQLSnowflake, BigQuery, Redshift

Platform implication

Do not run heavy analytics on the primary OLTP database. Replicate or or into a warehouse or lakehouse so production apps stay fast.

Interview answer

> "OLTP is transactional, row-focused, optimized for writes. OLAP is analytical, set-focused, optimized for aggregates. Data engineers usually move data from OLTP sources into an OLAP warehouse."