Star schema concept in SQL

Introduction

A star schema helps organize data simply so you can find answers fast. It makes reports and analysis easier to understand.

When you want to build a simple and fast database for reporting.

When you have a main subject like sales and details like customers or products.

When you want to make it easy for non-technical users to explore data.

When you need to improve query speed by reducing complex joins.

When you want to create clear and organized dashboards.

Syntax

SQL

-- Star schema has one big fact table in the center
-- and several smaller dimension tables around it.

-- Example structure:
CREATE TABLE FactSales (
  SaleID INT PRIMARY KEY,
  ProductID INT,
  CustomerID INT,
  DateID INT,
  SalesAmount DECIMAL(10,2)
);

CREATE TABLE DimProduct (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Category VARCHAR(50)
);

CREATE TABLE DimCustomer (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100),
  Region VARCHAR(50)
);

CREATE TABLE DimDate (
  DateID INT PRIMARY KEY,
  Date DATE,
  Month VARCHAR(20),
  Year INT
);

The fact table holds numbers like sales or counts.

The dimension tables hold details like names or dates.

Examples

This fact table stores order details with keys linking to dimensions.

SQL

CREATE TABLE FactOrders (
  OrderID INT PRIMARY KEY,
  ProductID INT,
  CustomerID INT,
  OrderDateID INT,
  Quantity INT
);

This dimension table stores product details for easy lookup.

SQL

CREATE TABLE DimProduct (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Brand VARCHAR(50)
);

This dimension table stores date information to analyze trends over time.

SQL

CREATE TABLE DimDate (
  DateID INT PRIMARY KEY,
  Date DATE,
  Quarter VARCHAR(10),
  Year INT
);

Sample Program

This example shows how the star schema tables connect and how to query them to get sales details with names and dates.

SQL

-- Simple star schema example
CREATE TABLE FactSales (
  SaleID INT PRIMARY KEY,
  ProductID INT,
  CustomerID INT,
  DateID INT,
  SalesAmount DECIMAL(10,2)
);

CREATE TABLE DimProduct (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100)
);

CREATE TABLE DimCustomer (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100)
);

CREATE TABLE DimDate (
  DateID INT PRIMARY KEY,
  Date DATE
);

-- Sample query to get sales with product and customer names
SELECT
  d.Date,
  p.ProductName,
  c.CustomerName,
  f.SalesAmount
FROM FactSales f
JOIN DimProduct p ON f.ProductID = p.ProductID
JOIN DimCustomer c ON f.CustomerID = c.CustomerID
JOIN DimDate d ON f.DateID = d.DateID
WHERE d.Date BETWEEN '2024-01-01' AND '2024-01-31';

Important Notes

Keep dimension tables small and descriptive for easy understanding.

Fact tables usually have many rows and numeric data for calculations.

Star schema is great for fast queries but may duplicate some data in dimensions.

Summary

A star schema organizes data with one fact table and many dimension tables.

It makes data easy to explore and speeds up reporting.

Use it when you want simple, clear, and fast data models for business analysis.

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