This lesson's interactive features are locked, please to keep using them

Star Schema

Now that we know how to aggregate data, let's talk about how to organize it. In a star schema, data is split into fact tables and dimension tables. Fact tables sit in the center, and dimension tables branch out like points of a star.

A fact table stores events: things that happened. Each row represents one event, like an order placed, a payment processed, or a page viewed.

What Makes a Fact Table

Fact tables have 4 types of columns:

Event key – an ID for the event, like order_id (optional)
Foreign keys – references to dimension tables (customer_id, product_id, store_id)
Metrics – measurable values (revenue, quantity, discount_amount)
Timestamps – when the event occurred (order_date, payment_time)

orders = pd.DataFrame(
    {
        "order_id": ["ORD-7K2M", "ORD-9P4X", "ORD-3D8V", "ORD-6N1R"],
        "customer_id": ["CUS-4821", "CUS-7316", "CUS-4821", "CUS-2059"],
        "product_id": ["CHL-482", "TST-731", "CHL-482", "OVN-205"],
        "order_date": ["2024-01-15", "2024-01-15", "2024-01-16", "2024-01-16"],
        "quantity": [2, 1, 3, 1],
        "revenue": [1598.00, 129.00, 2397.00, 349.00],
    }
)

No customer names or product descriptions – just IDs, metrics, and timestamps. The descriptive details live elsewhere, in dimension tables.

Benefits of Fact Tables

Fact tables are where your metrics live. When someone asks, "What's total revenue by region?" the revenue comes from a fact table. You join it to dimension tables to get the region.

Keeping facts separate from descriptive data means:

No duplication – customer name appears once in the customer table, not on every order
Consistent metrics – one source of truth for revenue, quantity, etc.
Flexible analysis – join to any dimension table to slice the data differently