A star schema is a dimensional data modeling pattern in which one central fact table of numeric measurements is surrounded by several dimension tables that describe those measurements, such as date, product, customer and store. In data modeling, it is the standard structure for analytical databases and data warehouses, named for the star shape it forms in an ER diagram.
The fact table holds foreign keys to each dimension plus measures such as quantity or revenue, while each dimension is a single, deliberately denormalized table. This design favors simple, fast aggregation queries over the strict avoidance of redundancy that normalization brings to transactional systems.
Structure of a star schema
Every star schema has a declared grain: a precise statement of what one row in the fact table represents, such as one product on one sales receipt line. Choosing the grain first is the core discipline of dimensional modeling, because all measures and dimensions must be true at that grain.
| Component | Content | Example |
|---|---|---|
| Fact table | Foreign keys and numeric measures | sales_fact with quantity and amount |
| Dimension table | Descriptive attributes for filtering and grouping | product_dim with brand and category |
| Surrogate key | Meaningless integer key for each dimension row | product_key = 4182 |
| Natural key | Identifier from the source system | Product SKU code |
| Date dimension | One row per calendar day with attributes | Weekday, month, fiscal quarter |
Fact tables are usually narrow and very long, often with millions or billions of rows, while dimension tables are wide and comparatively short, often with dozens of descriptive columns. Each fact row relates to exactly one row in each dimension, a one-to-many relationship from dimension to fact.
Types of fact tables and measures
Fact tables come in three classic forms, distinguished by how and when rows are written. Mixing these forms in one table is a frequent source of wrong totals.
| Fact table type | One row represents | Typical use |
|---|---|---|
| Transaction fact | A single event at a point in time | Sales lines, payments, clicks |
| Periodic snapshot | The state at regular intervals | Daily account balances, monthly inventory |
| Accumulating snapshot | A process instance updated through milestones | Order from placement to delivery |
Measures differ in how they can be summed. Additive measures such as revenue can be summed across all dimensions; semi-additive measures such as account balances cannot be summed over time; and non-additive measures such as ratios must be recomputed from their components rather than added.
Star schema compared with other models
The star schema sits between a fully normalized model and a single flat table. Its main neighbor is the snowflake schema, which normalizes dimensions into sub-tables.
| Model | Dimension structure | Best fit |
|---|---|---|
| Star schema | One denormalized table per dimension | Business intelligence and reporting |
| Snowflake schema | Dimensions split into normalized sub-tables | Large shared hierarchies, storage-sensitive designs |
| Third normal form | Fully normalized entities | Transactional systems and enterprise integration layers |
| One big table | All attributes flattened into the fact rows | Columnar engines with simple dashboards |
| Data vault | Hubs, links and satellites | Auditable, history-preserving raw layers |
Normalization removes redundancy to protect transactional updates, but analytics are mostly read-only and join-heavy. Repeating a category name on thousands of product rows costs little storage and saves joins, which is why star schemas accept controlled redundancy in dimensions.
Query engines exploit the shape directly. Many databases recognize star joins and filter the fact table using the selective dimension filters first, and columnar warehouses compress the repetitive dimension keys in fact tables very efficiently.
Handling change with slowly changing dimensions
Dimension attributes change over time: customers move, products change category. Slowly changing dimension techniques decide whether history is kept, and the choice determines whether past reports stay reproducible.
| Type | Handling of a change | Effect on history |
|---|---|---|
| Type 1 | Overwrite the old value | History is restated with the new value |
| Type 2 | Add a new row with validity dates | Facts keep the value valid at the time |
| Type 3 | Add a column for the previous value | Limited history of one prior value |
Type 2 is the most common choice when history matters. Each version receives its own surrogate key, so a sale recorded last year stays linked to the customer’s old region, while new sales link to the current version.
Design guidelines and common mistakes
Good star schemas follow a few consistent rules that keep queries simple and totals correct across reports.
- Declare the grain before choosing any dimension or measure.
- Use conformed dimensions, shared identically across fact tables, so different business processes can be compared.
- Avoid nulls in foreign keys by pointing to an explicit unknown member row.
- Do not snowflake by default; normalize a dimension only for a concrete reason.
- Handle many-to-many relationships with a bridge table instead of duplicating fact rows.
The most damaging error is mixed grain, where daily totals and individual transactions share one fact table, causing double counting. Another is fan-out, where joining a fact table to a many-to-many relationship without a bridge multiplies measures and inflates revenue figures silently.
In a larger warehouse, several star schemas share their dimensions. Planning this with an enterprise bus matrix, a grid of business processes against conformed dimensions, prevents each team from building its own incompatible customer or product table. When queries over billions of fact rows become slow, aggregate fact tables at a coarser grain, such as daily sales per store, can serve common dashboards while the detailed table remains available for drill-down.
In everyday language
A star schema organizes business data like a receipt surrounded by reference cards. The receipt in the middle records what happened: how many items, what they cost. The cards around it explain the details: which product, which store, which day, which customer.
Because every detail is just one step away from the receipt, questions such as sales by region and month can be answered quickly. The design deliberately repeats some information on the cards to keep those answers simple.
Seen in the real world
A grocery chain with 200 stores wants daily sales reporting. The data team builds a sales_fact table at the grain of one product per receipt line, with keys to date, product, store, customer and promotion dimensions, and measures for quantity, sales amount and discount.
The product dimension tracks category changes as Type 2, so last year’s results remain comparable. Analysts in the business intelligence tool now answer questions such as weekly sales of dairy products by region during a promotion with a single star join, and reports that previously took hours of spreadsheet work refresh within minutes.