open source · v0.1.0

nfl-bigquery

nfl-bigquery ingests NFL play-by-play from nflverse into BigQuery, back to 1999. Unlike its siblings — which ingest raw observations — the source here is nflfastR's already-modeled 372-column feed, carrying EPA, WPA, CPOE and air-yards alongside the raw event. That shapes the library: it can't verify against an upstream oracle, so it reconciles itself — aggregating play rows up to the weekly player table and checking six metrics agree.

PythonBigQuerynflverseCLIrepo ↗PyPI ↗

architecture

nfl_playsone row per play — 373 columns (nflfastR's 372-column feed + ingested_at), DAY-partitioned on game_date, clustered season/week/posteam

gamesschedule dimension, DAY-partitioned on game_date

weekly_player_statsper-player per-week stats — INTEGER RANGE partitioned on season, clustered week/player_id

dim_playersplayer dimension, MERGE-upserted on gsis_id

_nfl_ingest_runsappend-only log of every season chunk, powering --resume

nflreadpy pull per season → normalize against dictionary-seeded ColumnSpecs → season-chunked DELETE-then-INSERT for the fact tables (MERGE for dim_players) → verify: 8 checks including a plays→weekly reconciliation of passing/rushing/receiving yards + TDs.

install & usage

pip install nfl-bigquery

gcloud auth application-default login
# resumable full-history backfill
nfl-bigquery sync --seasons 1999-2025 --resume \
    --pbp-table myproject.mydataset.nfl_plays

# player dimension (MERGE upsert on gsis_id)
nfl-bigquery players --players-table myproject.mydataset.dim_players

# reconcile plays against the weekly table
nfl-bigquery verify --source internal --season 2024 \
    --pbp-table myproject.mydataset.nfl_plays --tolerance 5

design decisions

Two partition strategies, chosen by grain

`nfl_plays` and `games` are DAY time-partitioned on `game_date`; `weekly_player_stats` is INTEGER RANGE partitioned on `season` and clustered by week/player_id. A weekly-grain table has no meaningful date to partition on, so forcing one storage strategy across every table would have made the largest fact table cheap and the aggregate table needlessly expensive to scan.

Self-reconciliation, with an honest tolerance

`verify` runs 8 checks — duplicate plays, orphan plays, and a plays→weekly reconciliation across six metrics (passing/rushing/receiving yards and TDs). The `--tolerance` flag exists because lateral plays produce legitimate aggregate yard differences: the library documents exactly where its own reconciliation cannot be exact instead of quietly loosening the check.

Schema drift is a red test, not a runtime surprise

A guard test asserts the nflverse fixture columns and `PLAYS_SCHEMA` match exactly in both directions — no undocumented source column, no orphan spec — and instructs the reader not to weaken the assertion but to refresh the dictionary. This is scar tissue from a sibling pipeline, where an upstream provider silently added a column and broke an explicit-schema BigQuery load in production.

interview talking points

The source is a modeled feed (EPA/WPA/CPOE), so correctness is proven by internal reconciliation rather than against an external oracle.
Storage strategy is chosen per table grain — DAY partitioning for the play/game facts, INTEGER RANGE on season for the weekly aggregate.
Two write strategies coexist: season-chunked DELETE-then-INSERT for facts, MERGE-on-gsis_id upsert for the player dimension.
77 tests across 19 files; ColumnSpec renders to llm / bq-apply / markdown / dictionary / dbt; MIT, Python ≥3.11, published to PyPI — `pip install nfl-bigquery`.