Sports Analytics data model
Kimball warehouse for sports analytics: transaction-grain play-by-play events with spatial coordinates, transaction-grain player game stat lines, and a daily team-standings periodic snapshot over conformed player/team/game/venue/season/position dimensions. Player and team histories are tracked with SCD2.
Sports performance analytics covering play-by-play game events, player per-game box scores, and daily team standings snapshots over conformed player, team, game, venue, season, and position dimensions — a Kimball star schema with SCD2 player and team history. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 119
- columns
- 16
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Play-by-play as a transaction-grain fact
Each on-field event (shot, pass, foul) is an atomic action with spatial and clock context; a transaction grain enables advanced tracking, possession, and win-probability models.
Separate player-game-stats fact from play-by-play
Box-score totals are at player/game grain while events are at the play grain; keeping them separate avoids mixed-grain aggregation and serves fantasy/reporting use cases directly.
Daily standings snapshot
Win percentage, rank, and games-back are semi-additive standings metrics that evolve daily; a periodic snapshot supports trend and playoff-probability analysis without replaying every game.
SCD2 on player and team
Player team, jersey, and status change with trades and injuries, and team coach/division can change; SCD2 preserves the attributes in effect at each game.
Tables in the Sports Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_game_event
Grain: one row per play-by-play event (transaction)
Transaction-grain play-by-play — one row per on-field event.
| Column | Type | Keys |
|---|---|---|
| game_event_key | bigint | PK |
| date_key | bigint | |
| game_key | bigint | |
| player_key | bigint | |
| team_key | bigint | |
| position_key | bigint | |
| event_type_key | bigint | |
| season_key | bigint | |
| event_id | varchar | |
| period | smallint | |
| game_clock_seconds | integer | |
| event_sequence | integer | |
| points_scored | smallint | |
| x_coordinate | decimal | |
| y_coordinate | decimal | |
| is_successful | boolean | |
| is_scoring_play | boolean |
fct_player_game_stats
Grain: one row per player per game (transaction)
Transaction-grain box-score line — one row per player per game.
| Column | Type | Keys |
|---|---|---|
| player_game_stats_key | bigint | PK |
| date_key | bigint | |
| game_key | bigint | |
| player_key | bigint | |
| team_key | bigint | |
| position_key | bigint | |
| season_key | bigint | |
| minutes_played | decimal | |
| points | smallint | |
| assists | smallint | |
| rebounds | smallint | |
| steals | smallint | |
| blocks | smallint | |
| turnovers | smallint | |
| fouls | smallint | |
| plus_minus | smallint | |
| shots_attempted | smallint | |
| shots_made | smallint | |
| did_start | boolean | |
| performance_rating | decimal |
fct_team_standings_snapshot
Grain: one row per team per season per day (periodic snapshot)
Daily standings snapshot — ranks and win pct are semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| team_key | bigint | |
| season_key | bigint | |
| games_played | smallint | |
| wins | smallint | |
| losses | smallint | |
| ties | smallint | |
| win_percentage | decimal | |
| points_for | integer | |
| points_against | integer | |
| point_differential | integer | |
| conference_rank | smallint | |
| division_rank | smallint | |
| games_back | decimal | |
| streak | varchar |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | varchar | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean | |
| is_holiday | boolean |
dim_player
Grain: one row per player per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| player_key | bigint | PK |
| player_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| nationality | varchar | |
| height_cm | smallint | |
| weight_kg | smallint | |
| handedness | varchar | |
| jersey_number | smallint | |
| current_team_id | varchar | |
| draft_year | smallint | |
| rookie_flag | boolean | |
| status | varchar |
dim_team
Grain: one row per team per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| team_key | bigint | PK |
| team_id | varchar | BK |
| team_name | varchar | |
| team_abbreviation | varchar | |
| city | varchar | |
| conference | varchar | |
| division | varchar | |
| head_coach | varchar | |
| founded_year | smallint | |
| home_venue_id | varchar |
dim_game
Grain: one row per scheduled game
| Column | Type | Keys |
|---|---|---|
| game_key | bigint | PK |
| game_id | varchar | BK |
| home_team_id | varchar | |
| away_team_id | varchar | |
| game_type | varchar | |
| week_number | smallint | |
| attendance | integer | |
| broadcast_network | varchar | |
| is_overtime | boolean | |
| game_status | varchar |
dim_venue
Grain: one row per venue
| Column | Type | Keys |
|---|---|---|
| venue_key | bigint | PK |
| venue_id | varchar | BK |
| venue_name | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| capacity | integer | |
| surface_type | varchar | |
| is_indoor | boolean | |
| elevation_meters | integer |
dim_season
Grain: one row per season
| Column | Type | Keys |
|---|---|---|
| season_key | bigint | PK |
| season_id | varchar | BK |
| season_year | varchar | |
| league | varchar | |
| season_type | varchar | |
| start_date | date | |
| end_date | date |
Lookup tables (2)
dim_position
Grain: one row per playing position
| Column | Type | Keys |
|---|---|---|
| position_key | bigint | PK |
| position_code | varchar | BK |
| position_name | varchar | |
| position_group | varchar |
dim_event_type
Grain: one row per play event type
| Column | Type | Keys |
|---|---|---|
| event_type_key | bigint | PK |
| event_type_code | varchar | BK |
| event_type_name | varchar | |
| event_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_game_event.date_key → dim_date (many-to-one)
- fct_game_event.game_key → dim_game (many-to-one)
- fct_game_event.player_key → dim_player (many-to-one)
- fct_game_event.team_key → dim_team (many-to-one)
- fct_game_event.position_key → dim_position (many-to-one)
- fct_game_event.event_type_key → dim_event_type (many-to-one)
- fct_game_event.season_key → dim_season (many-to-one)
- fct_player_game_stats.date_key → dim_date (many-to-one)
- fct_player_game_stats.game_key → dim_game (many-to-one)
- fct_player_game_stats.player_key → dim_player (many-to-one)
- fct_player_game_stats.team_key → dim_team (many-to-one)
- fct_player_game_stats.position_key → dim_position (many-to-one)
- fct_player_game_stats.season_key → dim_season (many-to-one)
- fct_team_standings_snapshot.date_key → dim_date (many-to-one)
- fct_team_standings_snapshot.team_key → dim_team (many-to-one)
- fct_team_standings_snapshot.season_key → dim_season (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.