Urban Mobility Analytics with BigQuery and Gemini

Overview

This project builds an end-to-end urban mobility analytics system using NYC taxi and for-hire vehicle data on Google Cloud. It combines BigQuery for scalable data modeling and Vertex AI Gemini for natural language to SQL generation, deployed through a Streamlit application on Cloud Run.

The system enables users to ask questions in plain English and receive SQL-backed insights from a unified dataset covering NYC Yellow Taxi, Green Taxi, and For-Hire Vehicle (FHV) trips.

---

Problem Statement

NYC taxi and mobility data is fragmented across multiple datasets with inconsistent schemas and varying data quality. This project addresses these challenges by:

• Integrating multiple mobility datasets into a unified analytical model
• Enabling scalable analysis of urban transportation patterns
• Supporting natural language querying through an LLM-powered interface
• Evaluating the reliability of LLM-generated SQL in real-world analytics

---

Data Source

All data is stored in Google Cloud Storage:

gs://msca-bdp-data-open/final_project_taxi/

Datasets

• Yellow Taxi — Monthly Parquet files
• Green Taxi — Monthly Parquet files
• FHV (For-Hire Vehicles) — Monthly Parquet files (sparse schema)
• Taxi Zone Lookup — CSV mapping LocationID → Borough, Zone

Coverage: Calendar Year 2025
Access Method: BigQuery external tables (no local downloads required)

---

Project Structure

UrbanMobilityAnalytics/
│
├── .streamlit/
│   └── config.toml
│
├── sql/
│   ├── 01_create_dataset.sql
│   ├── 02_external_tables.sql
│   ├── 03_unify_3_tables.sql
│   ├── 04_Final_Analytic_Table.sql
│   └── 05_Data_Analytics.sql
│
├── app.py
├── Dockerfile
├── pyproject.toml
├── poetry.lock
├── .dockerignore
│
├── README.md
│
└── .gitignore

---

Data Modeling Approach

1. External Tables

• Yellow Taxi
• Green Taxi
• FHV
• Zone Lookup

---

2. Unified Table

• Standardized schema across all trip types
• Added trip_type
• Missing fields mapped to NULL

---

3. Final Analytical Table

Materialized table used by the application with:

• Joined pickup/dropoff zones
• Derived features:
  • pickup_date
  • pickup_hour
  • pickup_month
  • pickup_dayofweek
  • day_type
  • tip_rate

---

Data Quality Decisions

This section documents schema-level cleaning decisions applied before modeling.

---

🚖 Yellow Taxi Data Decisions

Column	Decision	Reason
passenger_count	Keep	Missing due to vendor reporting, not invalid
RatecodeID	Keep	Missing fare type but still useful metadata
store_and_fwd_flag	Optional drop	Not used in analysis
congestion_surcharge	Fill NULL → 0	Not applicable to all rides
airport_fee	Fill NULL → 0	Only applies to airport trips

Summary Decisions

• passenger_count → keep
• RatecodeID → keep
• store_and_fwd_flag → optional drop
• congestion_surcharge → fill 0
• airport_fee → fill 0

---

🚕 Green Taxi Data Decisions

Column	Decision	Reason
ehail_fee	DROP	100% NULL (unused column)
passenger_count	Keep	Valid but incomplete
trip_type	Keep	Some missing due to older records
cbd_congestion_fee	Fill NULL → 0	Rarely applicable
store_and_fwd_flag	Optional drop	Not used in analysis

Summary Decisions

• ehail_fee → drop column
• passenger_count → keep
• trip_type → keep
• cbd_congestion_fee → fill 0
• store_and_fwd_flag → optional drop

---

🚐 FHV Data Decisions

This dataset is the most sparse and requires special handling.

Column	Decision	Reason
PUlocationID	Keep (filter selectively)	~82% NULL, but expected in FHV
DOlocationID	Keep	Same reasoning as pickup
SR_Flag	DROP	100% NULL (unused column)
Affiliated_base_number	Keep	Only 39 nulls

---

Key FHV Insight

• High NULL rate in location fields is expected behavior
• Used only for:
  • volume analysis
  • limited geographic analysis
• Not reliable for full spatial modeling

---

Data Quality Strategy Summary

What was removed

• Fully NULL columns (e.g., ehail_fee, SR_Flag)

What was retained

• Partially missing but meaningful fields
• Metadata columns useful for grouping

What was standardized

• NULL normalization (e.g., fees → 0)
• Schema alignment across taxi types
• Consistent handling of missing geography

---

Key Analytical Areas

• Trip volume comparison across services
• Temporal demand patterns (hour/day/month)
• Geographic distribution (zones & boroughs)
• Fare, distance, and tipping behavior
• Route-level mobility patterns
• Outlier and anomaly detection

---

Streamlit + Gemini Application Design

This section describes the design decisions behind the AI-powered analytics application.

---

1. System Design Philosophy

The application is designed as a lightweight AI analytics layer on top of BigQuery, not a full backend system.

Key principle:

Keep computation in BigQuery, not in the application layer.

---

2. LLM Integration (Gemini via Vertex AI)

• Model: gemini-2.5-flash
• Purpose: natural language → SQL conversion
• Prompt includes:
  • full schema definition
  • strict rules (SELECT-only)
  • column constraints

Design Decision:

The LLM is constrained with explicit schema grounding to reduce hallucinated columns.

---

3. SQL Extraction & Cleaning Layer

The app includes a post-processing step:

• Removes markdown formatting (```sql)
• Extracts valid SQL using regex
• Ensures only SELECT or WITH queries are allowed

Why:

LLMs frequently return:

• explanations
• markdown fences
• invalid SQL formatting

---

4. SQL Validation Layer (Security Control)

Before execution, SQL is validated:

Allowed:

SELECT WITH (CTEs)

Blocked:

- INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, MERGE, etc.

Automatically enforces:

`LIMIT 1000` if missing

Prevents:

• accidental data modification
• expensive full-table scans
• unsafe query execution

---

5. FHV-Aware Query Filtering

Special logic is applied when queries involve:

• fare
• tip
• distance
• revenue

Rule:

FHV data is excluded from geographic queries due to missing fields.

Implementation:

Automatically injects:

WHERE trip_type != 'fhv'

---

6. Query Execution Strategy

• Uses BigQuery Python client
• Applies 10GB query limit safety guard
• Always queries final materialized table
• Avoids runtime joins for performance
• All heavy transformations are precomputed in BigQuery, not in Streamlit.

---

7. Visualization Layer (Auto Charting)

The app dynamically detects chart types:

Logic:

• If time column → line chart
• If categorical column → bar chart
• If lat/lon → map view
• Otherwise → default bar chart

---

8. Performance Considerations

• Streamlit caching:
  • BigQuery client cached (@st.cache_resource)
  • Gemini model cached
• SQL generation cached (@st.cache_data)
• Avoid repeated model calls for identical questions

Benefit:

• Lower latency
• Reduced Vertex AI cost

---

9.Security Design

• No API keys in code
• Uses Cloud Run service account authentication
• IAM-based access control:
  • BigQuery Data Viewer
  • BigQuery Job User
  • Vertex AI User

---

10. Streamlit Application Features

• Natural language → SQL generation
• SQL validation layer
• BigQuery execution
  • Automatic chart rendering
  • Example-driven sidebar UI

---

Key Constraints

• No raw data locally
• Use BigQuery external tables only
• Final app must query a materialized table, not views
• No hardcoded credentials allowed
• App must be live at submission time

---

Run the App

poetry env activate

poetry run streamlit run app.py