# FDA Orange Book — exclusivity

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

FDA Orange Book exclusivity periods by application/product — exclusivity code and expiry. Complements patents for generic/biosimilar entry-timing analysis.

**Grain.** One row per (FDA application type + application number + product number + exclusivity code + exclusivity expiry-date text) per monthly Orange Book snapshot. In plain words: one row = one specific exclusivity grant on one drug product, as it appeared in one captured edition of the Orange Book. Full table = 36,485 rows. Restricting to is_current=true collapses each exclusivity grant to its most-recently-observed version = 4,446 rows (one row per appl_type+appl_no+product_no+exclusivity_code+exclusivity_date_text).

**How to use it.** "Generic-entry / loss-of-exclusivity scan: SELECT e.appl_no, e.product_no, p.ingredient, p.trade_name, e.exclusivity_code, date_parse(e.exclusivity_date_text,'%b %e, %Y') AS expiry FROM fact_orange_book_exclusivity e JOIN dim_orange_book_product p ON e.appl_type=p.appl_type AND e.appl_no=p.appl_no AND e.product_no=p.product_no AND p.is_current=true WHERE e.observed_period=(SELECT max(observed_period) FROM fact_orange_book_exclusivity) AND date_parse(e.exclusivity_date_text,'%b %e, %Y') BETWEEN DATE '2026-01-01' AND DATE '2026-12-31' ORDER BY expiry — answers 'which branded drugs lose an FDA exclusivity in 2026', the upstream signal for generic competition and downstream price/cost reductions."

**Traps.**
- is_current=true is NOT a single point-in-time snapshot. It flags the last-seen version of each exclusivity grant, and those rows span 10 different observed_periods. For a true 'Orange Book as of latest edition' view, filter observed_period = (SELECT max(observed_period) ...) instead. Use is_current only when you want each grant's most recent record regardless of edition.
- appl_no+product_no+exclusivity_code is NOT unique even within is_current. One product+code can have several rows because exclusivity_date_text differs (multiple PED/ODE grants with different expiry dates). The minimal unique key must include exclusivity_date_text (and observed_period for the full table). Profiler reported 'no unique grain' because it never tested exclusivity_date_text.
- exclusivity_date_text is the EXCLUSIVITY EXPIRY date and is stored as a STRING ('Apr 26, 2021'), not a date column. You must date_parse(exclusivity_date_text,'%b %e, %Y') to do date math (e.g. 'which exclusivities expire in 2026 → generic-entry opportunity'). Do NOT use effective_period_end for this — that column just mirrors the snapshot date.
- exclusivity_code mixes a type prefix with a per-grant sequence (ODE-209, I-766, M-225) AND an FDA footnote asterisk (NCE*, ODE*, 748 rows). To analyze by exclusivity TYPE, strip both the numeric suffix and the trailing '*' (e.g. regexp_extract prefix). Counting raw exclusivity_code over-fragments (828 distinct current codes for ~16 real types).
- Despite the cms_vintage_sequence / drug_source_version_id column names, this is FDA Orange Book data and those lineage columns are inert (cms_vintage_sequence=1, drug_source_version_id all NULL, vintage_tag='none', parse_revision=1). Join to dim_orange_book_product on (appl_type, appl_no, product_no) for ingredient/trade_name/applicant (100% match on the current slice); fact_orange_book_patent is the patent sibling. effective_period_start=effective_period_end=observed_period on every row.

| column | type | meaning |
|---|---|---|
| appl_type | string | FDA application type. Two values: 'N' = NDA (brand/innovator drug, 35,430 rows / 4,095 current) and 'A' = ANDA (generic, 1,055 rows / 351 current). Part of the join key to dim_orange_book_product and fact_orange_book_patent. |
| appl_no | string | FDA application (NDA/ANDA) number, stored as a 6-char zero-padded string (e.g. '009190', '211155'). 1,137 distinct on the current slice. With appl_type+product_no it identifies a marketed drug product. Keep as string — leading zeros are significant. |
| product_no | string | Product/strength number within an application, 3-char zero-padded string ('001','002',...). One application can have several products (different strengths/forms). appl_no+product_no = 2,054 distinct current products. |
| exclusivity_code | string | FDA exclusivity-type code = the reason and length of market protection. Prefix is the type, suffix (where present) is a per-grant sequence counter. Current-slice frequency: ODE-### (Orphan Drug Exclusivity, 7yr) 1,069; NCE/NCE* (New Chemical Entity, 5yr) 732; PED (Pediatric, +6mo) 545; I-### (New Indication, 3yr) 532; M-### (new patient-population/labeling, 3yr) 431; NPP 335; NP 301; CGT (Competitive Generic Therapy) 187; PC 164; GAIN (antibiotic) 56; D-### (new dosage form) 52; plus NS, NC, W, NDF, RTO. A trailing '*' (NCE*, ODE*, 748 rows total) is an FDA footnote/delisting marker on the same code type. |
| exclusivity_date_text | string | The exclusivity EXPIRY date — the date the protection ends and generic/biosimilar entry becomes possible. Stored as a STRING in 'Mon D, YYYY' form (e.g. 'Apr 26, 2021'); 100% parseable with date_parse(...,'%b %e, %Y'). Across all snapshots parsed values span 2019-05-23 to 2035-12-12. This is a discriminator in the grain: one product+code can carry several distinct expiry dates (e.g. multiple PED or ODE grants). |
| observed_period | date | The snapshot month — which captured Orange Book edition this row came from (1st-of-month date). 18 distinct values 2021-03-01 to 2026-04-01. This is the time/vintage dimension; the same exclusivity grant reappears across consecutive snapshots while it is still active. Equal to effective_period_start and effective_period_end for every row. |
| asserted_at | date | Date the snapshot was asserted/parsed into the lake (a few days to weeks after observed_period, e.g. observed 2026-04-01 → asserted 2026-05-08). Exactly 1:1 with observed_period; metadata, not a business date. |
| effective_period_start | date | Start of the validity window for this snapshot row. Identical to observed_period for ALL 36,485 rows (point-in-time snapshot, not a true SCD interval). |
| effective_period_end | date | End of the validity window. Identical to effective_period_start (and observed_period) for ALL rows — carries no independent information; do NOT read it as 'exclusivity end date' (that is exclusivity_date_text). |
| cms_vintage_sequence | int | Lineage counter, constant = 1 for every row. Despite the 'cms_' name this source is FDA, not CMS — a shared drug_silver schema column that is inert here. |
| parse_revision | int | Parser revision number, constant = 1 for all rows. Inert lineage field. |
| parser_artifact_hash | string | Hash of the parser build that produced the rows. 18 distinct values, exactly one per snapshot (1:1 with observed_period). |
| source_url | string | Origin URL, constant = 'https://www.fda.gov/media/76860/download' (the FDA Orange Book data-file download). |
| sha256_content_hash | string | SHA-256 of the downloaded source file. 18 distinct values, one per snapshot — the integrity fingerprint of each Orange Book ZIP edition. |
| source_id | string | Logical source name, constant = 'fda_orange_book'. |
| is_current | boolean | Boolean dedup flag. TRUE marks the row from the LATEST snapshot in which that exact exclusivity grant (appl_type+appl_no+product_no+exclusivity_code+exclusivity_date_text) was last observed — exactly one TRUE per such natural key. It is NOT a single-snapshot filter: is_current=TRUE rows span 10 different observed_periods because grants that have since dropped out of the Orange Book stay 'current' at their last-seen month. 4,446 of 36,485 rows are TRUE. |
| bronze_object_key | string | S3 key of the raw bronze object for the snapshot (e.g. 'drug/bronze/fda_orange_book/2026-04-01/...-EOBZIP_2026_04.zip'). 18 distinct, one per observed_period. |
| s3_version_id | string | S3 object version id of the bronze file. 18 distinct, no nulls, 1:1 with snapshot. |
| vintage_tag | string | Free-text vintage label, constant = 'none' for all rows. Inert. |
| drug_source_version_id | string | Cross-source version linkage id — NULL for every row in this table. Not populated for the Orange Book exclusivity source. |

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