PharmaDataVault

Pharmaceutical Data Warehouse

A Data Vault 2.0 warehouse for clinical trial, manufacturing, and regulatory data, with hub/link/satellite modelling, hash-based change detection, and star-schema marts.

Methods & contributions

• Data Vault 2.0 — MD5 hash keys over cleaned business keys, hashdiff change detection with NVL placeholders, and insert-only satellites.
• Dimensional marts — star schemas built downstream for analytics consumption.
• Data quality — completeness, orphan, and uniqueness checks with correct denominators; DEA schedule and NDC 5-4-2 validation.
• Automation — PL/SQL ETL scheduled by Control-M with UNIX automation scripts.

Data flow

sources → staging (hash keys + hashdiff)
        → hubs / links / satellites → star-schema marts

Technologies