Welcome to Braedyn Thompson's portfolio!
See the roles I'm targeting →

Now playing
Berkeley Lab SeismicSoCal BearLM
PROJECT 04 · DATA PLATFORM · CAUSAL INFERENCE · AI AGENT · LIVE

Bay
Ridership

A Bay Area transit analytics platform: eight years of BART and Bay Wheels ridership cleaned with PySpark, modelled with dbt on BigQuery and orchestrated by Dagster; a 14-day forecast for every station; a pre-registered causal study of the eBART extension; an analysis of the 15 stations BART may close; and an AI analyst that answers questions by writing guarded SQL. The site draws every BART train from the timetable.

68M BART trips26M bike tripsDagster · Spark · dbt · BigQueryLangGraph + Gemini
station forecast MAE · entries / day548naive283LightGBM

Overview01

67.8MBART trip rowshourly origin-destination, 2018–2025, 4.2 GB
48%lower forecast errorMAE 283 vs 549, seasonal naive
27 → 50%agent accuracypre-registered A/B, McNemar p = 0.004
+4.2%eBART net riders95% placebo interval +1.6% to +7.1%

How far has BART ridership come back, where is it heading next week, did the newest extension bring new riders, and which stations can the system least afford to close? BayRidership answers all four from one warehouse, and lets anyone ask their own question in plain English.

Public BART and Bay Wheels data lands in BigQuery through a Dagster pipeline that runs itself on a free Oracle VM. PySpark cleans the raw trip files, dbt models them into a star schema with 84 data tests, and a LightGBM model forecasts every station two weeks out. A FastAPI service on Cloud Run serves the numbers and a LangGraph text-to-SQL agent to a hand-built site that is live on this domain. The whole thing runs at $0, inside every service's free tier.

  • 01

    Built a scheduled Dagster pipeline (PySpark + dbt on BigQuery) over 67.8M BART trip rows and 26.0M Bay Wheels trips: an SCD2 station dimension, partitioned and clustered facts, 17 dbt models and 84 data tests, with one definition per KPI feeding the site and the agent.

  • 02

    Forecast daily entries at all 50 stations 14 days ahead with global LightGBM quantile models, cutting MAE 48% against a seasonal-naive baseline (283 vs 549) under walk-forward validation with bootstrap CIs, and calibrated the band with split-conformal prediction; it covers 73% of back-test days against an 80% target.

  • 03

    Shipped a guarded text-to-SQL agent (sqlglot AST checks, a read-only service account, BigQuery dry runs, byte budgets) and tested its retrieval step in a pre-registered A/B test with a power analysis: execution accuracy 27% → 50% on 66 gold-SQL questions, exact McNemar p = 0.004.

  • 04

    Ran a pre-registered difference-in-differences study of the 2018 eBART extension against 34 control stations with placebo inference validated on synthetic data: the line gained about +4.2% while the three old stations lost 23.9%, and a post-hoc trend check says the gain may be trend.

  • 05

    Analysed the 15 BART stations named for possible closure with four stated ridership rules, checked the press's claims against the warehouse, and let visitors close them on the live map.

Not built: the Looker Studio and Power BI reports (a build guide, DAX measures and a check week are ready) and a QLoRA text-to-SQL fine-tune, deferred. The live agent runs Gemini, which hasn't been scored on the full eval set; the 50% is a local Llama 3.1 8B.

Data platform02

01IngestBART OD files, GTFS and Bay Wheels zips → GCS; a sensor re-downloads a year when its Last-Modified changes
02CleanPySpark tags every bad row with a drop reason and writes partitioned Parquet + an audit; Bay Wheels through DuckDB
03Modeldbt: staging → SCD2 snapshot → dims / facts → KPI marts in BigQuery, 84 tests
04Forecastweekly LightGBM retrain writes forecasts + a run log with every metric and CI
05ServeFastAPI on Cloud Run reads the marts with a 100 MB per-query cap

Cleaning, with a reason for every dropped row

The Spark job reads the headerless BART files with an explicit schema and permissive corrupt-record handling, then tags each row as kept or dropped for one reason: malformed, bad date or hour, bad station code, non-positive trips, or a duplicate (the latest source file wins). Kept rows get weekday and US-holiday flags and are written as Parquet partitioned by year and month, replacing only the months present, with an audit of rows in, rows out and drops per reason. Bay Wheels arrives as irregularly named monthly zips in two schemas (Ford GoBike and Lyft); a DuckDB step normalises both into one, with its own drop reasons (bad timestamps, trips over 24 hours, coordinates outside the Bay Area, trips outside their file's month). Raw files are never edited; downloads are skipped when ETag and size match a manifest.

One definition per number

Every KPI (recovery vs 2019, rolling 28-day entries, year-over-year change, peak-hour share, busiest station) is computed once in a dbt mart, and the site and the agent read those columns instead of re-deriving them. Recovery compares each service weekday with the same ISO week of ISO year 2019, paired with its ISO year, so the turn of the year never compares January with December. The hourly fact is incremental: it re-processes the last 35 days plus any date not yet loaded, so a back-filled year is picked up. A singular test checks that every staged trip date reaches it, and the station dimension is an SCD2 snapshot, so renames and openings are tracked over time.

20192025
Average daily BART entries324,958149,335
Service-weekday recovery vs 20191.000.43
Bay Wheels trips2,506,8674,395,116
Bikes per 1,000 BART entries21.180.6

Bikes recovered where trains didn't: in 2025 Bay Wheels ran at 127–210% of its 2019 monthly level, BART at 43–48%. The busiest station on most 2025 days was Embarcadero, and the busiest weekday hour at the top five stations is 5 PM.

Orchestration on a shared free VM

Assets with lineageYearly partitions for BART and Bay Wheels, every dbt model and snapshot as an asset, and the forecast downstream of the station fact.
Sensors and schedulesBART is checked every 6 hours and reloaded when its file changes; dbt rebuilds after every ingest; Bay Wheels reloads on the 7th, a full rebuild runs on the 6th, the forecast retrains every Monday.
Oracle VMDagster daemon and UI as systemd units on the 2-core box it shares with SeismicSoCal, with memory caps and a lower OOM priority so the quake detector keeps priority.
Same models, two warehousesdbt builds on DuckDB locally and BigQuery in production from the same SQL; BigQuery facts are partitioned by date and clustered by station, with a bytes-billed cap per query.

Station forecasts03

One global LightGBM model per quantile (p10, p50, p90) forecasts every station from 1 to 14 days ahead. Each training row is a station, a forecast origin and a horizon, and every feature uses only data up to the origin: the last value, the previous day and 7-day mean, the seasonal-naive value and the last four same weekdays, 28-day volatility, the horizon, the target's weekday, month and holiday flags, and the station itself. Values are scaled by each station's 28-day level, so one model fits Embarcadero and the quietest suburban stop alike.

Back-test error, daily entries per station (lower is better)04008001,200LightGBMSeasonal naiveMAEMAE — LightGBM: 283283MAE — Seasonal naive: 548548RMSERMSE — LightGBM: 643643RMSE — Seasonal naive: 1,2471,247
FigureWalk-forward back-test: 12 origins every 14 days, the first 6 for calibration and the last 6 for testing, pooled over 50 stations and 14 horizons. MAE 283 [216, 365] against 549 [417, 698] for the seasonal-naive baseline; the improvement's 95% bootstrap interval (paired over stations) is 200 to 340 entries a day.

Calibrating the error band

The band is calibrated with rolling split-conformal prediction on conformalised quantile scores: each test fold widens the p10–p90 band using only the folds before it, so a fold never calibrates itself. It held the actual value on 73% of back-test days against an 80% target (raw p10–p90: 70.5%; the Christmas fold alone is 44%). So the site calls it the "calibrated model range", never the "80% range", and its source line says the band is narrower than it should be. That wording is chosen by the API from the run's own coverage numbers, and the label switches to "80% range" only when a back-test earns it.

Every retrain appends a row to a run log: MAE, RMSE, coverage, each with its CI, plus per-fold metrics.

Causal study: eBART04

Did the 2018 eBART extension to Antioch bring new riders to BART, or just move existing ones to the new stations?

A rider who used to drive to Pittsburg/Bay Point and now boards at Antioch leaves the corridor total unchanged; a rider new to BART raises it. So the study measures both the three old stations and the whole Concord → Antioch line summed into one unit, against 34 BART stations far from the line, with station and week fixed effects on log weekly entries.

EstimateEffect95% placebo interval
Whole line, Concord → Antioch+4.2% (about 505 entries / day)+1.6% to +7.1%
Pittsburg/Bay Point, North Concord, Concord−23.9%−25.8% to −21.8%
Per station: Pittsburg/Bay Point · North Concord · Concord−40.1% · −23.1% · −4.3%each excludes 0
Rest of the yellow line (spillover check)+0.5%−1.7% to +3.0%
Whole line, net of the pre-opening trend (post-hoc)+1.0%−2.4% to +4.2%
Two event-study charts: the legacy stations fall about 25 percent after the opening; the corridor rises a few percent
Figure Weekly event study, 4-week bins with 95% placebo bands. The old stations drop 20% in the first month and keep sliding to about −27% after a year. The whole line sits +4–5% above the controls in the first half-year, then +1–3%; only 3 of the 23 post bins have a band that excludes 0.

Pre-registered before any result was seen

Fixed designStation sets, windows, bins, the seed, the verdict rule and the caution rule are in a hashed JSON file committed before any treated-vs-control number was computed.
Placebo inferenceTreatment is reassigned to all 5,984 sets of 3 control stations. The real line is more extreme than all but 3 of them (RI p = 0.0007); the old stations beat every placebo.
Validated on synthetic data firstA 200-simulation study checked interval coverage under null, moved and new scenarios. It caught a too-narrow corridor interval (0.84), fixed by one dated amendment before any real estimate: 0.925 after.
Robustness and falsificationExits instead of entries, 16 suburban-only controls and a monthly 2016–2020 series all agree in sign; a fake opening date in the pre-period finds nothing.
Monthly event study showing the corridor drifting upward against the controls before the opening
Figure Monthly event study from BART's average-weekday workbooks, 2016–2020. Both series rise against the controls through the pre-period, from about −6% in early 2016 to 0 at the reference bin, which is the drift the post-hoc trend adjustment carries forward.

So eBART brought new riders?

By the rule fixed in advance, yes: +4.2% with an interval above zero. But a quarter of the old stations' riders just moved.

And that upward drift before the opening?

Carry it forward and the gain is about +1% with an interval containing zero. The verdict stays, because the rule came first; the write-up says both.

Reported honestly: the gain is largest in the first year and fades to +1–3% after; the trend-adjusted estimate was chosen after the results and is labelled post-hoc; there are only 3 treated stations; the daily files begin in 2018, so the weekly pre-period is 20 weeks; and the window stops in February 2020. Cluster-robust intervals are reported too, but with 3 treated clusters they under-cover on synthetic data (0.79–0.83), so the placebo intervals are the headline.

Ask BayRidership: a guarded text-to-SQL agent05

Ask BayRidership is a LangGraph graph: a router (keyword pre-filters for data-changing requests, prompt injection and secrets; plain data questions skip the model), SQL generation against a schema card of the mart tables with 5 retrieved example queries and one repair retry, a forecast tool, and an answer that cites the SQL it ran. The page streams the steps over server-sent events (thinking → SQL → rows → answer), offers "Show SQL" and "Copy SQL", keeps the last five answers across a reload, and refuses off-topic questions with suggestions. It has been live on Gemini since 2026-10-16, with Ollama locally.

Guardrail layers

sqlglot AST checkExactly one SELECT, a table allowlist (the marts only, never the 68M-row hourly fact), no DDL / DML, file readers or INFORMATION_SCHEMA; LIMIT added or clamped to 200.
Read-only identityThe service account can only read the marts and ml datasets; BigQuery dry-runs every query and refuses anything over 1 GB, then runs it with that as the billing cap and a 20 s job timeout.
Budgets10 questions per IP per minute and a 10 GB daily byte budget; a Gemini quota error ends the stream with a clear message instead of a 500.
Scored by execution66 in-scope questions with gold SQL scored by execution accuracy (the result rows must match), plus 10 off-topic questions that must be refused; an oracle run scores 1.0 by construction.

A/B test: do retrieved examples help?

Execution accuracy by question tag, llama3.1:8b0%25%50%75%100%Arm A: no examplesArm B: 5 retrieved examplesAll 66 questionsAll 66 questions — Arm A: no examples: 27%27%All 66 questions — Arm B: 5 retrieved examples: 50%50%KPI (15)KPI (15) — Arm A: no examples: 27%27%KPI (15) — Arm B: 5 retrieved examples: 53%53%Station (17)Station (17) — Arm A: no examples: 47%47%Station (17) — Arm B: 5 retrieved examples: 47%47%Trend (15)Trend (15) — Arm A: no examples: 7%7%Trend (15) — Arm B: 5 retrieved examples: 40%40%Bikes (11)Bikes (11) — Arm A: no examples: 18%18%Bikes (11) — Arm B: 5 retrieved examples: 36%36%OD flows (6)OD flows (6) — Arm A: no examples: 0%0%OD flows (6) — Arm B: 5 retrieved examples: 67%67%Forecast (6)Forecast (6) — Arm A: no examples: 50%50%Forecast (6) — Arm B: 5 retrieved examples: 50%50%
FigureArm B adds five BM25-retrieved question → gold-SQL pairs to the SQL prompt only (leave-one-out in the eval). It helped most where a question needs a specific mart or join: trend, origin-destination and KPI questions. Station questions were flat: B fixed four and broke four.

The design was written down first: the question set and scorer frozen by hash, temperature 0, exact two-sided McNemar's test on the discordant pairs, a paired bootstrap CI over questions, and a three-part decision rule (p < 0.05, B ahead, refusal accuracy ≥ 0.9). Arm A ran, then a power analysis simulated from its accuracy (minimum detectable improvement +14 points at 80% power), and only then arm B.

Arm AArm B
Execution accuracy (66 questions)18 / 66 = 27%33 / 66 = 50%
Refusal accuracy (10 off-topic)10 / 1010 / 10
Guardrail rejections00
Latency p50 / p952.46 s / 6.71 s1.74 s / 4.71 s
Discordant pairs5 (A right, B wrong)20 (A wrong, B right)

Decision: ship B. +22.7 points, 95% CI +9.1 to +36.4, McNemar p = 0.004, all three conditions met, so five examples became the default. B's prompt is 34% longer but not slower, because fewer queries failed. A --check command recomputes every number in the write-up from the committed summaries and fails if any differs.

Reported honestly: the pool is the eval set's own questions, so for 8 of the 20 questions B fixed, the top example was a near-twin; the lift for unfamiliar questions will be smaller. The test used a local 8B model, and the live Gemini agent hasn't been scored on the full set. A fine-tuned arm C (QLoRA) is deferred, with the harness ready for it.

Which stations can BART afford to close?06

If the Connect Bay Area measure fails on November 3, 2026, BART's adopted plan allows up to 15 station closures from July 2027. The press named the 15 from a February 2026 staff presentation. What does the ridership say about each one?

The list lives in one dbt seed; three marts compute each station's 2025 service-weekday trips, rank from the fewest, recovery vs 2019, change on 2024, the nearest station that would still be open (haversine, assuming all 15 close) and its top three destinations. Four rules, each a point for keeping the station, are stated in one place in the API and shown on the page as text, so anyone can check a verdict by eye against the rounded numbers it displays.

RidersRanked above the 10 least-used stations: BART staff's own cut.
RecoveryAt or above BART's systemwide 43% of 2019 weekday trips.
DistanceThe nearest open station is 3.0 miles or more away in a straight line.
GrowingWeekday trips in 2025 are up on 2024. Added after the first three at the owner's request, and the write-up says so.
StationWeekday trips% of 2019Nearest open stationRules metCase to keep
Oakland International Airport1,08541%San Leandro, 2.9 minoneweak
Pittsburg Center1,12146%Concord, 8.2 mi3strong
North Concord / Martinez1,38234%Concord, 2.1 mi1weak
Warm Springs / South Fremont2,44328%Fremont, 4.3 mi2mixed
Castro Valley2,54945%Hayward, 1.6 mi2mixed
West Dublin / Pleasanton2,57337%Hayward, 8.9 mi2mixed
Orinda2,76147%Lafayette, 3.4 mi3strong
South San Francisco2,83941%Daly City, 3.2 mi2mixed
South Hayward2,95949%Hayward, 2.9 mi2mixed
San Bruno3,11443%SFO Airport, 2.0 mi3strong
Colma3,34038%Daly City, 1.5 mi2mixed
Antioch3,70461%Concord, 13.6 mi4strong
Millbrae3,83031%SFO Airport, 1.1 mi2mixed
Pittsburg / Bay Point4,57258%Concord, 5.6 mi4strong
Dublin / Pleasanton6,24038%Union City, 10.0 mi3strong

Case to keep: strong 6, mixed 7, weak 2. The two stations meeting all four rules are the last two on the Yellow line's eastern end, Antioch and Pittsburg/Bay Point: busier than the bottom 10, recovering better than BART overall, growing, and 13.6 and 5.6 miles from Concord once the whole segment closes. Fourteen of the 15 carried more trips in 2025 than in 2024; the airport station is the exception.

Checking the press against the warehouse

  • 01

    "The 10 lowest-ridership stations." 9 of the first 10 are among the 10 least-used in 2025. San Bruno is 12th from the bottom, and Milpitas, 5th, isn't on the list. The write-up doesn't guess why.

  • 02

    "About 12% of ridership." 12.6% of 2025 service-weekday trips start or end at the first 10 (a trip between two listed stations counts once); 24.1% at all 15, about 42,500 trips a day.

  • 03

    "South San Francisco, 1,538 weekday riders." The warehouse's 2025 average is 1,409 exits, a ratio of 0.92 to BART's January 2026 figure, inside the range set in advance for revisiting the measure.

What the data can't see, and the page says so: what each station costs to run, who rides it, parking and bus links, Caltrain transfers at Millbrae, whether riders would switch or stop riding, and how SFO would be served (it isn't on the list, so it counts as open, which alone decides San Bruno's and Millbrae's Distance rule; the write-up shows both outcomes). Close calls at a rounding edge are reported, not moved. The page takes no position on the measure, cites 13 sources, and will need revisiting after the vote.

The website07

BayRidership desktop page: hero with a departure board, a map of the Bay Area with trains on six BART lines, and a what-if stations card
Screenshot The top of the live page at 1440 px: the hero with its live strip and departure board, the live map with trains on every line at 8:00 AM, and the what-if card that closes BART's listed stations.

How the map draws every train from the timetable

The map needs no API. A build step turns BART's GTFS feed into 88 KB of JSON: 50 stations, 51 track edges with their lines in lane order, 11 service calendars, 34 stop patterns and 3,762 trips in minutes. For the current Pacific time the browser picks the services running that day (weekday rules plus holiday exceptions, including the previous day's after-midnight trips) and interpolates each trip between its departure and next arrival, dwelling at stations. Shared track is drawn as parallel lanes; trains are canvas pills in their line's lane; labels are placed greedily and dropped when they would collide. There is a Now button, a time scrubber, 10× and 60× replay, a list view, a legend that filters lines, and tapping a station loads its forecast without scrolling. Positions are scheduled, not live, and the page says so.

What if stations close?Presets close the first 10 or all 15 listed stations, or one at a time. Closed stations lose their marker; the same trips keep their timetable at every open station and run through the closed ones without stopping, re-timed across the hops.
Departure boardThe six soonest arrivals anywhere, published by the map every 10 seconds, with a live strip counting trains moving now and recovery vs 2019.
Trends and forecastKPI tiles with ⓘ definitions, ridership since 2019 and bikes vs trains as a share of 2019 with crosshair readouts, and a station picker (combobox, or tap the map) for the 14-day forecast and its band.
Findings and closuresThe eBART verdict with hedged wording per outcome and two event-study charts, every number read from the study's JSON; one card per at-risk station with a verdict pill, never green or red.
BayRidership on a phone: the BART map with trains and the time controls below it
Screenshot The live map band at 390 px: a 1:1 map, nine hub labels, controls stacked under it. Sizing is fluid from 320 px phones to 2560 px screens, and the whole page has a dark mode.

The site is vanilla ES modules with no framework, design tokens that mirror a versioned design document, and a style guide page that renders every token and component in both themes. axe finds 0 contrast violations in light and dark mode at 390 and 1440 px; Lighthouse mobile scores 98 / 100 / 100 (performance / accessibility / best practices, LCP 2.3 s, CLS 0). Reduced-motion users get positions every 30 s with no animation; a polite live region announces the train count once a minute; every control is 44 px on touch.

Shipping08

01Push to mainGitHub Actions: ruff, pytest, Spark, browser, container and Terraform jobs
02Buildmulti-stage, non-root image, 119 MB compressed, smoke-tested before it is pushed
03Deploykeyless: Workload Identity Federation pinned to this repo's main branch
04Cloud Runus-west1, 0–2 instances, 512 MiB, scales to zero
05Pagesthe static site is exported into this domain and calls the API across origins

Everything in GCP is Terraform: BigQuery datasets, the GCS bucket, Artifact Registry, Cloud Run, the service accounts and their least-privilege roles, the Workload Identity pool, and $1 and $5 budget alerts as code. The agent's Gemini key lives in Secret Manager and reaches the service only when the agent is enabled. CI is the only thing that can deploy: no service-account keys in GitHub, and the OIDC condition accepts only this repository on main.

UsersRequests / sForecast p95KPIs p95Ask p95
11.262 ms540 ms870 ms
1011.88 ms11 ms58 ms
2529.714 ms22 ms140 ms

Locust against the API on this PC with a fixture warehouse and the LLM mocked (so it measures the code, not Gemini): 0 failures, and the 1-user p95s are the cold cache. On Cloud Run the first request after scaling to zero takes about 4.4 s to the first byte and warm requests 0.09–0.17 s; the map shows immediately because it needs no API. Tests: 179 pytest, 52 node and 75 Playwright tests (axe contrast in both themes), 84 dbt data tests, container smoke tests, a compose smoke test, and reproducibility checks that recompute the A/B, the causal study and the trend adjustment from committed inputs. Expected cost: $0.

make up starts the site, the API and the agent on an Ollama container with one command.