Course contentsShow
← Data Science
Lesson 956 of 2,14524. SQL JoinsFree lesson

Star Schema Joins

Joining fact tables to multiple dimension tables in data warehouse patterns.

Star Schema Joins

What you'll learn: How to join a central fact table to multiple dimension tables to create analytical queries in data warehouse patterns.

What is a Star Schema?

A star schema is a common data warehouse design where one central fact table (containing measurements like sales amounts, quantities, or counts) connects to multiple dimension tables (containing descriptive attributes like customer names, product details, or dates). When viewed visually, this structure resembles a star.

Think of a retail sales fact table at the center, surrounded by dimensions like customers, products, stores, and dates. Each fact record links to these dimensions through foreign keys.

Star Schema Joins in Practice

In a star schema query, you typically:

  1. Start with the fact table as your base
  2. Use INNER JOINs to connect each relevant dimension
  3. Filter or aggregate using dimension attributes
SELECT 
    d.date,
    p.product_name,
    c.customer_name,
    SUM(f.sales_amount) AS total_sales
FROM sales_fact f
INNER JOIN date_dim d ON f.date_id = d.date_id
INNER JOIN product_dim p ON f.product_id = p.product_id
INNER JOIN customer_dim c ON f.customer_id = c.customer_id
WHERE d.year = 2024
GROUP BY d.date, p.product_name, c.customer_name;

Notice how each dimension adds context to the numeric facts. The fact table stays compact (just IDs and measures), while dimensions provide the descriptive details.

Why Star Schemas Matter

Star schemas optimize analytical queries by separating what happened (facts) from the context (dimensions). You can join only the dimensions you need for each query, keeping things flexible and efficient.

Key Takeaway: Star schema joins connect a central fact table to multiple dimension tables using INNER JOINs on foreign key relationships, enabling flexible analytical queries that combine measurements with descriptive context.