# Star Schema Basics: Facts, Dimensions and Grain

> Design analytics tables that answer business questions quickly and correctly — starting from the grain.

- Source: https://akshaythakare.com/notes/data-modeling/star-schema-basics
- Topic: Data Modeling
- Level: beginner
- Reading time: 3 min
- Tags: data-modeling, dimensional-modeling, star-schema
- Updated: 2026-10-11

## Start with the grain

The **grain** is one sentence: *what does one row of this fact table represent?* Everything else follows from it.

> One row in `fact_sales` = one order line.

## Facts and dimensions

```mermaid
erDiagram
  DIM_DATE ||--o{ FACT_SALES : "date_key"
  DIM_CUSTOMER ||--o{ FACT_SALES : "customer_sk"
  DIM_PRODUCT ||--o{ FACT_SALES : "product_key"
  DIM_STORE ||--o{ FACT_SALES : "store_key"
  FACT_SALES {
    int date_key
    int customer_sk
    int product_key
    int quantity
    decimal net_revenue
  }
  DIM_PRODUCT {
    int product_key PK
    string name
    string category
  }
```

| | Fact table | Dimension table |
| --- | --- | --- |
| Holds | Measurements (quantity, revenue) | Descriptions (name, category, city) |
| Rows | Many — one per event at the grain | Fewer — one per entity (or version) |
| Keys | Foreign keys to dimensions | Surrogate primary key |
| Changes | Appended / corrected | Updated (Type 1) or versioned (Type 2) |

## Design in four steps

  
### Pick the business process
Orders, payments, shipments — one process per fact table.
  
### Declare the grain
One sentence. If two measures have different grains, they belong in different facts.
  
### Choose the dimensions
Who, what, where, when — anything you filter or group by.
  
### Add the facts
Numeric measures that are true at that grain. Prefer additive measures (revenue) over ratios (margin %).

> **Warning: Mixed grains break totals**
>
> Putting payment attempts in the same fact as order lines duplicates revenue. See the join fan-out note for the full example.