Snowflake Schema

`
-- Star schema (denormalized) vs Snowflake (normalized)

-- STAR SCHEMA (single-level dimensions)
CREATE TABLE dim_product_star (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255),
  category VARCHAR(100),
  category_description TEXT,
  subcategory VARCHAR(100),
  subcategory_description TEXT,
  department VARCHAR(50),
  supplier_id INT
);

-- SNOWFLAKE SCHEMA (normalized dimensions)
CREATE TABLE dim_category (
  category_id INT PRIMARY KEY,
  category_name VARCHAR(100),
  category_description TEXT
);

CREATE TABLE dim_subcategory (
  subcategory_id INT PRIMARY KEY,
  subcategory_name VARCHAR(100),
  category_id INT REFERENCES dim_category,
  subcategory_description TEXT
);

CREATE TABLE dim_department (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

CREATE TABLE dim_product_snowflake (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255),
  subcategory_id INT REFERENCES dim_subcategory,
  department_id INT REFERENCES dim_department,
  supplier_id INT
);

-- Query comparison:

-- Star schema query (fewer joins)
SELECT 
  p.category,
  p.department,
  SUM(f.revenue) as total_revenue
FROM fct_sales f
JOIN dim_product_star p ON f.product_id = p.product_id
GROUP BY p.category, p.department;

-- Snowflake schema query (more joins)
SELECT 
  c.category_name,
  d.department_name,
  SUM(f.revenue) as total_revenue
FROM fct_sales f
JOIN dim_product_snowflake p ON f.product_id = p.product_id
JOIN dim_subcategory s ON p.subcategory_id = s.subcategory_id
JOIN dim_category c ON s.category_id = c.category_id
JOIN dim_department d ON p.department_id = d.department_id
GROUP BY c.category_name, d.department_name;
`