⭐ Star Schema vs Snowflake Schema

⭐ Schemas & Relationships

Analogy

If Fact and Dimension tables are building blocks, a Schema is the blueprint of how you connect them together to build the house.

When we organize data in a Data Warehouse, we almost always use one of two main blueprints: the Star Schema or the Snowflake Schema.

🌟 1. Star Schema

Analogy

Think of the Sun and the planets. The Sun is in the dead center (Fact Table), and the planets orbit directly around it (Dimension Tables). There is only one step to get from any planet to the Sun.

In a Star Schema, the Fact Table is in the center, and the Dimension Tables are connected directly to it, forming the shape of a star.

Structure

1 Central Fact Table (contains the numbers/measures).
Multiple Dimension Tables (contains the descriptive text).
Dimension tables do not connect to each other. They only connect to the Fact table.

Advantages

Extremely fast for querying: Because everything is just one step away from the center, the database doesn't have to work hard to join tables together.
Very easy to understand: Business users can easily look at it and understand how the data connects.

Tip

The Star Schema is the gold standard for Data Warehousing. It is the most common and preferred design.

❄️ 2. Snowflake Schema

Analogy

Think of a tree. The trunk is the Fact Table. The main branches are the Dimensions. But in a Snowflake, those branches have smaller branches attached to them (Sub-dimensions).

A Snowflake Schema is a Star Schema where the Dimension tables have been broken down into even smaller tables. This process of breaking down tables to avoid repeating data is called Normalization.

Structure

The Fact Table is still in the center.
But a Dimension table (like Product) might connect to another table (like Category), which connects to another table (like Department).
It looks like a complex snowflake.

Use Cases

When you need to save storage space (because you aren't repeating text over and over).
When a dimension is incredibly large and complex.

⚔️ Star vs. Snowflake

Feature	⭐ Star Schema	❄️ Snowflake Schema
Structure	Simple (Sun and planets)	Complex (Tree branches)
Dimension Tables	Not normalized (Data repeats)	Normalized (No repeating data)
Query Speed	Very Fast (Fewer joins)	Slower (Many joins needed)
Storage Space	Uses more space	Uses less space
Best For	Fast analytics and BI reporting	Saving space and complex hierarchies

🔗 3. Cardinality (Relationships)

Cardinality is just a fancy word for describing how two tables are related to each other. How many rows in Table A match with rows in Table B?

1️⃣ One-to-One (1:1)

One record in Table A relates to exactly one record in Table B.

Example: A Person and a Passport. One person has one passport. One passport belongs to one person.
Use in DW: Very rare. Usually, you just combine them into a single table.

2️⃣ One-to-Many (1:M)

One record in Table A relates to multiple records in Table B.

Example: A Customer and Orders. One customer can place many orders. But a specific order only belongs to one customer.
Use in DW: This is the foundation of the Star Schema. One Dimension row (One Customer) connects to many Fact rows (Many Sales).

3️⃣ Many-to-Many (M:M)

Multiple records in Table A relate to multiple records in Table B.

Example: Students and Classes. One student takes many classes. One class has many students.
Use in DW: Databases hate M:M relationships. To fix this, we create a "Bridge" table in the middle to break it into two 1:M relationships.

🧪 Practice Drill

text

// Try answering these:
// Q1. Why is the Star Schema preferred over the Snowflake Schema for Business Intelligence and Reporting?
// Q2. Breaking a single `Product` dimension table into three tables (`Product`, `Category`, `Brand`) to save space is an example of which schema?
// Q3. In a Star Schema, do Dimension tables connect directly to each other?
// Q4. A single `Store` location has thousands of `Sales` transactions. What type of Cardinality is this?
// Q5. Match the analogy to the schema:
// | Analogy | Schema |
// |---|---|
// | A sun with planets orbiting it. | ? |
// | A tree trunk with main branches and sub-branches. | ? |

💡 Click for Solutions

A1. Because it is much faster for querying. The Snowflake schema requires too many table joins, which slows down reports.

A2. Snowflake Schema (because the dimension has been normalized/broken down).

A3. No. Dimension tables only connect to the central Fact table.

A4. One-to-Many (1:M). One store -> Many sales.

A5.

Analogy	Schema
A sun with planets orbiting it.	Star Schema
A tree trunk with main branches and sub-branches.	Snowflake Schema

← Previous Topic | Next Topic → Next Topic