The Star Schema: Facts, Dimensions, Grain & Surrogate Keys

Build and query a star schema — fact vs. dimension tables, grain as the most important design decision, surrogate keys, conformed dimensions, snowflakes, and slowly changing dimensions — with runnable roll-up queries on real Postgres.

The shape of an analytical schema

The star schema is the data model for a data mart. It deliberately *separates context from measurement*: a central fact table holds the numeric measures of the business, and the surrounding dimension tables hold the descriptive context that qualifies, categorizes, and summarizes those measures. Drawn out, the dimensions radiate from the fact table like the points of a star — hence the name.

- A fact table contains the quantitative facts — units, revenue — plus one foreign key per dimension. Its primary key is the composite of all those foreign keys. Fact tables have *many* rows (one per event at the chosen grain) and dwarf the dimensions. - A dimension table holds descriptive attributes — a product's name/category/brand, a store's city/region, a date's month/quarter/year. Each dimension has a one-to-many relationship to the fact table. Dimensions are *denormalized* on purpose: keeping category and brand in the same row as the product (instead of in separate lookup tables) means analytical queries need fewer joins.

The seeded comp378_star schema is exactly this — one sales_fact ringed by dim_date, dim_product, dim_store, dim_customer:

         dim_date
            |
dim_store — sales_fact — dim_product
            |
        dim_customer

Grain: the one decision everything else depends on

The grain of a fact table is *the level of detail represented by one row*, fixed by the intersection of all the components of its primary key (all the foreign keys). In comp378_star, the grain is *one product sold to one customer at one store on one date*. Grain is the single most important and most difficult design decision: it sets the finest analysis possible. You can always aggregate upward (sum daily rows to a month, sum products to a category) but you can never drill below the grain without going back to another data source. The rule of thumb (Kimball): choose the finest grain you can afford, because users inevitably ask more detailed questions than you anticipated.

Surrogate keys, conformed dimensions, snowflakes, SCDs

- Surrogate keys. Every dimension's primary key is a system-assigned, nonintelligent integer (product_key, not the business product code). Four reasons: business keys get reused/changed across source systems; surrogates let you keep multiple rows for the same entity over time (for slowly changing dimensions); they're short and fast to join; and they give a uniform key format across all dimensions. - Conformed dimension. A dimension shared by two or more fact tables with the *same meaning and the same surrogate keys*, so you can analyze consistently *across* facts (e.g. one dim_date shared by sales_fact and a returns_fact). - Snowflake schema. A star whose dimensions have been normalized into several related tables (e.g. splitting category out of dim_product into its own table). Saves storage but adds joins and complexity — experts keep dimensions denormalized unless there's a strong reason not to. - Slowly changing dimensions (SCD). How you record a dimension attribute that changes over time. Type 1: overwrite (history lost). Type 2: insert a new dimension row with a new surrogate key and date-stamp it (full history — the most common). Type 3: add an extra column for the prior value (limited history). Type 2 is why surrogate keys matter: the same business entity gets several dimension rows.

Worked example: a roll-up query

The payoff of a star schema is how *clean* analytical queries become. To get total revenue and units by product category, join the one fact table to the product dimension and GROUP BY the descriptive attribute:

SELECT p.category,
       SUM(f.units)   AS units,
       SUM(f.revenue) AS revenue
FROM sales_fact f
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY revenue DESC;

One join, one GROUP BY, and you've *rolled up* twelve transaction rows into a per-category summary. That is the whole point: the fact table holds the numbers, the dimension supplies the grouping label, and analytics is SUM(...) ... GROUP BY <dimension attribute>. The exercises below have you write the same shape against each dimension — by period, by region, and the classic two-dimension cross-tab.