Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. What is a star schema?

Data modeling · Foundations

What is a star schema?

Mediummodel-03
star schemafactsdimensionsmodeling

Question

What is a star schema in analytics modeling?

Solution

A star schema 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).

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

Facts

  • Many rows, numeric measures (amount, quantity)
  • Foreign keys to dimensions
  • A clear grain: "one row per order" or "one 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.

PreviousNext