Stop 04 · SQL & Data Engineering BasicsTopics · 6/9

Data Warehouses (Concept)

In short

The app's database is built to handle one order at a time. Questions like 'revenue by city for three years' need a different kind of store. That's a data warehouse.

Centralized, structured stores (Snowflake, BigQuery, Redshift) optimized for analytical queries across large historical datasets — where training data often lives at scale.

The purple blob walks with a clipboard through a huge tidy godown of racked sacks and boxes.

Think of it like this

The organised grain godown

A ration shop counter serves one family at a time, fast. The big godown behind it stores years of stock, neatly racked by type and date, so a manager can ask 'how much rice moved this year?' and get an answer quickly. The counter is your app database. The godown is the warehouse.

Watch it happen

A star schema for orders

fact_orders

dim_customer

dim_product

dim_date

city, segment

category, brand

month, weekday

1/4The fact table has one row per order: ids plus numbers like amount and quantity.

The big ideas

What you need to know

Built for big questions

Warehouses like BigQuery, Snowflake and Redshift scan millions of rows quickly for analysis. They're not built for the app's live writes.

Columnar storage

Data is stored by column, so SUM(amount) only reads the amount column, not whole rows.

Facts and dimensions

Facts are events with numbers. Dimensions describe the who, what, where and when. Together they make a star schema.

History lives here

Years of data from many sources end up in one place, which is exactly what training datasets need.

See it in code

Walk through the code

sql

1SELECT d.year,2       d.month,3       p.category,4       SUM(f.amount)  AS revenue,5       COUNT(*)       AS orders6FROM fact_orders AS f7JOIN dim_date    AS d ON d.date_id = f.date_id8JOIN dim_product AS p ON p.product_id = f.product_id9WHERE d.year >= 202410GROUP BY d.year, d.month, p.category11ORDER BY d.year, d.month, revenue DESC;

1/4Start from the fact table, the big table of order events.

A classic warehouse query: one fact table, a couple of dimension joins, then group and sum. In a warehouse this stays fast even over years of orders.

Save yourself the pain

Common mistakes

Oops

Running heavy analysis queries on the live app database.

Do this instead

Copy data to a warehouse, or at least a read replica, so analysis can't slow down real users.

Oops

Using SELECT * on a huge warehouse table.

Do this instead

Select only the columns you need. Columnar warehouses often charge or slow down by data scanned.

Your mission

Build a tiny star schema

~30 min

Quick check

Why do warehouses store data by column instead of by row?

Go deeper

1Learn the difference between a data warehouse, data lake and database at a conceptual level.
2Read documentation for one warehouse (e.g. BigQuery) to see how querying at scale works.
3Understand columnar storage and why it's fast for analytical queries.