The Star Schema is a popular database schema design used in data warehousing. It is named for its resemblance to a star, where a central fact table is surrounded by dimension tables.
Consider an e-commerce sales data warehouse:
The fact table sits at the centre and records business events at a declared grain, for example one row per product per order line. It holds foreign keys to each dimension and numeric measures such as quantity and amount. Dimension tables surround it and hold descriptive attributes that users filter and group by: calendar attributes in a date dimension, category and brand in a product dimension, region and city in a store dimension. Dimensions are denormalized on purpose, so a product row carries its category name directly rather than pointing to a separate category table. That is the main difference from a snowflake schema.
Dimensions use surrogate keys, integers generated by the warehouse, rather than natural keys from source systems. Surrogate keys keep joins narrow, isolate the warehouse from source key changes, and make slowly changing dimensions possible, where a changed attribute creates a new dimension row with a new key and validity dates.
Designing a star schema follows four steps described by Kimball: choose the business process, such as retail sales; declare the grain, the exact meaning of one fact row; identify the dimensions that describe that row; and identify the numeric facts measured at that grain. Getting the grain right first is what keeps the model consistent. If the grain is one row per order line, every dimension must be single-valued at that level, and every measure must be true for that line. Attributes that apply only to whole orders, such as a shipping charge, either need allocation down to lines or belong in a separate fact table at the order grain.
A date dimension deserves special mention. Rather than relying on date functions in every query, the warehouse stores one row per calendar day with precomputed attributes such as fiscal period, week number, holiday flag, and day name. Queries then filter and group on ordinary columns, and business calendars that differ from the civil calendar are handled in one place.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- 20250131
full_date DATE NOT NULL,
month_name VARCHAR(10),
year_num INT
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
sku VARCHAR(20),
product_name VARCHAR(100),
category VARCHAR(50)
);
CREATE TABLE dim_store (
store_key INT PRIMARY KEY,
store_name VARCHAR(100),
region VARCHAR(30)
);
CREATE TABLE fact_sales (
date_key INT REFERENCES dim_date(date_key),
product_key INT REFERENCES dim_product(product_key),
store_key INT REFERENCES dim_store(store_key),
quantity INT,
amount DECIMAL(12,2)
);
-- Revenue by category and region for 2025
SELECT p.category, s.region, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store s ON f.store_key = s.store_key
WHERE d.year_num = 2025
GROUP BY p.category, s.region
ORDER BY revenue DESC;Analytical queries follow one shape: restrict the dimensions, join to the fact table, group by dimension attributes, and aggregate measures. Because dimensions are small relative to the fact table, databases can filter them first and use the resulting key sets to prune fact rows, a technique called star join or star transformation in some engines and dynamic partition pruning or runtime filters in others. Columnar warehouses benefit further because only the referenced fact columns are read.
| Design | Joins per query | Redundancy | Best for |
|---|---|---|---|
| Star | One per dimension | Some, in dimensions | BI tools, ad hoc analysis |
| Snowflake | More (dimension hierarchies) | Lower | Large, shared hierarchies |
| 3NF normalized | Many | Minimal | Transactional systems |
| One big table | None | High | Single-purpose dashboards |