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

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

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

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

Hint

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

Practice

1. What is the main characteristic of a star schema in a data warehouse?

easy

A. One central fact table connected to multiple dimension tables

B. Multiple fact tables connected to one dimension table

C. Only dimension tables without any fact tables

D. A flat table with no relationships

Solution

1. Step 1: Understand star schema structure

   A star schema has a central fact table that stores measurable data.

2. Step 2: Identify dimension tables role

   Dimension tables surround the fact table and provide descriptive attributes.

3. Final Answer:

   One central fact table connected to multiple dimension tables -> Option A

4. Quick Check:

   Star schema = central fact + many dimensions [OK]

Hint: Fact table is the center, dimensions are spokes [OK]

Common Mistakes:

• Confusing fact and dimension tables
• Thinking star schema has multiple fact tables
• Assuming no relationships exist

2. Which SQL JOIN type is typically used to connect fact and dimension tables in a star schema?

easy

A. INNER JOIN

B. LEFT JOIN

C. FULL OUTER JOIN

D. CROSS JOIN

Solution

1. Step 1: Understand fact-dimension relationship

   Fact tables usually have matching keys in dimension tables.

2. Step 2: Choose JOIN type for matching rows

   INNER JOIN returns only matching rows, ideal for star schema queries.

3. Final Answer:

   INNER JOIN -> Option A

4. Quick Check:

   Fact and dimension use INNER JOIN [OK]

Hint: Use INNER JOIN to link matching keys [OK]

Common Mistakes:

• Using CROSS JOIN which multiplies rows
• Using FULL OUTER JOIN causing unnecessary nulls
• Using LEFT JOIN when INNER JOIN suffices

3. Given a star schema with a fact table Sales and dimension table Product, what does this SQL query return?

SELECT p.ProductName, SUM(s.SalesAmount) AS TotalSales
FROM Sales s
JOIN Product p ON s.ProductID = p.ProductID
GROUP BY p.ProductName;

medium

A. Sales amount grouped by sales date

B. Total sales amount for each product

C. Total sales amount for all products combined

D. List of products without sales data

Solution

1. Step 1: Analyze JOIN and GROUP BY

   The query joins Sales and Product on ProductID and groups results by product name.

2. Step 2: Understand aggregation

   SUM(s.SalesAmount) calculates total sales per product group.

3. Final Answer:

   Total sales amount for each product -> Option B

4. Quick Check:

   GROUP BY product sums sales per product [OK]

Hint: GROUP BY product sums sales per product [OK]

Common Mistakes:

• Ignoring GROUP BY causing total for all products
• Confusing JOIN keys
• Assuming query lists products without sales

4. Identify the error in this SQL query using a star schema:

SELECT d.CustomerName, SUM(f.SalesAmount)
FROM FactSales f
JOIN DimProduct d ON f.ProductID = d.ProductID
GROUP BY d.CustomerName;

medium

A. JOIN uses wrong dimension table alias

B. GROUP BY column not in SELECT

C. SUM aggregation missing alias

D. JOIN condition uses wrong keys

Solution

1. Step 1: Check JOIN condition keys

   FactSales.ProductID should join with DimProduct.ProductID, but SELECT and GROUP BY use CustomerName from a different dimension.

2. Step 2: Identify mismatch in dimension usage

   CustomerName likely belongs to DimCustomer, not DimProduct, so JOIN keys are incorrect.

3. Final Answer:

   JOIN condition uses wrong keys -> Option D

4. Quick Check:

   JOIN keys must match correct dimension [OK]

Hint: Match JOIN keys to correct dimension table [OK]

Common Mistakes:

• Grouping by column not selected
• Using alias inconsistently
• Missing aggregation alias

5. You want to design a star schema for sales data including customers, products, and time. Which approach best supports fast queries and easy analysis?

hard

A. Store all data in one big table without relationships

B. Create multiple fact tables for each dimension and one large dimension table

C. Create one fact table for sales and separate dimension tables for customers, products, and time

D. Use normalized tables with many joins instead of star schema

Solution

1. Step 1: Understand star schema design principles

   Star schema uses one fact table for measurable events and multiple dimension tables for descriptive data.

2. Step 2: Evaluate options for query speed and simplicity

   Create one fact table for sales and separate dimension tables for customers, products, and time matches star schema best, enabling fast joins and simple queries.

3. Final Answer:

   Create one fact table for sales and separate dimension tables for customers, products, and time -> Option C

4. Quick Check:

   Star schema = 1 fact + many dimensions [OK]

Hint: One fact table, many dimension tables for star schema [OK]

Common Mistakes:

• Using multiple fact tables unnecessarily
• Flattening all data into one table
• Choosing normalized schema over star schema for BI