Video Gaming EDW data model
Data Vault 2.0 raw vault for video gaming: hubs (player/game/session/virtual_item/account), links (player-account, account-session, session-game, and a transactional gameplay-event link with score/xp), and satellites (player PII, game, session details, item details, a player-status effectivity satellite, and a transactional purchase satellite on the player-account link). Hash keys and load metadata are generated by the DDL layer.
Video gaming enterprise data warehouse modeled as a Data Vault 2.0 raw vault: player, game, session, virtual item, and account hubs; identity and session links plus a transactional gameplay-event link; and descriptive, effectivity, and transactional purchase satellites — built for integrating telemetry, identity, and commerce source systems with full historization and the player-privacy lineage (GDPR, age-gating) that makes Data Vault attractive for high-volume live-games data. 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
- 100
- columns
- 14
- 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: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs hold only gaming business keys
Separating player_id, game_code, and session_id into hubs lets telemetry, identity, catalog, and store source systems attach independently without reworking existing structures — the core agility of Data Vault.
Transactional link for high-volume gameplay events
Telemetry events are immutable and arrive at extreme volume, so the gameplay-event link is insert-only and carries its measures (score, xp) directly — no satellite needed.
Effectivity satellite for player status
A player's state (active/banned/suspended) and level change over time; an effectivity satellite records the valid period of each status for accurate as-of cohort and retention reporting.
GDPR/COPPA-driven masking on the player satellite
Email is hashed and date_of_birth and ip_address are restricted so the governance report flags privacy- and age-sensitive identifiers; load_date + record_source give the lineage needed for erasure and age-gating audits.
Tables in the Video Gaming 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
| Column | Type | Keys |
|---|---|---|
| player_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_game
Grain: one row per unique hub_game business key
| Column | Type | Keys |
|---|---|---|
| game_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_session
Grain: one row per unique hub_session business key
| Column | Type | Keys |
|---|---|---|
| session_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_virtual_item
Grain: one row per unique hub_virtual_item business key
| Column | Type | Keys |
|---|---|---|
| item_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_account
Grain: one row per unique hub_account business key
| Column | Type | Keys |
|---|---|---|
| account_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_player_account
Grain: one row per lnk_player_account relationship
Which platform account a player profile is linked to.
| Column | Type | Keys |
|---|---|---|
| player_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_session
Grain: one row per lnk_account_session relationship
Which account opened which play session.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| session_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_session_game
Grain: one row per lnk_session_game relationship
Which game a session was played on.
| Column | Type | Keys |
|---|---|---|
| session_hk | binary | |
| game_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_game_event
Grain: one row per gameplay telemetry event (non-historized link)
Transactional (non-historized) link — high-volume gameplay events.
| Column | Type | Keys |
|---|---|---|
| session_hk | binary | |
| game_hk | binary | |
| event_id | varchar | BK |
| event_ts | timestamp | |
| event_type | varchar | |
| score_delta | integer | |
| xp_gained | integer | |
| 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 |
| display_name | varchar | |
| varchar | PII | |
| date_of_birth | date | PII |
| country | varchar | PII |
| ip_address | varchar | PII |
| language | varchar | |
| platform | varchar | |
| 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_title | varchar | |
| genre | varchar | |
| publisher | varchar | |
| release_date | date | |
| age_rating | varchar | |
| is_live | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_session_details
Grain: one row per session_hk per change
Session descriptive history.
| Column | Type | Keys |
|---|---|---|
| session_hk | binary | PK |
| load_date | timestamp | PK |
| start_ts | timestamp | |
| end_ts | timestamp | |
| duration_seconds | integer | |
| device_type | varchar | |
| client_version | varchar | |
| region | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_item_details
Grain: one row per virtual_item_hk per change
Virtual item descriptive history.
| Column | Type | Keys |
|---|---|---|
| virtual_item_hk | binary | PK |
| load_date | timestamp | PK |
| item_name | varchar | |
| item_category | varchar | |
| rarity | varchar | |
| price | decimal | |
| currency | varchar | |
| is_tradeable | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_player_status
Grain: one row per player per status change
Effectivity satellite tracking player status over time.
| Column | Type | Keys |
|---|---|---|
| player_hk | binary | PK |
| load_date | timestamp | PK |
| account_state | varchar | |
| level | integer | |
| vip_tier | varchar | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_purchase_transaction
Grain: one row per in-game purchase transaction
Transactional purchase satellite on the player-account link.
| Column | Type | Keys |
|---|---|---|
| player_account_hk | binary | PK |
| load_date | timestamp | PK |
| transaction_id | varchar | |
| transaction_ts | timestamp | |
| amount | decimal | |
| currency | varchar | |
| store | varchar | |
| payment_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_code | varchar | BK |
| currency_name | varchar | |
| minor_units | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_player_account.player_hk → hub_player (many-to-one)
- lnk_player_account.account_hk → hub_account (many-to-one)
- lnk_account_session.account_hk → hub_account (many-to-one)
- lnk_account_session.session_hk → hub_session (many-to-one)
- lnk_session_game.session_hk → hub_session (many-to-one)
- lnk_session_game.game_hk → hub_game (many-to-one)
- lnk_game_event.session_hk → hub_session (many-to-one)
- lnk_game_event.game_hk → hub_game (many-to-one)
- sat_player_details.player_hk → hub_player (many-to-one)
- sat_game_details.game_hk → hub_game (many-to-one)
- sat_session_details.session_hk → hub_session (many-to-one)
- sat_item_details.virtual_item_hk → hub_virtual_item (many-to-one)
- sat_player_status.player_hk → hub_player (many-to-one)
- sat_purchase_transaction.player_account_hk → lnk_player_account (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.