San Francisco Municipal Transportation Agency boosts citizen services with Microsoft Azure

Making San Francisco accessible to all

The SFMTA operates and manages all ground transportation in the city: buses, trains, light rail, cable cars, taxis, and emerging forms of transportation like bikes and scooters. Because the SFMTA operates as a single entity, the huge amounts of data that might normally be siloed—bus routes, parking frequency, pedestrian density, and more—flow to just one organization.

To put the data scale in perspective, the Muni railway alone generates 5 million rows a day. “You can imagine every time someone swipes a credit card or drops coins in a parking meter, every time a bus arrives at a stop or pulls away, every time a taxi trip is taken, that’s generating data,” said Donovan Corliss, Middleware and GIS Manager at the SFMTA.

And in that data, the SFMTA saw huge opportunity to provide the residents of San Francisco with better ground transport. The agency envisioned a big data solution that would improve services and preserve its budget by reducing operating costs and optimizing revenue based on demand.

Centralizing the sprawling, dated architecture

In the past, the team relied on traditional relational databases and middleware, which were difficult to manage on-premises and presented a number of major challenges. Old-school, point-to-point solutions were labor intensive. A comma-separated values (CSV) file from a vendor would be pulled into Excel, sliced and diced, and then passed up the chain. “The scale was not ideal, and it took up a lot of time,” says Corliss. In-house middleware platforms require patching, maintenance, and infrastructure work, which means expensive engineering hours. Scaling and upgrading on-premises were big asks. As a result, answering important questions about how to get people from A to B became slow and resource intensive.

It was clear that the team needed a centralized way to manage data. If the team could build a unified system, then it could spend less time collecting and managing data. And more time analyzing and gaining insights to better help the people of San Francisco traverse the city while keeping the department under budget.  

Going serverless leads to streamlined processes

The SFMTA knew that a serverless data solution would best suit the agency’s needs, allowing it to combine all its data sources into a common data entity and form one model from ingestion, streamlining its entire process and making it lightning fast to spin up an analysis on collected data. The data and middleware team partnered with Microsoft to build a comprehensive cloud solution centered on Azure Synapse Analytics.

SFMTA uses Azure Synapse Analytics for warehousing and orchestration of the agency’s massive amount of data, easily handling Muni’s daily rows, plus data from every taxi, bus, and parking meter in the city. That data is housed in Azure Data Lake Storage, before it’s cataloged with Azure Purview Preview.

The SFMTA will use Azure Event Hubs and Azure Logic Apps to build a unified platform that gathers data from the various vendors who manage parking meters, bus routes, and special events.

Finally, adding Power BI in an Azure Synapse Analytics workspace creates streamlined reporting for the team.

Using this architecture, the SFMTA effectively and efficiently makes data-driven business decisions to better serve the community. This is the core of the SFMTA leadership’s commitment to the agency’s digital transformation.

Providing insights leads to more efficiencies

Now, SFMTA team members can spend their time working on what they do best. “It’s really valuable for us to have platforms that are available on Azure because it allows us to not have to focus so much on some of the infrastructure work and really just focus on working with data directly,” says Corliss.

And they no longer have to worry about downtime. “I’m just not worried about Azure going down. I know the services are there and replicated, with really high availability,” says Corliss.

The team can scale with ease to tackle those previously expensive and challenging problems. “On the compute side of things, being able to scale up and down is going to be a big benefit for us,” says Cheong-Tseng Eng, Data Engineering/Data Services Manager for the SFMTA.

The SFMTA’s unified system is already creating new efficiencies. One use case involves managing street parking. “The goal there was to minimize the amount of time people spend parking because too many cars circle around, slowing the traffic throughout the city. We want a certain percentage of parking spaces always available. And the only way to do that is to have a price point that leads to the correct amount of turnover. You have to collect a lot of information about parking and use rate adjustments to then say, ‘Okay, here’s what the price is going to be,’ in order to get the turnover. Doing this with Azure really simplifies the process,” says Eng.

Moving the city forward through unified data

Looking ahead, the SFMTA plans to build a comprehensive view of the city from every possible data point, bringing together information that was previously siloed. “This will allow us to ask, ‘How much traffic is going down the street?’ Regardless of whether it’s someone walking, someone on a scooter, or a bus, we’ll be able to capture that and get a 360-view of the street,” says Eng. These invaluable insights allow the SFMTA to continue achieving its mission to provide accessible transit for the people of San Francisco today—and many years in the future.