Case study  ·  Data engineering  ·  AWS

TMC performance analytics platform

A full-stack AWS data platform that pulls four traffic operations systems into one schema and turns them into per-operator response metrics. Manual reconciliation became automated weekly and monthly reporting.

RoleSolutions architect, sole technologist
ClientMunicipal transportation agency
Data in productionSince October 2024
EngagementConsulting, end-to-end delivery

No way to see how operators were actually performing

A regional traffic management center (TMC) ran four separate platforms for incident management, CCTV, dynamic message signs and work orders, and none of them talked to each other. Supervisors had no single view of operator activity, response times or SLA adherence. Reporting was manual, late and incomplete.

The client wanted to answer a basic question: are operators responding to incidents on time, and with the right tools? Every source used a different schema, format and identity model, so there was no foundation to answer it with.

Four systems that were never meant to talk to each other

01
The same person under different names
One operator could appear as a full name in one system, first initial plus surname in another and different capitalization in a third. Nothing could be measured per operator until a matching layer resolved every variant to one canonical operator record, with an alternate-names registry to catch new variants as they showed up.
02
No shared key between an incident and the response to it
Tying a camera verification (Genetec) or a sign activation (Centracs) back to a specific incident (Rekor) meant building a time-windowed join on camera IDs, sign IDs, operator identity and incident timestamps, across tables with no foreign keys between them.
03
A small AWS budget
Cost shaped the architecture: a Lambda that stops and starts the Aurora cluster so idle compute isn't billed, S3-triggered batch ingestion instead of streaming and materialized views in MySQL so expensive queries aren't recomputed on every read.

Event-driven ingestion into a batch metrics engine

Ingestion
S3 + Lambda ETL
Export files from all four systems land in S3. A Lambda fires on each file, identifies the source by its header pattern, applies that source's transformations and writes validated records to Aurora MySQL.
AWS LambdaS3Pythonpandas
Identity resolution
Canonical operator registry
A multi-pass matching procedure backfills operator relationships across every source table and resolves each name variant to one operator ID. Alternate names are stored and versioned.
MySQL stored proceduresfuzzy matching
Metrics engine
Batch stored procedures
Stored procedures build materialized views in configurable date batches and compute SLA compliance, verification times and sign response metrics per operator per period. Refresh is incremental to keep compute time down.
materialized viewsincremental refresh
Presentation
Secure portal + Looker Studio
An Amplify-hosted web portal with Cognito sign-in and an API Gateway backend gives role-based access. A two-tier Looker Studio layer delivers weekly and monthly operator performance reports to supervisors.
AmplifyCognitoAPI GatewayLooker Studio

Operator KPIs per period and per incident type

120s
Crash confirmation SLA
Tiered deadline: 120 seconds for crashes and 300 for every other incident type.
≤300s
Camera verification
Seconds from incident close to the first camera interaction.
≤300s
Sign activation
Seconds from incident start to the first message sign activation.
MetricGrain
SLA compliance %Per operator, per month and quarter
Response completion %Incidents with both a camera check and a sign activation recorded
Crash response timeAverage response isolated to crash events

From four export files to one metrics layer

01
Source file ingestion
Exports from Rekor, Genetec CCTV, Centracs DMS and CityWorks land in their S3 folders. The Lambda identifies each one by its header signature and maps it to the canonical schema.
02
Operator identity resolution
Exact match first, then alias lookup, then fuzzy partial match. Resolved mappings are stored and reused by every later join.
03
Materialized incident base
The incident base view is rebuilt with operator relationships, camera IDs, sign IDs and the tiered SLA deadlines attached, giving every downstream metric one stable surface to join against.
04
Metrics and view refresh
Stored procedures work through incidents in date batches with time-windowed joins to the camera and sign tables, then refresh the period views the reports read from.
05
Portal and scheduled reports
Authorized users sign in through Cognito and see live metrics in the portal. Weekly and monthly Looker Studio reports generate on their own and go to supervisors.

From no visibility to automated accountability

For operations

  • The first quantified view of each operator's SLA compliance, crash response and tool use
  • Supervisors went from no data to weekly and monthly reports that generate themselves
  • Camera verification and sign activation timed to the second instead of judged by impression
  • Incident history back to October 2024 for trend analysis

Technical delivery

  • Four unrelated source systems unified under one schema without touching any of them
  • Operator identity resolved across every source by a matching layer that keeps learning new variants
  • Stop/start automation and batch ingestion kept the AWS bill small
  • SOP documentation and a version control strategy handed over with the platform

Serverless where it pays, SQL where the logic lives

Compute
AWS Lambda
Storage
S3 + Aurora MySQL
Auth
Cognito
API
API Gateway
Frontend
Amplify
Reporting
Looker Studio
ETL
Python / pandas
DB logic
Stored procedures