DATA ARCHITECTUREWorkloads

OLTP vs OLAP: Choosing How To Structure Data

How data engineers design data that lasts — pick a topic on the left and its full breakdown loads here: the mental model, ERDs and worked schemas, trade-offs, edge cases, and the decisions that separate a durable model from a fragile one.

⏱ 18 min readTopics chapter readerLevel · Foundations
01 · Orientation

What You'll Master Here

transaction integrity and current lookup for OLTP; historical scans and aggregation for OLAP.

⏱ 4 min · Topic 1 of 16

A transit network writes one row every time a card touches a gate, and two completely different readers come back for it: the gate itself, in 80 milliseconds, and three years of demand analysis. OLTP and OLAP are the names for those two readings — not for two products competing to be the database.

Step the three moves below. Each states a question, the physical work it implies, and what asking the other system would have cost.

Core mental model

Model for the workload: transaction integrity and current lookup for OLTP; historical scans and aggregation for OLAP.

Why it matters

Running analytical scans against the transactional source can slow customer operations, while forcing a write path through a reporting model weakens integrity.

OLTP
Transactional workload optimized for many small concurrent writes and current-state reads.
OLAP
Analytical workload optimized for large historical reads, calculations, and trend analysis.
serving boundary
The system or model responsible for a particular consumer workload.
One transit network, two questionsEvery card tap is written once and read back by two completely different readers.
Move 1 · Open the gate

Does card C-4471 hold enough balance to let this passenger through, right now?

Rows touched1 rowColumns read3 of 14 columnsBudget80 ms40,000 taps/minute at peak
A row store answers this in one index seek: the whole card row sits on a single page, so one read returns balance, status and expiry together.
What you’ll be able to do1/3
  • Open the gate
  • Explain three years of demand
  • Keep both answering the same truth
OLTP and OLAP are not two products competing for the same job. They are two answers to “how should these bytes be laid out?”, and the workload picks.
Common mistake

Treating OLTP and OLAP as two names for the same tables. One workload eventually degrades the other.

Better habit

Start from query shape.

Separate source-of-truth writes from analytical reads.

What to say

I would keep the write path normalized and transactional and move historical reporting into an analytical store, because the workloads, latency, and query shapes are different.

Remember this

Choose the model from the workload, not from a fashionable schema name.