Star Schema and Fact Tables
What You'll Learn
You'll learn how the star schema organizes data around a central fact table connected to dimension tables, optimizing analytical queries in data warehouses.
The Star Schema Design Pattern
The star schema is a data warehouse modeling technique that structures data like a star: a central fact table surrounded by multiple dimension tables. Think of it as a hub-and-spoke model where all analysis flows through the center.
Fact Tables: The Analytical Core
A fact table stores the quantitative metrics (facts) you want to analyze—revenue, units sold, page views, conversion events, etc. Each row represents a measurable business event or transaction.
Critically, fact tables contain:
- Metrics/Measures: Numerical values you aggregate (sum, average, count)
- Foreign Keys: References to dimension tables that provide context
For example, a sales fact table might have revenue, quantity, discount_amount as metrics, plus foreign keys like customer_id, product_id, store_id, date_id.
Dimension Tables: The Context
Dimension tables surround the fact table, providing descriptive attributes. A customer dimension has names, addresses, segments. A product dimension has categories, prices, brands. Each dimension connects to the fact table through its primary key.
This design creates a star shape: one fact table at the center, dimension tables radiating outward.
Why This Matters
Star schemas are optimized for analytical queries. You can easily slice metrics by any dimension (e.g., "total revenue by product category and region") with simple joins. The denormalized dimension tables reduce join complexity, and the separation of facts from dimensions keeps fact tables lean and fast to scan.
Key Takeaway:
Star schemas organize data warehouses around central fact tables containing metrics and foreign keys, connected to denormalized dimension tables that provide business context—enabling fast, flexible analytics.