Chess Data Platform - R-Marq.com

The question the platform answers

“Where does a player lose points, and what should they practise?”

Source: the chess.com public API, one JSON archive per player per month.
Platform: a Fabric F2 capacity, OneLake lakehouses, Spark notebooks, Data Factory pipelines.
Serving: an Azure SQL star schema and a Power BI Direct Lake model.
Configuration: the tracked account is a row in a config table. No account name lives in a pipeline, a notebook or the report.
Schedule: a run is daily, weekly or monthly by setting. The capacity is paused between runs.

66monthly archives landed raw

9,772games in fact_game

871,210moves in fact_game_move

11gold tables: 2 facts, 9 dimensions

~15 mincompute per run

37DAX measures in the semantic model

Live from the mart’s load audit. Last run: 24 Sep 2026.

Microsoft Fabric
OneLake
Delta Lake
PySpark
Fabric Data Factory
Azure Data Factory
Azure SQL
Direct Lake
TMDL / PBIR
Key Vault
Entra ID
GitHub

Architecture

A medallion lakehouse on Microsoft Fabric

Left to right. A Copy activity lands the API responses, PySpark notebooks refine bronze to silver and silver to gold, and a Copy activity publishes gold to Azure SQL by truncate and reload. The Fabric workspace and the Azure resources sit in separate directories, bridged by one service principal; secrets are in Key Vault; a contained SQL user does the load and nothing else.

Azure Data Factory · PL_Mart_Chess_MonthlyResumes the F2 capacity, waits for the Fabric run, pauses the F2. The pause runs whether or not the load succeeded.

Fabric · PL_Chess_MonthlyReads the tracked accounts from mart.tracked_account, runs PL_Chess_Bronze per account, then PL_Chess_Transform for silver, gold and publish.

Source
api.chess.com player game archives, one JSON per month.
originexternal REST API
Bronze
lh_bronze / Files. Raw JSON, exactly as returned.
Fabric Copy activityHTTP, binary
Silver
lh_silver / Tables. Delta: chess_game, chess_move.
PySpark notebooknb_silver_chess
Gold
lh_gold / Tables. Delta star schema, 11 tables.
PySpark notebooknb_gold_chess
Serve
Azure SQL mart.* plus a Direct Lake semantic model.
Copy activityTRUNCATE + INSERT
Consume
Power BI report: 6 pages, 37 measures.
Direct Lake / Importand the browser dashboard

Naming conventions

Each item name says what kind of thing it is, then what it is for, so a workspace listing or a run log reads without a legend.

Prefix	Kind	Examples	Rule
lh_	Lakehouse	lh_bronze, lh_silver, lh_gold	One per medallion layer, named for the layer.
PL_	Pipeline	PL_Chess_Monthly, PL_Chess_Bronze, PL_Chess_Transform	Domain, then role. The parent carries its cadence in its name; children carry the stage. The ADF bracket is `PL_Mart_Chess_Monthly`, so the two orchestrators read apart in a log.
nb_	Notebook	nb_silver_chess, nb_gold_chess	The layer it writes, then the domain.
env_	Spark environment	env_chess	The domain whose libraries it pins.
dim_ / fact_	Gold tables	dim_opening, fact_game_move	Kimball: dimensions by subject, facts by grain. Keys end in `_key`; the unknown member is `−1`.
chess_	Silver tables	chess_game, chess_move	Domain, then the real-world entity, one row each.
mart.	Azure SQL schema	mart.fact_game, mart.load_audit, mart.tracked_account	The serving copy of gold, its audit, and the config table, in one schema a login can be scoped to.
DS_ / LS_ / TR_	ADF datasets, linked services, triggers	DS_Mart_LhBronze_Binary, TR_Mart_Chess_Monthly	ADF’s own object types keep their own prefixes.

A run

One run, start to finish

Timings from the load on 24 September 2026. The publish step is the longest because the move fact is 871,210 rows. Spark jobs started by a service principal fail in Fabric with “unable to acquire user token”, so a user-owned Fabric schedule runs the work and ADF only resumes and pauses the capacity.

0:00ADFResume the F2 if paused.
0:01ADFPoll until Active, settle a minute.
0:15FabricThe schedule fires the parent; it looks up the tracked accounts.
0:16BronzeLand the newest archives, four at a time.
0:17SilverFlatten, reshape, replay every game.
0:21GoldDimensions, facts, surrogate keys.
0:24PublishCopy gold to Azure SQL, write the load audit.
0:31ADFSees the run finish and pauses the F2.

Layer 1

Bronze: the source as it arrived

Bronze is a replayable copy of the source. Because the API bytes are kept, silver and gold can be rebuilt without calling the API again, and an account that stops being tracked keeps its archives on disk. The folder names become partition columns when Spark reads them.

Where: lh_bronze / Files in OneLake, not Tables.
Format: Raw JSON, byte for byte the API response.
Layout: One file per player-month, Hive-style folders.
Volume: 66 archives, December 2014 to today.
Rule: Never edited. The newest archives are re-landed each run.

PL_Chess_Bronze

Lookup the archive list
Set variable: last N months
ForEach, 4 in parallel
Copy binary, HTTP to lakehouse

lh_bronze/Files/chessdotcom/games/
  username=<account>/
    archive=2026-08/games.json
    archive=2026-09/games.json

{ "games": [ {
  "url": ".../game/live/...",
  "time_control": "180+2",
  "time_class": "blitz",
  "rated": true,
  "end_time": 1785721149,
  "eco": ".../openings/Sicilian-Defense...",
  "white": { "rating": 3262, "result": "win",
             "username": "<account>" },
  "black": { ... },
  "pgn": "[Event ...] 1. d4 {[%clk 0:03:02]} ..."
} ] }

Layer 2

Silver: one row per game, one per move

The notebook keeps the accounts it is handed, flattens the JSON, reshapes white and black into player and opponent, and replays every game with python-chess to get the board position, material and clocks after each move. The library is pinned in a Fabric Environment so every Spark executor has it.

Where: lh_silver / Tables as Delta.
chess_game: 9,772 rows, one per game from the tracked player’s side.
chess_move: 871,210 rows, one per half-move.
Checked: The replay was tested locally against real archives before it ran on Fabric: every final position matched chess.com’s own.

chess_move, a 3+2 blitz game from August 2023

ply	side	SAN	piece	capture	castle	clock	material W / B	position after
1	white	d4	P			182.0	39 / 39	rnbqkbnr/pppppppp/8/8/3P…
2	black	d5	P			182.0	39 / 39	rnbqkbnr/ppp1pppp/8/3p4/…
3	white	Bf4	B			183.2	39 / 39	rnbqkbnr/ppp1pppp/8/3p4/…
6	black	dxe4	P	yes		172.7	38 / 39	rnbqkbnr/pp2pppp/8/2p5/…
13	white	O-O-O	K		yes	180.4	38 / 39	rnbqkb1r/1p3ppp/p3pn2/…
19	white	Rxd5	R	yes		160.9	34 / 35	rnb1kb1r/1p3ppp/p7/q1pR4…

Layer 3

Gold: a Kimball star schema

Two facts at different grains share seven conformed dimensions, so a slicer on time control or opening filters games and moves alike. The move fact carries two dimensions of its own. Surrogate keys are generated in Spark; a −1 member stands in for unknowns. The whole mart is truncated and reloaded each run, which is why there is no merge logic at this scale.

dim_date4,748 rows
dim_player1 row
dim_opponent1,314 rows
dim_time_control17 rows
dim_opening4,950 rows
dim_result15 rows
dim_color2 rows

fact_gameOne row per game, from the player’s side. 9,772 rows: result, ratings, accuracy, material by phase, time trouble, session position.

fact_game_moveOne row per half-move. 871,210 rows: clock, time spent, material, and the board position kept on the fact.

Only on the move fact: dim_position_profile · 47 rows, phase and castling flagsdim_position · 4,096 rows, the opening tree

The position stays on the fact rather than in a dimension: 86% of the 871,210 positions occur in exactly one game, so a row per position would be as large as the fact. dim_position keeps the positions reached in five or more games, which is the opening tree the report’s explorer reads.

Serving

Serving: Azure SQL and Direct Lake

The ChessImprovement semantic model reads the Delta tables in lh_gold through Direct Lake; the deploy script reframes it after each run. The same 11 tables are copied to Azure SQL, which stays available while Fabric is paused and feeds the live figures on this page. The model and the report are generated by a script from the gold schema and pushed through the Fabric REST API, so nothing is edited by hand.

Model: 11 tables, 16 relationships, 37 measures in display folders, hidden keys, friendly names, sort-by columns.
Report: six pages, one question each, sliced by time control and colour. The player’s name comes from the model, not the report.
Checked: every measure was run through the executeQueries API before a visual used it.
Import twin: the generator also writes an Import-mode project on Azure SQL for Power BI Desktop.

measure 'Performance Rating' =
    [Avg Opponent Rating]
      + 400 * DIVIDE([Wins] - [Losses], [Games])
  formatString: #,0
  displayFolder: Rating

measure 'Conversion %' =
    CALCULATE([Score %],
      'Game'[Max Material Diff] >= 3)

partition Game = entity
  mode: directLake
  source
    entityName: fact_game
    expressionSource: DatabaseQuery

Fabric

The workspace

Fabric draws the lineage from the items and their connections. Pipeline canvases were captured before the account lookup was added to the parent.

Fabric workspace lineage view: HTTP and SQL connections feeding pipelines, notebooks and lakehouses through to the semantic model and report — **Lineage**
An HTTP connection for the chess.com API and a SQL connection feed the pipelines. env_chess supplies python-chess to the silver notebook. The gold lakehouse’s SQL endpoint feeds the semantic model, which feeds the report.

PL_Chess_Monthly pipeline canvas — **PL_Chess_Monthly**Looks up the tracked accounts, runs bronze per account, then the transform child.

PL_Chess_Bronze pipeline canvas — **PL_Chess_Bronze**Lookup the archive list, pick the newest, copy four at a time to lh_bronze.

PL_Chess_Transform pipeline canvas — **PL_Chess_Transform**Silver notebook, gold notebook, then ForEach table: copy to Azure SQL and write the load audit.

nb_silver_chess notebook open in Fabric — **nb_silver_chess**The replay: every game re-played move by move for position, material, castling and clocks.

Fabric Environment env_chess with the chess library pinned — **env_chess**Pins chess 1.11.2 for every Spark executor.

lh_gold lakehouse showing fact_game_move as a Delta table — **lh_gold**fact_game_move as a Delta table, with its surrogate keys.

ChessImprovement semantic model in Fabric model view — **ChessImprovement**The star schema in model view, generated as TMDL.

Fabric Monitor hub listing pipeline, notebook and semantic model runs — **Monitor hub**
Pipeline, notebook and semantic model runs with status and start time. Each publish also writes a row per table to mart.load_audit.

Power BI

The report

Six pages, one question each, over 9,772 games. Every page can be sliced by time control and by the colour played. The figures below are from the 24 September 2026 load.

Report page 1: rating by month and time control, results by year, accuracy by year

Rating progress

2,880 → 3,394blitz rating, June 2016 to September 2026, with a peak of 3,410.

76.4%score across all games, against opponents averaging 2,982.

89average accuracy, and it barely moves by year: 88.4 to 90.9.

Report page 4: material by game phase and score by material balance

Game phases

+1.28pawns of material won per game in the endgame; +0.51 in the middlegame, even in the opening.

77% vs 75%score when up three or more against down three or more. Material barely predicts the result.

6,520of 9,772 games are decided in the endgame.

Report page 3: opening families and the most common move sequences with scores

Openings

81.5%after 1.Nf3, the best-scoring first move, in 869 games.

70.7%in the Ruy Lopez after 3.Bb5, the weakest common line, in 408 games.

1,332games in the Sicilian Defense, the most played family, at 76.6%.

Report page 2: results by colour — **White and Black**78.7% as White, 74.2% as Black. The gap is widest in rapid: 75.0% against 67.5%.

Report page 5: clock management — **The clock**58.2% in the 2,654 games that reach time trouble, 83.2% in the rest. 359 of 1,698 losses are on time.

Report page 6: performance by session position and previous result — **Habits**The first game of a session scores 81.6%; later games 75.8%. After a loss, 74.0%; after a win, 77.8%.

In short

Architecture: a medallion lakehouse on Fabric and OneLake.
Engineering: Data Factory, PySpark, Delta, a config table instead of names in code.
Modeling: a Kimball star schema with the position kept on the fact.
BI: a Direct Lake model and report generated from code.

The repository is private and available on request. A live walkthrough shows the most: resume the capacity, run the pipeline, watch the lineage and the report update.

Ask for a walkthrough Open the browser dashboard Portfolio