Fact and Dimension Table Modeling

In this lesson, you will learn how to distinguish between "what happened" and "who/where/what it happened to" to build a clean star schema.

When designing a data warehouse, we organize data into two distinct roles: Fact tables and Dimension tables. Thinking of your data this way moves you away from the complex, normalized structures of transactional databases and toward a design optimized for analytical speed.

1. Fact Tables (The "What Happened")

A Fact table stores the measurable, quantitative data about a business process. These are your metrics—things you can count, sum, or average. Examples include the sales_amount, quantity_sold, or discount_value. A Fact table usually contains foreign keys pointing to various dimension tables and the raw numeric values themselves.

2. Dimension Tables (The "Descriptive Context")

A Dimension table provides the context for those facts. They contain the descriptive attributes that you use to filter, group, or label your reports. Think of these as the "Who, What, Where, and When" of your data. If you have a sales_fact table, you need a dim_product table to tell you the product name, category, and color.

Practical Example

Imagine an e-commerce platform. Instead of one massive table, you split it:

The Fact Table (fact_sales)

CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    date_key INT,       -- FK to dim_date
    product_key INT,    -- FK to dim_product
    store_key INT,      -- FK to dim_store
    quantity INT,
    total_price DECIMAL(10, 2)
);

The Dimension Table (dim_product)

CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(100),
    brand VARCHAR(100)
);

By separating these, you gain clarity. If you need to know "Total revenue per category," the query engine simply joins the fact_sales table to the dim_product table. Because the Fact table is kept lean with numeric data and foreign keys, the join operations are incredibly fast.

When designing your own schema, ask yourself: "Is this a value I want to perform math on?" If yes, it belongs in a Fact table. "Is this a label I want to use to group my results?" If yes, it belongs in a Dimension table.

Key Takeaway: Separate your business process measurements into Fact tables and the descriptive attributes used to analyze them into Dimension tables to ensure your analytical queries remain performant and scalable.