Skip to content
Analytics Engineering
Start free

Galaxy schema

A warehouse design with two or more fact tables that share conformed dimensions, such as orders and refunds both joined to the same customer, product, and date tables. Also called a fact constellation.

Also: fact constellation · fact constellation schema

On this page · 7 sections
  1. Definition
  2. Example
  3. Why it matters
  4. Common mistakes
  5. FAQ
  6. Go deeper
  7. Related terms

A galaxy schema is several star schemas that share dimensions. Each business process keeps its own fact table at its own grain (one row per order line, one row per refund, one row per daily inventory balance), and those facts connect to the same customer, product, and date dimensions instead of to each other.

Most real warehouses become galaxies as soon as they model more than one process. The name describes the result, not a separate technique: you still design one star per process, then make sure the shared dimensions are conformed, meaning the same keys and the same attribute definitions everywhere.

Compared with a single star, a galaxy lets you answer cross-process questions (refund rate by product category, sell-through by warehouse) without merging facts that have different grains into one table.

with sales as (
  select p.category, sum(f.line_total) as item_revenue
  from fct_order_lines f
  join dim_product p on p.product_id = f.product_id
  group by p.category
),
refunds as (
  select p.category, sum(r.refund_amount) as refunded
  from fct_refunds r
  join dim_product p on p.product_id = r.product_id
  group by p.category
)
select
  s.category,
  s.item_revenue,
  coalesce(r.refunded, 0) as refunded,
  round(coalesce(r.refunded, 0) / s.item_revenue, 4) as refund_rate
from sales s
left join refunds r on r.category = s.category
order by refund_rate desc
Combine two facts through a shared dimension: aggregate each fact to the category first, then join the results.
Why it matters

Cross-process metrics are where warehouses most often double-count. Joining two fact tables directly multiplies rows; aggregating each fact to the shared dimension first and then combining the results keeps both measures correct.

Conformed dimensions are what make a galaxy trustworthy. If orders and refunds use different customer tables, a refund rate by segment compares two different definitions of a customer.

Common mistakes
  • Joining one fact table directly to another on a shared key, which repeats each order line once per refund and inflates both totals.
  • Giving each fact its own copy of a dimension with slightly different attributes, so the facts cannot be compared.
  • Merging processes with different grains into one wide fact table to avoid having two.
FAQ
What is the difference between a star schema and a galaxy schema?
A star schema has one fact table surrounded by dimensions. A galaxy schema has two or more fact tables that share some of those dimensions. A galaxy is what you get when you model several business processes with conformed dimensions.
Is a galaxy schema the same as a fact constellation?
Yes. Galaxy schema and fact constellation are two names for the same design: multiple fact tables sharing conformed dimension tables.