Data Warehouse Concepts: Star Schema, Snowflake Schema & Dimensional Modeling

-- Star Schema: Fact Table with Surrogate Keys
CREATE TABLE fact_sales (
    sale_key        BIGINT IDENTITY(1,1) PRIMARY KEY,
    customer_key    INT NOT NULL REFERENCES dim_customer(customer_key),
    product_key     INT NOT NULL REFERENCES dim_product(product_key),
    store_key       INT NOT NULL REFERENCES dim_store(store_key),
    date_key        INT NOT NULL REFERENCES dim_date(date_key),
    promotion_key   INT NOT NULL REFERENCES dim_promotion(promotion_key),
    quantity_sold   INT,
    unit_price      DECIMAL(12,2),
    discount_pct    DECIMAL(5,4),
    gross_amount    DECIMAL(14,2),
    net_amount      DECIMAL(14,2),
    cost_of_goods   DECIMAL(14,2),
    profit_margin   DECIMAL(5,4),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Star Schema: Denormalized Customer Dimension
CREATE TABLE dim_customer (
    customer_key    INT IDENTITY(1,1) PRIMARY KEY,
    customer_id     VARCHAR(50) UNIQUE NOT NULL,
    full_name       VARCHAR(200),
    email           VARCHAR(300),
    phone           VARCHAR(50),
    address_line1   VARCHAR(200),
    address_line2   VARCHAR(200),
    city            VARCHAR(100),
    state_province  VARCHAR(100),
    postal_code     VARCHAR(20),
    country         VARCHAR(100),
    region          VARCHAR(50),
    segment         VARCHAR(50),
    loyalty_tier    VARCHAR(20),
    lifetime_value  DECIMAL(14,2),
    valid_from      TIMESTAMP,
    valid_to        TIMESTAMP,
    is_current      BOOLEAN DEFAULT TRUE
);

-- Star Schema: Date Dimension
CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,
    full_date       DATE UNIQUE NOT NULL,
    day_of_week     SMALLINT,
    day_name        VARCHAR(10),
    month_number    SMALLINT,
    month_name      VARCHAR(10),
    quarter         SMALLINT,
    year            INT,
    fiscal_year     INT,
    fiscal_quarter  SMALLINT,
    is_weekend      BOOLEAN,
    is_holiday      BOOLEAN
);

-- Sample Star Schema Query
SELECT
    d.year,
    d.quarter,
    c.country,
    c.segment,
    p.product_category,
    SUM(f.net_amount)           AS total_revenue,
    SUM(f.quantity_sold)        AS units_sold,
    AVG(f.profit_margin)        AS avg_margin,
    COUNT(DISTINCT f.customer_key) AS unique_customers
FROM fact_sales f
JOIN dim_customer c  ON f.customer_key = c.customer_key
JOIN dim_product p   ON f.product_key = p.product_key
JOIN dim_date d      ON f.date_key = d.date_key
WHERE d.year = 2025 AND c.country = 'United States'
GROUP BY d.year, d.quarter, c.country, c.segment, p.product_category
ORDER BY total_revenue DESC;

-- Snowflake Schema: Normalized Dimensions
CREATE TABLE dim_customer (
    customer_key    INT IDENTITY(1,1) PRIMARY KEY,
    customer_id     VARCHAR(50) UNIQUE NOT NULL,
    full_name       VARCHAR(200),
    email           VARCHAR(300),
    city_key        INT REFERENCES dim_city(city_key),
    segment_key     INT REFERENCES dim_segment(segment_key),
    valid_from      TIMESTAMP,
    valid_to        TIMESTAMP,
    is_current      BOOLEAN DEFAULT TRUE
);

CREATE TABLE dim_city (
    city_key        INT IDENTITY(1,1) PRIMARY KEY,
    city_name       VARCHAR(100),
    state_key       INT REFERENCES dim_state(state_key)
);

CREATE TABLE dim_state (
    state_key       INT IDENTITY(1,1) PRIMARY KEY,
    state_name      VARCHAR(100),
    country_key     INT REFERENCES dim_country(country_key)
);

CREATE TABLE dim_country (
    country_key     INT IDENTITY(1,1) PRIMARY KEY,
    country_name    VARCHAR(100),
    continent       VARCHAR(50),
    currency_code   VARCHAR(3)
);

-- Snowflake Query with Multiple Joins
SELECT
    co.country_name,
    st.state_name,
    ci.city_name,
    sg.segment_name,
    SUM(f.net_amount) AS total_revenue
FROM fact_sales f
JOIN dim_customer cu  ON f.customer_key = cu.customer_key
JOIN dim_city ci      ON cu.city_key = ci.city_key
JOIN dim_state st     ON ci.state_key = st.state_key
JOIN dim_country co   ON st.country_key = co.country_key
JOIN dim_segment sg   ON cu.segment_key = sg.segment_key
GROUP BY co.country_name, st.state_name, ci.city_name, sg.segment_name;

-- SCD Type 2 Implementation
CREATE TABLE dim_customer_scd2 (
    customer_key    BIGINT IDENTITY(1,1) PRIMARY KEY,
    customer_id     VARCHAR(50) NOT NULL,
    full_name       VARCHAR(200),
    email           VARCHAR(300),
    segment         VARCHAR(50),
    city            VARCHAR(100),
    valid_from      TIMESTAMP NOT NULL,
    valid_to        TIMESTAMP NOT NULL DEFAULT '9999-12-31',
    is_current      BOOLEAN DEFAULT TRUE,
    row_hash        VARCHAR(64)  -- MD5 hash for change detection
);

-- Merge logic for SCD Type 2
MERGE INTO dim_customer_scd2 AS target
USING (
    SELECT
        customer_id,
        full_name,
        email,
        segment,
        city,
        MD5(CONCAT(full_name, '|', email, '|', segment, '|', city)) AS row_hash
    FROM staging.customers
) AS source
ON target.customer_id = source.customer_id AND target.is_current = TRUE
WHEN MATCHED AND target.row_hash != source.row_hash THEN
    UPDATE SET
        is_current = FALSE,
        valid_to = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, full_name, email, segment, city, valid_from, is_current, row_hash)
    VALUES (source.customer_id, source.full_name, source.email,
            source.segment, source.city, CURRENT_TIMESTAMP, TRUE, source.row_hash);

-- Insert new versions for changed records
INSERT INTO dim_customer_scd2
    (customer_id, full_name, email, segment, city, valid_from, is_current, row_hash)
SELECT
    source.customer_id, source.full_name, source.email,
    source.segment, source.city, CURRENT_TIMESTAMP, TRUE, source.row_hash
FROM staging.customers source
JOIN dim_customer_scd2 target
    ON source.customer_id = target.customer_id
    AND target.row_hash != MD5(CONCAT(source.full_name, '|', source.email,
        '|', source.segment, '|', source.city))
WHERE target.is_current = TRUE;

-- Transaction Fact Table
CREATE TABLE fact_web_events (
    event_key       BIGINT IDENTITY(1,1) PRIMARY KEY,
    visitor_key     INT NOT NULL,
    session_key     INT NOT NULL,
    page_key        INT NOT NULL,
    event_date_key  INT NOT NULL,
    event_timestamp TIMESTAMP,
    page_view_count INT DEFAULT 1,
    click_count     INT DEFAULT 0,
    bounce_flag     BOOLEAN,
    session_duration_sec INT,
    referrer_domain VARCHAR(200)
);

-- Periodic Snapshot Fact Table (Monthly)
CREATE TABLE fact_inventory_monthly (
    inventory_key   BIGINT IDENTITY(1,1) PRIMARY KEY,
    product_key     INT NOT NULL,
    warehouse_key   INT NOT NULL,
    month_date_key  INT NOT NULL,  -- First day of month
    quantity_on_hand INT,
    quantity_reserved INT,
    quantity_available INT,
    unit_cost       DECIMAL(12,4),
    total_value     DECIMAL(14,2),
    reorder_point   INT,
    stockout_flag   BOOLEAN
);

-- Accumulating Snapshot Fact Table
CREATE TABLE fact_order_lifecycle (
    order_key           BIGINT PRIMARY KEY,
    customer_key        INT NOT NULL,
    product_key         INT NOT NULL,
    order_date_key      INT NOT NULL,
    ship_date_key       INT,  -- NULL until shipped
    delivery_date_key   INT,  -- NULL until delivered
    return_date_key     INT,  -- NULL if not returned
    order_amount        DECIMAL(14,2),
    shipping_cost       DECIMAL(10,2),
    days_to_ship        INT,  -- Calculated: ship_date - order_date
    days_to_deliver     INT   -- Calculated: delivery_date - ship_date
);