03.02 · Walkthrough
Star Schemas for Analytics and ML
Design a star schema with fact tables and dimension tables that can support both BI queries and feature generation.
A star schema puts measurable business events in a central fact table and descriptive context in surrounding dimension tables. The key design choice is the fact grain, because it determines valid measures, joins, BI aggregations, and point-in-time feature generation for machine learning without duplicating analytics logic.
What this lesson answers
- how to choose fact table grain
- star schema for machine learning features
- fact table versus dimension table design
Notes
A star schema organizes analytical data around a central fact table that records business events, surrounded by dimension tables that describe the entities involved in those events. For example, an orders fact table might contain one row per order line with measures such as quantity, revenue, discount, and cost, plus foreign keys to dimensions like customer, product, store, date, and promotion. The fact table is usually tall and narrow, while dimensions are wider and contain descriptive attributes used for filtering, grouping, and labeling.
Common questions
- What belongs in a fact table versus a dimension table?
- A fact table stores events at a declared grain, such as transactions, sessions, or daily account states. It contains measures and foreign keys. Dimension tables store descriptive attributes for the entities referenced by those events, such as customer, product, store, date, campaign, or account properties used for filtering, grouping, and labelling.
- Why does fact table grain matter so much?
- The grain defines what a row means, so it controls which metrics can be added, which joins are valid, and how downstream queries should aggregate data. If the grain is vague or mixed, BI numbers become hard to trust and feature generation can accidentally combine incompatible events.
- How does a star schema help avoid ML data leakage?
- It gives events clear timestamps, stable joins, and a place to version changing descriptive attributes. Feature jobs can compute behaviour only from facts available before the prediction time, while using the dimension values that were valid then. That structure helps keep training data aligned with what production would actually know.
Short definition: what is Star Schemas for Analytics and ML?