The game feed warehouse

The pitch warehouse starts from a flat export. This one starts from the document. MLB’s public game feed returns one JSON object per game, about two megabytes. The game’s players and venue are at the top. The plays are inside that, the events inside each play, and the pitch’s coordinates and the runner movements inside each event. Nothing about it is tabular until someone makes it so. The warehouse page (opens in a new tab) lets you query what came out, and it serves one game’s document as delivered so the unnesting can be tried by hand.

In a businessMost integration work is exactly this: a partner delivers nested documents, and someone has to make them queryable without losing anything and say what happens when the volume grows a hundredfold. This pipeline lands the delivery as it came, unnests it in SQL with every step tested, and answers the scaling question in writing.

Technical skills: SQL · dbt · PostgreSQL · Python · Nested JSON unnesting · Data modeling (medallion layers) · Data quality tests · Idempotent, resumable ingestion · Docker Compose · DuckDB-WASM

My contribution

I built the pipeline: the resumable pull with a versioned file log, the bronze load as jsonb, the silver unnesting in SQL from a field inventory of the real documents, the gold tables with the base state, pitch-of-outing and tempo derivations, the tests against the feed's own linescore and boxscore, the three questions, the scaling plan, the exports and the page.

Result and limit

2,395 games of 2025 landed as delivered (1.91 GB), 180,757 plays and 814,181 events unnested, 38 of 39 tests passed, and the three questions answered from one command.

It was built to mirror a data engineering take-home a Major League team gave me in early 2026. That assignment asked for exactly this on a confidential tracking feed. Take a nested delivery, make it efficiently queryable, join it to play metadata, answer a time-relative question and one of your own, and explain how it scales. That feed is not public and cannot be used. The game feed is public, and it has the same nesting and the same volume, about 2,430 regular-season games a season at close to 300 plays each. So the pipeline can be shown, run end to end from one command, and pointed at, without touching anything confidential.

Bronze is the file

Every document lands gzipped, byte for byte, with a row in a pull log: the game, the version, when, the HTTP status, the bytes, a hash, the batch. The document is never edited. A re-pull is a new version, and silver reads the newest good one per game. The Postgres table bronze.games_raw is a load of those files as jsonb. A rebuild never touches the network, and the API never has to answer the same way twice. The pull is one request a second with a stated user agent. It skips every game that already has a good version, so a failed hour resumes where it stopped. The 2025 schedule lists 2,430 games: 2,395 final, 5 completed early, 30 postponed, and 34 listed on two dates. Every one of those numbers is in the decision log, and the postponed games are listed by a test rather than dropped.

Silver is jsonb_array_elements

Each silver table is one dbt model whose body is jsonb_array_elements over the layer above, with every cast visible. Games come from gameData. Plays come from allPlays[], 180,757 of them. Pitch events come from playEvents[] inside each play, 814,181 rows, and nothing is filtered. The pickoffs, mound visits, timeouts and automatic balls stay, with an is_pitch flag, because a pickoff between pitches is exactly what a tempo query needs. Runner movements come from runners[] and the fielding credits from credits[] inside those. Every column keeps the feed’s own name in snake case. Before any of that SQL was written, one script walked the real documents and wrote a field inventory: every path, its types, and how often it is present. The models were written against what the feed sends, not what anyone remembered.

Tests are what the design promised

The feed says things about itself, and the tests hold the tables to them. Runs per game summed from the plays equal the linescore, for both teams. Pitches per game equal the boxscore’s count, which holds exactly once automatic balls are excluded, and that exclusion is a logged decision. The feed states the base state twice, once as three runner fields and once as a split label, and the two agree on every play. Every event has a play and every play a game. Every scheduled game has a document or is listed with its status. Every game with tracking on fewer than 95 percent of its pitches is listed, not dropped. 38 of 39 tests passed in the last build, and the page shows each one’s status by table.

One thing the tests caught. The feed’s runner fields describe the bases after the play, not before it, which the sample made plain when a leadoff single read as men on base. The base state before a pitch is therefore the previous play’s state within the same half inning. The derivation’s test was rewritten to check the feed’s two statements against each other instead of against an assumption.

Three questions, fixed before the pull

Each is one SQL file, one CSV, and one paragraph on the page with the reason a coach would ask it and what the answer cannot tell you.

The first is the assignment’s coordinate question, translated. For each pitcher with enough of both, the mean release point of the fastball and of the breaking ball, and the gap between them in inches. A pitcher whose slider leaves his hand two inches from where his fastball does is tipping it, and the number is what a pitching coach checks with video. The limit is that a gap can also be a deliberate change of arm slot.

The second is the time-relative question. For each starter and game, mean four-seam velocity in pitches 1 to 15, 16 to 30, and so on through the outing. Then the drop from the first bucket to the last. The drop is fatigue made visible, and a manager’s hook is a decision about it. It needs the pitch’s number in the outing, which is a window function over the events in time order, computed once in gold so the analyst’s query is a filter.

The third is mine, and it is the one that needs the non-pitch events. For each pitcher, the median seconds between consecutive pitches with the bases empty against a runner on first, keeping only gaps whose previous event was a pitch. Since the pitch clock, the gap is bounded, and how a pitcher spends it with a runner on is a signal the running-game coach wants. The finding so far is that the medians sit just above the clock’s limits, 15 seconds empty and 18 with a runner on. The rule sets the floor, and the spread between pitchers above that floor is the number worth reporting. The feed’s timestamps are an operator’s, not a clock’s, and the page says so.

Ten thousand games

The assignment’s last question was how this runs at production scale, on a feed with thirty frames a second of joint positions for every play. That answer is written out in the repository, and the short form is here. What does not change: immutable deliveries keyed by game mean bronze holds as it is. Land, hash, never edit, re-pull to replace. Idempotent loads per game mean a failed day reruns without cleanup. What changes is where the big table lives. On the order of a hundred billion joint rows a season is not a Postgres table. Silver becomes partitioned Parquet by season, game and play, one row per frame and person. Postgres keeps the metadata and the gold aggregates. Unnesting moves to a columnar engine that reads JSON directly, one game per task, scheduled by the delivery arriving rather than by the clock. The time-relative join, the frame closest to a given moment after release, becomes a window function computed once and stored as a column. The analyst filters instead of searching. The pitch warehouse already runs the first of those. This project adds the unnesting and the time-relative pattern, and the site’s own Parquet exports are the partitioning in miniature.

Stack

Python and uv for the pull and the load, with requests against two public endpoints and nothing else. PostgreSQL 17 under docker-compose or Homebrew. dbt for every table after bronze, with schema and singular tests. ./run.sh does the whole thing from one command. The page serves 15 tables as Parquet and runs DuckDB in the browser. The game-level tables and the gold aggregates are the whole season. The event-level tables are one sample week, because the site serves derived tables and a sample, never a copy of the feed. Every constant is a logged decision. No skeletal or frame-level data was used, and nothing here is a claim about any team’s pipeline.