# FDA Drug Applications (NDA/ANDA/BLA dimension)

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

FDA drug applications — application type (NDA/ANDA/BLA), sponsor, product and submission counts, earliest/latest submission dates + status. The application-level spine linking Orange Book, labels, and approvals.

**Grain.** One row per FDA application_number per weekly snapshot. On the current slice (is_current=true) it is exactly one row per application_number: 29,101 rows = 29,101 distinct application_numbers (verified equal). The full table holds 119,326 rows = five weekly snapshots stacked, so the table-wide unique key is (application_number, vintage_tag); (application_number, vintage_tag) distinct count = 119,326 = total rows.

**How to use it.** SELECT application_type, COUNT(*) AS apps, SUM(products_count) AS products FROM drug_silver.dim_drug_application WHERE is_current = true GROUP BY application_type — returns the current count of FDA applications and total drug products by pathway (ANDA generic / NDA brand / BLA biologic). To resolve a specific drug's approval and sponsor, filter WHERE is_current = true AND application_number = 'NDA050565'. Always include WHERE is_current = true; join out to NDC/product tables for cost (ASP/NADAC) rather than expecting prices here (this table has none).

**Traps.**
- is_current is mandatory for the live view. The table stacks 5 weekly snapshots (119,326 rows); application_number is only unique WITHIN a snapshot. Querying without WHERE is_current = true returns ~4x duplicated applications. Only the 2026-05-28 vintage is current (29,101 rows).
- effective_period_end is a trap: it is ALWAYS 2099-12-31, even for superseded/historical rows. SCD-2 supersession is driven ONLY by the is_current flag, never by closing effective_period_end. Do not filter on effective_period_end to find the live record.
- drug_source_version_id is 100% NULL for every row despite its key-like name and presence in the profiler's candidate-key list. It is unpopulated; never join or filter on it.
- NULL latest_submission_status carries meaning: it (plus NULL dates and submissions_count=0) marks 2,706 current applications that exist in Drugs@FDA but have no submission history in the feed. Treat NULL status as 'no submissions', not 'unknown approval'. Status vocabulary is only AP/TA/NULL — not a complete FDA status set.
- application_type is redundant with the application_number prefix (verified perfect match), and sponsor_name is raw un-normalized FDA text (e.g. MERCK vs MERCK SHARP DOHME), so do not treat sponsor_name as a clean manufacturer key. This is an application-level roll-up: products_count/submissions_count are aggregates, so the table is one-row-per-application, not one-row-per-product or per-submission.
- latest_submission_date can be future-dated relative to the snapshot (max 2026-08-19 vs snapshot 2026-05-28) because FDA records scheduled action dates; do not assume submission dates are <= the observed_period.
- observed_period, asserted_at, effective_period_start, and vintage_tag are four redundant stamps of the same snapshot date (all equal per vintage). The 2026-05-16 snapshot is a partial load (only 3,000 rows vs ~29,071 for full snapshots) — exclude it from historical comparisons.

| column | type | meaning |
|---|---|---|
| application_number | string | FDA application identifier and the primary business key, e.g. NDA000734, ANDA060362, BLA125514. The leading letters encode the application type and ALWAYS equal application_type (verified: ANDA->ANDA, NDA->NDA, BLA->BLA, no mismatches). Unique per snapshot. |
| application_type | string | FDA application pathway, exactly 3 values: ANDA (abbreviated/generic, 22,778 current rows), NDA (new drug/brand, 5,850), BLA (biologics license, 473). Fully derivable from the application_number prefix; redundant. |
| sponsor_name | string | The company that holds/submitted the application (manufacturer or marketer), e.g. LILLY, MERCK SHARP DOHME, ASPEN GLOBAL INC. Free-text FDA label name, NOT normalized (e.g. MERCK vs MERCK SHARP DOHME both appear). Never null on the current slice. |
| products_count | int | Count of distinct drug products (strength/form SKUs) under this application. Range 0-53 on current slice; 359 current rows have 0 products. A roll-up, so this table is one-per-application, NOT one-per-product. |
| submissions_count | int | Count of FDA regulatory submissions (original + supplements) recorded for the application. Range 0-229; 2,706 current rows have 0 submissions (no submission history in the feed). |
| earliest_submission_date | date | Date of the first submission for the application. Min observed 1939-02-09 (the Drugs@FDA dataset epoch). NULL when the application has no submission records (~2,708 current rows). |
| latest_submission_date | date | Date of the most recent submission. Can be slightly future-dated relative to the snapshot (max 2026-08-19 vs snapshot 2026-05-28) because FDA records scheduled/post-dated action dates. NULL when no submissions (~2,708 current rows). |
| latest_submission_status | string | Status code of the latest submission. Vocabulary is only 3 values: AP = Approved (dominant, 25,291 current rows), TA = Tentative Approval (1,101), and NULL = application has no submissions (2,709). Not a full FDA status list — only AP/TA/NULL appear. |
| observed_period | date | Snapshot date the data was observed/extracted. Identical to asserted_at and effective_period_start within each snapshot (one distinct value per vintage). 5 values: 2026-05-16, 05-17, 05-18, 05-21, 05-28. |
| asserted_at | date | Date the snapshot was asserted into silver. Redundant with observed_period and effective_period_start (all three equal per snapshot). |
| effective_period_start | date | SCD-2 effective-from date; the profiler's chosen time column. Equals observed_period/asserted_at. Earliest 2026-05-16, latest 2026-05-28, ~3-day cadence (weekly business cadence with mid-week loads). |
| effective_period_end | date | SCD-2 effective-to date, but ALWAYS the open-ended sentinel 2099-12-31 for every row including superseded ones. The period is NEVER closed on supersession, so do not use this to find the live row — use is_current. |
| cms_vintage_sequence | int | Vintage sequence integer; constant value 1 across all current rows. Provenance scaffolding, not yet used to differentiate vintages here. |
| parse_revision | int | Parser revision number; constant 1. Bumps if the parse logic is re-run with new code. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced the row (provenance for which code version parsed it). |
| source_url | string | Origin URL; always https://api.fda.gov/drug/drugsfda.json (the openFDA Drugs@FDA API). Confirms a single upstream source. |
| sha256_content_hash | string | Content hash of the source extract. Single distinct value per current vintage (one logical extract per snapshot). |
| source_id | string | Source system id; always 'drugsfda'. Single source. |
| is_current | boolean | Boolean SCD-2 flag marking the live snapshot. TRUE only for the latest vintage (drugsfda_2026-05-28 = 29,101 rows); FALSE for all 90,225 rows of the 4 prior snapshots. This is the ONLY reliable way to select the current view. |
| bronze_object_key | string | S3 key of the bronze JSON batch the row came from. 15 distinct keys on the current slice (the openFDA JSON was sharded into ~15 bronze batch files). |
| s3_version_id | string | S3 object version id of the bronze file, for exact-byte provenance. |
| vintage_tag | string | Human-readable snapshot label, drugsfda_<YYYY-MM-DD>, e.g. drugsfda_2026-05-28. 1:1 with observed_period and the snapshot; the partitioning dimension that makes (application_number, vintage_tag) table-wide unique. |
| drug_source_version_id | string | Intended source-version linkage, but 100% NULL across all 119,326 rows. Dead/unpopulated column — do not join on it. |

Tools that read it: ira_drug_label_risk_screen

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