Star Schema — Facts, Dimensions and Surrogate Keys — Data Warehouse & Analytics Engineering

The star schema is the foundational data warehouse design pattern introduced by Ralph Kimball and still the dominant warehouse modelling approach today. It organises data into two types of tables: fact tables (storing measurable events — every IPL delivery, every ticket sale) and dimension tables (storing descriptive context — who bowled, which venue, which season). The visual shape of a fact table surrounded by dimensions resembles a star. Star schemas optimise for the most common analytical query: aggregating a fact metric across one or more dimension attributes.

The fact table sits at the centre of the star and contains quantitative measures (runs scored, wickets, economy) and foreign keys to every surrounding dimension. Fact tables tend to be very wide (many measure columns) and very deep (billions of rows for mature warehouses). Dimension tables are comparatively narrow and shallow. The star schema's key optimisation is that common analytical queries — 'total wickets per bowler per venue per season' — require joining only the relevant dimensions to the fact table without complex multi-hop joins.

Analogy🏏Cricket

🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.