OLTP systems power applications — fast, small, row-level transactions. OLAP systems power analytics — large, column-scanning aggregations. Almost every foundational data-engineering decision (which database, which schema, where analytics runs) flows from this split, and confusing the two produces systems that are slow at both jobs. This is bedrock knowledge that shows up in interviews constantly.
Why this appears in interviews
"Why not just run analytics on the production database?" and "why is BigQuery faster than Postgres for this query?" are near-universal warm-ups. They test whether you understand why two fundamentally different workloads need two fundamentally different systems — storage layout, schema, and execution model. Getting this right frames every later storage and modeling decision.
The mental model — a bank teller vs an accountant
OLTP is a bank teller: process one customer's deposit in milliseconds, touching a handful of rows. Speed on individual rows is everything. OLAP is an accountant doing the year-end audit: aggregate millions of transactions into totals by region and month. Speed on large scans and aggregations is everything. The teller's system would be hopeless at the audit; the audit system would be hopeless at live deposits. They're optimized for opposite access patterns.
OLTP — Online Transaction Processing
- Purpose — power live applications (checkout, login, messaging).
- Query pattern — many short queries touching few rows: "get order 12345," "update this balance." High concurrency of tiny operations.
- Optimized for — low-latency reads/writes, high write throughput, and ACID transactional integrity (a transfer must debit and credit atomically).
- Storage — row-oriented: a whole record sits together, so reading/writing one row is one seek.
- Schema — normalized (3NF): many small tables with foreign keys, minimizing redundancy so updates are consistent and cheap.
- Indexes — B-tree indexes for fast point lookups.
- Examples — PostgreSQL, MySQL, DynamoDB, SQL Server.
OLAP — Online Analytical Processing
- Purpose — power analytics, dashboards, and reporting.
- Query pattern — few long queries scanning millions of rows: "revenue by category per month across 2025 by region." Low concurrency of huge operations.
- Optimized for — high read throughput over large data via columnar compression and massively parallel processing (MPP).
- Storage — columnar: each column stored together, so an aggregation reads only the columns it needs and compresses well.
- Schema — denormalized (star schema): fewer, wider tables (facts + dimensions) so analytical queries avoid expensive many-table joins.
- Examples — Snowflake, BigQuery, Redshift, ClickHouse, Databricks SQL.
Why the storage layout is the crux
The deepest version of the answer: OLTP is row-oriented because applications read/write whole records; OLAP is columnar because analytics read a few columns across many rows. To sum revenue, a row store must read every column of every row; a column store reads only the revenue column — often 100× less I/O on a wide table — and compresses it better because adjacent values are similar (dictionary/run-length encoding). Add MPP (many nodes scanning partitions in parallel) and OLAP crushes analytical queries that would cripple an OLTP database. That's the real reason "BigQuery beats Postgres" — not that Postgres is bad, but that it's built for the opposite workload.
HTAP and the modern blur
Interviewers may probe the edges. HTAP (hybrid transactional/analytical) systems and engines like DuckDB (embedded OLAP) or Postgres columnar extensions blur the line for smaller scales, and CDC/streaming (later in the track) is how OLTP data continuously feeds OLAP systems. But the mental model holds: at scale you separate the two, and you replicate transactional data into an analytical system rather than querying the app database directly.
How this drives real decisions
- Don't run analytics on your production OLTP database — a big scan locks resources and degrades the app; replicate to a warehouse instead.
- Don't use a normalized (3NF) schema in your warehouse — join-heavy 3NF is right for OLTP but slow for OLAP; denormalize into star schemas.
- Pick the system by workload — transactional feature → OLTP DB; dashboards/reports/ML aggregates → OLAP warehouse.
Common interview mistakes
Mistake 1: Running analytics on the production OLTP DB. Scans slow the app; analytics belong on a separate OLAP system fed by replication/CDC.
Mistake 2: Normalizing the warehouse. 3NF is for OLTP; OLAP denormalizes (star schema) on purpose to avoid joins.
Mistake 3: Not explaining why columnar is faster. The point is reading fewer columns + better compression + parallel execution, not "it's just faster."
Mistake 4: Ignoring how data gets from OLTP to OLAP. Batch ETLETL / ELTExtract-Transform-Load / Extract-Load-Transform — patterns for moving data from source systems to a data warehouse. or CDC replicates transactional data into the warehouse; they're connected, not isolated.
Key vocabulary
- OLTP — Row-oriented, normalized systems optimized for fast, small transactions; power applications.
- OLAP — Columnar, denormalized systems optimized for large aggregations; power analytics.
- Row vs columnar storage — Whole-record layout (OLTP) vs per-column layout (OLAP); the root of the performance split.
- Normalization (3NF) — Minimizing redundancy with many related tables; right for OLTP, avoided in OLAP.
- Star schema — Denormalized facts + dimensions optimized for analytical queries.
- MPP / ACID — Massively parallel processing (OLAP scale) / transactional guarantees (OLTP integrity).