Fact Table vs Dimension Table: Key Differences Explained
A Fact Table stores quantitative, transactional metrics—sales revenue, clicks, temperature—each row a measurable event. A Dimension Table holds descriptive context—customer names, product colors, store locations—each row a lens through which to filter facts.
Picture a cashier scanning a cereal box. The beep records price and quantity (Fact Table) but the cereal’s brand, flavor, and shelf number live elsewhere (Dimension Table). Analysts juggle both tables daily, yet newcomers conflate them because both appear in the same star schema diagram.
Key Differences
Fact Tables are long and narrow, loaded with numeric keys pointing to dimensions and additive measures. Dimension Tables are short and wide, with verbose text attributes and slowly changing values. One captures “what happened”; the other explains “who, what, where, when, and why.”
Which One Should You Choose?
Choose a Fact Table when you need to aggregate millions of events. Build a Dimension Table to enrich those events with searchable labels. In practice you always use both—join them, never replace one with the other.
Examples and Daily Life
Your DoorDash order: the Fact Table logs $23.50 at 7:12 pm; Dimension Tables hold “Spicy Thai,” “Jane Doe,” and “123 Maple Ave.” Spotify’s play count is the fact; artist genre, song length, and user subscription tier are dimensions shaping every chart you scroll.
Can a Fact Table hold text?
Only foreign keys and numeric measures—descriptive text belongs in Dimension Tables.
Do dimensions ever change?
Yes, slowly: a customer moves cities, a product rebrands. Use slowly changing dimension techniques to track history without breaking old reports.