Esports & Game Telemetry data model
Kimball warehouse for game telemetry: match and per-player performance facts, high-frequency in-match events, play sessions, and monetization over conformed player/title/mode/map/hero/item dimensions.
Live-game and esports analytics: matches, per-player match performance (KDA, MMR), in-match event telemetry, play sessions, and monetization (IAP, battle pass, loot boxes) — a Kimball star schema with conformed player, title, mode, map, and hero dimensions for matchmaking, retention, balance, and LiveOps revenue analysis. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 14
- tables
- 103
- columns
- 17
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Per-player match participant fact
Most esports analysis (KDA, win rate, MMR movement, hero balance) is at the player-per-match grain, separate from match-level facts.
In-match events as a separate high-frequency fact
Event streams (kills, objectives, positions) are far larger than match summaries and are isolated for cheap partitioned scans.
Monetization fact for LiveOps
IAP / battle-pass / loot-box revenue drives the business; a dedicated purchase fact ties spend to player cohorts and items.
Tables in the Esports & Game Telemetry 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 (5)
fct_match
Grain: one row per match
| Column | Type | Keys |
|---|---|---|
| match_key | bigint | PK |
| date_key | bigint | |
| game_title_key | bigint | |
| match_mode_key | bigint | |
| map_key | bigint | |
| match_id | varchar | |
| start_timestamp | timestamp | |
| duration_seconds | integer | |
| player_count | smallint | |
| winning_team_key | bigint | |
| is_ranked | boolean |
fct_match_participant
Grain: one row per player per match
Per-player match performance — the core esports fact.
| Column | Type | Keys |
|---|---|---|
| match_participant_key | bigint | PK |
| date_key | bigint | |
| player_key | bigint | |
| match_id | varchar | |
| team_key | bigint | |
| hero_key | bigint | |
| kills | integer | |
| deaths | integer | |
| assists | integer | |
| score | integer | |
| damage_dealt | integer | |
| healing_done | integer | |
| result | varchar | |
| placement | smallint | |
| mmr_change | integer |
fct_match_event
Grain: one row per in-match event
High-frequency in-match telemetry fact.
| Column | Type | Keys |
|---|---|---|
| match_event_key | bigint | PK |
| date_key | bigint | |
| player_key | bigint | |
| match_id | varchar | |
| event_type | varchar | |
| event_timestamp | timestamp | |
| game_time_seconds | integer | |
| position_x | decimal | |
| position_y | decimal |
fct_player_session
Grain: one row per play session
| Column | Type | Keys |
|---|---|---|
| player_session_key | bigint | PK |
| date_key | bigint | |
| player_key | bigint | |
| device_key | bigint | |
| session_start_timestamp | timestamp | |
| duration_minutes | integer | |
| matches_played | smallint | |
| xp_earned | integer |
fct_monetization
Grain: one row per purchase
| Column | Type | Keys |
|---|---|---|
| monetization_key | bigint | PK |
| date_key | bigint | |
| player_key | bigint | |
| item_key | bigint | |
| purchase_type | varchar | |
| amount | decimal | |
| currency | varchar | |
| virtual_currency_spent | integer | |
| is_first_purchase | boolean |
Dimension tables (9)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| is_weekend | boolean | |
| month_number | smallint | |
| year | smallint |
dim_player
Grain: one row per player per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| player_key | bigint | PK |
| player_id | varchar | BK |
| username | varchar | PII |
| country | varchar | |
| platform | varchar | |
| skill_tier | varchar | |
| account_created_date | date | |
| is_premium | boolean | |
| lifecycle_stage | varchar |
dim_game_title
Grain: one row per game title
| Column | Type | Keys |
|---|---|---|
| game_title_key | bigint | PK |
| title_code | varchar | BK |
| title_name | varchar | |
| genre | varchar | |
| publisher | varchar | |
| release_date | date |
dim_match_mode
Grain: one row per match mode
| Column | Type | Keys |
|---|---|---|
| match_mode_key | bigint | PK |
| mode_code | varchar | BK |
| mode_name | varchar | |
| team_size | smallint | |
| is_competitive | boolean |
dim_map
Grain: one row per map
| Column | Type | Keys |
|---|---|---|
| map_key | bigint | PK |
| map_code | varchar | BK |
| map_name | varchar | |
| environment | varchar |
dim_team
Grain: one row per team/roster
| Column | Type | Keys |
|---|---|---|
| team_key | bigint | PK |
| team_id | varchar | BK |
| team_name | varchar | |
| region | varchar | |
| organization | varchar |
dim_hero
Grain: one row per playable hero/character
| Column | Type | Keys |
|---|---|---|
| hero_key | bigint | PK |
| hero_code | varchar | BK |
| hero_name | varchar | |
| role | varchar | |
| difficulty | varchar |
dim_item
Grain: one row per purchasable item
| Column | Type | Keys |
|---|---|---|
| item_key | bigint | PK |
| item_code | varchar | BK |
| item_name | varchar | |
| item_type | varchar | |
| rarity | varchar | |
| list_price | decimal |
dim_device
Grain: one row per device profile
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| platform | varchar | BK |
| operating_system | varchar | |
| gpu | varchar | |
| form_factor | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_match.date_key → dim_date (many-to-one)
- fct_match.game_title_key → dim_game_title (many-to-one)
- fct_match.match_mode_key → dim_match_mode (many-to-one)
- fct_match.map_key → dim_map (many-to-one)
- fct_match.winning_team_key → dim_team (many-to-one)
- fct_match_participant.date_key → dim_date (many-to-one)
- fct_match_participant.player_key → dim_player (many-to-one)
- fct_match_participant.team_key → dim_team (many-to-one)
- fct_match_participant.hero_key → dim_hero (many-to-one)
- fct_match_event.date_key → dim_date (many-to-one)
- fct_match_event.player_key → dim_player (many-to-one)
- fct_player_session.date_key → dim_date (many-to-one)
- fct_player_session.player_key → dim_player (many-to-one)
- fct_player_session.device_key → dim_device (many-to-one)
- fct_monetization.date_key → dim_date (many-to-one)
- fct_monetization.player_key → dim_player (many-to-one)
- fct_monetization.item_key → dim_item (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.