Pharma / Clinical Trials EDW data model
Data Vault 2.0 raw vault for clinical trials: hubs (study/site/subject/investigator/drug), links (study-site, site-subject, subject-investigator, study-drug), and satellites for study, site, subject (PHI), investigator, and drug history plus a site-subject enrollment effectivity satellite. PHI is classified and masked; hash keys and load metadata are generated by the DDL layer.
Pharmaceutical clinical-trials enterprise data warehouse as a Data Vault 2.0 raw vault: study, site, subject, investigator, and drug hubs; the relationships among them; and descriptive plus effectivity satellites — designed to consolidate trial data from many EDC, IRT/RTSM, CTMS, and lab systems with full historization and the HIPAA, GxP, and 21 CFR Part 11 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
- 97
- columns
- 14
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Subject hub on a de-identified screening id
Subject identity is consolidated across EDC, IRT, and lab systems; anchoring the subject hub on a de-identified screening/randomization id keeps the hub a non-sensitive key while every source attaches its own satellite — limiting PHI sprawl for HIPAA / Part 11.
Investigational product as its own hub and study link
Drug supply is governed independently of the protocol; modeling the investigational product as a hub with a study-drug link lets IRT/RTSM accountability data attach without reworking study structures.
Subject status as an effectivity satellite
Enrollment moves through screening, randomization, and withdrawal over time; an effectivity satellite on the site-subject link records the valid period of each status for as-of reporting and audit.
Attributability drives Data Vault in GxP
load_date + record_source on every row provide the queryable, tamper-evident audit trail 21 CFR Part 11 requires — who/what/when without scraping application logs.
Tables in the Pharma / Clinical Trials 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_study
Grain: one row per unique hub_study business key
Clinical trial / protocol by its protocol identifier.
| Column | Type | Keys |
|---|---|---|
| study_protocol_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_site
Grain: one row per unique hub_site business key
Investigational site by its trial site number.
| Column | Type | Keys |
|---|---|---|
| site_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_subject
Grain: one row per unique hub_subject business key
Trial subject by de-identified screening / randomization id.
| Column | Type | Keys |
|---|---|---|
| subject_screening_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_investigator
Grain: one row per unique hub_investigator business key
Principal/sub investigator by National Provider Identifier.
| Column | Type | Keys |
|---|---|---|
| investigator_npi | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_drug
Grain: one row per unique hub_drug business key
Investigational product / comparator by drug code.
| Column | Type | Keys |
|---|---|---|
| drug_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_study_site
Grain: one row per lnk_study_site relationship
Sites activated for a study.
| Column | Type | Keys |
|---|---|---|
| study_hk | binary | |
| site_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_site_subject
Grain: one row per lnk_site_subject relationship
Subjects enrolled at a site.
| Column | Type | Keys |
|---|---|---|
| site_hk | binary | |
| subject_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_subject_investigator
Grain: one row per lnk_subject_investigator relationship
Investigator responsible for a subject.
| Column | Type | Keys |
|---|---|---|
| subject_hk | binary | |
| investigator_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_study_drug
Grain: one row per lnk_study_drug relationship
Investigational products used in a study.
| Column | Type | Keys |
|---|---|---|
| study_hk | binary | |
| drug_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_study_details
Grain: one row per study_hk per change
Study descriptive history.
| Column | Type | Keys |
|---|---|---|
| study_hk | binary | PK |
| load_date | timestamp | PK |
| study_title | varchar | |
| therapeutic_area | varchar | |
| phase | varchar | |
| indication | varchar | |
| sponsor_name | varchar | |
| study_status | varchar | |
| planned_enrollment | integer | |
| first_subject_in_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_site_details
Grain: one row per site_hk per change
Site descriptive history.
| Column | Type | Keys |
|---|---|---|
| site_hk | binary | PK |
| load_date | timestamp | PK |
| site_name | varchar | |
| institution_name | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| irb_approval_status | varchar | |
| activation_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_subject_details
Grain: one row per subject_hk per change
Subject demographic and randomization history (PHI).
| Column | Type | Keys |
|---|---|---|
| subject_hk | binary | PK |
| load_date | timestamp | PK |
| date_of_birth | date | PII |
| gender | varchar | |
| race | varchar | |
| ethnicity | varchar | |
| initials | varchar | PII |
| treatment_arm | varchar | |
| randomization_date | date | |
| informed_consent_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_investigator_details
Grain: one row per investigator_hk per change
Investigator descriptive history.
| Column | Type | Keys |
|---|---|---|
| investigator_hk | binary | PK |
| load_date | timestamp | PK |
| investigator_name | varchar | PII |
| role | varchar | |
| specialty | varchar | |
| gcp_certified | boolean | |
| license_state | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_drug_details
Grain: one row per drug_hk per change
Investigational product descriptive history.
| Column | Type | Keys |
|---|---|---|
| drug_hk | binary | PK |
| load_date | timestamp | PK |
| drug_name | varchar | |
| drug_class | varchar | |
| dosage_form | varchar | |
| strength | varchar | |
| manufacturer | varchar | |
| is_investigational | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_subject_status
Grain: one row per site-subject enrollment per effectivity change
Effectivity satellite on the site-subject enrollment link.
| Column | Type | Keys |
|---|---|---|
| site_subject_hk | binary | PK |
| load_date | timestamp | PK |
| enrollment_status | varchar | |
| withdrawal_reason | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_meddra
Grain: one row per MedDRA preferred term
| Column | Type | Keys |
|---|---|---|
| meddra_code | varchar | BK |
| preferred_term | varchar | |
| system_organ_class | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_study_site.study_hk → hub_study (many-to-one)
- lnk_study_site.site_hk → hub_site (many-to-one)
- lnk_site_subject.site_hk → hub_site (many-to-one)
- lnk_site_subject.subject_hk → hub_subject (many-to-one)
- lnk_subject_investigator.subject_hk → hub_subject (many-to-one)
- lnk_subject_investigator.investigator_hk → hub_investigator (many-to-one)
- lnk_study_drug.study_hk → hub_study (many-to-one)
- lnk_study_drug.drug_hk → hub_drug (many-to-one)
- sat_study_details.study_hk → hub_study (many-to-one)
- sat_site_details.site_hk → hub_site (many-to-one)
- sat_subject_details.subject_hk → hub_subject (many-to-one)
- sat_investigator_details.investigator_hk → hub_investigator (many-to-one)
- sat_drug_details.drug_hk → hub_drug (many-to-one)
- sat_subject_status.site_subject_hk → lnk_site_subject (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.