Stage 2 · Lesson 6 of 9

Star and snowflake schemas

A puts one fact table in the middle, with around it like the points of a star. Each dimension is , meaning its details sit in one wide table, and it joins to the fact table by a single key. Star schemas are easy to query and fast in columnar warehouses.

A snowflake schema splits dimensions further. For example, product links to a separate category table. That saves a little storage but adds joins.

Most modern teams prefer stars. Some go further and build wide, pre-joined tables for dashboards. Join facts to dimensions with .

These are meaningless numbers or hashes that the warehouse creates. Avoid joining on the 's own IDs, because they can change or clash between systems.

Common trapcopying the application's normalised schema into the warehouse and calling it a model.

A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock

Resources for this stage

Start with

CourseFree
SQL Tutorial for Data Analysis (formerly Mode)
ThoughtSpot · Beginner
Its advanced section covers window functions, performance tuning and pivoting on realistic data

Practise

LeetCode SQL 50
Fifty graded problems; the window function and aggregation ones match what pipeline SQL needs
Select Star SQL
An interactive book on one real dataset that ends with window functions and self joins

Keep handy

Kimball Dimensional Modeling Techniques
The original definitions of grain, conformed dimensions and SCD types, straight from the source
roadmap.sh: SQL
Check which SQL topics you still lack before moving on

Also worth your time

Data Warehouse Design: Schemas, Steps, and Best Practices (opens in a new tab)Read
Star and snowflake schemas, facts and dimensions, and the steps of a warehouse design in one read