Data Engineering Foundations
Beginner 14 min read Data Engineering Foundations

OLTP vs OLAP

Why one database can't serve every workload

Online Transaction Processing and Online Analytical Processing are two workloads with opposite requirements — wide row writes versus huge scans — and they need different engines.

OLTPOLAPcolumnarrow storageworkloadsdatabase design
Prerequisites: The Data Lifecycle
Before we start

What you will learn

  • Distinguish transactional and analytical workloads by their access patterns
  • Explain why columnar storage wins for analytics
  • Choose OLTP vs OLAP when faced with a new workload
Introduction

What is this?

OLTP (Online Transaction Processing) systems handle high volumes of small, fast reads and writes — one customer order, one login. OLAP (Online Analytical Processing) systems serve complex queries that scan millions of rows to aggregate or summarize them. Because their access patterns are opposites, they are built differently and usually deployed separately.

Motivation

Why do you need it?

  • Running analytics on the production OLTP database slows the app down and distorts OLAP query shapes.
  • OLAP engines store data column-wise, letting queries read only the columns they need.
  • Separating them means each can tune its own characteristics: row-level locking for writes, compression for scans.
  • Understanding the distinction is how you know to reach for an analytics warehouse instead of 'just another table'.
Think about it

Mental model

Imagine

A point-of-sale terminal vs a supermarket annual report

Think of

OLTP vs OLAP

The terminal must record each sale in milliseconds; the annual report can spend hours aggregating millions of sales. Different jobs, different machinery.

Mechanics

How it works

  • OLTP workloads issue short transactions that read or write one row or a few rows, many times per second.
  • Transactional engines store data row-wise, so fetching a full order (all its fields) touches one contiguous block.
  • OLAP workloads issue long-running scans over millions of rows, aggregating only a few columns.
  • Analytical engines store data column-wise, so reading one column streams only that column's blocks, massively reducing I/O.
  • Columnar storage also compresses better because values in a column are similar.

Row-oriented (OLTP) vs column-oriented (OLAP) storage.

Row store
OLTP query
Column store
OLAP query
Both query
Hands-on

Examples

The same query, two engines

The same query, two engines
-- OLTP: fetch one customer row (row store shines)
SELECT * FROM customers WHERE customer_id = 83721;
 
-- OLAP: aggregate 3 columns over 2 years (column store shines)
SELECT country, SUM(revenue), AVG(session_len)
FROM daily_metrics
WHERE date BETWEEN '2025-01-01' AND '2026-12-31'
GROUP BY country;

The first query needs a single row; a row-oriented index gives it instantly. The second reads three of fifty columns over millions of rows; a columnar store reads only those three blocks.

Real world

In production

  • The payments app runs on PostgreSQL (OLTP) while the finance team's revenue reports run on BigQuery or Snowflake (OLAP).
  • A retailer uses MySQL for cart operations and a data warehouse for 'what did we sell last winter' queries.
  • Columnar stores power nearly every BI dashboard in the modern data stack.
Guidelines

Best practices

  • Never run heavy analytic queries against the source of record — export to an analytics store.
  • Model metrics at a stable grain (daily, by store) to bound scan size.
  • Push aggregation work into the OLAP engine instead of bringing millions of rows into app code.
  • Use materialized views or aggregations for the few hottest dashboard queries.
Watch out

Common mistakes

  • Putting 'just a summary table' into the OLTP database, then watching transactional performance collapse.
  • Using an OLTP engine for analytics and fighting it with indexes until both workloads are slow.
  • Copying entire columns 'just in case' into both systems, doubling cost and drift.
Root cause analysis

Common issues

Error
Production database CPU pegs every time a report runs
Root cause

Analytic scan is running against the transactional store

Fix

Route reporting queries to the warehouse and keep analytic loads off the OLTP engine.

Error
Warehouse query fetches all columns for one row
Root cause

Grain/dashboard is fetching wide rows — a transactional pattern in an OLAP engine

Fix

Add filters and aggregate in SQL so the scan stays columnar and bounded.

When things break

Debugging tips

  • Slow analytic query in a row store? Check if you actually need the OLTP engine — scan-heavy workloads suggest OLAP.
  • Compare query shape: WHERE on a unique key = transactional; GROUP BY over a range = analytical.
  • If a warehouse query scans many columns, check you aren't cartesing transformations at run time.
Interviews

Interview checkpoint

Practice

Hands-on lab

Measure Row vs Column Storage
30 min · beginner
In progress
Objectives
  • Build a synthetic dataset that resembles transactional rows
  • Simulate row-oriented and column-oriented reads
  • Observe how each layout serves transactional vs analytical queries differently

Measure Row vs Column Storage

30 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

A query scans 40 million rows to compute revenue by country. Which engine is the right fit?

2

OLTP and OLAP can always be served from the same database engine without impact.

Go deeper

Official documentation

Snowflake columnar storage
docs
BigQuery columnar + serverless
docs