Sports League EDW data model
Data Vault 2.0 raw vault for a sports league: hubs (player/team/game/venue/season), links (player-roster over player+team+season, game-team, game-venue, team-season), and satellites for player (PII), team, game, and venue history plus a roster effectivity satellite and a per-game player-stats transactional satellite. Hash keys and load metadata are generated by the DDL layer.
Sports-league enterprise data warehouse as a Data Vault 2.0 raw vault: player, team, game, venue, and season hubs; roster and scheduling links including a three-hub player-roster link; and descriptive, effectivity, and game-stats satellites — designed to consolidate player, roster, and game data from many league, scouting, ticketing, and tracking systems with full historization for as-of roster and statistical reporting. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 99
- columns
- 15
- relationships
- 4
- 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
Three-hub player-roster link
Roster membership is the intersection of a player, a team, and a season; a single link over all three hubs models it once so roster effectivity and game stats hang off that exact grain.
Roster membership as an effectivity satellite
Players are traded, injured, and released mid-season; an effectivity satellite on the player-roster link records the valid period of each assignment for as-of roster reconstruction.
Per-game stats as a transactional satellite
Box-score stat lines are immutable per-game events; a transactional satellite on the roster link captures each game's line with its load metadata, keeping descriptive satellites compact and stats append-only.
Game and venue as separate hubs
A game is played at a venue but both are referenced independently by schedules, broadcasts, and ticketing; modeling each as a hub keeps those relationships as independent, reusable links.
Tables in the Sports League EDW 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.
Hubs (5)
hub_player
Grain: one row per unique hub_player business key
Player by league-assigned player identifier.
| Column | Type | Keys |
|---|---|---|
| player_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_team
Grain: one row per unique hub_team business key
Team / franchise by team code.
| Column | Type | Keys |
|---|---|---|
| team_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_game
Grain: one row per unique hub_game business key
Game / match by its game identifier.
| Column | Type | Keys |
|---|---|---|
| game_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_venue
Grain: one row per unique hub_venue business key
Stadium / arena by venue identifier.
| Column | Type | Keys |
|---|---|---|
| venue_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_season
Grain: one row per unique hub_season business key
Season by its season code.
| Column | Type | Keys |
|---|---|---|
| season_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_player_roster
Grain: one row per lnk_player_roster relationship
Player on a team's roster for a season.
| Column | Type | Keys |
|---|---|---|
| player_hk | binary | |
| team_hk | binary | |
| season_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_game_team
Grain: one row per lnk_game_team relationship
Teams participating in a game (home / away).
| Column | Type | Keys |
|---|---|---|
| game_hk | binary | |
| team_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_game_venue
Grain: one row per lnk_game_venue relationship
Where a game was played.
| Column | Type | Keys |
|---|---|---|
| game_hk | binary | |
| venue_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_team_season
Grain: one row per lnk_team_season relationship
A team's participation in a season.
| Column | Type | Keys |
|---|---|---|
| team_hk | binary | |
| season_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_player_details
Grain: one row per player_hk per change
Player descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| player_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| nationality | varchar | |
| position | varchar | |
| height_cm | integer | |
| weight_kg | integer | |
| dominant_hand | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_team_details
Grain: one row per team_hk per change
Team descriptive history.
| Column | Type | Keys |
|---|---|---|
| team_hk | binary | PK |
| load_date | timestamp | PK |
| team_name | varchar | |
| city | varchar | |
| conference | varchar | |
| division | varchar | |
| founded_year | integer | |
| head_coach | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_game_details
Grain: one row per game_hk per change
Game descriptive history.
| Column | Type | Keys |
|---|---|---|
| game_hk | binary | PK |
| load_date | timestamp | PK |
| game_date | date | |
| game_type | varchar | |
| home_score | integer | |
| away_score | integer | |
| attendance | integer | |
| game_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_venue_details
Grain: one row per venue_hk per change
Venue descriptive history.
| Column | Type | Keys |
|---|---|---|
| venue_hk | binary | PK |
| load_date | timestamp | PK |
| venue_name | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| capacity | integer | |
| surface_type | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_roster_effectivity
Grain: one row per player-roster assignment per effectivity change
Effectivity satellite on the player-roster link.
| Column | Type | Keys |
|---|---|---|
| player_roster_hk | binary | PK |
| load_date | timestamp | PK |
| roster_status | varchar | |
| jersey_number | varchar | |
| contract_type | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_current | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_player_game_stats
Grain: one row per player-roster per game stat line (transactional satellite)
Transactional satellite — per-game box-score stats on the roster link.
| Column | Type | Keys |
|---|---|---|
| player_roster_hk | binary | PK |
| load_date | timestamp | PK |
| game_id | varchar | |
| stat_ts | timestamp | |
| minutes_played | integer | |
| points | integer | |
| assists | integer | |
| rebounds | integer | |
| turnovers | integer | |
| plus_minus | integer | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_position
Grain: one row per playing position
| Column | Type | Keys |
|---|---|---|
| position_code | varchar | BK |
| position_name | varchar | |
| position_group | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_player_roster.player_hk → hub_player (many-to-one)
- lnk_player_roster.team_hk → hub_team (many-to-one)
- lnk_player_roster.season_hk → hub_season (many-to-one)
- lnk_game_team.game_hk → hub_game (many-to-one)
- lnk_game_team.team_hk → hub_team (many-to-one)
- lnk_game_venue.game_hk → hub_game (many-to-one)
- lnk_game_venue.venue_hk → hub_venue (many-to-one)
- lnk_team_season.team_hk → hub_team (many-to-one)
- lnk_team_season.season_hk → hub_season (many-to-one)
- sat_player_details.player_hk → hub_player (many-to-one)
- sat_team_details.team_hk → hub_team (many-to-one)
- sat_game_details.game_hk → hub_game (many-to-one)
- sat_venue_details.venue_hk → hub_venue (many-to-one)
- sat_roster_effectivity.player_roster_hk → lnk_player_roster (many-to-one)
- sat_player_game_stats.player_roster_hk → lnk_player_roster (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.