---
title: "Star schema"
description: "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."
canonical: "https://motherduck.com/glossary/star-schema/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Information schema | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/information-schema/"
  - title: "Query-Log-Informed Schema Descriptions and their Impact on Text-to-SQL - MotherDuck Research Papers"
    url: "https://motherduck.com/research/query-log-informed-schema-descriptions-text-to-sql/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
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.