Exploring Snowflake Schema and Galaxy Schema in Data Modeling

While Star Schema is a widely adopted schema design in data warehousing, more complex data modeling scenarios may necessitate alternative approaches such as Snowflake Schema and Galaxy Schema. In this article, we explore these advanced data modeling techniques, providing detailed explanations, examples, and outputs to broaden your understanding.

1. Snowflake Schema

Snowflake Schema extends the Star Schema concept by normalizing dimension tables, thereby reducing redundancy and improving data integrity. In a Snowflake Schema, dimension tables are organized into multiple levels of hierarchy, with each level linked through primary and foreign key relationships.

Example:

Consider the retail sales data warehouse scenario. In a Snowflake Schema, the dimension tables may be normalized to represent hierarchical relationships. For instance, the “Product” dimension could have separate tables for “Product Category,” “Subcategory,” and “Product Details.”

CREATE TABLE DimProduct (
    ProductID INT PRIMARY KEY,
    CategoryID INT,
    SubcategoryID INT,
    ProductName VARCHAR(100),
    ...
);

CREATE TABLE DimCategory (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50)
    ...
);

CREATE TABLE DimSubcategory (
    SubcategoryID INT PRIMARY KEY,
    SubcategoryName VARCHAR(50),
    CategoryID INT,
    ...
);

CREATE TABLE FactSales (
    SalesID INT PRIMARY KEY,
    ProductID INT,
    StoreID INT,
    TimeID INT,
    CustomerID INT,
    SalesAmount DECIMAL(10, 2),
    QuantitySold INT
);

CREATE TABLE FactInventory (
    InventoryID INT PRIMARY KEY,
    ProductID INT,
    StoreID INT,
    TimeID INT,
    QuantityOnHand INT,
    ...
);