Data modelling case study

iGaming Data Mart

A star schema for bets, payments and bonuses, built with dbt on DuckDB. Every report reads the same GGR, NGR and FTD, in euros, on Malta reporting days, with each player's VIP tier as it was at the time.

86 of 86 data tests passing Python · dbt · DuckDB · SQL Simulated data

What the mart holds

70,367Simulated players
1.22MSportsbook bets
950KCasino sessions
582KPayment attempts
7 · 6Markets · currencies
18Months, Apr 2025 to Sep 2026
The model

One star schema, seven fact tables

Every fact carries the player's version key, the reporting day, the brand and the market. Select a table to see its grain.

SHARED BY EVERY FACT FACTS SPECIFIC DIMENSIONS dim_playerPlayer x VIP tier version SCD 2 dim_affiliatevia the player dim_dateMalta reporting day dim_marketCurrency and time zone dim_brandBrand fct_wagering_dailyPlayer x game x day fct_sportsbook_betsOne row per bet fct_paymentsOne row per payment attempt fct_bonusesOne row per bonus fct_campaign_assignmentsPlayer x campaign fct_player_activity_dailyPlayer x day fct_account_eventsLimits, cool-offs, exclusions dim_gameCasino game or sport dim_providervia the game dim_pspPayment provider dim_campaignWelcome and CRM offers kpi_daily_revenue · kpi_daily_ggr_by_product · kpi_daily_accounts Metrics layer: complete Malta days only, every number from the shared definitions
Table
fct_wagering_daily
Grain
One row per player, game (or sport) and Malta reporting day
About
Real-money and bonus wagering. Casino play is dated by session start, sportsbook by settlement. 1,861,761 rows.
All 19 tables in the mart, with their grain and row counts
TableTypeGrainRows
fct_wagering_dailyFactPlayer x game x reporting day1,861,761
fct_sportsbook_betsFactBet1,222,610
fct_player_activity_dailyFactPlayer x reporting day with real-money play1,002,694
fct_paymentsFactDeposit or withdrawal attempt581,857
fct_campaign_assignmentsFactPlayer x CRM campaign (offer or holdout)167,705
fct_bonusesFactBonus, from grant to settlement46,507
fct_account_eventsFactDeposit limit, cool-off or self-exclusion8,134
dim_playerDimensionPlayer x VIP tier version (SCD Type 2)77,858
dim_dateDimensionMalta reporting day647
dim_gameDimensionCasino game or sport114
dim_affiliateDimensionAffiliate and its deal (CPA, revenue share or hybrid)60
dim_campaignDimensionWelcome or CRM offer15
dim_providerDimensionGame provider, plus the in-house sportsbook9
dim_marketDimensionMarket, with currency and time zone7
dim_pspDimensionPayment service provider5
dim_brandDimensionBrand3
kpi_daily_ggr_by_productMetricsDay x brand x market x product29,841
kpi_daily_revenueMetricsDay x brand x market x product group15,188
kpi_daily_accountsMetricsDay x brand x market7,606
Shared definitions

One definition per metric

GGR and NGR are dbt macros. Every model that reports them calls the same macro, so a definition changes in one place.

MetricDefinitionDated by
GGRReal-money stakes minus real-money winnings. Bonus-funded play is left out.Casino: session start. Sportsbook: settlement.
Bonus costBonus money that converted to withdrawable cash. Bonuses that are lost or expire cost nothing.The day the bonus converts
NGRGGR minus bonus cost.As GGR and bonus cost
Expected GGRWhat the house edge predicts: stake x (1 − RTP) for casino, stake x the bet's margin for sportsbook.As GGR
FTDA player's first successful deposit, counted once per player.The day of the deposit
DepositsSuccessful deposits only. Failed attempts and duplicate PSP callbacks are excluded.The day of the request
Active playerPlaced at least one real-money bet. Counted once per player in any period.Casino: session start. Sportsbook: placement.

The macros

Models never write stake - win themselves. They call the macro, and the dashboards show the wording from one reference table.

-- macros/metrics.sql
{% macro ggr(cash_stake, cash_win) -%}
    ({{ cash_stake }} - {{ cash_win }})
{%- endmacro %}

{% macro ngr(ggr, bonus_cost) -%}
    ({{ ggr }} - {{ bonus_cost }})
{%- endmacro %}

-- marts/metrics/kpi_daily_revenue.sql
{{ ngr('ggr_eur', 'bonus_cost_eur') }} as ngr_eur

What NGR leaves out

Operators define NGR differently. Many also deduct gaming duty and payment fees. This project deducts bonus cost only, and says so wherever NGR appears, so nobody compares it with a figure that means something else.

Why bonus play is outside GGR

Bonus money that a player loses never cost the operator anything. Counting its stakes would inflate GGR, and then counting the bonus again in NGR would double-count it. So bonus stakes and wins are kept in their own columns, and the cost enters NGR once, when the bonus converts to cash.

Currencies and time zones

One currency, one calendar

The platform logs UTC and the player's currency. The mart reports every amount in euros, on the Malta calendar day.

A late-night session in Auckland

One simulated casino session from New Zealand, followed through the mart.

  1. 13:41 UTC, Saturday 5 September 2026The platform's timestamp
  2. 01:41, Sunday 6 SeptemberThe player's clock in Auckland
  3. 15:41, Saturday 5 September in MaltaSo the session is reported on Saturday 5 September
  4. NZ$158.16 × 0.5640 = €89.20Converted at Saturday's rate, which is Friday's, carried forward

Weekend exchange rates

Reference rates are published on business days. The mart fills the 780 currency-days without a published rate with the last rate before them.

DayEUR per GBPSource
Fri 4 Sep 20261.2323Published
Sat 5 Sep1.2323Carried forward
Sun 6 Sep1.2323Carried forward
Mon 7 Sep1.2268Published

Rates are simulated.

Casino sessions reported on a different day than the player's own date

Canadian evenings run past midnight in Malta. New Zealand mornings start before Malta's day does. Reporting on local dates would split the same day differently in every market.

Canada53.2%
New Zealand15.2%
United Kingdom7.3%
Finland1.5%
Germany, Sweden, Norway0%

Sportsbook revenue is booked when a bet settles, not when it is placed. 52% of settled bets settle on a later Malta day than they were placed, and 7,076 bets were still open when the period ended.

VIP history (SCD Type 2)

Revenue stays with the tier it was earned in

The CRM logs every tier change. The mart turns the log into date ranges, so each bet joins the tier the player held on that day.

One simulated player, six versions

A VIP manager upgraded this player to Gold a week after they joined. Monthly reviews then moved them down and back up. GGR is what the player lost to the house in each version. It was negative while they were Gold, because they won more than they staked.

  1. Bronze16–23 Jan€1,022
  2. Gold24 Jan – 31 May · manual upgrade−€3,002
  3. SilverJune−€49
  4. BronzeJuly – August€1,658
  5. SilverSeptember€2,387
  6. GoldFrom 1 Octcurrent

2026. The CRM log had 78,366 rows. The dimension has 77,858 versions: 508 rows repeated the current tier or were replaced later the same day, so they don't open a version.

GGR from July to September 2026, by VIP tier

Joining revenue to each player's current tier moves it up the ladder, because the players who lost the most were promoted on 1 October. Silver would get €620K instead of the €222K its members produced while they were Silver.

Bronze
€989K
€447K
Silver
€222K
€620K
Gold
€35K
€178K
Platinum
€15K
€17K
Tier held at the time (SCD Type 2)Tier on 1 October 2026
Data quality

86 tests, run on every build

dbt runs the tests after each model it builds. If one fails, nothing downstream of it is built.

31Not-null checks
19Unique keys
11Relationships
9Grain checks
8Accepted values
8Custom checks

The custom checks

  • GGR in the mart matches GGR computed straight from the source tables, so no join duplicated or dropped rows.
  • The KPI tables add up to the facts they summarise.
  • NGR equals GGR minus bonus cost on every row.
  • Every amount finds an exchange rate for its currency and day.
  • Each player's VIP versions follow each other with no gap or overlap.
  • Each player has exactly one current VIP version.
  • Each player has at most one FTD, and it is a successful deposit.
  • No player is active, or deposits, before the day they registered.

Problems in the source data

The simulator writes data the way real platforms do, problems included. The staging models fix them.

1,746PSP callbacks delivered twice. The first delivery is kept.
120,997Payment statuses sent in capitals by one PSP. Normalised.
780Currency-days with no published rate. The last rate is carried forward.
508VIP log rows that change nothing. They don't open a version.
7,076Bets still open at the end. Left out of GGR until they settle.

The tests caught a bug in my simulator. On a player's first day, play could be timestamped before they registered. For a Canadian player who registered late in the evening, that put their first bets on an earlier Malta day than their registration. The activity check failed, and I fixed the simulator.

Portability

Runs on a laptop, written to move

DuckDB builds and tests the whole mart locally. The SQL is kept easy to move to BigQuery.

Engine differences in four macros

Time-zone conversion, the date series, day of week and the float type differ between DuckDB and BigQuery. Each lives in one macro with a version per engine, and dbt picks the right one. Everything else uses SQL both engines accept. The BigQuery versions have not been run yet.

{% macro default__date_in_tz(ts, tz) -%}
    cast(timezone({{ tz }}, timezone('UTC', {{ ts }})) as date)
{%- endmacro %}

{% macro bigquery__date_in_tz(ts, tz) -%}
    date({{ ts }}, {{ tz }})
{%- endmacro %}

Rebuild it yourself

One command simulates the platform, builds the 41 models and runs the tests, in under a minute on a laptop.

# Python 3.12
pip install -r requirements.txt
python run_pipeline.py

The simulator uses a fixed seed, so every run produces the same data and the same numbers as this page.

Built on this mart

What comes next

Continue exploring

Want to walk through the model?

I can talk through the grain decisions, the definitions and how this would run on a production warehouse.