Star Schema
A star schema is a dimensional data warehouse design with a central fact table (quantitative events) surrounded by dimension tables (descriptive attributes), optimizing for analytical queries.
Definition
A star schema arranges warehouse data into two types of tables: a fact table at the center (containing quantitative measures like sales amount, quantity, revenue) and dimension tables around it (customer, product, date, region). Queries 'radiate' from the fact table through dimensions: 'show me sales (fact) by product category and region (dimensions) for Q4 (date dimension).' Star schemas are denormalized (dimension tables contain redundant data) to optimize read performance and simplify querying. They're named 'star' because the diagram looks like a star: fact in the center, dimensions radiating outward.
How It Works
1. Identify facts: What are you measuring? (sales, clicks, impressions). 2. Identify dimensions: How do you want to slice it? (by customer, product, time, region). 3. Design fact table: grain (one row per transaction? per day?), foreign keys to dimensions, metrics. 4. Design dimensions: attributes (customer dimensions include name, segment, country). 5. Query: SELECT SUM(sales_amount) FROM fact_sales JOIN dim_customer ... GROUP BY product_category.
When to Use It
Star schemas are the gold standard for data warehouses. They're intuitive, queryable, and performant. Use star schemas for business intelligence, reporting, and analytics. Avoid for OLTP (operational) systems where normalization and write performance matter more. Star schemas assume slowly-changing dimensions and stable grain.
Last updated: Jun 17, 2026