⚖️OLTP vs OLAP

Same SQL vocabulary, different physics

OLTP favors short transactions, selective indexes, and row-oriented storage. OLAP favors wide scans, vectorized aggregates, and dimensional layouts, often columnar. Mixing them on one primary without guardrails is how BI queries steal p99 from checkout.

📊At a glance

Interview answers should name shapes, not buzzwords alone.

Dimension	OLTP	OLAP
Query shape	Point lookups, small joins, many short txns	Scans, heavy aggregates, fewer longer queries
Schema	Normalized 3NF, tight write paths	Star / wide denormalized tables for read locality
Storage	Row store, selective secondary indexes	Column store, lakehouse parquet, often no row locks on files

SQL

OLTP-style

SELECT balance FROM wallets WHERE user_id = 9 FOR UPDATE;

✓Illustrative result set

balance
150

SQL

OLAP-style

SELECT d.month, SUM(f.revenue_usd) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
GROUP BY d.month
ORDER BY d.month;

✓Illustrative result set

month	revenue
2026-01	1840000
2026-02	1920000

Workload collision

Copying OLTP indexing strategies into a warehouse without measuring scan benefit.
Running heavy ETL during OLTP peak without throttles or resource groups.
Expecting transactional row locks on external parquet engines.

Route and schedule

Use connection policies: OLTP pool vs warehouse role with concurrency caps.
Document freshness SLAs per dataset; prefer replicas or lake paths for ad hoc analytics.
Teach both cost models with concrete examples from your stack.

Isolation

OLAP engines may expose snapshot or eventually consistent reads, do not assume SERIALIZABLE semantics across every “SQL” endpoint in the catalog.

TL;DR

OLTP databases optimize short, concurrent, row-level transactions (apps, POS, claims): normalize for write integrity and low latency. OLAP / warehouses optimize bulk scans and aggregations, often denormalized star schemas, columnar storage, and looser consistency for analytics. Same SQL keywords, very different tuning: do not copy OLTP index rules wholesale into a column store.