Star schema
A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
Overview
A star schema is the most common layout for analytical data warehouses. One fact table sits at the center, holding foreign keys to each dimension plus numeric measures (revenue, quantity, duration). Around it sit dimension tables — denormalized, descriptive tables like dim_customer, dim_product, and dim_date — each joined to the fact table by a single foreign key. Every dimension is exactly one join away from the fact table, which is what gives the schema its star shape and what makes it fast and easy to query.
Ralph Kimball popularized the star schema as the core deliverable of dimensional modeling: business users can browse dimension attributes, filter, and roll measures up or down without navigating a maze of joins. Because dimensions are denormalized (a dim_product table might repeat category and department text on every row instead of normalizing them into separate tables), read queries stay simple — a handful of joins instead of dozens — at the cost of some storage and update complexity, which is an acceptable trade for analytics workloads that are read-heavy.
Example
Copy code
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY,
customer_id VARCHAR, -- natural/business key
customer_name VARCHAR,
region VARCHAR,
segment VARCHAR
);
CREATE TABLE fact_sales (
order_key INTEGER PRIMARY KEY,
customer_key INTEGER REFERENCES dim_customer(customer_key),
product_key INTEGER REFERENCES dim_product(product_key),
date_key INTEGER REFERENCES dim_date(date_key),
quantity INTEGER,
revenue DECIMAL(12,2)
);
SELECT c.region, SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_customer c USING (customer_key)
GROUP BY ALL
ORDER BY total_revenue DESC;
DuckDB angle
DuckDB is a natural engine for star schemas: its columnar, vectorized execution and cost-based join ordering handle fact-to-dimension joins efficiently even on a laptop, and you can build one directly from Parquet files with read_parquet() and CREATE TABLE ... AS SELECT — no separate ETL cluster required. Sharing the same star schema with a team is a matter of syncing the DuckDB database file to MotherDuck.
Related terms
A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Fact constellation schema →A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.
Snowflake schema →A snowflake schema is a dimensional model where dimension tables are normalized into multiple related sub-tables instead of one flat table, reducing redundancy at the cost of extra joins.
Fact table →A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.
Dimensional modeling →Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.
Conformed dimension →A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
FAQS
A star schema keeps dimension tables denormalized (flat), so each dimension joins to the fact table directly. A snowflake schema normalizes dimensions into related sub-tables, trading query simplicity for reduced redundancy.
Star schemas minimize the number of joins needed to answer analytical questions, which makes queries easier to write and faster to execute — the priorities for reporting and BI workloads, as opposed to the write-optimized, normalized models used in transactional systems.
