City and County of San Francisco Elevates Data Management and Sharing to Better Serve the Public

Home to everything from the Golden Gate Bridge to groundbreaking tech innovations, San Francisco is a metropolis with global renown — and serving its nearly 1 million residents is no easy feat. Helping deliver equitable public services to San Franciscans and visitors alike, the city’s Department of Technology (DT) provides innovative, secure technology services to the 52 departments and agencies that comprise the City and County of San Francisco. 

“We serve departments that serve the public,” says Jeff Johnson, data integration lead and manager of the San Francisco Enterprise GIS program. Since each of these departments functions as a company of its own, DT wanted to unify and share data across discrete departments — a goal they’re bringing to fruition with Snowflake. “Our goal is to build a centralized enterprise data platform in Snowflake and make it easier to share data within different city departments,” says Rohit Gupta, chief technology officer for the city and county of San Francisco. “Within a year, we engaged 16 city departments with Snowflake.”

The Department of Technology tailors its approach to the size and needs of each department. “We provide both a platform and a service,” Gupta says. “The bigger departments have resources and a data strategy, whereas the smaller departments rely on the Department of Technology to build solutions for their unique needs.” 

One need that’s consistent across all departments, regardless of size, is efficient data sharing to unlock collaboration. Previously, data was inefficiently and disparately shared through large lists of FTP files and individual ETL connections between SQL servers.

Snowflake has helped departments modernize legacy methods and provide access to data without the need to export, transport and import data. These advances in modernization also extend to San Francisco’s open data portal, DataSF, which provides data transparency to the public. With Snowflake Secure Data Sharing now baked into departments’ data architecture, not as a separate workstream, agencies can make more informed decisions and gain fresh insights that help them ultimately deliver a better resident experience.

San Francisco International Airport (SFO) is one of the largest city and county departments — making its data structures and processes more complex. “The airport has data everywhere across multiple systems and data sources,” says SFO’s principal engineer. “We had been looking for a cloud-based data warehouse solution for some time to unlock innovation, and Snowflake fits well with our integrated information infrastructure.”

One area of focus was the airport’s wide array of third-party applications, which generate and send data to SFO on a near real-time basis. The Transportation Network Companies (TNC) app, for example, tracks rideshare movement within the airport boundaries. When a ridesharing vehicle passes through SFO’s geofence, the TNC app streams “events,” which include data that describes event type like entry, drop-off, pick-up and exit. The event type — along with timestamp, vehicle identification and location — are stored in SFO’s data warehouse. This data is critical for optimizing processes, ensuring compliance, making better decisions and auditing revenue. 

Previously, data engineers at SFO had to process and distribute this data in a traditional relational database management system (RDBMS), which was difficult to manage due to rising data volumes, duplicate data and complex schemas. As a result, query performance was unsatisfactory. SFO used Snowflake’s Snowpipe Streaming connector with Apache Kafka to build a pipeline that streams data from TNC data sources directly into a JSON table in Snowflake Data Cloud, which has significantly more columns and contains eighteen times more data than before. This tight partnership between Snowflake and SFO Information Technology and Telecommunications (ITT) yielded immediate results. 

“We configured the whole architecture in a few sessions and verified that Snowflake is very performant and more efficient for this critical streaming data,” says SFO’s principal engineer. While a simple refresh of data previously took 737.27 seconds with a traditional RDBMS, Snowflake requires only 2.53 seconds — a 300x improvement. Thanks to better query performance, ground transportation officers are more effective in investigating TNC incidents and permit compliance infringements, while employees now use their mobile devices to make data-informed decisions in the field. 

“I see Snowflake directly contributing to SFO’s strategic plan, which seeks to establish a robust enterprise framework to enable technological advancements and new business opportunities,” says SFO’s principal engineer. As part of this effort, the SFO IT team has begun incorporating new Snowflake features, such as Dynamic Tables, to continue fueling innovation for years to come.

The Department of Technology also helps departments like Parks and Recreation gain easier access to their data and increase efficiency. Previously, the department was transitioning from paper-based processes to a digital platform for use cases like overtime requests, purchases and public golf card applications. 

Because Parks and Rec didn’t have a platform to easily use APIs, DT implemented Snowflake as a conduit for integrated APIs to further simplify access to data. With democratized data across the department, the team devotes less time to operational tasks and more time to providing beautiful parks and experiences to San Franciscans.

“We helped Parks and Rec transition from their initial attempt with an SQL server database on-prem to the Snowflake Data Cloud, which eliminated the burden of maintenance and infrastructure,” Johnson says. “Now, their business analysts can easily create their own reports with Snowflake.”

Moving forward, the City and County of San Francisco looks to expand Snowflake usage across more departments to continue modernizing infrastructure and unlocking new insights. The Department of Technology seeks to eventually make Snowflake the central enterprise data sharing platform for all city departments. 

The city has also established an initiative to optimize IT resources in the coming year. The team sees Snowflake as a key way to accomplish this goal while also generating value across departments — both big and small — for a richer experience for San Francsicans.