Star Schema vs Snowflake vs Data Vault: Data Modeling Guide

-- Create fact and dimension tables for a star schema
CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE dim_time (
    time_id INT PRIMARY KEY,
    date DATE,
    month INT,
    quarter INT,
    year INT
);

CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    time_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id),
    FOREIGN KEY (time_id) REFERENCES dim_time(time_id)
);

-- Query: total sales by category
SELECT p.category, SUM(f.amount) AS total_sales
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY p.category;

-- Snowflake schema with normalized dimensions
CREATE TABLE dim_category (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    price DECIMAL(10,2),
    FOREIGN KEY (category_id) REFERENCES dim_category(category_id)
);

CREATE TABLE dim_city (
    city_id INT PRIMARY KEY,
    city_name VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES dim_city(city_id)
);

CREATE TABLE dim_time (
    time_id INT PRIMARY KEY,
    date DATE,
    month INT,
    quarter INT,
    year INT
);

CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    time_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id),
    FOREIGN KEY (time_id) REFERENCES dim_time(time_id)
);

-- Query: total sales by category (now requires 3 joins)
SELECT c.category_name, SUM(f.amount) AS total_sales
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_category c ON p.category_id = c.category_id
GROUP BY c.category_name;

-- Data Vault example for sales
CREATE TABLE hub_customer (
    customer_hk CHAR(32) PRIMARY KEY, -- hash key
    customer_id INT NOT NULL,
    load_date TIMESTAMP,
    record_source VARCHAR(50)
);

CREATE TABLE hub_product (
    product_hk CHAR(32) PRIMARY KEY,
    product_id INT NOT NULL,
    load_date TIMESTAMP,
    record_source VARCHAR(50)
);

CREATE TABLE link_sale (
    sale_hk CHAR(32) PRIMARY KEY,
    customer_hk CHAR(32),
    product_hk CHAR(32),
    load_date TIMESTAMP,
    record_source VARCHAR(50),
    FOREIGN KEY (customer_hk) REFERENCES hub_customer(customer_hk),
    FOREIGN KEY (product_hk) REFERENCES hub_product(product_hk)
);

CREATE TABLE sat_customer (
    customer_hk CHAR(32),
    load_date TIMESTAMP,
    customer_name VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50),
    PRIMARY KEY (customer_hk, load_date),
    FOREIGN KEY (customer_hk) REFERENCES hub_customer(customer_hk)
);

CREATE TABLE sat_product (
    product_hk CHAR(32),
    load_date TIMESTAMP,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    PRIMARY KEY (product_hk, load_date),
    FOREIGN KEY (product_hk) REFERENCES hub_product(product_hk)
);

-- Query: current customer name and total sales (requires multiple joins)
SELECT sc.customer_name, SUM(f.amount) AS total_sales
FROM link_sale ls
JOIN hub_customer hc ON ls.customer_hk = hc.customer_hk
JOIN sat_customer sc ON hc.customer_hk = sc.customer_hk
    AND sc.load_date = (SELECT MAX(load_date) FROM sat_customer WHERE customer_hk = hc.customer_hk)
JOIN hub_product hp ON ls.product_hk = hp.product_hk
JOIN sat_product sp ON hp.product_hk = sp.product_hk
    AND sp.load_date = (SELECT MAX(load_date) FROM sat_product WHERE product_hk = hp.product_hk)
GROUP BY sc.customer_name;

-- Example: Creating a star schema for a sales dashboard
-- This is a typical use case for star schema
CREATE TABLE fact_sales (
    sale_id INT,
    product_id INT,
    customer_id INT,
    time_id INT,
    amount DECIMAL(10,2)
);

-- For a snowflake schema, normalize product
CREATE TABLE dim_category (category_id INT, category_name VARCHAR(50));
CREATE TABLE dim_product (product_id INT, product_name VARCHAR(100), category_id INT);

-- For Data Vault, create hubs and satellites
CREATE TABLE hub_product (product_hk CHAR(32), product_id INT);
CREATE TABLE sat_product (product_hk CHAR(32), load_date TIMESTAMP, product_name VARCHAR(100));

-- Create a star schema view from snowflake tables
CREATE VIEW star_sales AS
SELECT f.sale_id, f.quantity, f.amount,
       p.product_name, c.category_name,
       cu.customer_name, ci.city_name,
       t.date, t.month, t.quarter, t.year
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_category c ON p.category_id = c.category_id
JOIN dim_customer cu ON f.customer_id = cu.customer_id
JOIN dim_city ci ON cu.city_id = ci.city_id
JOIN dim_time t ON f.time_id = t.time_id;

-- Now query the view as if it were a star schema
SELECT category_name, SUM(amount) AS total_sales
FROM star_sales
GROUP BY category_name;