Problem
A regional network of 12 hospitals ran its reporting on spreadsheets. Every morning, an analyst pulled exports from 6 systems, pasted them into a master workbook, and emailed a PDF to leadership. The numbers rarely matched between departments. When someone asked why revenue looked different in two reports, no one could answer without a half-day investigation.
The finance team could not trust its own dashboards. The analytics team spent most of its week copying data instead of answering questions. Leadership made calls on figures that were already a day old.
Approach
We started with the people doing the morning export. We watched the process, listed every source system, and mapped which fields fed which report. That gave us a clear picture of where the numbers diverged.
Then we designed a warehouse as the single place those numbers live. We loaded each source once, modeled it with tested logic, and defined every metric in one spot. We shipped the first governed dashboard in week 4 so the team saw value before the full build was done.
What we built
- A BigQuery warehouse with role-based access for finance and operations
- Ingestion pipelines from 6 source systems, with monitoring and morning alerts
- A dbt project with 80 tested, documented models
- A metrics layer that defines revenue, census, and utilization once
- Runbooks for backfills, on-call, and adding a new source
Outcome
The morning export is gone. Dashboards now refresh on their own before staff arrive. When a number looks off, the team traces it to a model in minutes, not hours.
- Reporting prep dropped from 4 hours a day to 0
- Numbers now match across every department report
- The analytics team added 3 new dashboards without our help in the first month
- Data is current as of the prior night, every night
Client quote
The daily spreadsheet was a tax we paid for years. Now the numbers are just there in the morning, and they agree with each other. Our analysts finally spend their time on questions instead of copy and paste.
VP of Analytics, Regional healthcare network