Drugs and formularies

Clinical Trials (ClinicalTrials.gov, drug-regulated)

drug_silver.fact_drug_clinical_trial·live·silver·static·Clinical Trials (ClinicalTrials.gov, drug-regulated)

Rows

N/A

Columns

History

study_first_post_date 1999-09-20 (registry coverage; == data_year 1999). The profiler's 'weekly, 1900-01-01' is a sentinel/derived-column artifact, not real periodicity. to study_first_post_date 2026-06-02 (data_year 2026). Single ingest snapshot taken 2026-06-03 (observed_period/asserted_at).

Card

yesgrain, traps, filters

Typed tools

What it is

1A registry of US/global clinical trials sourced from the ClinicalTrials.gov API v2, one row per registered trial (NCT), holding trial metadata (status, phase, enrollment, sponsor, first drug intervention, first condition, key dates).
2In a healthcare COST-data catalog it is a reference/context dataset (the drug R&D and indication pipeline), not a price/rate table — it carries no dollar amounts and no coded drug identifiers.

Grain

Key Grain

Key	Grain
`nct_id`	1One row per clinical trial, keyed by nct_id (the ClinicalTrials.gov registry ID). 2Verified: 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.

nct_id

1One row per clinical trial, keyed by nct_id (the ClinicalTrials.gov registry ID).
2Verified: 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 read it

1Find 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%'.
2Answers: which sponsors are running late-stage trials for which drugs/indications — useful as drug-pipeline context alongside drug-cost tables.

Traps

1NOT 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.
2data_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.
3first_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.
4Single-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).
5Nulls/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).

Columns (34)

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

describe_data_source(source_fqn="drug_silver.fact_drug_clinical_trial")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_drug_clinical_trial WHERE nct_id = '<value>' LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`nct_id`
Join	`how_to_join(source_fqn="drug_silver.fact_drug_clinical_trial")`
Raw door	Athena (read-only via query_substrate): drug_silver.fact_drug_clinical_trial

This page as markdown: /docs-md/data/drug_silver_fact_drug_clinical_trial.md · every page in one file: /llms-full.txt