Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Most introductions to dimensional modeling start with the star schema – a single fact table surrounded by dimensions. It’s clean, simple, and perfect for explaining the basics. But real businesses are rarely that simple and you can model from a textbook. You don’t just have sales data or just inventory data. You have sales, inventory, shipping, returns, customer service interactions, and marketing campaigns all flowing through your organization simultaneously. Read more about star schemas here:
Enter the galaxy schema, also known as a fact constellation. This is where dimensional modeling grows up and faces the complexity of enterprise data warehousing.
What Is a Galaxy Schema?
A galaxy schema is a dimensional model containing multiple fact tables that share common dimension tables. When you diagram it, you see several “stars” (fact tables with their dimensions) interconnected through shared dimensions, creating a constellation or galaxy pattern.
It is basically multiple star schemas that aren’t isolated islands but rather connected parts of a unified data warehouse. Your sales facts, inventory facts, and shipping facts might all reference the same product dimension, date dimension, and location dimension.
The Structure
Multiple Fact Tables
Each fact table represents a different business process or subject area. You might have a sales_fact table capturing transactions, an inventory_fact table tracking stock levels, and a shipment_fact table recording deliveries. Each operates at its own grain (the level of detail at which facts are recorded).
Sales facts might be at the transaction line item level, while inventory facts are at the daily snapshot level per product per warehouse. These different grains are perfectly normal and necessary to represent different business processes accurately.
Conformed Dimensions
The beauty of galaxy schemas lies in conformed dimensions – dimension tables shared across multiple fact tables. A product dimension serves both your sales facts and inventory facts. A date dimension supports analysis across all your business processes.
These shared dimensions are what enable integrated analysis across different parts of your business. They’re the threads that weave separate fact tables into a cohesive analytical fabric.
Why Galaxy Schemas?
Your organization will not have just one business process to analyze. It likelynhas thousands. A galaxy schema provides a framework for organizing analytical requirements without removing the simplicity of individual star schemas.
The power of conformed dimensions becomes apparent when you need to answer questions that span multiple business processes.
– How do inventory levels correlate with sales patterns?
– What’s the relationship between marketing spend and customer acquisition?
These questions require joining across multiple fact tables through shared dimensions.
Consistent Definitions
When multiple fact tables share the same customer dimension, everyone’s working with the same definition of what constitutes a customer, the same customer segmentation, the same hierarchies. This eliminates the “which customer master is correct” debates that plague many organizations. This is critical and I talk about the wider MDM (Master Data Management) implications here.
The success of a galaxy schema hinges on properly designed conformed dimensions. This requires more discipline and planning than building isolated star schemas.
Conformed dimensions need enterprise-level agreement on definitions and attributes. What attributes does the product dimension need to support both sales analysis and inventory management? How granular should the date dimension be?
Not every fact table uses every attribute in a conformed dimension. Your online sales might not care about physical store attributes in the location dimension. That’s fine. Conformed dimensions can contain attributes that only some fact tables utilize. The key is that when multiple fact tables do use the same attribute, they mean the same thing.
Conformed dimensions must evolve carefully. Adding attributes is usually safe, but changing existing attributes affects all fact tables that reference the dimension. This requires coordination and careful rollout strategies.
Common Patterns
Sometimes you have a core set of attributes that are truly conformed across all business processes, plus custom attributes specific to certain fact tables. One approach is creating a core conformed dimension with extension tables for process-specific attributes, though this starts blurring into snowflake territory.
While you want one conformed date dimension, individual fact tables often need multiple date references. An order might have order_date, ship_date, and delivery_date – all pointing to the same date dimension but through different foreign keys. This is called a role-playing dimension.
When relationships between facts and dimensions get complicated – like a single transaction involving multiple products or a customer belonging to multiple segments -bridge tables help navigate many-to-many relationships while maintaining the galaxy structure.
Design Challenges
Different fact tables operate at different grains, which can cause confusion when joining them. You can’t directly join daily inventory snapshots with individual sales transactions without careful aggregation or filtering. Analysts need clear guidance about which joins make sense and which don’t.
Sometimes different business processes need to attribute the same dimension differently. A return transaction might reference both the original sale date and the return date. A product might be in different categories for merchandising versus financial reporting. These conflicts require careful modeling decisions—sometimes creating separate dimensions is the right answer, even if they contain similar data.
Joining multiple large fact tables can be expensive. While conformed dimensions enable these joins, that doesn’t mean every such join is practical. Often, aggregate fact tables or summary tables are needed to make cross-process analysis performant at scale.
Maintaining conformed dimensions across an enterprise requires strong data governance. Someone needs to own the dimension, manage change requests, and ensure new fact tables conform to existing standards. Without this governance, galaxy schemas devolve into disconnected star schemas with inconsistent dimensions.
Implementation Strategies
Don’t try to build the entire galaxy on day one. Start with two or three related fact tables and a small set of conformed dimensions. Prove the pattern works, establish governance processes, then expand incrementally.
Every fact table needs clear documentation about its grain, the level of detail it represents. This prevents misuse and helps analysts understand which cross-fact-table analyses make sense.
When adding a new fact table, resist the temptation to create a slightly different version of an existing dimension. Use the conformed dimension even if the new fact table only needs some of its attributes. Consistency trumps efficiency here.
Not every possible join in a galaxy schema is meaningful. Provide clear guidance about which fact tables can be analyzed together and which combinations don’t make business sense, even if technically possible.
Modern Relevance
Galaxy schemas are more relevant than ever in modern data environments. As organizations build comprehensive data platforms, they’re naturally creating multiple fact tables representing different business processes. The question isn’t whether to use a galaxy schema, it’s whether to do it intentionally with proper conformed dimensions or accidentally with inconsistent dimensions that undermine analytical coherence.
Cloud data warehouses make galaxy schemas more practical than ever. The storage and compute power to maintain multiple large fact tables with shared dimensions is readily available. Tools like dbt make building and maintaining conformed dimensions more manageable through code-based dimension management.
When Galaxy Schemas Fit
Galaxy schemas are the right choice when you have multiple related business processes that need integrated analysis, when you’re building an enterprise data warehouse rather than a single-purpose data mart, and when you have the organizational maturity to maintain conformed dimensions.
They’re probably overkill if you truly have just one business process to analyze or if your analytical needs are completely isolated with no need for cross-process insights.
The Path Forward
If you’re building a data warehouse that will grow beyond a single star schema, and most do, design with the galaxy pattern in mind from the start. Identify your key conformed dimensions early. Build consensus on their structure. Establish governance for how they’ll evolve.
The galaxy schema isn’t a different modeling technique, it’s what happens when you apply star schema principles at enterprise scale. It’s the recognition that your organization is complex, your data needs are multifaceted, and your analytical requirements span multiple business processes.
Done well, a galaxy schema provides the coherent, integrated view of your business that star schemas promise but can’t deliver alone. It’s the difference between having data and having an enterprise data warehouse.
