Case study · data pipeline

Transfer Tracker

The D1 baseball transfer portal is a web page that changes several times a day and keeps no history. This captures it on a schedule, stores every version, and turns "what changed" into a SQL question — for about a dollar a month.

~$1/mo all in
Python · AWS · DuckDB
shipped: 2026-06

Open the dashboard

The Transfer Portal Intelligence dashboard: 2,593 players in the portal, 70.4% committed, a median of 30 days to commit, and 74 snapshots captured. Below, a stacked daily-commitments chart peaking at 237 on 25 June, net roster flow by conference from the SEC at plus 25 down to the NEC at minus 33, a weekday heatmap, entries by position, and a table of recent commitments to Power Four schools. — Built from the same snapshots the pipeline writes: S3 to Parquet, queried through Athena, rendered to a static page. Nothing is computed when you load it — the numbers are as of the most recent capture, twice a day.

What it captures

A single authenticated page listing every player currently in the portal — where they came from, where they landed, and whether they have landed anywhere yet. The source publishes the current state and nothing else: no API, no history, no record of yesterday. Refresh it and the previous answer is gone.

Almost every interesting question is about change over time. Who committed this week? How long does the median player sit uncommitted? Which conferences are net importers? None of that is answerable from a page that only knows about right now — so the first job is simply to remember.

How it works

Scheduler
EventBridge fires a cron expression a few times a day.
Lambda
Authenticates, pulls the table, parses it, writes both storage zones. Credentials live in SSM; the session is established per run rather than stored.
S3 · raw
The untouched response, timestamped and never edited. This zone is the history.
raw/dt=YYYY-MM-DD/HHMMSS.json
S3 · curated
The same run, parsed and typed into Parquet, partitioned by snapshot date.
curated/snapshots/dt=YYYY-MM-DD/data.parquet
Query
DuckDB locally, Athena in the cloud — both read the Parquet directly, joined at query time against hand-maintained reference tables for conference and school metadata.
Dashboards
Metabase, for click-to-build charts over the query layer.

Decisions that mattered

Store whole snapshots; compute diffs in SQL: The scraper never decides what changed. It writes the full table every run and stops. "What changed" becomes a window function — LAG() over destination, partitioned by player, ordered by date — which means I can ask a new question of two years of history without ever re-scraping. Snapshots are a few thousand rows; keeping all of them is cheaper than being clever.
Files in S3, not a database: The data is tiny and changes slowly. An always-on Postgres would have cost more per month than the entire rest of the system, to serve queries I run a handful of times a week.
Enrich with a join, never in the raw data: Conference, state, and school names are reference tables joined at read time on the team slug — not fields written into the captured record. The capture stays a faithful copy of what the source said; reference data can be corrected later without rewriting history. A LEFT JOIN means an unknown school still shows up, and WHERE conference IS NULL doubles as the maintenance to-do list.
Join on the slug, not the display name: The source already carries a stable identifier. Matching on it avoids an entire category of fuzzy-string bugs that would otherwise surface as quietly wrong conference totals.
No headless browser: The payload is one authenticated request. Driving a real browser would have meant more moving parts, more memory, and more to break, for no additional data.

What it costs

Storage is pennies, the scheduler is negligible, and the function runs comfortably inside the Lambda free tier. The design target was zero to one dollar a month, and the architecture is shaped around that constraint more than any other: it is the reason there is no database, no crawler, and no always-on anything.

Stack

Python · AWS Lambda · EventBridge Scheduler · S3 · Parquet · Athena · DuckDB · Metabase · AWS CDK · GitHub Actions (OIDC)

Infrastructure and function code deploy together as one CDK asset, from main, with no stored AWS keys. Tests and lint gate every pull request, and the parser is tested against a scrubbed fixture so the suite never depends on the live source.