CUSTOMER STORIES

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

With Snowflake, San Francisco’s Department of Technology empowers the city’s dozens of departments to maximize data — from helping SFO increase productivity to enabling the Shared Spaces program. 

KEY RESULTS:

50%

Faster data refresh operations with 18x more data

300x

Faster data refresh in Tableau dashboards

Government worker looking at a tablet deep in thought
City and County of San Francisco logo
Industry
Public Sector
Location
San Francisco, CA

Data management that isn’t foggy

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.”

Snowflake is helping us fulfill our vision to centralize data and is quickly becoming our enterprise data platform across the city.”

Rohit Gupta
Chief Technology Officer, Department of Technology, City and County of San Francisco
Story Highlights
  • Faster data streaming for higher productivity: San Francisco International Airport uses Snowflake’s Snowpipe Streaming to stream transportation data with lower latency and better performance to increase productivity and ensure compliance. 
  • Easier data sharing for better collaboration: Snowflake’s Secure Data Sharing expands access to insights while creating a central source for data — without the need for multiple copies of data across the city. 
  • Centralized data accelerates new programs: Snowflake is unifying data sources across a collective of departments to support the Shared Spaces program, which was created in record time during the pandemic and continues to require tight coordination across departments.

Data sharing made easy

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.

City and County of San Francisco quote image

“With Snowflake, data sharing became easy without the need for multiple data copies across the city, so we’re always relying on the true source of data.”

Rohit Gupta
Chief Technology Officer, Department of Technology, City and County of San Francisco

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 elevates innovation to new heights

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.

SFO TNC Data Flow

Parks and Recreation reduces operational burden

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.

Where can the city’s departments get their data easily? In Snowflake. It’s that easy to use. And from there, they can operate independently and pursue what’s important to their departments.”

Jeff Johnson
Data Integration Lead and Manager of the Enterprise GIS Program, Department of Technology, City and County of San Francisco

“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.”

Optimizing IT resources while generating more value

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.

Start your 30-DayFree Trial

Try Snowflake free for 30 days and experience the AI Data Cloud that helps eliminate the complexity, cost and constraints inherent with other solutions.