What is a star schema?

A star schema is a data model made of one central fact table, holding the measurable events, an order, a page view, a transaction, joined to several dimension tables that describe the who, what, and when around each fact. It's called a star schema because a diagram of the fact table with its dimensions branching out looks like a star.

How it works

The fact table holds numeric measures (order amount, quantity, duration) and foreign keys pointing to dimension tables. Dimension tables hold the descriptive attributes: a dim_customer table with name and region, a dim_date table with day, month, and fiscal quarter. Because dimensions are shared across many facts, a BI tool can join a handful of tables instead of many normalized ones, and each dimension's attributes only need to be defined once.

Why it matters

Dashboards run the same handful of query shapes over and over: filter by date range, group by region, sum a measure. A star schema is built for exactly that access pattern, so those queries stay fast and the joins stay simple, instead of forcing a BI tool to traverse a deeply normalized schema on every request.

How OptimaFlo builds this

The Ready (gold) layer is where OptimaFlo builds business metrics on top of Clean, and that includes star schema and denormalized models sized for BI queries. Updates run incrementally, so a Ready table refreshes with new or changed data instead of rebuilding from scratch on every run. The SQL behind these models is generated and version controlled the same way as the rest of the pipeline, reviewed before it runs.

Related terms

• Slowly changing dimensions: How dimension tables in a star schema handle a value that changes over time.
• Medallion architecture: The Raw, Clean, Ready layers; a star schema is what Ready is typically shaped as.
• Bronze, silver, and gold: The traditional names for Raw, Clean, and Ready, where a star schema lives in the gold layer.