Weather Whisperer
BigQuery ML for Ride Operations

The ride impact scoring problem has a small feature set (9 input fields per ride × 5 weather variables) and a deterministic scoring function. BigQuery ML handles this as a SQL join + arithmetic, not a trained neural network. Using BigQuery keeps everything in one platform — ingest, store, transform, score, and export — with no model serving infrastructure to maintain. The "ML" here is structured feature engineering and threshold-based scoring, which is exactly the right tool when the physics is well-understood (wind knots vs. a roller coaster's known closure threshold) and explainability is required (the recommendation_reason string must be human-readable for operators).

CAPE, CIN, and Lifted Index from upper-air soundings are leading indicators of convective development — they signal thunderstorm potential 3–6 hours before surface conditions deteriorate. METAR alone would only react to thunderstorms after they're already overhead. The atmospheric component of the ride impact score gives operations staff advance warning: a high CAPE + low CIN sounding at 12Z means the afternoon convective risk is elevated even if current surface conditions look fine. The University of Wyoming archives soundings at 00Z and 12Z — the 12-hour ingestion schedule matches this availability window exactly.

The consuming application is hosted on Azure. A GCP API Gateway in front of BigQuery would add ~$50–100/month in API call costs and a round-trip to GCP on every dashboard load. Instead, the publisher job runs every 15 minutes and writes pre-computed JSON to a CORS-enabled public GCS bucket. Azure web apps fetch the JSON directly — no GCP API calls at read time. GCS standard storage for ~200 objects costs under $1/month. The manifest.json lets the client skip fetching if objects haven't changed since the last poll. This architecture decouples the compute (GCP) from the consumption (Azure) with no persistent connection required.

METAR observations accumulate at ~96 records/day per station (every 15 min). At 10 monitored stations, that's ~960 rows/day, ~350,000 rows/year — small in absolute terms, but the curated views query these tables on every publisher run (every 15 min = 96 times/day). Without partitioning, BigQuery would scan the full table on every query. DAY partitioning on observation_time with clustering on station_id means the curated_station_latest view only scans today's partition (~960 rows) and skips all other stations via clustering — reducing per-query bytes scanned from GB to KB. BigQuery charges $5/TB scanned; partition + clustering drops the query cost to near zero.

Cloud Run Jobs are the right primitive for batch/scheduled workloads that run for seconds to minutes and then exit. Each ingestion job runs for <2 minutes every 15–60 minutes — an always-on container or Cloud Function with a long timeout would be idle >95% of the time. Cloud Run Jobs bill per CPU-second of execution, not per allocation time. Cloud Functions have a 9-minute max timeout — the atmospheric ingestion job parses complex sounding data and could approach this. Cloud Run Jobs have a 1-hour timeout with no per-invocation startup overhead once the container is cached. Cloud Scheduler triggers each job on its own schedule independently.