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

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, 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 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, 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 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, 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:

-- 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, 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 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:

-- 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}