Building an Urban Mobility Data Platform: Addressing Last-Mile Connectivity in the DMV Region

Photo by Maria Oswalt on Unsplash

The project aimed to bridge last-mile connectivity gaps in the DMV region by building a low-latency, geospatially-aware, multi-source analytics platform that integrates open transportation data, shared mobility trends, and socio-demographic context. This blog provides a step-by-step technical breakdown of how we implemented each data engineering layer, from ingestion through modeling to visualization, with precise descriptions of decisions, edge cases, and low-level configurations.

Architecture

Architecture

The architecture follows a modular, serverless design built entirely on AWS. It supports both batch and real-time ingestion using Lambda functions and Glue Python Shell jobs, with raw data stored in Amazon S3 following a structured, source-partitioned layout. PySpark-based Glue ETL jobs handle normalization, geospatial enrichment, and schema alignment before writing Parquet outputs to a partitioned processed zone. Athena powers analytical querying with support for spatial joins, while QuickSight dashboards provide interactive visualizations. Observability is built in from the ground up using CloudWatch, SNS alerts, and daily cost reports via a custom Lambda scraping AWS Cost Explorer. The design ensures scalability, traceability, and low operational overhead.

Raw Data Ingestion: Source-by-Source Deep Dive

We began by identifying the four main data sources, each requiring a distinct ingestion strategy:

1. Capital Bikeshare Trip Data (CSV)

• Source: Capital Bikeshare Data Portal
• Frequency: Monthly
• Ingestion:
• Lambda function written in Python 3.9, deployed using AWS SAM CLI.
• Script used requests.get() with streaming enabled (stream=True) to avoid memory overload during large file downloads (typically ~300MB).
• Validated CSV header structure using Python’s built-in csv module before uploading to S3.
• Created S3 prefix s3://lastmile/raw/capitalbikeshare/year=YYYY/month=MM/ for time-based partitioning.
• Attached metadata for lineage: Content-MD5, ETag, Ingest-Timestamp, and Source-URL as part of PutObject call.

2. WMATA Ridership Data (CSV)

• Source: WMATA Open Data
• Frequency: Daily
• Ingestion:
• Glue Python Shell job written in Python 3.6.
• Utilized tenacity library for retries with backoff in the presence of 5xx errors.
• Raw files stored with naming convention: station_ridership_YYYYMMDD.csv.
• Generated audit trail logs using logging module and sent to CloudWatch Log Group.

3. Transit App API (JSON)

• Source: Live feeds with stops, predictions, and vehicle locations.
• Frequency: Every 4 hours
• Ingestion:
• Lambda function used urllib3 for persistent connection pooling.
• Parsed nested JSON with schema drift using pydantic to validate and coerce fields.
• Compressed responses with gzip and wrote to S3 using put_object(Body=buffered_stream) to reduce I/O time.
• Partitioned by api_name, ts_hour, and region.

4. U.S. Census Bureau (ACS 5-year Estimates)

• Source: Census Data API
• Frequency: Static
• Ingestion:
• Glue Spark job invoked Python function via mapPartitions to call REST API and process response line-by-line.
• Used .repartition(20) before API call to parallelize queries per FIPS region.
• Wrote processed data to s3://lastmile/raw/census/acs_5yr/ as newline-delimited JSON.

Schema Evolution and Storage in S3

Our goal was to support versioned schema tracking, raw lineage, and query-efficient structure. We implemented a zone-based layout:

s3://lastmile/
 ├── raw/                  # Lineage-preserving, unparsed
 │    ├── source=.../      # One folder per dataset
 │    └── ...
 └── processed/            # Query-optimized outputs
      ├── domain=.../      # One folder per analytical domain
      └── ...

• All processed outputs used parquet with Snappy compression.
• Partition strategy: region, year, month, with enforced data typing.
• Added JSON schema definitions to a separate s3://lastmile/schemas/ folder for cross-checks and downstream tooling.

ETL Logic and Data Transformations (AWS Glue)

Each transformation pipeline was unit-tested locally with PySpark and deployed via Glue 3.0. Example config:

• Job bookmark enabled for deduplication.
• --enable-continuous-cloudwatch-log for detailed step logs.
• Memory: 6 DPUs

Bikeshare Trip Normalization

trip_df = spark.read.option("header", True).csv("s3://lastmile/raw/capitalbikeshare/*.csv")
trip_df = trip_df.withColumn("trip_id", sha2(concat_ws("-", col("start_time"), col("end_time")), 256))
trip_df = trip_df.withColumn("duration_min", round(col("duration") / 60, 2))
trip_df = trip_df.dropna(subset=["start_station", "end_station"])

WMATA Station Aggregation

wmata_df = spark.read.option("header", True).csv("s3://lastmile/raw/wmata/*.csv")
wmata_df = wmata_df.withColumn("weekday_normalized", col("avg_weekday") / col("station_capacity"))

Real-time Stop Coordinates Normalization

@udf(returnType=StringType())
def round_point(lat, lon):
    return f"{round(float(lat), 4)}|{round(float(lon), 4)}"

api_df = api_df.withColumn("location_key", round_point("stop_lat", "stop_lon"))

Data Modeling and Spatial Indexing

To analyze multimodal proximity relationships, we designed a hybrid star schema:

• Fact: fact_trip_metrics, fact_station_load
• Dims: dim_metro, dim_bike_station, dim_nearby_stops, dim_demographics

Spatial joins were executed via Athena SQL:

SELECT a.station_id, b.metro_id
FROM dim_bike_station a
JOIN dim_metro b
  ON ST_Distance(ST_Point(a.lon, a.lat), ST_Point(b.lon, b.lat)) < 1000

We originally attempted H3 resolution 9 for binning, but performance degradation in Athena due to nested JSON geometry forced us back to bounding box.

Orchestration and Monitoring

• Glue workflows with up to 8 steps, linear and branching (e.g., retry if schema mismatch)
• Added TaskToken step to pause pipeline on validation failures for human intervention

Metrics tracked:

• Row input/output delta per job
• Ingestion duration (p90, p95) via custom CloudWatch metric filters
• Data freshness via Athena “latest date” audit query

SNS topics connected to PagerDuty for ingestion or schema drift failures. Lambda function extracted cost-per-query from Cost Explorer and reported as daily Slack digest.

Visualization Layer (QuickSight)

• Dashboards consumed Athena views with predicate pushdown to minimize cost
• Used calculated fields to show proximity gaps: proximity_score = IF(distance < 0.5, 1, 0)
• Implemented RLS using QuickSight namespaces and IAM group mapping

Cost Optimization Summary

• Reduced average Athena query time by 68 percent after adding proper partitions and bucketing
• Transformed Glue job runtimes from 9 minutes to under 3 using broadcast joins and repartition(1) for small files
• Bypassed unnecessary refreshes using timestamp checkpoints and Glue bookmark filters

Analytics and Business Intelligence Layer

The analytics layer was engineered to surface location-specific insights using Athena-backed queries exposed through QuickSight dashboards. Each view was designed with predicate pushdown and partition pruning in mind to optimize for cost and latency. This allowed planners to explore high-cardinality datasets like bike trips and transit predictions interactively without compromising performance.

BI Modeling Strategy

Athena table definitions were manually curated using Glue Crawlers with schema overrides to ensure consistency across partitions and avoid schema drift from semi-structured sources like Transit App APIs.

We created multiple semantic views to abstract raw tables into analysis-ready datasets:

• vw_bikeshare_routes: Most-used station pairs with derived fields like trip duration buckets and time-of-day segmentation.
• vw_station_capacity_trends: Aggregated daily entries normalized against station capacity from WMATA data.
• vw_shared_mobility_density: Cross-tab of vehicle type (bike, e-bike) vs location clusters.
• vw_proximity_scores: Aggregated distances to nearest metro/bus stops with tiered scoring logic.
• vw_demographic_transport: Joins ACS data with mobility datasets by FIPS code and census tract for travel method inference.

Dashboard Design (QuickSight)

QuickSight dashboards used parameterized controls for:

• Time range slicing (e.g., daily, weekly, monthly trends)
• Mode-specific filtering (Metro, Bikeshare, Bus, etc.)
• Geographic scoping (ZIP code, FIPS region, custom bounding box)

We enforced cost control via:

• Disabling autorefresh on visuals with high-latency joins
• Setting refresh schedules to off-peak hours for datasets above 100K rows
• Using SPICE cache for static datasets like census or station master data

Metrics Engineered:

• Proximity Score: Derived using spatial joins between shared mobility coordinates and static stop locations. Helps planners detect under-served pockets.
• Utilization Rate: For Capital Bikeshare: (total trip count) / (station capacity * operational hours)
• Coverage Index: Number of shared mobility assets per 10K population in a ZIP code, calculated using joined ACS estimates.
• First-Mile Access Time: Time delta between user trip start and nearest public transit arrival based on predictions.

Visual Explorations and Interpretations

1. Top Routes Chart

Routes “70” and “X2” consistently lead ridership, peaking over 7,500 weekday riders. These high-throughput corridors represent prime candidates for bike station expansion, especially at terminals lacking mobility options within 800 meters.

2. Bike Station Utilization Map

“Wisconsin Ave & L St.” station ranks highest in throughput with 3,700+ annual rides. Demand trends indicate preference for electric bikes over standard models, validating operational insights from e-bike recharging constraints and repositioning strategies.

3. VA to MD/DC Redistribution Simulation

Currently, several VA stations near metro terminals exhibit low bike turnover. By redistributing 10–15% of idle bike capacity toward high-demand D.C. and MD stops, simulations predict a 12–18% increase in revenue per ride due to improved access alignment.

4. Real-Time Mobility Metrics

Active shared mobility assets across the region: 751

• Maryland continues to top average commute time charts (31.4 mins), underlining the need for improved modal handoffs near suburban metro zones.

5. Demographic Layered Insight

More than 70% of the young working population (18–34) is concentrated in D.C. and inner Maryland counties, correlating with higher shared mobility adoption.

• 350K+ VA residents commute using private vehicles or carpools.
• Over 250K people across D.C., Baltimore City, Montgomery, and Prince George’s do not own a vehicle, indicating high dependency on transit infrastructure.

Final Thoughts

This system serves as a practical blueprint for building civic data infrastructure that is resilient, observable, and efficient. The project supports downstream planners and stakeholders by surfacing real, data-backed gaps in urban mobility. We plan to expand this to include predictive modeling, anomaly detection, and integrate bike rebalancing optimization models.

If you’re working on mobility analytics, civic tech, or building data platforms for public good, I’d love to connect. Whether it’s to discuss infrastructure choices, pipeline observability, or how to make geospatial analytics more accessible, feel free to reach out.

📫 Connect with me on LinkedIn
💻 Explore more of my work at ashishsalunkhe.com