The pitch warehouse

The player comps tool stands on this warehouse, and the warehouse page (opens in a new tab) lets you query it. It has the shape of Gamebot, the medallion lakehouse the Survivor analyses stand on, applied to 1.4 million pitches. Bronze is the source as delivered. Silver casts and labels. Gold is what the model and the page read. dbt declares the dependencies and runs the tests, and the page draws the lineage from them.

In a businessMost data work starts with landing a source faithfully and testing it before anyone builds on it. This warehouse keeps raw data as delivered, does every transformation in SQL with its reason logged, and lets a reader click through the lineage.

Technical skills: SQL · dbt · PostgreSQL · Python · Data modeling (medallion layers) · Data quality tests · Resumable ingestion · DuckDB-WASM

My contribution

I built the pipeline: the resumable weekly pull with a batch log, the bronze landing as delivered, the silver labeling in SQL with the attack-zone rebuild, the gold count matrix and its companions, the 24 dbt tests, the exports, and the page.

Result and limit

1,373,138 pitches of 2025 and 2026 through September 15 in bronze and 902 qualified hitter-seasons, every one matched to the register. 24 of 24 tests pass, and the page serves 2 MB of Parquet with SQL in the browser.

Bronze

Baseball Savant’s Statcast search export arrives through pybaseball in seven-day windows, one batch per window. Every column lands as text, exactly as delivered, with the batch id, the window bounds, the time of the pull and a hash of the window’s CSV. Bronze is never edited. A re-pull is a new batch, and silver reads the newest good batch per window, so a bad week is replaced by pulling it again. The Chadwick Bureau register lands the same way and supplies names, birth years and the crosswalk between Savant’s ids and everyone else’s. A pull log holds one row per batch with its row count, which is how a partial window gets caught.

Silver

pitches is one row per regular-season pitch with the three labels the vocabulary needs. The pitch class comes from Savant’s pitch type. The attack zone is rebuilt from the pitch coordinates and the batter’s own strike zone, using Savant’s published rings, because the export’s zone column is the older nine-cell grid. The action is a take, a whiff, a foul, or one of Savant’s six contact classes. The three join into a word, and a pitch that gets no word carries a stated reason instead. player_seasons is one row per batter per season. It carries pitches, plate appearances, wOBA from Savant’s own per-pitch fields, the team he appeared for most, and his age from the register. A 400-pitch flag decides whether he is in the model’s corpus.

Gold

word_counts and count_matrix are the same counts, long and wide. The wide one has 108 word columns and is what the topic model reads. vocabulary is the 108 words with their league-wide counts and the 500-appearance cut. pitch_diet is the share of each pitch-class-by-zone cell thrown to each hitter, kept beside the topics and never fed into them. word_counts_halves splits each season two ways, by date and at random, for the reliability test. player_splits holds platoon and home-away wOBA for the evaluation. player_positions counts games at each fielding position from the fielder ids on every pitch, so the tool can filter by eligibility without another source.

Tests

dbt runs 24 tests on every build and the page shows each one with its status. The three the design asked for are singular tests over silver. Every pitch has exactly one word or a stated reason for none. The four attack-zone rings partition every pitch with a location. The six contact classes cover every non-bunt ball in play with tracking. The rest are the usual ones, unique keys, accepted values, the count matrix rows summing to the pitch counts, the two halves adding back to the whole, and every hitter having one primary position.

What is served

The page serves thirteen tables as Parquet, about 2 MB, and DuckDB reads them in the browser on demand. Every gold table and the two silver summaries are complete. The two pitch-level tables are one sample week, the same week in both layers, because the full tables hold 1.4 million rows each. The sample is marked in the lineage map and in the sidebar. The row counts on the map are the warehouse’s, not the sample’s.

Stack

PostgreSQL 17 from Homebrew, the same server Gamebot uses, with its own database and role. dbt with schema tests and a script that runs the steps in order without Airflow, since the Mac has no Docker; an Airflow DAG is declared beside it. pybaseball for the two sources, psycopg and COPY for the landing, pyarrow for the exports, pytest with three days of saved pitches as fixtures so nothing in the tests needs the network. Every constant is logged with its reason in the project’s decision log.