Star schemas: facts, dimensions & grain

Analyst and analytics-engineer loops routinely ask you to design a schema: 'model the data for a food-delivery app.' The expected answer in a warehouse is dimensional modeling — a central fact table of measurable events surrounded by descriptive dimension tables. The shape gives it its name: a star schema.

• Fact table: one row per business event at a declared grain (an order line, a trip, an impression), holding measures (amount, quantity) plus foreign keys to dimensions.
• Dimension tables: the who/what/where/when context — dim_customer, dim_product, dim_date. Wide, descriptive, and what you filter and group by.
• Grain: the sentence that defines a fact row — 'one row per order line item'. State it first; every measure and join follows from it.
• Star vs snowflake: a snowflake normalizes dimensions into sub-tables. Analytics defaults to the star — fewer joins, simpler for BI tools and other analysts.
• Additivity: revenue sums across any dimension (additive); an account balance does not sum over time (semi-additive — snapshot facts need care).

-- The star in action: revenue by product category and month
SELECT d.month, p.category, SUM(f.amount) AS revenue
FROM fact_order_items f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.month, p.category;