Showcase - Modern Data Stack Accelerator

City and County of San Francisco

Modern data stack enables continuous integration and quality improvements around tracking personal protective equipment (PPE)

At the beginning of the pandemic, the Chief Data Officer (CDO) of San Francisco got a call from the Deputy City Administrator with a problem and a request.

The problem: The logistics unit for the City's pandemic response, under the auspices of the City Administrator, was having difficulty tracking the supply of personal protective equipment (PPE). There were 2 fundamental issues:

1. Data on PPE was captured in 2 different systems. One used by logistics and another used by the San Francisco Department of Public Health. Each organization also had different physical locations for receiving and distributing PPE; and

2. As is often the case in systems built by different people, data was captured at varying levels of granularity and rolled up into different categories and units of measure. This made answering important logistical questions difficult (for example: how many gloves do we have on hand and of those, how many are nitrile gloves, and where are they?)

The request: Could the CDO's team address this issue without requiring to move the logistics division onto a different operational system in the midst of the pandemic?

The answer: Yes, with the right tools! Having completed an assessment of cloud data warhouses just before the pandemic, the CDO and his team procured and set up a Snowflake Data Warehouse, dbt (a tool for transforming data in the warehouse), and Microsoft Power BI. Within 2 weeks there was an end to end system in place that refined raw data automatically, displaying the refined data in dashboards for users. dbt enabled quality monitoring and ongoing improvements to the data as the team learned more, allowing them to move in fast iterations.

This example of a modern data stack automatically moved raw data from two databases into the Snowflake Data Warehouse, refined that data through orchestrated SQL statements, and then displayed it in dashboards developed based on user needs.