problem solved
FAERS (Federal Adverse Event Reporting System) is where doctors input data when someone inhibits unusual symptoms from drug use. This data can be used to detect signal between certain drugs, reactions, and demographics.
FAERS event reporting is known for being messy. Text fields are free-text and inconsistent. Duplicate rows are scattered across different quarters. It's impossible to derive any signal from a messy and inconsistent database.
PharmaWatch solves this by joining together all 90+ quarter's worth of FAERS data, removing all duplicate rows, and normalizing messy rows, such as drug names. It is meant to be used by clinicians, health journalists, and analysts.
ingestion pipeline
download.py
Fetch quarterly ZIP extracts from openFDA
parse.py
Extract raw delimited text files into Parquet
schema.py
Map each era's column names to one canonical schema
clean.py
Canonical renames, null removal, row deduplication within a quarter
merge.py
Union all quarters per table into single Parquet files
dedup.py
Case-version deduplication across all quarters
validate.py
Five invariants that must pass before data leaves the pipeline
load.py
Upload deduped Parquet to Cloudflare R2
architecture
star schema
roadmap
Future updates include the normalization of drug names and reactions using Jev, RxNorm, or other NLP processes. Additionally, a frontend (likely pharma.deanslist.dev) will offer analysts and the like live query access to the warehouse without writing SQL.
A FastAPI service layer, RAG combining warehouse stats with drug label text (pgvector), and ML-based signal detection are under consideration.
♜ stack ♖
languages
ingestion & transformation
infra & storage