Turning Dublin bus telemetry into something you can query
Notes on shaping raw vehicle telemetry into partitioned, queryable tables with Apache Spark — and the small decisions that made the pipeline predictable.
Why the raw feed is the hard part
Vehicle telemetry arrives noisy: duplicate pings, clock drift between devices, gaps when a bus goes under a bridge. Before any analysis is credible, the ingest layer has to make hard promises about what a row means.
I settled on one rule early — every record is keyed by vehicle and event time, and late arrivals overwrite rather than append. That single decision removed a whole category of double-counting bugs later on.
Partitioning for the questions you actually ask
Most queries were 'what happened on this route, on this day'. Partitioning by date first and route second cut scan volume dramatically, and made the slow queries obvious rather than mysterious.
What I'd do differently
I would invest in data quality checks on day one instead of week three. Row counts per partition, null rates on the key columns, and a simple freshness check catch almost everything before it reaches a dashboard.