Warehouses and operational databases

The database behind an app (an operational, or OLTP, database such as PostgreSQL or MySQL) is built for many small, quick reads and writes: place this order, update that address. It stores whole rows together, keeps data normalised so each fact lives in one place, and usually holds the current state: an order's status now, not every status it had.

Analysis asks different questions: revenue by month by region for three years. That means reading millions of rows but only a few columns. A data warehouse (BigQuery, Snowflake, Redshift, or a lakehouse on Databricks, to name a few) stores each column separately and compressed, so it reads only the columns a query needs, and spreads the work over many machines. It's slow at changing one row and fast at scanning a billion.

Running heavy reports on the app's own database slows the app down for customers, so the data is copied into the warehouse, on a schedule or continuously. That copying is the pipeline, and the rest of this course is about building it well.

Warehouse tables are often arranged as a star schema. Fact tables hold events or measurements, one row per thing that happened (an order line, a payment), with numbers to add up and keys to the dimensions. Dimension tables describe the who, what and where (customers, products, dates) with the attributes you filter and group by.

-- order_items is the fact table; products and customers are dimensions
SELECT p.category, c.city, SUM(i.qty * p.price) AS revenue
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN products p ON p.id = i.product_id
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
GROUP BY p.category, c.city
ORDER BY revenue DESC;

Raw files often sit alongside the warehouse in a data lake: cheap object storage holding files in open column formats such as Parquet. A lakehouse lets warehouse-style SQL run over those files directly.