1The point
Clinical Trial Risk · independent project · 2026
Open the riskiest trial first

The trial registry and the FDA's adverse-event reports had never been joined. I built the ETL that joins them, on Google Cloud and Snowflake. The joined data scores every running drug trial for its risk of stopping early, with a reason.

1
The ETL

118 GB of FDA safety reports (20.7M of them) and 605k registry records, joined into one row per trial.

2
The check

It ranks a terminated trial above a completed one 71% of the time, on trials it never saw.

3
The use

Reviewing its top 10% finds terminations at 2.1× the rate of picking at random.

2The ETL · Google Cloud + Snowflake

Two streams that had never met, joined as of the day each trial started.

Public sourcestwo datasets, no shared key
Google Cloud · land + processCloud Run · Cloud Storage · Dataproc Serverless
Snowflake · model-ready dataRAW → INT → FEATURES → SERVING
Google Cloud · serveCloud Storage · Cloud Run
118 GB20.7M reports73.1M rows605k studiesraw tables81k trialsParquet66k labelled111k scored
publicopenFDA · FAERSEvery adverse-event report, 2004–26
Cloud Run JobLand24 parallel tasks into Cloud Storage
PySparkFlattenOne row per report × drug
PySparkMatch drugsTyped names → 13,707 substances
publicClinicalTrials.govAACT snapshot + 25 archives
Cloud StorageLandAs published
PySparkClean + labelTerminated vs completed
PySparkAttributes + textDesign, eligibility, text
PySparkPoint-in-time joinAs of each trial's start
External stageRAWOff Cloud Storage
SQLINTSafety + sponsor history
Zero-copy cloneFEATURESOne row per trial, cloned per model
LightGBM + SHAPScoreRisk + reasons
ViewsSERVING111,118 trials
COPY INTOserving/One file, swapped in
FastAPI · Cloud RunLookup + APIAny trial, by NCT ID
3Does it work?

Right 71% of the time, on trials it never saw.

coin flip · 0.50AUC 0.710Review the top 10%:catches 22% of terminations0%0%50%50%100%100%Completed trials wrongly flaggedTerminated trials caught
What the curve shows

Slide down the ranking: each step catches more terminated trials (up) at the cost of flagging more that completed (across). The more the curve bows up-left, the better.

AUC, in plain words

Pick one trial that was terminated and one that completed. 71% of the time the model scores the terminated one higher. A coin flip gets 50%.

Tested honestly

Trained on trials started 2008–14; tested on 10,242 from 2015–16. A model that scored 0.714 was rejected because four of its fields leaked the outcome.

4Open these first

Most running trials sit low and left. The ones to open first stand out.

OPEN FIRST · TOP 10% RISK↑ likely to stall on recruitment↓ at risk for other reasons123456780%20%40%60%0%25%50%Chance the trial ends terminated, any reason →Chance it stops for low enrolment →
#1 · NCT01174121 · Phase 2 · Recruiting

Immunotherapy Using Tumor Infiltrating Lymphocytes for Patients With Metastatic Cancer

National Cancer Institute (NCI) · started 2010-08-26

56%terminated, any reason
19%stops for low enrolment
Top 0.1%of 29,894 running trials
Why it scores here
Registration wordingraises
Accepts healthy volunteers: Noraises
Sponsor's past termination rate: 8.4%raises
Has a data monitoring committee: not givenlowers

Reasons explain the score, not why a trial would stop. Snapshot of model v6, 2026-10-07.

BackCode Live lookup Progress
Scroll or press
01 / 9 · OverviewOne project on one board. Scroll and I'll walk you through it in order.

Stack

Google Cloud: Cloud Run Jobs, Cloud Storage, Dataproc Serverless (PySpark), Cloud Run. Snowflake: external stages, SQL layers, zero-copy clones. LightGBM · SHAP · FastAPI · GitHub Actions

A note on what's shown

The trials are a frozen snapshot of the v6 lookup published 2026-10-07. The live lookup ↗ searches all 111,118 trials; the code ↗ is public.