A galaxy schema - also called a fact constellation - is what a star schema becomes when one fact table is no longer enough. Instead of a single fact surrounded by dimensions, you have several fact tables that share the same dimension tables. The shared dimensions are conformed dimensions: identical definitions and keys reused across facts, which is exactly what lets you compare numbers from different processes on the same axis.

When you outgrow a single star

A single star answers questions about one business process at one grain - say, sales orders. The moment you also need to report on shipments, returns, or inventory snapshots, you have a second measurement at a different grain. You can't cram both into one fact table without breaking the grain or padding rows with nulls. The galaxy schema keeps each process in its own fact table and reuses the dimensions both processes have in common.

Two facts sharing dimensions

Suppose a retailer tracks both sales and inventory. Sales records a transaction; inventory records a daily on-hand count. Both are described by date and product, so those two dimensions are shared:

dim_date    (date_key, full_date, month, quarter, year)
dim_product (product_key, sku, name, category)

fact_sales     (date_key, product_key, store_key, units_sold, revenue)
fact_inventory (date_key, product_key, warehouse_key, units_on_hand)

Because both facts join to the same dim_date and dim_product, you can ask "units sold versus units on hand by product, by month" - a question neither star could answer alone. store_key and warehouse_key stay private to their own facts, since they don't apply to both.

Pros and cons against a single star

The gain is breadth: many processes modeled side by side, cross-process drill-across reporting, and no dimension duplicated per fact. The cost is discipline. The dimensions have to stay genuinely conformed - if dim_product drifts between teams, the comparison silently breaks. Queries that span facts are also written as drill-across (query each fact at the shared grain, then join the results), not a single naive join across both fact tables, which would multiply rows. A galaxy is the right structure for an enterprise warehouse with many subject areas; a lone star is simpler and correct when you only have one process to report on.

Frequently asked questions

What is a galaxy schema? A schema with multiple fact tables that share conformed dimensions - also called a fact constellation. It extends the star pattern to cover several business processes at once.

How is it different from a star schema? A star has exactly one fact table; a galaxy has several, joined to the same dimension tables. You move from star to galaxy as you add processes that share context like date, product, or customer.

Why must the shared dimensions be conformed? Cross-process comparison only works if the dimensions mean the same thing everywhere. If two facts use different definitions of "product," the numbers can't be compared on that axis.

How do you query across two fact tables? Use drill-across: aggregate each fact to the shared dimension grain separately, then join the summarized results. Joining both raw fact tables in one query fans out rows and inflates measures.