OLAP vs OLTP & Data Warehousing

Starting from Zero — A Physical Intuition

Before comparing data systems, let's understand OLTP and OLAP through physical analogies:

• The Supermarket Checkout vs The Annual Boardroom (OLTP vs OLAP):
  • OLTP (Online Transaction Processing): Imagine a busy checkout line. The cashier handles one customer at a time, checks item prices, debits credit cards, and prints receipts. The operations are small, rapid, and must be 100% correct (Atomic).
  • OLAP (Online Analytical Processing): Imagine the supermarket's executive boardroom. The directors ask: "What was our total revenue on frozen foods across all stores in the Northwest during Q3 for the last three years?" They don't care about a single customer's receipt; they need to scan and aggregate millions of rows of transaction history.
• Single-Sheets vs Columnar Lists (Row-Store vs Column-Store):
  • Row-store: Imagine you write each employee's details on a separate index card (ID, Name, Age, Salary, Department). Finding Employee #42 means grabbing their single card (O(1) read). But calculating the average employee salary requires reading every single card in the box.
  • Column-store: Imagine you write all employee IDs on Page 1, all Salaries on Page 2, and all Departments on Page 3. Finding Employee #42 is slow (you must jump pages to compile their info). But calculating the average salary is fast: you grab Page 2, read the salaries sequentially, and ignore all other pages completely.

Two workloads pulling in opposite directions

• OLTP (Online Transaction Processing) — your app's database. Many tiny, concurrent reads/writes touching a few rows: "fetch order 42", "insert a payment". Optimized for point lookups, low latency, and correctness.
• OLAP (Online Analytical Processing) — the analytics/reporting database. A few huge queries scanning billions of rows to aggregate: "revenue by region by month for 3 years". Optimized for scan throughput, not single-row latency.

They want opposite physical layouts, which is why they live in different systems.

Row store vs column store — the core idea

A row store keeps each row's columns together on disk — great when you need the whole row (SELECT * WHERE id = 42). A column store keeps each column together:

Row store:    [id,name,amount,region][id,name,amount,region]...   ← read a whole order fast
Column store: [id,id,id...][amount,amount,amount...][region...]   ← read one column fast

For SELECT SUM(amount) WHERE region = 'EU', the column store reads only the amount and region columns — not the 40 columns you don't need — and those columns compress beautifully (runs of similar values) and vectorize. That's why analytics queries are orders of magnitude faster on columnar engines.

The modern data stack

• Data warehouse (Snowflake, BigQuery, Redshift): columnar, SQL, built for OLAP at scale. Structured, modeled data.
• Data lake (S3 + Parquet/ORC): cheap object storage of raw/semi-structured files in columnar formats. Schema-on-read.
• Lakehouse (Databricks/Delta, Iceberg): a table layer over the lake giving warehouse features (ACID, schema) on cheap storage — converging the two.

ETL vs ELT

• ETL (Extract → Transform → Load): clean/reshape before loading. Older, used when compute was scarce.
• ELT (Extract → Load → Transform): dump raw data into the warehouse, transform in it with SQL (e.g. dbt). Dominant now because warehouse compute is cheap and elastic.

Star schema

Warehouses model data as a central fact table (the events — one row per sale, with foreign keys and numeric measures) surrounded by dimension tables (the descriptive context — date, product, customer, store):

-- Fact: one row per sale; FKs to dimensions + measures
SELECT d.month, p.category, SUM(f.amount) AS revenue
FROM   fact_sales        f
JOIN   dim_date     d ON d.date_id    = f.date_id
JOIN   dim_product  p ON p.product_id = f.product_id
WHERE  d.year = 2025
GROUP  BY d.month, p.category;

Denormalizing into a star means analytics queries join a few wide dimensions instead of navigating a deeply normalized OLTP schema — fewer joins, faster scans. (A snowflake schema further normalizes the dimensions; usually not worth it.)

Getting data from OLTP to OLAP

Never point dashboards at your production primary — a full-table analytical scan will starve the transactional workload. Instead replicate the data out:

• Batch ETL/ELT on a schedule (nightly loads), or
• CDC (change-data-capture) streaming the OLTP write-ahead log into the warehouse for near-real-time analytics.

HTAP systems try to serve both workloads in one engine, but the default and safest answer in an interview is: separate the stores, and pipe OLTP → warehouse.

Think it through like the interview

Don't just mention row vs column formats — calculate the physical disk I/O savings when running analytical aggregates.

Interactive Quiz

Practice

1. Star Schema Design: Design a Star Schema for a ride-sharing app (Uber). Identify the metrics that belong in the central fact_rides table, and the fields that belong in dimensions (dim_driver, dim_rider, dim_location, dim_date).
2. Columnar Compression: Explain why columns compress much better (e.g., using run-length encoding) in a column-store compared to rows in a row-store.
3. CDC vs Batch: Design a data ingestion pipeline for an e-commerce order system. Decide whether to use nightly batch imports or real-time Change Data Capture (CDC) based on business requirements.

Next: Fundamentals & Core Theory — Level 11 curriculum.