jason blahovec
open source · v0.1.0

ffl-bigquery

ffl-bigquery is the fantasy layer on top of nfl-bigquery. It lands historical Average Draft Position from two independent markets, the nflverse id bridge that makes ADP joinable to play-by-play, and the coaching/usage/charting context that explains it — 13 tables, ~3.5M rows backfilled. Its defining constraint is that the upstream sources are quietly unreliable in ways no schema check catches, so the library’s job is as much documenting what is NOT knowable as loading what is.

PythonBigQuerynflverseCLIrepo ↗PyPI ↗
architecture
ff_adpsnapshot-grain ADP from Fantasy Football Calculator (2010+) and MyFantasyLeague (2011+), MERGE-upserted on a six-column grain so a repeated daily sync is a no-op
ff_player_xrefthe 20-system nflverse id bridge — the only path from a fantasy provider’s id space to gsis_id, and therefore to nfl_plays
team_scheme_weekper (season, week, team) scheme fingerprint joined to the head coach — shotgun/no-huddle/pass rate, PROE, EPA, plus era-gated personnel, coverage and blitz rates
nfl_coacheshead coach at PER-GAME grain, 1999–2026, which makes mid-season firings ordinary data instead of a special case
participation · ftn_charting · depth_charts · snap_counts · injuries · ff_opportunity · ff_points_weekly · ff_rankingsthe usage and charting surface; depth_charts reconciles two disjoint upstream schemas that share exactly one column
nfl_coordinatorsopt-in only — 46.2% measured fill, with per-row source/confidence/retrieved_at provenance
Two third-party HTTP sources + nflverse → normalize (schemas generated from upstream dtypes with a measured-landmine override overlay) → resolve gsis_id, refusing ambiguous matches rather than guessing → season-chunked DELETE-then-INSERT, or MERGE for the snapshot-grain tables → verify: resolution rate, grain uniqueness, snapshot idempotency, PPR reconciliation against upstream’s own column, and a scheme-denominator check.
install & usage
pip install ffl-bigquery

gcloud auth application-default login
# ADP backfill — two markets, throttled per FFC’s terms
ffl-bigquery sync-adp --seasons 2010-2026 --sources ffc,mfl \
    --adp-table myproject.mydataset.ff_adp \
    --xref-table myproject.mydataset.ff_player_xref

# the nine season-chunked nflverse tables, resumable
ffl-bigquery sync-nflverse --seasons 1999-2026 --resume \
    --dataset myproject.mydataset

ffl-bigquery verify --checks points-weekly,scheme-denominators --season 2024 \
    --points-weekly-table myproject.mydataset.ff_points_weekly \
    --scheme-week-table myproject.mydataset.team_scheme_week
design decisions
Every derived rate ships beside its own denominator
Play-charting coverage is a sample, never a census: `defense_man_zone_type` is 0.000 in 2016–17 and never exceeds .496 after. So `team_scheme_week` emits `plays_charted_coverage` next to `man_rate`, `plays_charted_ftn` next to `blitz_rate`, and so on — and out-of-era columns are NULL, never 0. A zero would read as “they never blitzed” rather than “nobody charted it.” A verify check enforces the pairing mechanically instead of trusting the convention.
Ambiguity is refused, never guessed
MyFantasyLeague publishes an id that IS one of nflverse’s 20 systems, so it joins exactly. Fantasy Football Calculator publishes an id present in NONE of them, so it can only resolve by normalized name — and `merge_name` + position is not unique. Any collision resolves to NULL. A wrong gsis_id silently attributes one player’s draft market to another; a NULL is visible and countable.
Upstream dtypes are vintage-dependent, so declared types are not trusted
`season`, `week`, `play_id` and `pos_slot` each change type between seasons in the same feed — `pos_slot` is a string label every year except 2025, where it is int32. Two production loads failed on exactly this before the writer stopped trusting a declared type to match the frame. A guard test now asserts every clustering column is a type BigQuery can actually cluster on, across all nine specs.
What is not knowable is documented as prominently as what is
FFC silently ignores its own start_date/end_date parameters, so intra-preseason ADP drift is forward-capture-only and cannot be backfilled. FFC has no 2025 data at all. `gsis_id` is NULL for 37.9% of the id bridge, capping resolution at a measured 89.9%/91.8%. `offense_personnel` reports 100% fill from 2023 while carrying defensive players in the offense column, so personnel is parsed from a different field for that era. Each of these is in the README, not discovered later.
interview talking points