Content
Summary
Data warehouses and lakes serve complementary roles in healthcare analytics, with warehouses optimizing for structured reporting through schema-on-write and lakes supporting advanced analytics via schema-on-read. Dimensional modeling organizes data into facts and dimensions, utilizing star, snowflake, or galaxy schemas to balance query performance with data integrity. Architectural strategies like Kimball's bottom-up and Inmon's top-down approaches determine how these structures are integrated, while metadata ensures governance and trust.
Intuition
Think of a data warehouse as a meticulously organized library where every book (data point) has a specific shelf and category before it is stored, ensuring that finding a specific report is fast and consistent. This is the 'schema-on-write' approach. In contrast, a data lake is like a vast, open field where raw materials (data) are dumped in their natural state, and you only decide how to use them—whether to build a house or a bridge—when you are ready to analyze them. This 'schema-on-read' approach offers flexibility for complex tasks like machine learning. To make sense of the warehouse, we use dimensional modeling, which separates measurable events (Facts) from the context describing them (Dimensions). Whether you build your library department by department (Kimball) or construct the entire central building first (Inmon), the goal is the same: to turn raw information into reliable, actionable insights for patient care and hospital operations.
Derivation
- Data warehouses are defined by four characteristics: subject-oriented, integrated, non-volatile, and time-variant, optimizing for reporting.
- Data lakes store raw, native data (structured, semi-structured, unstructured) to support advanced analytics and machine learning.
- Warehouses enforce 'schema-on-write' for consistency, while lakes use 'schema-on-read' for flexibility.
- ETL processes integrate fragmented source data into these repositories.
- Dimensional modeling structures data into Facts (measures) and Dimensions (attributes).
- Star schemas offer performance via denormalized dimensions; Snowflake schemas offer integrity via normalized dimensions.
- Galaxy schemas connect multiple fact tables through shared dimensions for enterprise-wide analytics.
- Kimball's bottom-up approach builds data marts first; Inmon's top-down approach builds a central warehouse first.
Worked example
A mid-sized hospital system wants to analyze cardiology outcomes and financial performance. They start with a cardiology data mart using a star schema for speed. Later, they need to integrate financial claims data and improve patient data consistency. How should they evolve their schema and architecture?
- Start with a Kimball-style bottom-up approach, creating a cardiology data mart.
- Use a Star Schema with a central 'CardiologyEncounters' fact table and denormalized dimensions (Patient, Cardiologist, ProcedureCode) for fast reporting.
- Expand to a Galaxy Schema by adding a 'Claims' fact table that shares dimensions with the encounter table, enabling cross-domain analysis.
- Normalize the Patient dimension into a Snowflake structure (separating Address and InsuranceInfo) to reduce redundancy and ensure consistent updates across clinical and financial reports.
- Implement metadata to define measures like 'ProcedureCost' to ensure a single source of truth.
Answer The system evolves from a simple Star Schema in a Kimball-style data mart to a Galaxy Schema with Snowflake-normalized dimensions, supported by robust metadata governance.
Common mistakes
- Over-normalizing data, which leads to excessive joins and poor query performance.
- Neglecting metadata, resulting in data silos and inconsistent interpretations of data.
- Choosing a schema without considering the trade-off between query speed and data integrity.
- Confusing the Kimball bottom-up approach with the Inmon top-down approach.