Public Health Register Data Platform

Context

A national public-health register needed reliable analytical datasets for operational insight and public-health reporting. The source systems captured patient journeys, clinical requests, activities and correspondence, but were designed around clinical workflows rather than analysis.

I worked within an established Snowflake platform to turn this interconnected operational data into structured, reusable models for downstream Power BI reporting.

Challenge

Each part of a patient’s journey could change independently over time. Case status, clinical activity, correspondence and demographic information all had their own histories, making a simple join to the latest record analytically inaccurate.

The solution needed to preserve those historical relationships, establish a consistent view across source systems and remain accessible to reporting teams while meeting strict healthcare data-governance requirements.

Approach

I developed a dimensional model based on Kimball principles, separating operational information into reusable dimensions and historical fact tables. Transformations were organised across the platform’s established Snowflake and dbt layers.

The model integrated shared person, demographic, geographical and date dimensions, and used incremental MERGE strategies to make transformations efficient and repeatable. Masking and access-control patterns protected sensitive information, while reporting-ready structures supported Power BI DirectQuery consumption.

Technical detail

Fact and dimension structures for case history, clinical requests, activities and correspondence
Slowly Changing Dimension Type 2 history for patient and case attributes
Date-effective joins matching events to the correct historical case version
Incremental dbt models using MERGE strategies
Shared person, demographic, geographical and date dimensions
Masking and access controls for sensitive healthcare information

Outcome

The delivered model provided a consistent historical view of patient pathways, operational activities and clinical workflows. It made complex register information easier to analyse and established reusable, governed datasets for downstream reporting.