Star and Snowflake Schemas
PedalCity's analysts keep writing seven-way joins across normalized app tables to answer simple questions, and each analyst joins them slightly differently. A dimensional model reorganizes the data around the questions people ask: how many, how much, by what. It is the most common way to model a warehouse.
The rule
Declare the grain first; facts measure events, dimensions describe them.
- Grain: exactly what one row of the fact table represents. For
fact_ridesthe grain is "one completed ride". Decide this before anything else. Every column must be true at that grain. - Fact table: one row per event at the grain, holding measures (numbers you add up, such as
fareandduration_min) and keys pointing to dimensions. - Dimension tables: the context used to filter and group, such as station, rider and date. They hold descriptive text: station name, zone, rider plan, day of week.
In a star schema each dimension is one wide table joined directly to the fact, which makes a star shape:
SELECT d.month_name, s.zone, SUM(f.fare) AS revenue
FROM fact_rides AS f
JOIN dim_date AS d ON d.date_key = f.date_key
JOIN dim_station AS s ON s.station_key = f.start_station_key
GROUP BY d.month_name, s.zone;dim_station holds the station name, zone and city together, even though the zone and city repeat for many stations. That redundancy is deliberate: it trades a little storage for fewer joins.
A snowflake schema normalizes the dimensions further: dim_station points to dim_zone, which points to dim_city. It stores less duplicate text, but "revenue by city" now needs three joins instead of one.
| Star | Snowflake | |
|---|---|---|
| Dimensions | Wide, denormalized | Split into sub-tables |
| Joins per question | Fewer | More |
| Duplicate text | Some | Less |
| Usual choice | Most warehouses | When dimensions are huge or shared |
The shortcut
Sort every column with one question: "Would I add this up, or group by it?" Add it up → a measure in the fact table. Group or filter by it → an attribute in a dimension.
Grain
One row per completed ride.
Drills
Answer fast — trust the rule.
What must you decide first when designing a fact table?
In fact_rides, which column is a measure?
Where does a station's zone belong in a star schema?
In a snowflake schema with station → zone → city, how many dimension joins does "revenue by city" need?
Remember
- Declare the grain first; facts measure events, dimensions describe them.
- Would I add it up (measure) or group by it (dimension attribute)?
- Star: wide dimensions, fewer joins. Snowflake: split dimensions, more joins.