Why analysis needs a warehouse
🎯You'll be able to
Explain the difference between transaction databases (OLTP) and analytical stores (OLAP), and why columnar storage makes analysis fast.
🏠The everyday picture
A shop's billing counter handles one customer at a time, quickly. The accountant who studies the whole year's sales works from a different set of books, arranged so totals are easy. Mixing the two jobs at the counter would slow down every customer.
📘What it really is
-
OLTP (online transaction processing): the app's own database, such as PostgreSQL or MySQL. Many small reads and writes, each touching a few rows; normalised tables (Module 54) so each fact is stored once.
-
OLAP (online analytical processing): a warehouse such as BigQuery, Snowflake, Amazon Redshift, ClickHouse or DuckDB. Few, large queries that scan many rows but only some columns.
-
Columnar storage: OLAP stores keep each column together. A query for revenue per store reads two columns, not all fifteen, and repeated values (three store codes) compress very well.
-
Why not query the app's database? Long reports slow down the app for customers, the app keeps only current data (not history), and data from several systems needs joining in one place.
-
Copies and freshness: a warehouse is a copy, refreshed by pipelines, so it is always a little behind. Say how far (freshness, DT7).
📝A worked example
Input: SabziSathi's warehouse is a DuckDB file, which is a columnar store. Run
explain analyze select store, sum(net_amount_paise) from marts.fct_order_lines group by 1.
Expected output: the plan shows only store and net_amount_paise being read from the 15-column table, and the whole query takes a few
milliseconds for 10,181 lines. Checked on 1 November 2026 with DuckDB 2.0.0.
🌍Where it's used
- Retail and quick commerce: sales, stock and delivery analysis in BigQuery or Snowflake.
- Banking: risk and regulatory reporting from a warehouse, never from the core banking system directly.
- Startups: DuckDB or a managed PostgreSQL as a small first warehouse.
📝Case scenario
An online pharmacy ran its monthly sales report on the live order database. On the first of each month, checkout slowed down for an hour while the report ran. Copying orders nightly to a warehouse moved the report off the live system, and the slowdowns stopped.
🖱️Try it
List SabziSathi's warehouse schemas and tables with
select table_schema, table_name, table_type from information_schema.tables order by 1, 2. Success: you can say which schema each
pipeline step writes, and which tables are views.
⚠️Common mistakes
- Reporting from the app's database. Sign: the app slows down during reports. Fix: copy to a warehouse.
- Treating the warehouse as live. Sign: people expect a sale from a minute ago to show. Fix: show freshness on every report.
- Normalising the warehouse like an app database. Sign: every question needs ten joins. Fix: model for questions (DT6).
🛠️For your project
Project 2 builds SabziSathi's warehouse layers in DuckDB; the dashboard in Project 3 reads only from it.
✅Quick check
Why is columnar storage fast for analysis?
Show the answer
A query reads only the columns it needs, and those compress well.
Why not run reports on the app's database?
Show the answer
They slow the app down, and it lacks history and other systems' data.