Data Warehousing

By the end of this lesson you'll be able to design an analytical data warehouse the way professionals do — separating fast-changing transactions (OLTP) from a read-optimised warehouse (OLAP), modelling a star schema from a central fact table and surrounding dimensions, choosing the right grain, and writing the star-join GROUP BY query that turns raw events into business numbers.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Your normal app database is OLTP (Online Transaction Processing): it handles lots of tiny, fast writes — place an order, update a profile, one row at a time. It's normalised (data split across many tables to avoid duplication) so writes stay safe and cheap.

A data warehouse is OLAP (Online Analytical Processing): it answers big questions like "revenue by category by month for the last three years". It's loaded in batches and shaped so those huge read queries are simple and fast — even if that means duplicating descriptive data.

OLTP is the supermarket checkout — fast, one customer at a time, optimised for the moment of sale. OLAP is the head office , where analysts compare sales across 500 stores over 5 years. Same business, but you wouldn't want the head-office reports slowing down the tills — so you give them separate systems.

A star schema has one central fact table ringed by dimension tables . Sketch it and it looks like a star, hence the name. It's the default warehouse design — when in doubt, build a star.

Load these rows so the analytical query below has data. Notice fact_sales stores only keys and numbers — every label lives in a dimension.

Almost every warehouse report follows the same shape: start at the fact table, JOIN out to each dimension you need, then GROUP BY the dimension labels and SUM the measures. You join "out from the centre of the star" — one hop per dimension.

Electronics in February is 25.00 + 240.00 = 265.00 and 1 + 3 = 4 units — the two February electronics sales collapse into one grouped row. That collapsing is exactly what a warehouse is built to do quickly.

Fill in the three blanks to total revenue per category per month. The expected result is in the comments so you can check yourself.

A snowflake schema is a star whose dimensions have been normalised — split into sub-tables to remove repeated text. In a star, dim_product repeats the word "Electronics" on every Electronics row. Snowflaking moves category into its own dim_category table and links to it by key.

The trade-off: snowflaking saves a little storage and keeps category data in one place, but every category report now needs an extra join . Stars are simpler and faster to query, so most warehouses stay with stars and only snowflake a dimension when it's genuinely large or shared across many facts.

Dimension attributes drift over time — a product gets re-categorised, a customer moves city. A Slowly Changing Dimension strategy decides what happens to history when that occurs.

No query to run — read the fact row and decide, for each column, whether it's a measure or a dimension . The answers are in the comments.

Default to a star. It has fewer joins, so reports are simpler to write and faster to run. Snowflake a single dimension only when it's very large or shared across several fact tables and the duplication genuinely hurts.

Q: Why use a surrogate key instead of the existing product_id?

So one real-world product can have several historical versions (SCD Type 2). Each version gets its own product_key ; old facts keep pointing at the version that was current when they happened. A surrogate key also insulates the warehouse from messy or changing source ids.

Q: Why a separate dim_date instead of just storing a date in the fact?

A date dimension pre-computes year, quarter, month name, weekday, holiday flags and fiscal periods once. Then every report can GROUP BY them with a plain join instead of repeating fragile date maths in each query.

Q: Do I need a special "OLAP database" to do this?

No — a star schema is just ordinary tables and SQL; you can build one in any database. Dedicated analytical engines (column stores like BigQuery, Redshift, DuckDB) run the same star-join queries far faster on huge data, but the design is identical.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary