---
title: "Fact constellation schema"
description: "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."
canonical: "https://motherduck.com/glossary/fact-constellation-schema/"
related:
  - title: "Database Concepts | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/database-concepts/"
  - title: "Context Management for Data Agents | MotherDuck"
    url: "https://motherduck.com/videos/context-management-data-agents/"
  - title: "Internal vs. External Storage: What's the Limit of External Tables?"
    url: "https://motherduck.com/blog/internal-vs-external-storage-whats-the-limit-of-external-tables/"
---

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

## Overview

A fact constellation — sometimes called a galaxy schema — extends the star schema to the reality that most real warehouses need more than one fact table. Instead of one star, you have several fact tables, each with its own set of dimensions, some of which are shared (conformed) across fact tables. Visualized, multiple stars overlap where they share dimension tables, resembling a constellation.

For example, a retail warehouse might have `fact_sales` and `fact_inventory`, both sharing `dim_date`, `dim_product`, and `dim_store`, while each also has dimensions unique to its own process (`fact_sales` might have `dim_customer`; `fact_inventory` might have `dim_warehouse_location`).

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

### Example

```sql
CREATE TABLE dim_date    (date_key INTEGER PRIMARY KEY, full_date DATE);
CREATE TABLE dim_product (product_key INTEGER PRIMARY KEY, product_name VARCHAR);
CREATE TABLE dim_store   (store_key INTEGER PRIMARY KEY, store_name VARCHAR);

CREATE TABLE fact_sales (
    date_key INTEGER REFERENCES dim_date(date_key),
    product_key INTEGER REFERENCES dim_product(product_key),
    store_key INTEGER REFERENCES dim_store(store_key),
    revenue DECIMAL(12,2)
);

CREATE TABLE fact_inventory (
    date_key INTEGER REFERENCES dim_date(date_key),
    product_key INTEGER REFERENCES dim_product(product_key),
    store_key INTEGER REFERENCES dim_store(store_key),
    units_on_hand INTEGER
);

-- Compare sales against inventory using the shared dimensions
SELECT d.full_date, p.product_name, s.revenue, i.units_on_hand
FROM fact_sales s
JOIN fact_inventory i USING (date_key, product_key, store_key)
JOIN dim_date d USING (date_key)
JOIN dim_product p USING (product_key);
```

A fact constellation is really just what Kimball's bus architecture produces at scale: as a warehouse grows to cover more business processes, it naturally accumulates multiple fact tables that conform to a shared set of dimensions, rather than existing as a single, isolated star.

### DuckDB angle

There's nothing special DuckDB needs for a fact constellation — it's the same tables and joins as a single star schema, just more of them — though joining two fact tables directly (as above) only works cleanly when they share the same grain on the join keys; otherwise, aggregate each to a common grain before joining.