Case study · August 12, 2026

Your Hospital's Next Board Report Depends on the System Behind It

From fragile pipelines to recoverable analytics

A board member asks why outpatient activity changed. Finance needs to reconcile year-end figures. An operational leader wants to understand occupancy. How confidently can the hospital answer?

For one Toronto specialty hospital, the information needed to answer those questions was spread across a EMR, surveys, operational systems, custom assessment applications, and incident systems. The EMR’s application database was designed to run clinical workflows and aggregating years of activity placed a very different demand on it.

While some ingestion and reporting work was already in place, the code and infrastructure had yet to reach production maturity. Jobs required manual intervention, failures were hard to diagnose, and the hospital had no tested way to rebuild the analytics environment if code, pipelines, or a server were lost.

Strategy set the reporting priorities

The hospital’s Head of Data and Strategy established what information leaders needed, which decisions it had to support, and why a shared warehouse mattered. Momo Analytics turned that direction into the architecture, reporting logic, and operating controls needed to run it.

The first task was to define recurring measures before designing dashboards. Patient counts, service activity, occupancy, and fiscal-period reporting each needed an agreed source, inclusion rules, calculation, and owner.

That precision matters. A patient seen in six different months contributes to six monthly counts and one annual distinct-patient count. Both calculations can be correct. The reporting question determines which calculation belongs in the answer.

One governed path from source to report

Momo Analytics organized the platform into layers with a specific responsibility at each stage. This made transformations traceable and gave every new source a defined route into reporting.

Python jobs ingest approved extracts into SQL Server. dbt turns those records into staging, shared, and reporting models, with dependencies and tests recorded in code. Git preserves each change, while CI/CD provides a controlled route into production.

The pipelines account for records that change after their first load. Recent outpatient activity is refreshed through a rolling window, staged before replacement, and updated inside a database transaction. A full-refresh path supports historical reconciliation.

Business rules remain visible in the models. Occupancy calculations, for example, combine census patient days with documented leave-of-absence adjustments. Patient mapping connects records across successive clinical systems while retaining the original identifiers for investigation.

Why we chose Metabase

The hospital needed a reporting tool it could operate within its own infrastructure and budget. Metabase was a strong fit because its core edition is free and open source, it has an active maintained project, supports LDAP authentication, and provides an official Docker image.

Dockerized deployment also made the reporting application reproducible. Production and development run as separate services with their own PostgreSQL application databases, storage, networks, startup checks, and pinned application versions. Changes and restore procedures can be rehearsed away from production.

Metabase’s application database holds saved questions, dashboards, collections, users, permissions, and settings. Losing it would leave the warehouse records intact while removing much of the work that makes them usable. We migrated that state from the embedded H2 database to PostgreSQL, the production approach recommended in Metabase’s documentation.

Disaster Recovery is a working procedure

We asked ourselves: if the code, pipelines, or reporting services were lost, could the hospital restore analytics within a defined period?

The recovery design covers the assets involved in a real end-to-end rebuild:

• Warehouse backups and retained source extracts.
• Versioned ingestion, transformation, and deployment code.
• Protected credentials and environment configuration.
• PostgreSQL backups of Metabase’s application state.
• Restore scripts, validation checks, and a documented recovery sequence.

The Metabase backup process creates a PostgreSQL dump, validates its catalog, and writes a checksum for later integrity verification. The restore process verifies the backup, recreates the selected application database, restores it, and waits for Metabase to become healthy. We rehearsed that procedure in development before treating it as a production recovery path.

Monitoring now checks pipeline results, Docker services, and the Metabase health endpoint. Alerts report failures and recoveries without sending the same notification repeatedly. Logs provide the detail needed to distinguish a late source feed from a failed transformation or an unavailable reporting service.

The result

The hospital gained a governed path from fragmented source systems to board and operational reporting. Recurring transformations are automated and versioned. Access is controlled. Failures produce evidence an operator can act on. The warehouse and reporting application have defined backup and recovery paths.

The result reflects the partnership behind the work. The Head of Data and Strategy set the hospital’s information priorities. Momo Analytics built the data models, automation, deployment, monitoring, and recovery systems that put those priorities into operation.

Read the related case study: A Hospital Analytics Platform Built to Survive a System Failure.

Talk to Momo Analytics about your hospital’s data warehouse and reporting platform.