Star schema concept in SQL - Mini Project: Build & Apply

Star schema concept

📖 Scenario: You are building a data warehouse for a retail company. Sales data from multiple stores needs to be organized for fast analytical queries. You'll design a star schema with a fact table and dimension tables.

🎯 Goal: Create a star schema with a fact_sales table, three dimension tables (product, store, date), and write analytical queries against it.

📋 What You'll Learn

Create dimension tables for product, store, and date

Create a fact table with foreign keys to all dimensions

Write an analytical query joining fact to dimensions

Add indexes on fact table foreign keys

💡 Why This Matters

🌍 Real World

Star schemas are the foundation of data warehouses at companies like Amazon, Walmart, and Netflix for analyzing sales, user behavior, and operational metrics.

💼 Career

Data engineers and analysts must understand star schema design to build and query data warehouses effectively.

1

Create the dimension tables

Create three dimension tables: dim_product with columns product_key (INT PRIMARY KEY), product_name (VARCHAR(100)), category (VARCHAR(50)). dim_store with store_key (INT PRIMARY KEY), store_name (VARCHAR(100)), city (VARCHAR(50)). dim_date with date_key (INT PRIMARY KEY), full_date (DATE), quarter (INT), year (INT).

SQL

-- Create dim_product, dim_store, and dim_date tables
-- Your code here

Need a hint?

Each dimension table needs an integer primary key (surrogate key) and descriptive attributes.

2

Create the fact table

Create fact_sales with columns: sale_id (INT PRIMARY KEY), date_key (INT REFERENCES dim_date(date_key)), product_key (INT REFERENCES dim_product(product_key)), store_key (INT REFERENCES dim_store(store_key)), quantity (INT), and total_amount (DECIMAL(10,2)).

SQL

CREATE TABLE dim_product (
  product_key INT PRIMARY KEY,
  product_name VARCHAR(100),
  category VARCHAR(50)
);

CREATE TABLE dim_store (
  store_key INT PRIMARY KEY,
  store_name VARCHAR(100),
  city VARCHAR(50)
);

CREATE TABLE dim_date (
  date_key INT PRIMARY KEY,
  full_date DATE,
  quarter INT,
  year INT
);

-- Create fact_sales with foreign keys to all dimensions
-- Your code here

Need a hint?

The fact table's foreign keys reference each dimension's primary key. This creates the star shape.

3

Write an analytical query

Write a query that shows total revenue and units sold per product category per quarter for the year 2024. Join fact_sales to dim_product and dim_date. Use SUM(fs.total_amount) as total_revenue and SUM(fs.quantity) as units_sold. Filter where dd.year = 2024. Group by category and quarter. Use aliases fs, dp, dd.

SQL

-- Total revenue and units sold by category and quarter for 2024
-- Your code here

Need a hint?

Join fact to dimensions, filter on dimension attributes, aggregate fact measures, group by dimension attributes.

4

Add indexes on foreign keys

Create three indexes on the fact_sales table: idx_fact_date on date_key, idx_fact_product on product_key, and idx_fact_store on store_key.

SQL

-- Create indexes on fact table foreign keys
-- Your code here

Need a hint?

Use CREATE INDEX name ON table(column) for each foreign key in the fact table.