# Fact Table vs Dimension Table Modeling in Star Schemas: Best Practices from the Data Engineer Handbook

> Master fact table vs dimension table modeling in star schemas with expert best practices. Optimize analytics performance and data integrity for your data warehouse.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: best-practices
- Published: 2026-08-07

---

**Star schemas optimize analytics performance by separating immutable, granular measurements in fact tables from descriptive, slowly changing attributes in dimension tables, using surrogate keys to maintain stable relationships.**

The DataExpert-io/data-engineer-handbook repository provides hands-on SQL patterns for implementing production-grade dimensional models. Mastering fact table vs dimension table modeling in star schemas enables data engineers to build warehouses that support fast aggregations while preserving historical context through SCD-Type 2 patterns.

## Defining Granularity and Purpose

The fundamental distinction lies in what each table represents. Fact tables capture business processes at a specific, atomic grain, while dimension tables provide the context that makes those facts meaningful.

### Fact Table Grain

A fact table must declare a single, atomic level of measurement that never changes. As demonstrated in [`intermediate-bootcamp/materials/2-fact-data-modeling/tables/games.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/2-fact-data-modeling/tables/games.sql), the grain should be one row per business event—such as one row per player per game per date. This fixed grain ensures that additive measures like `points_scored` or `minutes_played` can be summed across any dimension without double-counting.

The repository's [`monthly_user_site_hits.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/monthly_user_site_hits.sql) example illustrates grain choice trade-offs: monthly aggregation improves query speed but limits drill-down capability compared to daily transaction grains.

### Dimension Table Grain

Dimension tables use a different grain: one row per business entity. In [`intermediate-bootcamp/materials/1-dimensional-data-modeling/sql/player_seasons.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/1-dimensional-data-modeling/sql/player_seasons.sql), each row represents a unique player regardless of how many games they play. These tables answer the "who, what, where, when, why, and how" questions about the facts.

## Keys and Relationships

Proper key management decouples your warehouse from volatile business identifiers.

### Surrogate Keys in Dimensions

Dimension tables must implement **surrogate keys**—auto-generated integers that serve as the primary key. The `dim_player` example from [`player_seasons.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/player_seasons.sql) uses a `SERIAL PRIMARY KEY` column separate from the business key (`player_key`). This insulates the fact table from changes in source system identifiers.

Natural keys (like email addresses or player names) should be stored as descriptive attributes, not used for joins.

### Foreign Key Relationships in Facts

Fact tables contain only foreign keys to dimensions plus degenerate dimensions and measures. In the `fact_game_stats` example derived from [`games.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/games.sql), the table references `dim_player(player_id)` and `dim_game(game_id)` rather than storing player or game names directly. This creates a **conformed dimension** pattern where the same `dim_player` can service multiple fact tables like `fact_game_stats` and `fact_training_sessions`.

## Handling Slowly Changing Dimensions (SCD)

Dimensions change over time while facts remain immutable. The handbook demonstrates **SCD-Type 2** implementation to preserve history without altering fact table rows.

When a player's team changes, you insert a new dimension row with updated attributes and mark the old row as expired:

```sql
-- Insert new version in dim_player
INSERT INTO dim_player (
    player_key, player_name, team, position,
    birth_date, effective_date, expiration_date, is_current
) VALUES (
    'P123', 'Jane Doe', 'NewTeam', 'Guard',
    '1990-05-12', CURRENT_DATE, NULL, TRUE
);

-- Expire previous version
UPDATE dim_player
SET expiration_date = CURRENT_DATE, is_current = FALSE
WHERE player_key = 'P123' AND is_current = TRUE;

```

This pattern, detailed in [`intermediate-bootcamp/materials/1-dimensional-data-modeling/lecture-lab/scd_generation_query.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/1-dimensional-data-modeling/lecture-lab/scd_generation_query.sql), ensures that historical fact rows continue to reference the correct dimensional context for the time period in which they occurred.

## Physical Design Best Practices

### Denormalization Strategies

Dimension tables are intentionally **denormalized** (flat) to eliminate joins during attribute lookups. Store redundant data like `team` and `position` directly in `dim_player` rather than creating separate normalized tables.

Fact tables should be **wide** (many measure columns) but **narrow** in keys—containing only the necessary foreign keys and degenerate dimensions. The [`array_metrics_ddl.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/array_metrics_ddl.sql) file demonstrates storing pre-aggregated array measures within fact tables for performance.

### Indexing Patterns

Create a **clustered index** on the fact table's surrogate primary key and non-clustered indexes on foreign key columns used in joins. For dimension tables, index the surrogate primary key and any natural keys used for lookups.

### Naming Conventions

Consistency accelerates onboarding and query writing:

- **Prefix fact tables** with `fact_` (e.g., `fact_game_stats`)
- **Prefix dimension tables** with `dim_` (e.g., `dim_player`)
- Use singular nouns for dimension attributes and clear measure names like `points_scored` rather than ambiguous abbreviations

## Handling Nulls and Measures

Minimize nulls in fact tables. If a measure can be missing, use a default value like `0` rather than NULL to simplify aggregation logic:

```sql
-- From games.sql pattern
points_scored INT DEFAULT 0,
minutes_played INT DEFAULT 0

```

Dimension tables permit nulls for optional attributes (like `birth_date`), but these should be limited to avoid confusion in filters.

## Summary

- **Define atomic grain** for fact tables (one row per event) and entity grain for dimensions (one row per business object).
- **Implement surrogate keys** in dimensions to isolate the warehouse from source system changes and enable SCD-Type 2 history tracking.
- **Store only additive measures** in fact tables and descriptive attributes in dimensions.
- **Denormalize dimensions** to flat tables while keeping fact tables wide with many measures but narrow in key columns.
- **Use clear naming prefixes** (`fact_`, `dim_`) and maintain conformed dimensions across multiple fact tables for consistent analytics.

## Frequently Asked Questions

### What is the difference between a fact table and a dimension table?

A fact table stores quantitative measurements (like `points_scored` or `minutes_played`) at a specific, atomic grain, while a dimension table stores descriptive attributes (like `player_name`, `team`, or `position`) that provide context for those measurements. Fact tables are narrow and long with foreign keys to dimensions, whereas dimension tables are wide and short with surrogate primary keys.

### Why are surrogate keys preferred over natural keys in dimension tables?

Surrogate keys—auto-generated integers like `player_id`—insulate the data warehouse from changes in source system business keys. If a player's email address (natural key) changes, the surrogate key remains constant, preventing the need to update foreign keys in fact tables and preserving referential integrity across the star schema.

### How do you handle historical changes in dimension tables?

Implement **SCD-Type 2** by adding `effective_date`, `expiration_date`, and `is_current` columns to dimension tables. When an attribute changes, insert a new row with the updated values and mark the previous row as expired. This preserves historical accuracy without altering existing fact table rows.

### What naming conventions should you use for fact and dimension tables?

Prefix fact tables with `fact_` (e.g., `fact_game_stats`) and dimension tables with `dim_` (e.g., `dim_player`). Use singular nouns for dimension attributes and descriptive, unabbreviated names for measures (e.g., `points_scored` rather than `pts`). Consistent naming enables analysts to quickly identify table purposes in query joins.