Course contentsShow
← Data Science
Lesson 1808 of 2,14542. Working with Big DataFree lesson

Star Schema and Fact Tables

Learn the star schema design pattern with central fact tables storing metrics and foreign keys to dimensions.

Star Schema and Fact Tables

What You'll Learn

You'll learn how the star schema organizes data around a central fact table connected to dimension tables, optimizing analytical queries in data warehouses.


The Star Schema Design Pattern

The star schema is a data warehouse modeling technique that structures data like a star: a central fact table surrounded by multiple dimension tables. Think of it as a hub-and-spoke model where all analysis flows through the center.

Fact Tables: The Analytical Core

A fact table stores the quantitative metrics (facts) you want to analyze—revenue, units sold, page views, conversion events, etc. Each row represents a measurable business event or transaction.

Critically, fact tables contain:

  • Metrics/Measures: Numerical values you aggregate (sum, average, count)
  • Foreign Keys: References to dimension tables that provide context

For example, a sales fact table might have revenue, quantity, discount_amount as metrics, plus foreign keys like customer_id, product_id, store_id, date_id.

Dimension Tables: The Context

Dimension tables surround the fact table, providing descriptive attributes. A customer dimension has names, addresses, segments. A product dimension has categories, prices, brands. Each dimension connects to the fact table through its primary key.

This design creates a star shape: one fact table at the center, dimension tables radiating outward.

Why This Matters

Star schemas are optimized for analytical queries. You can easily slice metrics by any dimension (e.g., "total revenue by product category and region") with simple joins. The denormalized dimension tables reduce join complexity, and the separation of facts from dimensions keeps fact tables lean and fast to scan.

Key Takeaway:
Star schemas organize data warehouses around central fact tables containing metrics and foreign keys, connected to denormalized dimension tables that provide business context—enabling fast, flexible analytics.