Star Schema, Snowflake Schema & Multidimensional Modelling

-- DIMENSION TABLES (descriptive context — wide, denormalised)
CREATE TABLE dim_date (
    DateKey    INT          PRIMARY KEY,   -- Surrogate key e.g. 20241225
    FullDate   DATE         NOT NULL,
    Day        SMALLINT,    Month     SMALLINT,
    MonthName  VARCHAR(10), Quarter   SMALLINT,
    Year       SMALLINT,    DayOfWeek SMALLINT,
    DayName    VARCHAR(10), IsWeekend BOOLEAN,
    IsHoliday  BOOLEAN,     FiscalYear SMALLINT
);

CREATE TABLE dim_product (
    ProductKey   INT          PRIMARY KEY,   -- Surrogate key (stable, compact)
    ProductID    VARCHAR(20),                -- Business/natural key
    ProductName  VARCHAR(200) NOT NULL,
    SubCategory  VARCHAR(100),
    Category     VARCHAR(100),
    Brand        VARCHAR(100),
    UnitCost     DECIMAL(10,2),
    IsActive     BOOLEAN      DEFAULT TRUE,
    -- SCD Type 2: track historical changes
    EffectiveFrom DATE,  EffectiveTo DATE,  IsCurrent BOOLEAN DEFAULT TRUE
);

CREATE TABLE dim_customer (
    CustomerKey INT          PRIMARY KEY,
    CustomerID  VARCHAR(20),
    Name        VARCHAR(100),
    City        VARCHAR(100), State    VARCHAR(50),
    Country     VARCHAR(50),  Region   VARCHAR(50),
    Segment     VARCHAR(50)   -- Premium, Standard, Economy
);

CREATE TABLE dim_store (
    StoreKey  INT         PRIMARY KEY,
    StoreID   VARCHAR(20),
    StoreName VARCHAR(100),
    StoreType VARCHAR(50), City   VARCHAR(100),
    State     VARCHAR(50), Region VARCHAR(50)
);

-- FACT TABLE: measurable events (FKs to all dimensions + numeric measures)
CREATE TABLE fact_sales (
    SalesID     BIGSERIAL     PRIMARY KEY,
    DateKey     INT NOT NULL  REFERENCES dim_date(DateKey),
    ProductKey  INT NOT NULL  REFERENCES dim_product(ProductKey),
    CustomerKey INT NOT NULL  REFERENCES dim_customer(CustomerKey),
    StoreKey    INT NOT NULL  REFERENCES dim_store(StoreKey),
    -- MEASURES (the numbers you analyse)
    UnitsSold   INT           NOT NULL,
    Revenue     DECIMAL(12,2) NOT NULL,
    Cost        DECIMAL(12,2) NOT NULL,
    Profit      DECIMAL(12,2) GENERATED ALWAYS AS (Revenue - Cost) STORED,
    DiscountAmt DECIMAL(10,2) DEFAULT 0
);

-- Star schema query: revenue by category and quarter
SELECT
    p.Category, d.Year, d.Quarter,
    SUM(f.Revenue) AS Revenue,
    SUM(f.Profit)  AS Profit,
    SUM(f.UnitsSold) AS Units
FROM fact_sales f
JOIN dim_product p ON f.ProductKey = p.ProductKey
JOIN dim_date    d ON f.DateKey    = d.DateKey
WHERE p.IsActive = TRUE
GROUP BY p.Category, d.Year, d.Quarter
ORDER BY d.Year, d.Quarter, Revenue DESC;

-- SNOWFLAKE: dim_product split into sub-dimensions
CREATE TABLE dim_category    (CategoryKey INT PK, CategoryName VARCHAR(100), Department VARCHAR(100));
CREATE TABLE dim_subcategory (SubcatKey INT PK, SubcatName VARCHAR(100), CategoryKey INT REFERENCES dim_category);
CREATE TABLE dim_brand       (BrandKey INT PK, BrandName VARCHAR(100), Country VARCHAR(50));

-- Product dimension references sub-tables (normalised)
CREATE TABLE dim_product_sf (
    ProductKey  INT PRIMARY KEY,
    ProductName VARCHAR(200),
    BrandKey    INT REFERENCES dim_brand(BrandKey),
    SubcatKey   INT REFERENCES dim_subcategory(SubcatKey)
);

-- Snowflake query needs more JOINs
SELECT cat.CategoryName, b.BrandName, SUM(f.Revenue)
FROM fact_sales f
JOIN dim_product_sf p  ON f.ProductKey = p.ProductKey
JOIN dim_subcategory s ON p.SubcatKey  = s.SubcatKey
JOIN dim_category cat  ON s.CategoryKey = cat.CategoryKey
JOIN dim_brand b       ON p.BrandKey   = b.BrandKey
GROUP BY cat.CategoryName, b.BrandName;