What is a fact constellation (galaxy) schema, and when does a model become one?

## Definition A **fact constellation**, also known as a **galaxy schema**, is a dimensional model containing more than one fact table where those fact tables share dimension tables. Diagrammatically it is several stars overlapping at their dimension points, which is where the galaxy metaphor comes from. It is the normal end-state of any warehouse that covers more than one business process, not a special technique you choose to adopt. ```text dim_date dim_product dim_store | \ / \ / | | \ / \ / | fact_sales ----+ +---- fact_inventory (one row per (shared dimensions) (one row per sale line) product/store/day) ``` ## How a model gets there Start with a sales star: `fact_sales` at one row per sale line, joined to date, product, store and customer dimensions. Now the business asks about stock cover. Inventory is a different process measured at a different grain — one row per product, store and day — so it needs its own fact table. What it does *not* need is its own copy of the date, product and store dimensions. Reusing them gives you a constellation and, more importantly, gives you dimensions whose members and attribute values are identical across both subject areas. ## The condition that makes it work Sharing a dimension table is only valuable if the dimension means the same thing to each fact. If `dim_store` in the sales context silently excludes closed stores but inventory expects all of them, a filter on region returns two different store populations depending on which fact you queried, and the two reports will never reconcile. Ensuring shared dimensions carry identical keys and identical attribute meanings across processes is the discipline of conformance, and it is what makes a constellation more than a coincidence of table reuse. ## The trap: never join two fact tables directly This is the practical point interviewers probe. Two facts sharing `dim_product` invites a query like this: ```sql -- WRONG: joining two fact tables on a shared key multiplies rows SELECT p.category_name, SUM(s.net_amount) AS revenue, SUM(i.on_hand_qty) AS stock FROM fact_sales s JOIN fact_inventory i ON i.product_key = s.product_key JOIN dim_product p ON p.product_key = s.product_key GROUP BY p.category_name; ``` The join is a many-to-many between two fact tables. Every sale row pairs with every inventory row for that product, so a product with 50 sales and 365 daily inventory rows produces 18,250 combinations. Revenue is inflated by the number of inventory rows and stock by the number of sales rows, and both numbers are nonsense. Nothing errors; the query just lies. The correct approach is to aggregate each fact independently to the same set of dimension keys and combine the two summarized result sets on those keys — the drill-across pattern, which is a subject in its own right. The modelling takeaway that belongs here is structural: **fact tables relate to each other only through shared dimensions, never by joining to each other**. ## Grain differences between the facts The two facts in a constellation usually sit at different grains — a transaction fact per event, a snapshot fact per day. That is expected and fine. It only means the common ground on which you can combine them is the coarsest set of dimensions and levels both facts support. If sales are captured per store and inventory only per distribution centre, there is no store-level comparison to be made no matter how you write the query. ## Is it a distinct schema type? Somewhat academically. Textbooks list star, snowflake and fact constellation as three schema types, and interviewers who learned from those textbooks will ask for the third name. In working practice, a constellation is simply what a bus-matrix-driven warehouse produces: rows of business processes, columns of shared dimensions, and a fact table wherever a process needs one. Be able to define the term, place it correctly, and immediately say the two things that matter — dimensions must conform, and facts are never joined to facts. ## Contrast with the snowflake Do not confuse the axes. Star versus snowflake is about the *shape of a dimension*: one wide table or a normalized chain. Constellation is about the *number of fact tables* sharing dimensions. They are independent: a constellation's dimensions can be flat or snowflaked, and either choice leaves it a constellation.