Healthcare EDW data model
Data Vault 2.0 raw vault for healthcare: hubs (patient/encounter/provider/payer/facility), links among them, and satellites for demographics, encounter, provider, payer, and facility history plus a patient-payer coverage effectivity satellite. PHI is classified and masked.
Healthcare enterprise data warehouse as a Data Vault 2.0 raw vault: patient, encounter, provider, payer, and facility hubs; the relationships among them; and descriptive plus effectivity satellites — designed to consolidate patient data from many EHR, lab, and billing systems with full historization and the HIPAA auditability that makes Data Vault a strong fit for clinical EDWs. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 89
- columns
- 14
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Patient hub on the enterprise MRN / MPI
Patient identity is consolidated from many systems; anchoring the patient hub on the master patient index lets every source attach its own satellite without reconciling identity downstream.
Encounter as its own hub
An encounter is a first-class business object referenced by providers, facilities, diagnoses, and charges; modeling it as a hub keeps those relationships as independent links.
PHI isolated in satellites, masked
Keeping demographics in a satellite (not the hub) limits PHI exposure and lets it be masked/classified phi for HIPAA while the hub stays a non-sensitive key.
Tables in the Healthcare EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_patient
Grain: one row per unique hub_patient business key
Patient by medical record number / enterprise master patient index.
| Column | Type | Keys |
|---|---|---|
| patient_mrn | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_encounter
Grain: one row per unique hub_encounter business key
| Column | Type | Keys |
|---|---|---|
| encounter_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_provider
Grain: one row per unique hub_provider business key
Provider by National Provider Identifier.
| Column | Type | Keys |
|---|---|---|
| npi | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_payer
Grain: one row per unique hub_payer business key
| Column | Type | Keys |
|---|---|---|
| payer_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_facility
Grain: one row per unique hub_facility business key
| Column | Type | Keys |
|---|---|---|
| facility_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_patient_encounter
Grain: one row per lnk_patient_encounter relationship
Encounters belonging to a patient.
| Column | Type | Keys |
|---|---|---|
| patient_hk | binary | |
| encounter_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_encounter_provider
Grain: one row per lnk_encounter_provider relationship
Providers participating in an encounter.
| Column | Type | Keys |
|---|---|---|
| encounter_hk | binary | |
| provider_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_encounter_facility
Grain: one row per lnk_encounter_facility relationship
Where an encounter took place.
| Column | Type | Keys |
|---|---|---|
| encounter_hk | binary | |
| facility_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_patient_payer
Grain: one row per lnk_patient_payer relationship
Patient insurance coverage relationship.
| Column | Type | Keys |
|---|---|---|
| patient_hk | binary | |
| payer_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_patient_demographics
Grain: one row per patient_hk per change
Patient demographic history (PHI).
| Column | Type | Keys |
|---|---|---|
| patient_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | |
| ssn | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| race | varchar | |
| ethnicity | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_encounter_details
Grain: one row per encounter_hk per change
Encounter descriptive history.
| Column | Type | Keys |
|---|---|---|
| encounter_hk | binary | PK |
| load_date | timestamp | PK |
| encounter_type | varchar | |
| admit_date | date | |
| discharge_date | date | |
| encounter_status | varchar | |
| drg_code | varchar | |
| primary_diagnosis_code | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_provider_details
Grain: one row per provider_hk per change
Provider descriptive history.
| Column | Type | Keys |
|---|---|---|
| provider_hk | binary | PK |
| load_date | timestamp | PK |
| provider_name | varchar | PII |
| specialty | varchar | |
| provider_type | varchar | |
| network_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_payer_details
Grain: one row per payer_hk per change
Payer descriptive history.
| Column | Type | Keys |
|---|---|---|
| payer_hk | binary | PK |
| load_date | timestamp | PK |
| payer_name | varchar | |
| payer_type | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_facility_details
Grain: one row per facility_hk per change
Facility descriptive history.
| Column | Type | Keys |
|---|---|---|
| facility_hk | binary | PK |
| load_date | timestamp | PK |
| facility_name | varchar | |
| facility_type | varchar | |
| city | varchar | |
| state | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_patient_coverage
Grain: one row per coverage relationship per effectivity change
Effectivity satellite on the patient-payer link.
| Column | Type | Keys |
|---|---|---|
| patient_payer_hk | binary | PK |
| load_date | timestamp | PK |
| member_id | varchar | PII |
| coverage_type | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_diagnosis
Grain: one row per ICD-10 diagnosis code
| Column | Type | Keys |
|---|---|---|
| icd10_code | varchar | BK |
| diagnosis_description | varchar | |
| diagnosis_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_patient_encounter.patient_hk → hub_patient (many-to-one)
- lnk_patient_encounter.encounter_hk → hub_encounter (many-to-one)
- lnk_encounter_provider.encounter_hk → hub_encounter (many-to-one)
- lnk_encounter_provider.provider_hk → hub_provider (many-to-one)
- lnk_encounter_facility.encounter_hk → hub_encounter (many-to-one)
- lnk_encounter_facility.facility_hk → hub_facility (many-to-one)
- lnk_patient_payer.patient_hk → hub_patient (many-to-one)
- lnk_patient_payer.payer_hk → hub_payer (many-to-one)
- sat_patient_demographics.patient_hk → hub_patient (many-to-one)
- sat_encounter_details.encounter_hk → hub_encounter (many-to-one)
- sat_provider_details.provider_hk → hub_provider (many-to-one)
- sat_payer_details.payer_hk → hub_payer (many-to-one)
- sat_facility_details.facility_hk → hub_facility (many-to-one)
- sat_patient_coverage.patient_payer_hk → lnk_patient_payer (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.