Data warehousing

Why it matters

"Revenue by region by month for the last three years, compared with marketing spend" scans billions of rows and aggregates a few columns. On an operational Postgres database it takes minutes, locks out real traffic and still may not finish. A data warehouse answers it in seconds because every part of it, from storage layout to execution, is built for scanning and aggregating rather than for reading single rows. It is the analytical counterpart to every system in this track, and the destination of most pipelines.

The idea

An operational (OLTP) database is optimised for many small transactions touching a few rows by key. A data warehouse (OLAP) is optimised for fewer, large queries that scan many rows and aggregate a few columns. Three design choices make the difference.

	OLTP database	Data warehouse
Storage layout	Row-oriented: a row's columns together, ideal for fetching one record	Column-oriented: each column stored and compressed together, ideal for scanning a few columns of many rows
Execution	One node, index lookups	Massively parallel: a query split across many nodes and cores, each scanning a slice
Typical query	Fetch order 9001 and its items	Sum revenue by month and region over 3 years
Writes	Many small concurrent transactions	Bulk loads and merges in batches or micro-batches
Schema	Normalised for integrity	Dimensional (star) for analysis
Examples	Postgres, MySQL	Snowflake, BigQuery, Redshift, ClickHouse, Databricks SQL

Columnar storage and compression

Storing a column contiguously means a query reading 4 of 120 columns reads about 3 percent of the data. Values in a column are similar, so compression (dictionary, run-length, delta encoding) shrinks them 5 to 10 times, and engines often operate on compressed data directly. Per-block min and max statistics let the engine skip blocks that cannot match a filter (zone maps or pruning), and clustering the table by a common filter column (date) makes pruning very effective.

Separation of storage and compute

Cloud warehouses keep data in cheap object storage and spin up compute clusters on demand that read it. Storage grows independently of compute; several clusters can query the same data without contending (one for dashboards, one for data science, one for loads); and compute can scale to zero. The cost model follows: you pay for storage by the terabyte and for compute by the second or by the bytes scanned, so query design (pruning, selecting only needed columns) is directly a cost lever.

Dimensional modelling

The dominant warehouse schema is the star schema. A central fact table records measurable events at a declared grain (one row per order line, per page view, per payment) with numeric measures and foreign keys. Surrounding dimension tables describe the context (customer, product, date, store) with descriptive attributes analysts filter and group by. Queries join one fact to a few dimensions and aggregate, which warehouses execute extremely efficiently. A snowflake schema normalises dimensions further; wide denormalised tables ("one big table") trade storage for simpler queries and are common in columnar engines.

Slowly changing dimensions

A customer moves from London to Paris. Should last year's sales be reported under London or Paris? SCD Type 1 overwrites the attribute (history reflects the current value). Type 2 inserts a new dimension row with validity dates, keeping history accurate: last year's facts point to the London row, new facts to the Paris row. Type 2 is the usual choice for attributes that matter to historical reporting; deciding per attribute is part of the model.

Operating a warehouse

Load with idempotent merges by partition (the ETL lesson); cluster or partition big facts by date; build aggregated marts for heavy dashboards; govern with roles, column masking and row-level security; and watch cost per query and per team, since a careless SELECT * over a petabyte table is a real invoice. Keep operational traffic out: the warehouse is for analysis, the OLTP database for the product.