The problem
Three departments — finance, operations, clinical — were each maintaining their own spreadsheets, refreshed manually from a vendor portal.
A single board-level number could differ by ±9% between reports. Reconciliation took eleven days at month-end and burned a full FTE in clinical operations.
What we built
A warehouse-native stack: a thin BigQuery dataset, dbt models with tested transformations, and a semantic layer that defines every metric exactly once.
A scheduled Cloud Run job ingests the vendor portal nightly. dbt tests catch breakages before downstream dashboards refresh. Three role-based dashboards replaced the spreadsheets.
The hand-over
We trained two analysts to add new metrics in dbt and Looker. The runbook covers cost monitoring, contract test failures, and how to deprecate a metric.
Six months on, they have shipped twelve new metrics without us being involved.