Star Schema — The Default Analytics Layout

The most common analytics warehouse model. Looks like a star: one fact table in the centre, dimension tables around it. Almost every BI dashboard you'll ever query sits on top of one.

The shape

              dim_date
                 |
   dim_customer—FACT_ORDERS—dim_product
                 |
              dim_store

The fact table holds the measurements (revenue, quantity, count). Each dimension table holds the context (who, what, when, where).

Fact tables

A fact table represents an event or transaction. Each row is one occurrence.

Schema:

CREATE TABLE fct_orders (
    order_id      BIGINT,          -- the natural key
    date_key      INTEGER,          -- FK to dim_date
    customer_key  INTEGER,          -- FK to dim_customer
    product_key   INTEGER,          -- FK to dim_product
    store_key     INTEGER,          -- FK to dim_store

    quantity      INTEGER,          -- measure
    unit_price    NUMERIC,          -- measure
    discount      NUMERIC,          -- measure
    revenue       NUMERIC,          -- derived measure (quantity * unit_price - discount)

    inserted_at   TIMESTAMPTZ
);

Two types of columns:

• Foreign keys to dimensions — what context applies to this row.
• Measures — numbers you sum, average, count over.

That's it. Nothing else lives in a fact table.

Dimension tables

A dimension table holds the attributes of a business concept. Schema is simple:

CREATE TABLE dim_customer (
    customer_key   INTEGER PRIMARY KEY,   -- surrogate key
    customer_id    VARCHAR,                -- natural key from source
    name           VARCHAR,
    email          VARCHAR,
    signup_date    DATE,
    segment        VARCHAR,                -- 'enterprise', 'smb', 'consumer'
    country        VARCHAR,
    is_active      BOOLEAN,

    effective_from DATE,                   -- SCD Type 2
    effective_to   DATE,                   -- SCD Type 2
    is_current     BOOLEAN
);

Two types of columns:

• Keys — surrogate (warehouse-generated) and natural (source-system).
• Descriptive attributes — used to filter, group by, and label in dashboards.

Why surrogate keys

Notice customer_key is an integer, not the source customer_id. Reasons:

1. Smaller — integer keys compress better than VARCHAR/UUID natural keys.
2. Stable — if a source renames or re-uses customer_id, the surrogate key shields downstream models.
3. SCD-friendly — when a customer changes (SCD Type 2), they get a new surrogate row with a new surrogate key, but the natural customer_id stays the same.
4. Multi-source merging — if two source systems have overlapping IDs, surrogate keys disambiguate.

The query pattern

Every dashboard query on a star schema looks similar:

SELECT
    dd.year,
    dd.month,
    dc.country,
    dp.category,
    SUM(fo.revenue) AS total_revenue,
    COUNT(*) AS order_count
FROM fct_orders fo
JOIN dim_date dd     ON dd.date_key = fo.date_key
JOIN dim_customer dc ON dc.customer_key = fo.customer_key
JOIN dim_product dp  ON dp.product_key = fo.product_key
WHERE dd.year = 2026
GROUP BY 1, 2, 3, 4;

This pattern is so common that warehouse engines (Redshift, BigQuery, Snowflake) optimize for it specifically — "star join optimizations".

Why this shape wins

• Predictable query patterns — almost every BI query follows the SELECT…FROM fact JOIN dimensions…GROUP BY structure. Easy to optimize.
• Stable schema for the business — dimension tables hide source complexity behind business-friendly names.
• Easy to evolve — adding a new dimension is one table + one FK. Doesn't disturb existing queries.
• Aggregations are obvious — every measure is in the fact, every grouping is in a dimension.

What goes wrong

• Wrong grain — fact rows mix granularities (one row per order vs one row per order_line). Aggregations double-count. See lesson 2.3.
• Missing dimension — a measure with no dimension is hard to slice ("revenue by …?"). Add the right dimensions.
• Embedded text in facts — putting product_name as a string in the fact instead of joining to dim_product. Makes the fact wide, blocks slowly-changing-dimension behaviour.

When it's NOT the right shape

• Pure event logs with no fixed grain — e.g., raw clickstream. Better stored as wide tables or in a separate event store.
• Hierarchical / graph data — org charts, social networks. Star schemas can model "snapshot at a point in time" but graph queries are awkward.
• Highly aggregated KPI dashboards — sometimes a pre-aggregated cube (e.g., "daily revenue by region by product category") performs better than star + GROUP BY.

Takeaway

The star schema is the default for a reason: it matches how analysts think (measure + context), how engines optimize, and how dashboards filter. Most warehouse design boils down to deciding which facts, which dimensions, and the grain of each fact. The rest of this module is about doing that well.