Star and Snowflake Schemas

Once you have defined your Facts and Dimensions, they must be organized into a structure that supports analytical queries and business reporting. The two primary dimensional designs are:

• the Star Schema and
• the Snowflake Schema

The Star Schema

The Star Schema is the standard dimensional modeling pattern associated with Kimball's methodology. It consists of a central Fact table surrounded by Dimension tables, creating a shape resembling a star.

Key Characteristics

• Denormalized Dimensions: Dimension tables store related descriptive attributes together. For example, a Customer dimension may contain city, state, and country directly in the same row rather than storing them in separate normalized tables.
• Direct Joins: Each dimension typically joins directly to the fact table, usually through surrogate keys. This creates a simple, predictable structure for analytical queries.
• Flattened Hierarchies: Hierarchical attributes such as Department, Category, and Sub-Category are typically stored directly in the Product dimension rather than split across multiple related tables.
• Minimal Dimension-to-Dimension Joins: A standard star schema minimizes or eliminates the need for joins between dimensions. Queries generally follow the simple pattern of Fact → Dimension.

Benefits

• Simplicity: The structure is easy for analysts and BI tools to understand. Each dimension has a direct relationship with the fact table.
• Query Efficiency: Denormalized dimensions reduce the number of joins required for common analytical queries and make filtering, grouping, and aggregation straightforward.
• Usability: Business users can explore dimensions without needing to understand complex relational structures or navigate through multiple normalized tables.

The Default Choice

In Kimball-style dimensional modeling, the Star Schema is generally the preferred design for the presentation layer because it provides a simple, business-friendly structure for analytics and BI.

The Snowflake Schema

A Snowflake Schema is a variation of the Star Schema in which one or more dimensions are normalized into multiple related tables.

Instead of storing an entire hierarchy in a single dimension, the hierarchy is split into separate tables.

For example:

Product → Sub-Category → Category → Department

could be modeled as:

• Product links to Sub-Category
• Sub-Category links to Category
• Category links to Department

This creates additional branches around the central fact table, producing the characteristic snowflake shape.

The Tradeoffs of Snowflaking

Snowflaking introduces some degree of relational normalization into the dimensional model.

Potential Advantages

• Reduced Redundancy: Shared attributes such as category or department names are stored once rather than repeated across many dimension rows.
• Potentially Smaller Dimensions: Highly repetitive hierarchical attributes can require less storage.
• Centralized Hierarchies: In some environments, separately managed hierarchies may simplify certain forms of data maintenance or governance.

Why Star Schemas Are Generally Preferred

Although snowflaking can have legitimate uses, Kimball-style dimensional modeling generally favors denormalized dimensions in the presentation layer.

1. Simplicity Over Normalization

   The primary goal of a dimensional model is to make analytical data easy to understand and query. A star schema exposes business-friendly dimensions directly, whereas a snowflake introduces additional relationships that users and BI tools must understand.

2. Fewer Joins

   In a star schema, a query such as "Sales by Category" can typically join the Sales fact directly to the Product dimension. In a snowflake schema, the query may need additional joins through Sub-Category and Category.

   Modern database engines can optimize such joins effectively, but fewer joins generally result in a simpler query structure and simpler semantic model.

3. Better BI Usability

   BI tools and semantic layers work particularly well with straightforward star-shaped relationships. A denormalized dimension makes hierarchies and descriptive attributes easier to expose to business users.

4. Storage Savings Are Usually Not the Primary Design Goal

   Snowflaking can reduce redundancy, but in analytical systems, simplicity, usability, and query efficiency are generally more important design considerations than minimizing repeated dimensional attributes.

5. Complexity Can Spread Through the Model

   Excessive snowflaking can make the model harder to navigate, document, govern, and troubleshoot—especially for self-service analytics.

Keep the Presentation Layer Simple

As a general rule, prefer a Star Schema for the dimensional presentation layer. If the source system is highly normalized, such as an ERP, the ETL/ELT process will typically transform and integrate that source data into business-friendly, denormalized dimensions.

Snowflaking can still be appropriate when there is a specific modeling, governance, or architectural reason for normalizing part of a dimension. It should be a deliberate choice rather than the default.

Example: If you have a Customer dimension with 50 million rows, but all customers fall into one of 1,000 common demographic profiles, snowflaking those attributes into a separate Demographics table (an outrigger dimension) can save significant space and allow demographic definitions to be updated without rewriting millions of customer records.

Star vs Snowflake

Characteristic	Star Schema	Snowflake Schema
Dimension design	Denormalized	Normalized / partially normalized
Hierarchies	Usually flattened into dimensions	Often split across multiple tables
Query joins	Fewer	More
User experience	Simple	More complex
BI / semantic modeling	Generally easier	Can require more modeling
Redundancy	Higher	Lower
Typical dimensional-modeling choice	Preferred	Used when justified