One Big Table vs Star Schema
Pre-join everything, or keep facts and dimensions separate?
A star schema keeps a fact table of events separate from dimension tables of descriptive attributes, so definitions are reused and history is manageable. One big table (OBT) pre-joins everything into a single wide table at one grain, which is simple and fast to query in a columnar warehouse. Most teams use both: a star schema as the core model, and OBTs built from it for specific dashboards or tools.
On this page · 6 sections
- Option A
- One big table (OBT)
- Option B
- Star schema
- Compared on
- 6 dimensions
- Goes deeper in
- Data Modeling
Columnar warehouses made wide tables cheap to scan, which revived the one-big-table pattern: instead of joining orders to customers and products at query time, you join them once in the transformation layer and give analysts a single table.
The trade-off is maintenance. In an OBT, every customer attribute is copied onto every order line. When a customer's segment changes, every row for that customer has to be rebuilt, and two OBTs built at different grains can disagree about the same customer.
| Aspect | One big table (OBT) | Star schema |
|---|---|---|
| Structure | One wide, denormalized table | Fact table joined to several dimension tables |
| Query effort | No joins needed | Joins from fact to dimensions |
| Reuse | Attributes copied into each OBT | Conformed dimensions shared by every fact |
| Changing attributes | Rebuild every affected row | Update the dimension (or add a version with SCD Type 2) |
| Several grains | Needs one OBT per grain | One fact table per process, sharing dimensions |
| Power BI | Works, but large and harder to filter consistently | The model Power BI is designed around |
Build an OBT when
- One dashboard or tool needs one grain, and simplicity matters most.
- Consumers cannot or should not write joins, such as spreadsheets and some ML pipelines.
- You build it from a star schema, so definitions stay in one place.
Build a star schema when
- Several business processes share customers, products, or dates.
- Attributes change over time and you need history.
- You use Power BI or another tool whose model expects facts and dimensions.
- Definitions must stay consistent across many reports.
Use the star schema as the source of truth and treat OBTs as outputs. That keeps definitions in conformed dimensions while still giving analysts and tools the single flat table they want.
You can try both with the free ecommerce sales dataset: its flat sales file is an OBT at order-line grain, and its Power BI pack is the same data as a star schema.
- Is one big table faster than a star schema?
- Often for a single query in a columnar warehouse, because it avoids joins. The cost shows up elsewhere: larger storage, slower rebuilds when attributes change, and definitions duplicated across tables.
- Does Power BI need a star schema?
- It works with flat tables, but Microsoft's modeling guidance recommends a star schema because filters, relationships, and DAX measures behave most predictably with facts and dimensions.
Go deeper in the Data Modeling hub.