NBA Daily ETL

Overview

A daily pipeline that pulls NBA box scores, transforms them with PySpark, and lands partitioned Parquet plus a model-ready feature table. A trailing-window model sits on the feature layer, and a Streamlit dashboard reads from it.

It ran live and unattended through the 2025–26 playoffs and Finals, ending with New York over San Antonio 4–1 on 13 June 2026 — against a source that changed underneath it every night.

The problem

I wanted a pipeline that ran against live data rather than a Kaggle CSV, because the interesting failures in data engineering only appear when the source moves.

A static dataset lets you skip every question that matters. Nothing gets renamed mid-season. Nothing returns an empty body. Nobody has to decide what a rerun of yesterday means. Run the same code against a live API every night for three months and all of those arrive on their own schedule.

The full season in the processed layer is 2,630 team-game rows across 208 distinct game dates and 30 teams. The design target was operational behaviour — reruns, backfills, upstream changes — rather than volume.

Architecture

Four zones, each a deliberate boundary: raw/ holds what the API returned, staging/ holds the current run’s output, processed/ is the canonical history, and features/ is the model-ready table. Everything is partitioned by (season, game_date).

The DAG is five tasks — ingest_raw, transform_and_aggregate, write_processed, write_features, notify_done — running on a LocalExecutor with Postgres as Airflow’s metadata database. The pipeline’s sink is Parquet on S3 or local disk; Postgres never holds pipeline data. catchup=False and max_active_runs=1, so a late start doesn’t stampede the API with a month of scheduled runs.

The features are trailing windows — rolling true shooting, win rate, assist-to-turnover, points for and against — computed per team as of each game date, never including the game being predicted.

Key decisions

Staging, then promote. transform_and_aggregate writes to staging/run_date={ds}, and write_processed promotes that into the canonical processed/ prefix. A transform that dies halfway leaves a partial staging directory, not a half-written canonical history. The cost is an extra write of the same data and a second task to keep correct.

Dynamic partition overwrite. spark.sql.sources.partitionOverwriteMode=dynamic makes a rerun replace only the partitions the run actually produced. Without it, Spark’s default overwrite replaces the entire processed/ prefix — so rerunning one day deletes the season. That surfaced during the first backfill. The cost is that the safe behaviour is now load-bearing and invisible: it lives in one config line, and nothing in the DAG would fail loudly if someone removed it.

Single node, on purpose. Every run is local[*]. At roughly 2,600 rows a season, a cluster would be theatre. The code is written to be EMR-compatible — S3A configuration, partitioned writes, no driver-side collect() in transforms. The cost is that distributed behaviour — shuffle tuning, executor sizing, skew — stays untested until it runs on one.

What broke

A 200 that meant failure. BoxScoreAdvancedV2 started returning HTTP 200 with an empty {} body — soft-deprecated by stats.nba.com with no error status, no deprecation notice, and nothing an HTTP-level check would catch. Anything that trusted the status code would have written empty advanced stats every night and looked healthy doing it. The fix was moving to V3 with an explicit column mapping, but the lasting lesson is that “the request succeeded” and “the response is usable” are different assertions, and only the second one is worth making.

A calibration regression that wasn’t. Log loss drifted from 0.654 to 0.662, and the obvious reading was that the model had become mis-calibrated. It hadn’t. Playoff advanced stats were missing and being median-imputed, so the model was being fed made-up inputs for exactly the games that mattered most. Filling in the real data fixed the calibration with no calibration code written. Distinguishing “the model is mis-calibrated” from “the model is mis-fed” is most of the debugging work in a pipeline like this, and the instinct to reach for the modelling fix first is the one to resist.

How it’s verified

160 tests pass and 1 is skipped in under two minutes, with no AWS credentials and no network access. That constraint is the point: a test suite that needs a bucket and an internet connection is a suite nobody runs before pushing. The single skip is an Airflow-load smoke check that activates only when apache-airflow is installed.

GitHub Actions runs ruff, black, pytest, and a Docker image build on every push, so the container that would run in production is proven to build on every commit rather than at the moment it’s needed.

Re-running any day is safe, and one Spark setting is the whole mechanism: spark.sql.sources.partitionOverwriteMode=dynamic alongside mode("overwrite").partitionBy("season", "game_date"). Under Spark’s default static mode that same write wipes the entire processed/ prefix and replaces it with the current run, so re-running one day silently destroys the rest of the season. A 14-day backfill found that, not the documentation.

Idempotent here means convergent, not append-only: re-running is also how a day gets corrected when a box score is revised, and the partition ends up holding the new values rather than both versions. Seven tests in tests/test_idempotency.py cover it — re-running a day doesn’t duplicate, writing day 2 doesn’t clobber day 1, re-ingesting day 1 with corrected scoring updates day 1 and leaves day 2 alone, and a five-day load followed by an overlapping three-day re-run still holds one row per game date and team. The key test was checked for discrimination: under static mode only day 2 survives, under dynamic both do. A test that passes under the bug it guards against is decoration. An eighth test pins get_spark() to dynamic mode, so the behavioural tests can’t drift into testing a configuration the pipeline doesn’t use.

The features layer is gated on assertions before the write, so a violation fails the Airflow task rather than publishing a bad layer on a green run. That matters because a silent defect here crashes nothing — it quietly degrades every number downstream, and the first symptom is a model metric moving for reasons nobody can explain. etl/quality.py checks the grain (exactly one row per game and team), window completeness, a null contract, value ranges — a win rate above 1.0 means the averaging is broken, not that a team won more often than always — and reconciles the features row count against processed, which share a grain.

The null contract is the part worth stating. Ten columns may never be null; seven may, each with a stated reason — rolling_pts_home is null until a team plays at home in-window, and advanced-stat columns are null for partitions ingested before that phase. Nullable columns are reported as a rate, never failed. That allowlist is what gives the non-null set meaning: without it, “some nulls are fine” degrades into “nulls are never checked”, and a test asserts every nullable column carries a real justification. Against the committed season snapshot the gate reports:

[features quality PASS] rows=2630, teams=30, games=1315, seasons=1,
  max_games_in_window=10, null_rate__rolling_pts_home=0.8%,
  null_rate__rolling_pts_away=1.0%, processed_rows=2630

14 tests in tests/test_quality.py corrupt one property each, including that a failed gate leaves no output behind.

Operationally, the record is the evidence. A 14-day playoff backfill completed 70 of 70 task instances with zero failures at a mean run duration of 1:11. Across three-plus weeks of daily operation there was exactly one transient nba_api failure, recovered by Airflow’s retry policy without intervention.

The model

The model is built around evaluation discipline. Every number comes from strict date-boundary walk-forward cross-validation — train on the past, test on the future, with no random k-fold anywhere near it. Random splits on time-series data leak the future into training and produce numbers that feel excellent and mean nothing.

How leakage is prevented

The splitter is only half of it. Trailing windows are the classic place for target leakage, and the features layer’s window is inclusive of the current game: Window.partitionBy("team_id").orderBy("game_date", "game_id") .rowsBetween(-9, 0). That is correct for a descriptive layer — the dashboard leaderboard wants “how has this team played, through this game” — and fatal for a model predicting that game.

So models/dataset.py is the only thing that turns features into model inputs, and it shifts every rolling column by one game within each team’s own chronological sequence before the join. The model sees each team’s window as it stood entering the game, and the label is the only thing taken from the current game.

A team’s first game has no prior window and is dropped rather than imputed. The drop sentinel is games_in_window, because it is never null in the features layer — so a NaN after the shift uniquely identifies a first game. The other rolling columns can be legitimately null, so using one of them would silently discard valid rows.

A third, quieter firewall: every model is an sklearn Pipeline, so the imputer and scaler are fit inside each training fold. Fitting a scaler on all data before splitting is a classic silent leak. Isotonic calibration sits inside the same boundary.

Four tests pin one property each: test_build_training_frame_is_leak_free (game 3’s row carries each team’s game-2 values, and the failure message reads “Got 103 => the game’s own features were used”), test_walk_forward_never_leaks_future_into_train (max train date below min test date, every fold), test_walk_forward_does_not_split_a_single_day, and test_game_is_dropped_when_one_team_has_no_prior_history.

The lag is within the pipeline’s own data. If upstream revises a box score, a re-ingest changes the historical row and the lagged feature with it — correct behaviour for a corrected stat line, but it means model metrics are reproducible only against a fixed data snapshot.

Three baselines are named and reported on the same out-of-fold rows as the model:

Predictor	Accuracy
Always pick the home team	0.553
Better trailing win%	0.636
Better trailing TS%	0.567
Logistic regression (v1.3.1)	0.634

At the v1.3.1 snapshot the model scored 0.634 against the best baseline’s 0.636, on 993 out-of-fold rows drawn from 1,284 games — 0.2 percentage points behind. The season-final retrain reached 0.6407 against 0.6366, edging the baseline for the first time by 0.4 percentage points — inside the noise band for a sample this size.

The first publicly published pick is a no_bet. The raw model said 0.7943, isotonic calibration pulled that to 0.5095, and a 10-percentage-point disagreement guardrail caught the residual gap against Pinnacle’s de-vigged 0.6225. A system whose first public output is an audit-trailed refusal is behaving correctly; one that always has an opinion is the one to distrust.

Limits

Not a betting product, and no verified edge. The picks layer tests calibration and refusal against a real market price. A 0.4pp margin on 993 games is not an edge.

Single-node Spark. Never executed on a multi-node cluster, so shuffle tuning, executor sizing, and skew are untested.

No serving path. The trained model is a joblib artifact read by a Streamlit process. The feature store is partitioned Parquet on one node — no online store, no point-in-time lookup service, no feature versioning beyond the partition layout — and there is no inference API, no registry promotion gate, no drift or staleness monitoring. MLflow here is a run log, not a deployment surface.

One season of data. 2025–26 only. Walk-forward CV on a single season cannot separate a 0.4pp edge from sampling noise, and season-to-season regime changes are unobserved. A multi-season backfill is the highest-value next step.

Links

The repository and the live dashboard are linked at the top of this page. The repo’s README carries the full limits table, the engineering notes behind the upstream breakages, and the architecture walkthrough.