# OLTP vs OLAP: Why Analytics Needs a Different Database

> Row stores vs column stores, normalized vs dimensional schemas, and why you do not run dashboards on the production database.

- Source: https://akshaythakare.com/notes/data-warehousing/oltp-vs-olap
- Topic: Data Warehousing
- Level: beginner
- Reading time: 2 min
- Tags: data-warehousing, fundamentals, interview
- Updated: 2026-10-11

## Two workloads

| | OLTP (operational) | OLAP (analytical) |
| --- | --- | --- |
| Example | Checkout, bank transfer | Monthly revenue by region |
| Query shape | Read or write a few rows by key | Scan millions of rows, few columns |
| Concurrency | Thousands of tiny transactions | Fewer, heavy queries |
| Schema | Normalized (3NF) | Dimensional (star schema) |
| Storage | Row-oriented | Column-oriented |
| Examples | PostgreSQL, MySQL, DynamoDB | Snowflake, BigQuery, Redshift, Databricks SQL |

## Why column storage wins for analytics

```sql
SELECT region, SUM(net_sales) FROM sales GROUP BY region;
```

A row store reads every column of every row. A column store reads only `region` and `net_sales`, and similar values in a column compress very well.

## Why not query production directly?

- Heavy scans slow down customer-facing transactions.
- Operational schemas are normalized for writes, so analytical questions need many joins.
- History is often overwritten, so "what was true last month" is lost.

So data is copied (ETL/ELT) from OLTP sources into a warehouse modeled for analysis.

> **Tip: Interview framing**
>
> "OLTP optimises many small writes on rows; OLAP optimises big scans on columns. Pipelines move data from one to the other and reshape it on the way."