Data modeling · Foundations

What is a star schema?

Mediumstar schemafactsdimensionsmodeling

Interview question

What is a star schema in analytics modeling?

How to answer

The short answer

A is a warehouse modeling style with a central fact table of measurable events and surrounding dimension tables that describe those events (who, what, where, when).

Output
        dim_customer
             \
dim_date --- fct_orders --- dim_product
             /
        dim_store

Facts

  • Many rows, numeric measures (amount, quantity)
  • to dimensions
  • A clear grain: "1 row per order" or "1 row per order line"

Dimensions

  • Descriptive attributes (customer_name, product_category, region)
  • Used for filters and group-bys in BI

Why interviewers like it

Stars are simple for BI tools, performant for aggregates, and force you to clarify grain. Confusing order-level and line-item-level facts is a classic modeling bug.

Tiny example

fct_order_items holds qty and amount plus keys to dim_date, dim_customer, dim_product. Revenue by category by day is a join + SUM(amount) GROUP BY category, date.

Interview tip

Always state the fact grain first. Everything else in a star hangs from that sentence.

Why grain matters, with numbers. 2 orders have totals of 500 and 200, and 4 order lines. Run in DuckDB:

Output
SUM(order_total) on the orders fact                     -> 700
SUM(order_total) after joining orders to order lines    -> 1400
SUM(amount) on the order lines fact                     -> 700

Joining an order-level fact to a line-level fact repeats each order total once per line, so revenue doubled. Keep one fact per grain, and put an order-level number such as order_total only on the order-level fact.