Star schema table types: fact vs dimension vs bridge vs staging
Verdict: Use a star schema: a central fact table of additive measures and foreign keys, surrounded by wide denormalised dimension tables for fast filtering. Bridge tables resolve many-to-many links and are hidden from report view. Avoid snowflaking.
| Criterion | Fact table | Dimension table | Bridge table | Staging table |
|---|---|---|---|---|
| Stores | Additive measures plus foreign keys | Descriptive attributes of an entity | Rows resolving a many-to-many relationship | Raw ingested data before transformation |
| Grain | One row per event or observation | One row per entity member | One row per dimension pair | Source grain |
| Keys | Foreign keys to dimensions | Surrogate keys for history tracking | Both dimension keys | Not applicable |
| Shape | Narrow, high volume | Wide and denormalised for fast filtering | Minimal | Not for reporting |
| Choose when | Sales amount, quantity per transaction | Product, customer, date attributes | Customer-to-account many-to-many | Landing zone only |
Rules
- A star schema centres a fact table among dimension tables to minimise joins, and is the documented prerequisite for enterprise Power BI semantic models.
- Fact tables hold dimension foreign keys plus granular additive measures such as sales amount and quantity.
- Wide, denormalised dimension tables speed filtering and grouping; denormalisation is applied to dimensions, not facts.
- Conformed dimensions, denormalised attributes, and surrogate keys for history tracking are the documented enrichment practices.
- Model a manager hierarchy with a self-referencing ManagerKey column pointing to EmployeeKey in the same dimension table.
- Hide a bridging table from report view; it exists to resolve a many-to-many relationship, not for reporting.
Traps
- Snowflaking adds relationship hops and DAX filter-propagation overhead; there is no column count that forces it.
- A single flat table causes redundancy and cannot support conformed dimensions across multiple facts.
- Do not model a manager relationship as a fact table or store the manager name as free text.
- Do not delete a bridging table after creating relationships; the many-to-many resolution depends on it.