Gameday Gateway

An MCP gateway serving NFL/CFB analytics from Snowflake through curated, read-only tools. Scale model of the enterprise agentic-data-platform pattern: staged bulk ingest, RBAC-separated loader/reader roles, cost-governed compute, no raw SQL exposed to the model.

Architecture (v0.1)

nflreadpy ──parquet──▶ @INGEST_STAGE ──COPY INTO──▶ GAMEDAY.RAW  (loader role)
                                                        │
Claude ◀──stdio──▶ FastMCP server ──read-only role──────┘
                     └── TTL cache (warehouse stays suspended)

Related MCP server: nfl-mcp

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.

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?"

Tools

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.

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.

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.

Roadmap

• dbt: RAW → MARTS models with tests

• CFBD ingest (college) + cross-league draft-class join

• ESPN live endpoints (reverse-engineered upstream)

• Streamable HTTP transport + API keys + audit log table

• GitHub Actions scheduled ingest

• Deploy (Fly.io) as public MCP endpoint