OLTP vs OLAP: databases, warehouses, lakes and lakehouses

Where you store data decides what you can do with it cheaply. A database that is perfect for taking orders can be terrible for analysing a year of them, and the other way round. This lesson explains why, and then walks through the four storage styles you will meet in every data job: databases, warehouses, data lakes and lakehouses.

A kitchen analogy helps. The billing counter is built for speed on one customer at a time: scan an item, take the money, next. The store room at the back holds everything in bulk in whatever shape it arrived. The accounts office is built to answer questions about thousands of bills at once. Different jobs, different layouts.

OLTP (online transaction processing) is what applications do all day: many small reads and writes touching a few rows each. "Create this order." "Mark order 42 as ready." "Fetch Asha's profile." The data must always be correct, so OLTP databases focus on transactions, constraints and fast lookups by key. Examples: PostgreSQL, MySQL, SQL Server, Oracle.

OLAP (online analytical processing) is what analysts and dashboards do: few queries, but each reads millions of rows and aggregates them. "Revenue by category by month for the last two years." Examples of OLAP systems: BigQuery, Snowflake, Amazon Redshift, ClickHouse, and Databricks SQL.

A table is a grid. A row store keeps the grid row by row on disk: all fields of order 1, then all fields of order 2, and so on. That is ideal for "get everything about order 42" because the whole row sits together.

A column store keeps each column together: all the total values, then all the customer values. If you ask "what is the sum of total?" on a table with 30 columns, a column store reads only the one column it needs, around 1/30 of the data. Values in a column are also similar to each other, so they compress well. This is the main reason warehouses scan big tables quickly. Warehouses also spread the work over many machines in parallel.

Row store (on disk):     [1, Asha, 300, picked_up] [2, Ravi, 120, picked_up] [3, Asha, 180, cancelled]
Column store (on disk):  order_id: [1, 2, 3]  customer: [Asha, Ravi, Asha]  total: [300, 120, 180]  status: [picked_up, picked_up, cancelled]

SUM(total) in a column store reads only the total list.

A data warehouse is a database built for analytics. It stores clean, structured, modelled data (often star schemas), speaks SQL, and is fast for aggregations. You load data into it with pipelines; you read from it with BI tools and SQL.

Modern cloud warehouses such as BigQuery and Snowflake separate storage from compute: data sits in cheap storage and you start or stop compute separately. This lets you pay for query power only when you use it, and lets several teams query the same data without slowing each other. Details and pricing models differ, and they change, so check each provider's documentation (we compare them in *Cloud data platforms*).

A data lake is a large store of files in cheap object storage (Amazon S3, Google Cloud Storage, Azure Data Lake Storage). You can dump anything into it: CSV, JSON, logs, images, PDFs, database exports. You decide the structure when you read the data (called *schema on read*), not when you write it.

The lake's strength is flexibility and low cost. Its danger is the data swamp: files nobody understands, with no schema, no owner, duplicated and half-updated. Without management, finding trustworthy data becomes harder than collecting it.

A lakehouse tries to give you the best of both. Data stays as open files in cheap lake storage, but a table format adds the things a warehouse has: a schema, ACID transactions (changes are all-or-nothing), the ability to update and delete rows, and time travel (query the table as it was yesterday). Delta Lake, Apache Iceberg and Apache Hudi are the best-known open table formats. Platforms such as Databricks, and the major warehouses, now support lakehouse-style tables. The next module has a lesson, *File and table formats: Parquet, Delta Lake and Iceberg*, that explains how they work.

The idea is one copy of the data that SQL analysts, Spark jobs and ML engineers can all use.

Many teams organise a lake or lakehouse in three layers, often called the medallion pattern.

For Brew & Bean: bronze holds the raw order exports and payment files as received, silver holds validated order lines with correct types, gold holds daily_sales and the star schema from the previous lesson.

You are a senior data architect. Recommend a storage design (database, warehouse, lake or lakehouse) for this situation, and explain the trade-offs. Ask me up to 3 questions first if needed.

Business: [e.g. an online store in India]
Data sources and volume: [e.g. orders table 20,000 rows/day, clickstream 5 million events/day, product images]
Who uses the data and how: [e.g. 5 analysts on dashboards, 2 data scientists]
Freshness needed: [e.g. hourly]
Team skills and budget: [e.g. strong SQL, small budget]

Give a layered design (raw, clean, serving) and say what you would NOT build yet.

What to expect: A good answer is proportionate to the size, names a concrete layering, and explicitly says what to postpone. Be suspicious of any answer that recommends every tool.