Lesson 3 of 11 · 22 min
Star schemas
By the end of this lesson you can read a star schema, join a fact table to its dimensions on surrogate keys, and combine two fact tables through a conformed dimension without inflating either one.
An operational database (like the shop dataset) is normalised: each fact is stored once, in many small tables, so that taking an order is fast and consistent. Answering “net sales by region, category and fiscal quarter” there takes five or six joins and some date arithmetic.
A reporting database is shaped for the questions instead. The most common shape is the star schema, named after its diagram: one fact table in the middle, surrounded by dimension tables.
- A fact table records events or measurements at a declared grain:
fact_saleshas one row per order line. Its columns are measures (numbers you add up: quantity, net_amount, cost_amount) and foreign keys to dimensions. - A dimension table describes the context: who (
dim_customer), what (dim_product), when (dim_date), where/how (dim_channel). Its columns are attributes you filter and group by: category, region, fiscal_year, season.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons