Disease Surveillance & Treatment Outcomes Database
Project Overview
This project focuses on designing a Disease Surveillance & Treatment Outcomes Database that supports both transactional healthcare data capture and analytics-ready reporting. The goal was to model real-world disease episodes in a way that ensures data integrity, historical accuracy, and meaningful analytical insight.
The solution follows an end-to-end data architecture, starting with a normalized OLTP relational database for operational workflows and extending into a dimensional data warehouse (star schema) optimized for analytics. Emphasis was placed on defining the correct analytical grain, enforcing business rules at the database level, and enabling population-level analysis across demographics and locations.
Key Features
Analytics-Ready Data Modeling
Designed a normalized OLTP schema with strong referential integrity and a downstream star-schema data warehouse, ensuring consistency between transactional and analytical layers.
Clear Fact & Dimension Design
Defined a precise episode-level grain (one disease episode per person) and built fact and dimension tables that support reliable trend, outcome, and risk analysis.
Historical Tracking (SCD Type 2)
Implemented Slowly Changing Dimensions Type 2 to preserve historical changes in person attributes, enabling accurate longitudinal analysis.
Cloud & Scalability Considerations
Evaluated AWS-based architecture patterns and a NoSQL (MongoDB) document model alternative, highlighting scalability, flexibility, and trade-offs.