Fourth&Data

When it's fourth and short, ask the data.

Fourth&Data is a free, public MCP server that turns 26 seasons of NFL play-by-play (1999–2025) and 24 of college football (2002–2025) into analyst answers. Ask your AI assistant a football question in plain English; it calls curated tools and answers with the verdict first and the evidence attached — league ranks, trends, sample sizes, and only the caveats that actually apply.

Connect

Endpoint: https://fourthanddata.fly.dev/mcp

Claude (claude.ai or Desktop): Settings → Connectors → Add custom connector → paste the endpoint URL. Or with Claude Code:

claude mcp add --transport http fourthanddata https://fourthanddata.fly.dev/mcp

Cursor: add to ~/.cursor/mcp.json:

{ "mcpServers": { "fourthanddata": { "url": "https://fourthanddata.fly.dev/mcp" } } }

ChatGPT: Settings → Connectors (developer mode) → add the endpoint URL.

Then just ask: "Lions 4th-and-2 at the 34, down 4, 6:10 left — do they go?"

Free tier: no key needed, ~60 requests/hour per IP. The gateway may briefly serve cache-only answers under heavy load (it says so honestly when it does). No accounts, no tracking — client IPs are salted-hashed before they touch a log.

Data, attribution, honesty

• NFL data from nflverse (CC-BY 4.0 — thank you). College data from cfbfastR / sportsdataverse and the CollegeFootballData.com API.
• College EPA for 2022–2025 comes from our own model (cfb_ep_v1, validated r = 0.973 against cfbfastR's reference EPA); those seasons are labeled with their fidelity in every answer that uses them.
• Win-probability values are empirical — the share of games actually won from each state since 1999 — not a simulation. Every tool's envelope carries a methodology field saying exactly how its numbers are built.
• Coverage edges: PROE needs 2006+; NGS/FTN-derived context is 2016+/2022+; college player attribution in the API era (2022+) is parsed from play text (~87% coverage, labeled).
• Not affiliated with, endorsed by, or connected to the NFL, the NCAA, any team, or any data provider. For entertainment and analysis; nothing here is betting advice.

Tools — the Fourth&Data launch toolset

Every tool answers in the same envelope: answer (analyst read, verdict first), headline, values, context (rank/percentile/trend), sample, fidelity, season_type, methodology (standing method notes — always true of the tool), caveats (situational only — thin sample, ambiguous name, fidelity seam), sources. The caveat discipline is a calibration ruling: what is always true lives in methodology; prose carries only what this answer tripped.

Tool	What it answers	Example prompt
fourth_down_verdict	Go, kick, or punt — the signature call	"Lions 4th-and-2 at the 34, down 4, 6:10 left — do they go?"
player_trajectory	Multi-season arc, NFL/CFB/both	"How has Joe Burrow's efficiency moved year over year?"
explain_production	Where a season came from (descriptive PDR layer)	"Break down Tua's 2023 — how much was the system?"
schedule_adjusted_epa	Raw vs opponent-adjusted EPA, gap called out	"Was Goff's 2024 inflated by the schedule?"
prospect_comps	Statistical college comps + what happened to them	"Who does Jayden Daniels comp to statistically?"
team_identity	What a team actually does, vs league	"What's the Ravens' identity on offense in 2024?"
matchup_preview	Unit-collision analysis + head-to-head	"Chiefs–Bills: who has the edge and where?"
breakout_candidates	Rising usage/efficiency signals, no projections	"Which WRs are trending toward a breakout?"
weird_stats	Oddities ranked by rarity vs 26 years	"What was weird in week 12 of 2024?"
history_answer	Deep-history leaders/records/counts, filters done right	"Most 4th-quarter comeback wins since 1999?"
get_qb_epa_leaders	QB leaderboard by EPA per pass play	"Who led the NFL in EPA per play in 2024?"
compare_teams	Quick two-team EPA side-by-side	"Chiefs or Bills in 2024 — who was better?"
get_team_tendencies	Run/pass split and EPA by down	"What do the 49ers call on 2nd down?"
cache_stats	Gateway internals	—

season_type accepts REG (default), POST, or ALL. It defaults to regular season because that is what "the 2024 season" means in almost every football question — letting playoffs leak in silently inflates play counts and shuffles leaderboards.

Note that get_qb_epa_leaders measures EPA per pass play (attempts and sacks). Designed QB runs and scrambles are classified as runs upstream and are excluded, so mobile quarterbacks are understated relative to an all-plays EPA metric. MART_QB_SEASON (behind explain_production and player_trajectory) coalesces scrambles back in via the rusher attribution, so those tools carry the all-dropback number.

fourth_down_verdict — how the call is made

Each choice is valued by empirical win probability: league conversion rates by distance × field zone, modern-era FG make rates by kick distance, expected punt nets — each branch resolved against a WP surface built from actual game outcomes of every 1st-down state since 1999 (not a model's opinion). Three surfaces: fine (10-yd × score-diff × 7.5-min buckets), a coarse fallback for thin cells, and an endgame surface (final 5 minutes: 60-second buckets, exact score diff) so the clock is never erased in states where the clock is the state. For flat early-game toss-ups, ordering ties break on the state-only WP model — disclosed in sample.tiebreak when used. Every answer carries the coach-behavior line (how often coaches actually went in this spot, 2015 vs latest), and toss-ups end with the one-sentence tiebreaker: what would tip the call.

Tests

pytest tests/ runs two suites against live Snowflake:

• Sanity (test_sanity_fourth_down.py): ~10 obvious-answer situations — must-punt, obvious-go, never-punt-from-scoring-range — that fail loudly on regressions like the punt-geometry inversion. Wired into CI (.github/workflows/ci.yml; runs when SNOWFLAKE_* secrets are configured).
• Smoke (test_smoke_tools.py): every tool with real params, envelope shape checked.

---

Development & architecture

Everything below is for people running or extending the warehouse and gateway.

Architecture (v0.1)

nflreadpy ─────parquet──▶ @INGEST_STAGE ──COPY INTO──▶ GAMEDAY.RAW      (loader role)
cfbfastR-data ─parquet──▶ @INGEST_STAGE ──COPY INTO──▶ GAMEDAY.RAW_CFB  (loader role)
CFBD API ──────parquet──▶                                   │
                                                   dbt ──▶ GAMEDAY.MARTS
                                                            │
public ◀─streamable HTTP (Fly.io)─▶ FastMCP ──read-only role┘
local  ◀─stdio────────────────────▶ server   │
          ops layer: per-IP rate limit ·     └▶ GAMEDAY.OPS.TOOL_CALLS (audit)
          TTL cache · concurrency cap ·
          query circuit breaker (cache-only mode)

The deployed gateway (MCP_TRANSPORT=http) adds an ops layer (server/ops.py): per-IP sliding-window rate limiting with an optional env-configured API-key tier (API_KEYS="key:limit,..."), salted-IP-hash audit logging batched to GAMEDAY.OPS.TOOL_CALLS (the reader role's single, deliberate INSERT grant — see sql/setup_ops.sql), a global concurrent-query cap, per-query timeouts, and a circuit breaker that flips to cache-only mode on anomalous query volume. Cost stack, outermost first: rate limit → cache → concurrency cap → breaker → 45s query timeout → 60s warehouse statement ceiling → 30-credit monthly resource monitor (hard suspend).

Setup

1. Snowflake trial

Sign up at signup.snowflake.com (30 days / $400 credits, no card). Pick AWS + a nearby region. Note your account identifier (Admin → Accounts, format like ABC12345.us-east-1).

2. Key-pair auth (Snowflake now requires MFA/keys for programmatic access)

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out sf_key.p8 -nocrypt
openssl rsa -in sf_key.p8 -pubout -out sf_key.pub

In a Snowflake worksheet (paste the pub key contents, minus header/footer lines):

ALTER USER YOUR_USERNAME SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';

3. Create the Snowflake objects

Run sql/setup.sql in a worksheet as ACCOUNTADMIN (edit YOUR_USERNAME at the bottom first). This creates the database, RAW/MARTS schemas, an XSMALL warehouse with 60s auto-suspend and a 60s statement timeout, a 30-credit/month resource monitor, and the loader/reader roles.

4. Local environment

python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
cp .env.example .env   # fill in account, user, key path
set -a; source .env; set +a

5. Ingest

python ingest/ingest.py --seasons 2023 2024 --dry-run   # verify data pull works
python ingest/ingest.py --seasons 2023 2024             # full load to Snowflake
python ingest/ingest.py --recreate --skip-export        # rebuild schema from local parquet

--skip-export reuses whatever is already in data/*.parquet instead of re-pulling nflverse. --recreate DROPs each table before rebuilding it — required after any change to the schema template, since CREATE TABLE IF NOT EXISTS silently keeps the old shape. --fresh ignores the checkpoint and re-pulls.

Every (dataset, season) pull is checkpointed as a parquet shard under data/shards/, so an interrupted run resumes instead of restarting. Seasons the upstream does not publish are recorded as unavailable and not retried.

Season coverage is one constant. LATEST_SEASON in ingest/ingest.py drives every dataset's season list; bumping it for 2026 is a one-line change.

5b. College ingest (CFB)

First create the schema — the loader role cannot, so run sql/setup_cfb.sql in Snowsight as ACCOUNTADMIN (it is one BEGIN...END block because the worksheet rejects multi-statement pastes). Then:

python ingest/cfbd_ingest.py --bulk-only      # zero API calls
python ingest/cfbd_ingest.py --estimate-only  # print planned cost, spend nothing
python ingest/cfbd_ingest.py --with-pbp-api   # include metered play-by-play
python ingest/cfbd_ingest.py --max-calls 60   # tighter ceiling for one run

The ingest is bulk-first regardless of tier: anything published by cfbfastR-data is downloaded from GitHub at zero API cost, and the API covers only what bulk does not. Calls are counted in data/cfb/.cfbd_quota.json, which warns at 25/50/75% and hard-stops before the monthly limit. Every run prints its planned call count before spending anything, and --estimate-only prints it and exits.

Two season ranges, deliberately separate: BULK_EARLIEST (2002) and API_EARLIEST (2015). Widening the free bulk history can never silently widen a metered API pull.

Current tier is Tier 2 ($5/mo, 30,000 calls/month, play-by-play unlocked). Set TIER_NAME/TIER_MONTHLY if that changes. Reference costs: recruiting + portal + returning production for 2015–2025 is 38 calls; play-by-play for 2022–2025 is 89, since /plays is week-scoped (62 regular-season weeks + 27 postseason probes).

6. Wire into Claude Desktop

claude_desktop_config.json:

{
  "mcpServers": {
    "gameday": {
      "command": "/absolute/path/.venv/bin/python",
      "args": ["/absolute/path/server/server.py"],
      "env": {
        "SNOWFLAKE_ACCOUNT": "ABC12345.us-east-1",
        "SNOWFLAKE_USER": "YOUR_USERNAME",
        "SNOWFLAKE_PRIVATE_KEY_PATH": "/absolute/path/sf_key.p8"
      }
    }
  }
}

Restart Claude Desktop, then ask: "Who led the NFL in EPA per play in 2024?"

Data inventory

GAMEDAY.RAW — NFL, 30 tables, 5,780,790 rows, 682 MB in Snowflake. Every table verified row-for-row against its source parquet.

Table	Seasons	Rows	Cols
pbp	1999–2025	1,279,628	372
depth_charts	2001–2025	1,423,400	26
rosters_weekly	2002–2025	906,378	36
participation	2016–2025	478,989	26
player_stats	1999–2025	476,156	145
snap_counts	2012–2025	324,611	16
ftn_charting	2022–2025	185,215	29
rosters	1920–2025	139,685	36
ff_opportunity	2006–2025	112,297	159
injuries	2009–2025	90,752	17
pfr_def / pfr_rec / pfr_rush / pfr_pass	2018–2025	62,345 / 35,724 / 18,461 / 5,424	16–29
contracts	all	51,803	37
player_stats_season	1999–2025	49,514	143
players	all	25,035	39
officials	2015–2025	21,900	9
ngs_receiving / ngs_rushing / ngs_passing	2016–2025	14,731 / 6,059 / 5,933	22–29
team_stats	1999–2025	14,531	133
draft_picks	all	12,670	36
ff_playerids	current	12,470	35
combine	2000–2025	8,649	18
schedules	1999–2025	7,276	46
ff_rankings	current	5,281	25
trades	all	4,975	11
team_stats_season	1999–2025	862	131
teams	current	36	16

GAMEDAY.RAW_CFB — college, 10 tables, 5,163,561 rows, 1,009 MB in Snowflake. Every table verified row-for-row against its source parquet.

Table	Seasons	Rows	Cols	Source	API calls
cfb_pbp	2002–2021	2,456,357	701	bulk	0
cfb_player_stats	2014–2025	1,637,447	70	bulk	0
cfb_pbp_api	2022–2025	648,121	30	CFBD API	89
cfb_rosters	2004–2025	282,157	18	bulk	0
cfb_recruiting_players	2015–2025	45,735	20	CFBD API	11
cfb_team_info	2004–2025	39,323	29	bulk	0
cfb_schedules	2002–2025	36,231	31	bulk	0
cfb_transfer_portal	2021–2025	14,422	10	CFBD API	5
cfb_recruiting_teams	2015–2025	2,338	5	CFBD API	11
cfb_returning_production	2015–2025	1,430	15	CFBD API	11

GAMEDAY.MARTS.CFB_PBP_EPA — unified college EPA, 3,011,211 rows, 2004–2025. Our own EP/EPA/WP model (cfb_ep_v1) scores both pbp eras into one mart with OUR_EP, OUR_EPA, OUR_WP and a GARBAGE_TIME flag — closing the gap that 2022–2025 API plays carry no EPA. Validated against cfbfastR's reference EPA at r = 0.973 across 1.96M bulk-era plays (gate was 0.95); externally cross-checked at r = 0.83 vs CFBD's independent ppa on the API era. Model details, guards and limitations: docs/EPA_MODEL.md.

Play-by-play spans 2002–2025 across two deliberately separate tables. Bulk cfb_pbp (2002–2021) is cfbfastR's enriched ~700-column frame — EPA, win probability, participation. cfb_pbp_api (2022–2025) is the raw ~30-field CFBD play record, fetched week-by-week on Tier 2, with season/season_type/week stamped at fetch time since /plays echoes none of them. They are not unioned: a merged table would be mostly-null with column meaning depending on era. CFBD files every bowl and playoff game as postseason week 1.

Design decisions (read before interviews)

• No query(sql) tool. The model is an untrusted query author; curated parameterized tools + a read-only role with statement timeouts are the mitigation. Raw SQL access from an LLM is a prompt-injection → data-exfiltration vector.
• INFER_SCHEMA + COPY INTO instead of row inserts: the standard Snowflake bulk pattern, and nobody hand-writes a 372-column DDL. The template UPPER()s the inferred column names — see Lessons learned.
• TTL cache in the gateway so repeat questions never resume the warehouse — compute cost control lives in the app layer and in AUTO_SUSPEND.
• TRUNCATE + full reload is deliberate v1 simplicity; incremental merge comes with dbt.

Lessons learned

Parquet + INFER_SCHEMA gives you case-sensitive columns. CREATE TABLE … USING TEMPLATE (INFER_SCHEMA(…)) copies Parquet field names verbatim — lowercase — and Snowflake stores them as quoted identifiers. Unquoted SQL folds to uppercase, so every query failed with invalid identifier 'POSTEAM'. The load itself succeeded, because COPY INTO used MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE, so the break only surfaced at read time. Fix: build the template explicitly with OBJECT_CONSTRUCT('COLUMN_NAME', UPPER(COLUMN_NAME), …) and keep column order with WITHIN GROUP (ORDER BY ORDER_ID). Quoting every identifier in the queries also works, but it pushes the problem onto every future query instead of fixing it once.

CREATE TABLE IF NOT EXISTS hides schema changes. Fixing the template did nothing until the tables were dropped — hence --recreate. An idempotent DDL statement is not the same as a migration.

Snowflake has no role-level statement timeout. ALTER ROLE … SET STATEMENT_TIMEOUT_IN_SECONDS is not valid; the parameter lives on account, warehouse, user, or session. The ceiling belongs on GAMEDAY_WH, which every gateway query runs through anyway.

A tool's default filter is part of its contract. The tools originally had no season_type filter, so "2024" quietly meant regular season plus playoffs — inflating team play counts by ~10% and reordering the QB leaderboard. Defaulting to REG and making POST/ALL explicit removed a whole class of wrong answers.

A cost guardrail and a bulk load are the same knob. The 60s STATEMENT_TIMEOUT_IN_SECONDS on GAMEDAY_WH that protects the gateway also killed the 1.3M-row pbp COPY. Raising it per-session does not work: Snowflake enforces the lower of the session and warehouse values, so the warehouse ceiling wins (verified — a session set to 1800 still died at 60s). The fix is to keep statements short rather than raise the limit: load anything over 100 MB as one COPY per season shard. The guardrail stays at 60s and the load still completes.

"The file exists" is not a checkpoint. The first full run reported schedules at 570 rows — it had silently reused a two-season parquet left by an earlier run. A cached artifact is only trustworthy if this pipeline recorded producing it, so the cache test is now "file exists AND the checkpoint has a pulled_at", not path.exists().

Check the upstream's shape before batching it. participation has no season column, so splitting a multi-season pull by season silently packed whole batches into one shard; ff_opportunity types season as a string, so an == 2006 filter raised rather than returning empty. Both were invisible until the per-season shard counts were compared against the seasons requested.

UPPER()-ing column names can collide. The fix for lowercase quoted identifiers has a failure mode of its own: cfbfastR's pbp contains 40 column pairs differing only by case (EPA/epa, TFL/tfl) because the upstream renamed columns between eras, so the uppercase template generated duplicate identifiers and CREATE TABLE failed with "Object already exists". Verified across all 40 pairs that no row ever has both variants non-null — they're the same measure from different seasons — so the combine step now coalesces case-variants into one column, losslessly.

An API answers exactly what you asked, and no more. CFBD /plays takes year, week and seasonType as parameters and returns rows containing none of them. Load those rows as-is and week attribution is gone forever. Request parameters are data — stamp them onto the rows at fetch time. (Recoverable here without re-spending 89 calls only because the shard filenames encoded season/type/week.)

Free tiers have shape, not just size. The CFBD free tier's binding constraint isn't the 1,000 calls — a full 2015–2025 pull of recruiting, portal and returning production costs 38. It's that play-by-play is gated to a paid tier entirely, which is why the ingest is bulk-first and the API is a fallback rather than the default.

Name the metric you actually compute. get_qb_epa_leaders filters play_type = 'pass', which includes sacks but excludes scrambles and designed runs. Calling that "EPA per play" in the docstring would have had the model confidently report a passing-efficiency stat as total QB value.

MARTS layer (dbt + one python mart)

transform/ is a dbt project: stg_pbp_nfl staging plus 16 marts — the fourth-down stack (go conversion, FG make, punt nets, three WP surfaces, coach behavior), player marts (mart_qb_season with opponent adjustment, mart_player_usage with late-season splits and age), team marts (mart_team_identity with PROE, mart_team_def_season), history marts (mart_game_team comeback flags, mart_player_game_extremes all-time percentiles), and the college side (mart_cfb_qb_season_early, mart_cfb_team_season, mart_cfb_comps_features with recruiting + draft outcomes). Full build: ~12s, well under the 60s statement ceiling.

One mart is python-built on purpose: MART_CFB_QB_SEASON (2015–2025) reuses the PDR Phase-1 playText parser for API-era QB attribution (transform/python_marts/build_mart_cfb_qb_season.py) — validated code the SQL layer can't replicate.

Roadmap

• [x] dbt: RAW → MARTS models with tests
• [x] CFBD ingest (college) + cross-league draft-class join
• [x] Fourth&Data launch toolset (14 tools, envelope voice, sanity CI)
• [x] Streamable HTTP transport + API keys + audit log table
• [x] Deploy (Fly.io) as public MCP endpoint
• [ ] ESPN live endpoints (reverse-engineered upstream)
• [ ] GitHub Actions scheduled ingest
• [ ] Custom domain (fourthanddata.com) on the Fly endpoint