# Clinical Trials (ClinicalTrials.gov, drug-regulated)

`drug_silver.fact_drug_clinical_trial` · Drugs and formularies · status live

ClinicalTrials.gov records (drug-regulated): NCT id, title, overall status, study type, phase, enrollment, lead sponsor + class, FDA-regulated flag, has_results. The pipeline/evidence context for a drug.

**Grain.** One row per clinical trial, keyed by nct_id (the ClinicalTrials.gov registry ID). Verified: 208,178 distinct nct_id = 208,178 total rows, and every row has is_current=true. nct_id alone is the minimal unique key; the profiler's longer key sets (adding source_id/bronze_object_key/s3_version_id/drug_source_version_id) are a redundant superset since those lineage columns are constant or 1-per-bronze-page.

**How to use it.** Find FDA-regulated, industry-sponsored Phase 3 oncology trials with posted results: SELECT nct_id, brief_title, lead_sponsor_name, first_drug_intervention_name, enrollment_count FROM drug_silver.fact_drug_clinical_trial WHERE primary_phase='PHASE3' AND lead_sponsor_class='INDUSTRY' AND is_fda_regulated_drug=true AND has_results=true AND first_condition LIKE '%Cancer%'. Answers: which sponsors are running late-stage trials for which drugs/indications — useful as drug-pipeline context alongside drug-cost tables.

**Traps.**
- NOT a cost table and NOT drug-code-joinable: there are zero dollar amounts, and first_drug_intervention_name is FREE TEXT (92,976 distinct, case-variant dupes like 'Carboplatin'/'carboplatin', top value 'Placebo') with no RxNorm/NDC/ATC. You cannot reliably join this to drug-pricing tables on a code; only fuzzy name matching is possible.
- data_year is the FIRST-POSTED year (== year(study_first_post_date), verified 100%), NOT the trial start year. A given data_year mixes start_dates spanning decades. Use start_date for when a trial ran, data_year/study_first_post_date for when it was registered.
- first_drug_intervention_name and first_condition keep only the FIRST element of multi-item arrays. A multi-drug or multi-condition trial loses the rest — these columns under-represent the real intervention/indication set and 'Placebo' floating to the top is an ordering artifact.
- Single-snapshot table: every row is is_current=true (no history) and the entire lineage block is constant scaffolding — observed_period/asserted_at = 2026-06-03, source_id='clinicaltrials_gov', vintage_tag='none', cms_vintage_sequence=1, parse_revision=1. sha256_content_hash is the literal string 'pending' (never computed) and drug_source_version_id is ALWAYS NULL. The 'cms_vintage_sequence' name is a misnomer (source is CT.gov, not CMS).
- Nulls/sentinels carry meaning: is_fda_regulated_drug is NULL for 49% (101,289) — do not read NULL as False. start_date contains placeholder sentinels 1900-01-01 and 2099-01-01 (on withdrawn/never-started trials). enrollment_count maxes at 36.5M because OBSERVATIONAL pharmacovigilance studies (e.g. VigiBase) report database sizes, not enrolled patients — filter study_type='INTERVENTIONAL' for true enrollment. primary_phase has BOTH a literal 'NA' (21,409) and SQL NULL (11,037).

| column | type | meaning |
|---|---|---|
| nct_id | string | PRIMARY KEY. ClinicalTrials.gov registry identifier (e.g. 'NCT01202227'). Globally unique, 1 row each (208,178 distinct = row_count). The only true grain key; this is the join key to any external CT.gov data. |
| data_year | int | Partition/snapshot year = year(study_first_post_date) for 100% of rows (verified). The year the trial was FIRST POSTED to the registry, NOT the year it started. Ranges 1999-2026. Never null. Do NOT treat as trial start year (data_year=2010 contains start_dates from 1988 to 2025). |
| brief_title | string | Short human-readable trial title from CT.gov (e.g. 'A Study of RO5190591 (Danoprevir) in Healthy Volunteers'). Free text, never null. |
| overall_status | string | Trial recruitment/lifecycle status, CT.gov enum (13 values). Top: COMPLETED 118,384; UNKNOWN 26,123; RECRUITING 19,740; TERMINATED 18,607; ACTIVE_NOT_RECRUITING; NOT_YET_RECRUITING; WITHDRAWN; ENROLLING_BY_INVITATION; SUSPENDED; NO_LONGER_AVAILABLE; APPROVED_FOR_MARKETING; AVAILABLE; TEMPORARILY_NOT_AVAILABLE. Never null. |
| study_type | string | CT.gov study design type, 3 values: INTERVENTIONAL (197,148 = 95%), OBSERVATIONAL (10,273), EXPANDED_ACCESS (757). Never null. Filter to INTERVENTIONAL when reasoning about real patient enrollment. |
| primary_phase | string | Trial phase, CT.gov enum: PHASE2 65,545; PHASE3 38,674; PHASE1 38,523; PHASE4 28,869; NA (literal 'NA' = not applicable, e.g. device/behavioral) 21,409; EARLY_PHASE1 4,121; plus NULL 11,037. TWO kinds of missing: literal 'NA' AND SQL NULL — handle both. |
| enrollment_count | int | Number of participants. INT, range 0 to 36,580,288, p50=59, p95=735; 7,444 rows = 0. Million-scale values are real but belong to OBSERVATIONAL pharmacovigilance/database studies (e.g. VigiBase queries), not enrolled patients. 3,478 rows null. |
| enrollment_type | string | Whether enrollment_count is ACTUAL (138,428) or ESTIMATED/projected (60,157); NULL for 9,593. ESTIMATED means the trial hadn't completed enrollment at snapshot time. |
| lead_sponsor_name | string | Free-text name of the lead sponsor (22,384 distinct). NOT normalized — can include merger notes (e.g. "Pfizer's Upjohn has merged with Mylan to form Viatris Inc."). Never null in samples. |
| lead_sponsor_class | string | Sponsor category, CT.gov enum (8 values): OTHER (academic/hospital) 112,238; INDUSTRY 82,501; NIH 5,759; OTHER_GOV 3,932; NETWORK 2,326; FED 1,044; INDIV 314; UNKNOWN 64. Use INDUSTRY to isolate pharma-sponsored trials. |
| is_fda_regulated_drug | boolean | Boolean: trial involves an FDA-regulated drug. True 46,769; False 60,120; NULL 101,289 (49% — meaningful gap, field not provided by sponsor / pre-FDAAA trials). Do NOT assume NULL=False. |
| has_results | boolean | Boolean: results have been posted to CT.gov. True 48,414; False 159,764. NO nulls (always populated). Note ~23% have results. |
| first_drug_intervention_name | string | FREE-TEXT name of the FIRST drug intervention only (92,976 distinct). NOT a code — no RxNorm/NDC/ATC. Case-variant duplicates exist ('cisplatin' vs 'Carboplatin' vs 'carboplatin'). Top value is 'Placebo' (2,279), an ordering artifact. 1 row null. This column does NOT cleanly join to drug-pricing tables. |
| first_condition | string | FREE-TEXT name of the FIRST condition/indication only (33,833 distinct, e.g. 'Asthma', 'Metastatic Breast Cancer'). Not coded (no ICD/MeSH). 22 rows null. A trial may target many conditions; only the first is kept. |
| start_date | date | Trial start date (DATE). Real range mostly 1931-2030; contains placeholder sentinels 1900-01-01 and 2099-01-01 (seen on WITHDRAWN trials with no real start). 2,820 null. Future dates are legitimate (estimated starts of not-yet-recruiting trials). |
| primary_completion_date | date | Date of final data collection for the primary outcome (DATE). May be in the future for ongoing trials. |
| completion_date | date | Overall trial completion date (DATE). 8,957 null; 4,444 rows > 2030 (real projected/estimated completions of active trials). |
| study_first_post_date | date | Date the trial was first posted to CT.gov (DATE). Range 1999-09-20 to 2026-06-02. This is the canonical 'when it entered the registry' signal and DRIVES data_year (year(study_first_post_date)==data_year for 100% of rows). |
| last_update_post_date | date | Date the trial record was last updated on CT.gov (DATE). Range 2005-06-24 to 2026-06-02. Freshness indicator for the underlying registry record. |
| observed_period | date | Lineage: the snapshot/ingest date. CONSTANT = 2026-06-03 for every row (single load). Not a trial date — do not confuse with study dates. |
| asserted_at | date | Lineage: when this fact was asserted by the pipeline. CONSTANT = 2026-06-03 (single snapshot). Equal to observed_period here. |
| effective_period_start | date | Iceberg temporal-validity start. Derived: equals start_date for 98.6% of rows (205,358), with a fallback when start_date is null so it is NEVER null. Used by the bitemporal framework, not a CT.gov field. |
| effective_period_end | date | Iceberg temporal-validity end. Derived: equals completion_date for 95.7% (199,221), fallback otherwise; NEVER null. Framework column, not a native CT.gov date. |
| cms_vintage_sequence | int | Lineage vintage counter. CONSTANT = 1 (first/only ingestion). Generic drug-spine scaffolding column; 'cms_' prefix is a misnomer here (source is CT.gov, not CMS). |
| parse_revision | int | Parser revision number. CONSTANT = 1. Bumps only if the bronze JSON is re-parsed with new logic. |
| parser_artifact_hash | string | SHA256 of the parser code/config artifact. Single constant value across all rows (one parser version). Provenance/reproducibility token. |
| source_url | string | Origin API endpoint. CONSTANT = 'https://clinicaltrials.gov/api/v2/studies' for all rows. Identifies the upstream source as CT.gov API v2. |
| sha256_content_hash | string | Intended content hash of the source record. NOT computed — literal placeholder string 'pending' for all 208,178 rows. Do not use for dedup/integrity yet. |
| source_id | string | Logical source identifier. CONSTANT = 'clinicaltrials_gov'. Single-source table. |
| is_current | boolean | Bitemporal current-version flag. TRUE for 100% of rows (208,178). No superseded/historical versions exist in this single-snapshot table, so filtering on is_current is a no-op today (but required for forward-compatibility). |
| bronze_object_key | string | S3 key of the bronze JSON page this row was parsed from, e.g. 'drug/bronze/clinicaltrials_gov/2026-06-03/<hash>-ct_page_00070.json'. 209 distinct = the 209 paginated API response pages (~1000 studies/page). Snapshot date is embedded in the path. |
| s3_version_id | string | S3 object version id of the bronze page. 209 distinct (1 per bronze page). Provenance pointer back to the exact stored object. |
| vintage_tag | string | Human label for the data vintage. CONSTANT = 'none' (no special vintage tagged for this load). |
| drug_source_version_id | string | Intended FK to a drug source-version registry. ALWAYS NULL (0 distinct non-null) in this table. Reserved/unused for the CT.gov source. |

Query it: `query_substrate` with `SELECT ... FROM drug_silver.fact_drug_clinical_trial WHERE ... LIMIT 100`; read `describe_data_source("drug_silver.fact_drug_clinical_trial")` first.