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.

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.

What You'll Learn

1. OLTP vs OLAP — Two Different Jobs

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.

OLTP (app DB)

OLAP (warehouse)

2. The Star Schema — Facts at the Centre

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.

• A fact table holds the events you measure — sales, clicks, shipments. It's long and skinny: mostly foreign keys plus a few numbers.
• A dimension table holds the descriptive context — product, date, customer, store. It's short and wide: lots of text columns you'll filter and group by.
• Measures are the numbers in the fact you SUM / AVG (quantity, total_amount). Dimensions are the labels you GROUP BY (category, month_name).
• A surrogate key is the warehouse's own meaningless id ( product_key ), separate from the source system's natural key ( product_id ). Surrogate keys let a product have several versions over time (you'll see why in SCD).

Our Sample 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.

3. The Star-Join Query — Turning Events into Reports

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.

Your Turn: complete the star-join

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

4. Snowflake Schema — When Dimensions Are Normalised

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.

5. Slowly Changing Dimensions (SCD)

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.

• SCD Type 1 — overwrite. Just UPDATE the value. Simple, but old reports change because history is lost. Fine for fixing typos; bad for anything you'll analyse over time.
• SCD Type 2 — add a new row. Insert a new version with a fresh surrogate key plus effective_from / effective_to / is_current columns. Old facts keep pointing at the old version, so past reports stay correct. This is the workhorse of real warehouses.

Your Turn: name the grain, classify the columns

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.

Common Errors (and the fix)

• Wrong (or undecided) grain: mixing "one order line" rows with "one whole order" rows in the same fact table makes every SUM wrong. Decide the grain first, write it as a comment, and keep every row to it.
• Snowflaking everything: normalising every dimension out of habit buries simple reports under extra joins. Start with a star; snowflake only a big or shared dimension when there's a real reason.
• Counting a measure twice (fan-out): joining the fact to a dimension that has many rows per fact row multiplies the fact rows, so SUM(total_amount) double-counts. Join facts only to dimensions at the same or coarser grain; if you must, aggregate the fact first, then join.
• OLTP design for analytics: running heavy GROUP BY reports on the highly-normalised production database is slow and hurts the live app. Load a separate star-schema warehouse instead.
• Reusing the natural key as the primary key: if product_id is the dimension's only key, you can't keep two versions of the same product (SCD Type 2). Add a surrogate product_key .

📘 Quick Reference

Term

What it is

Frequently Asked Questions

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.

Mini-Challenge: Revenue by Brand

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.

🎉 Lesson Complete

• ✅ OLTP handles small fast writes; OLAP warehouses handle huge reads
• ✅ A star schema = one fact table + surrounding dimension tables
• ✅ Decide the fact grain first; separate measures from dimensions
• ✅ Surrogate keys let dimensions keep history (SCD Type 2)
• ✅ Snowflaking normalises dimensions at the cost of extra joins
• ✅ Next: running these star-join queries at scale with Big Data SQL tools like Hive, SparkSQL and DuckDB

Practice quiz

Continue this course

• Previous: Multi-Tenant Databases
• Next: Big Data SQL

Related lessons

• JOINs
• GROUP BY & aggregates