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:
- Start with the fact table as your base
- Use INNER JOINs to connect each relevant dimension
- 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.