Data Modeling Basics: Fact Tables, Dimensions, Keys & Star Schemas
Build a small star schema for a fictional retailer by hand: declare the grain, map surrogate keys, join facts to dimensions, build a date dimension, keep history with SCD type 2, avoid bridge-table double counting and run the QA checks a data team runs before every report.
The first lesson is free — no account needed.
About this course
A dimensional modeling course that builds a small star schema for one fictional home-goods retailer, Larkfield Home, by hand in a spreadsheet, for data analysts, BI developers and analytics engineers who want to see what their modelling tools do underneath. You declare the grain of the sales fact table and find order lines loaded twice, swap natural product codes for surrogate keys and send unknown codes to the -1 member, join facts to product and store dimensions with XLOOKUP and INDEX/MATCH, aggregate by category and region with SUMIFS, build a date dimension with date keys, quarters and weekdays, keep store history with slowly changing dimensions type 2, measure the double counting of a many-to-many bridge table and allocate with weights, and run orphan-key and reconciliation checks. The ideas carry over to Power BI and SQL warehouses, no affiliation with Microsoft or any vendor. The first lab is free.
Lessons
- The Grain of a Fact Table: One Row per Order Line & the Duplicate CheckFree
- Surrogate vs Natural Keys: Key Mapping and the Unknown Member Pro
- Joining Facts to Dimensions: Enriching Sales with XLOOKUP and INDEX/MATCH Pro
- Aggregating by Dimension Attributes: SUMIFS by Category and Region Pro
- The Date Dimension: Date Keys, Quarters, Months and Weekdays from a Date Pro
- Slowly Changing Dimensions Type 2: Valid-From/To and the Right Version by Date Pro
- Many-to-Many & Bridge Tables: Spotting Double Counting and Allocating with Weights Pro
- Model QA: Orphan Keys, Inner-Join Losses and Reconciling Totals Pro