Casino Gaming Analytics data model
Kimball warehouse for casino gaming: transaction-grain gaming sessions and atomic wagers (with CTR/AML flags), plus a daily player-worth periodic snapshot (coin-in, theoretical win, comps) over conformed player/game/machine/property/promotion dimensions. Player PII is classified and masked, with self-exclusion and loss-limit tracking for responsible gaming.
Casino gaming analytics covering carded gaming sessions, individual wagers, and a daily player-worth snapshot over conformed player, game, machine, property, and promotion dimensions — a Kimball star schema with SCD2 player history, regulated PII (tax ID, loyalty card) masked, and responsible-gaming and AML signals. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 116
- columns
- 16
- relationships
- 9
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Session and wager facts at two transaction grains
A wager is the atomic bet/win event needed for AML and game-math analysis; the session summarizes carded play for player tracking. Both are transaction grains at different levels of detail.
Daily player snapshot for worth and comps
Theoretical worth, comps, and free-play are semi-additive daily metrics that drive host decisions and reinvestment; a periodic snapshot avoids scanning every wager.
AML and CTR flags on the wager fact
Bank Secrecy Act requires currency-transaction reporting above thresholds; a per-wager CTR-reportable flag plus the player AML risk rating supports suspicious-activity monitoring.
Responsible-gaming attributes on dim_player
Self-exclusion status and daily loss limits must be enforced and audited; carrying them on the SCD2 player dimension preserves the limits in effect at each session.
Masking on tax ID and loyalty card
GDPR and gaming-jurisdiction rules require restricting access to personal identifiers; tax IDs are fully masked and loyalty card numbers partially masked.
Tables in the Casino Gaming 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_gaming_session
Grain: one row per carded gaming session (transaction)
Transaction-grain gaming session summarizing carded play.
| Column | Type | Keys |
|---|---|---|
| gaming_session_key | bigint | PK |
| date_key | bigint | |
| player_key | bigint | |
| game_key | bigint | |
| machine_key | bigint | |
| property_key | bigint | |
| session_id | varchar | |
| start_timestamp | timestamp | |
| end_timestamp | timestamp | |
| duration_minutes | integer | |
| coin_in | decimal | |
| coin_out | decimal | |
| theoretical_win | decimal | |
| actual_win | decimal | |
| games_played | integer | |
| average_bet | decimal |
fct_wager
Grain: one row per individual wager (transaction)
Transaction-grain wager — the atomic bet/win event.
| Column | Type | Keys |
|---|---|---|
| wager_key | bigint | PK |
| date_key | bigint | |
| player_key | bigint | |
| game_key | bigint | |
| machine_key | bigint | |
| property_key | bigint | |
| transaction_type_key | bigint | |
| gaming_session_key | bigint | |
| wager_id | varchar | |
| wager_timestamp | timestamp | |
| wager_amount | decimal | |
| payout_amount | decimal | |
| net_amount | decimal | |
| is_jackpot | boolean | |
| is_ctr_reportable | boolean |
fct_player_daily_snapshot
Grain: one row per player per property per gaming day (periodic snapshot)
Daily player snapshot — theoretical worth is semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| player_key | bigint | |
| property_key | bigint | |
| promotion_key | bigint | |
| total_coin_in | decimal | |
| total_coin_out | decimal | |
| theoretical_win | decimal | |
| actual_win | decimal | |
| comps_earned | decimal | |
| free_play_redeemed | decimal | |
| session_count | integer | |
| trips | smallint | |
| loyalty_points_earned | integer | |
| avg_daily_theo | decimal | |
| exceeded_loss_limit | boolean |
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 | |
| gaming_day | date |
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 |
| varchar | PII | |
| phone | varchar | PII |
| date_of_birth | date | PII |
| tax_id | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| loyalty_tier | varchar | |
| loyalty_card_number | varchar | PII |
| host_name | varchar | |
| self_exclusion_flag | boolean | |
| daily_loss_limit | decimal | |
| kyc_status | varchar | |
| aml_risk_rating | varchar | |
| enrollment_date | date |
dim_game
Grain: one row per game
| Column | Type | Keys |
|---|---|---|
| game_key | bigint | PK |
| game_id | varchar | BK |
| game_name | varchar | |
| game_category | varchar | |
| game_subtype | varchar | |
| provider | varchar | |
| theoretical_hold_percent | decimal | |
| min_bet | decimal | |
| max_bet | decimal | |
| is_progressive | boolean |
dim_machine
Grain: one row per machine/table position
| Column | Type | Keys |
|---|---|---|
| machine_key | bigint | PK |
| machine_id | varchar | BK |
| asset_number | varchar | |
| machine_type | varchar | |
| manufacturer | varchar | |
| denomination | decimal | |
| floor_zone | varchar | |
| bank_name | varchar | |
| is_active | boolean |
dim_property
Grain: one row per casino property
| Column | Type | Keys |
|---|---|---|
| property_key | bigint | PK |
| property_id | varchar | BK |
| property_name | varchar | |
| jurisdiction | varchar | |
| regulator | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| license_number | varchar |
dim_promotion
Grain: one row per promotion/offer
| Column | Type | Keys |
|---|---|---|
| promotion_key | bigint | PK |
| promotion_id | varchar | BK |
| promotion_name | varchar | |
| promotion_type | varchar | |
| offer_value | decimal | |
| start_date | date | |
| end_date | date | |
| is_targeted | boolean |
Lookup tables (1)
dim_transaction_type
Grain: one row per wager transaction type
| Column | Type | Keys |
|---|---|---|
| transaction_type_key | bigint | PK |
| transaction_type_code | varchar | BK |
| transaction_type_name | varchar | |
| direction | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_gaming_session.date_key → dim_date (many-to-one)
- fct_gaming_session.player_key → dim_player (many-to-one)
- fct_gaming_session.game_key → dim_game (many-to-one)
- fct_gaming_session.machine_key → dim_machine (many-to-one)
- fct_gaming_session.property_key → dim_property (many-to-one)
- fct_wager.date_key → dim_date (many-to-one)
- fct_wager.player_key → dim_player (many-to-one)
- fct_wager.game_key → dim_game (many-to-one)
- fct_wager.machine_key → dim_machine (many-to-one)
- fct_wager.property_key → dim_property (many-to-one)
- fct_wager.transaction_type_key → dim_transaction_type (many-to-one)
- fct_wager.gaming_session_key → fct_gaming_session (many-to-one)
- fct_player_daily_snapshot.date_key → dim_date (many-to-one)
- fct_player_daily_snapshot.player_key → dim_player (many-to-one)
- fct_player_daily_snapshot.property_key → dim_property (many-to-one)
- fct_player_daily_snapshot.promotion_key → dim_promotion (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.