Analisis Kesenjangan Supply-Demand Taksi NYC

#Problem

Where is a city losing revenue to fleet misallocation, and what does that gap cost? An independent geospatial data engineering study on a full year of NYC TLC trip records (35M+ trips, 185 zones).

#Architecture

• Seven components: GCS → Dataproc/PySpark → BigQuery → dbt → PostGIS on Supabase → Streamlit, orchestrated in Kestra on Docker Compose.
• Infrastructure provisioned with Terraform and service-account-scoped IAM.
• A full year of TLC Parquet processed into 35M validated records on ephemeral Dataproc clusters, handling a mid-year schema change through per-file read, cast and union; H3 resolution 8 enrichment.
• dbt staging, intermediate and mart layers with data tests as regression guardrails.

#Key decisions

• Rate-of-totals instead of average-of-ratios: the naive aggregation would have understated the finding by 30%.
• PostGIS validation: ST_Within borough checks (181/185 cells matched, surfacing the Marble Hill anomaly) and ST_Buffer transit-hub proximity ($11.4M commuter-exit gap near Penn Station).
• Road-segment falsification: a join over 72,005 OpenStreetMap drivable segments showed undersupplied zones are not worse served by roads, so dispatch and positioning, not physical access, is the bottleneck.

#Results

• $81.6M in unmet demand quantified; top gaps JFK $26.3M (79% unfulfilled) and LaGuardia $15.0M.
• 56 architecture decisions documented with explicit trade-offs and rejected alternatives; the limits of the supply proxy are stated, not hidden.

#Links

• Live dashboard
• GitHub repo