Drugs and formularies

FDA Orange Book — exclusivity

drug_silver.fact_orange_book_exclusivity·live·silver·monthly·FDA Orange Book

Rows

N/A

Columns

History

2021-03-01 to 2026-04-01

Card

yesgrain, traps, filters

Typed tools

What it is

1FDA Orange Book "Patent and Exclusivity" data, exclusivity half — the regulatory market-protection windows (orphan-drug, new-chemical-entity, pediatric, new-indication, etc.) attached to approved brand/generic drug products.
2For a cost-data catalog it answers "when does a drug's FDA exclusivity expire, after which generic/biosimilar competition (and price drops) can begin." Sourced from FDA monthly Orange Book ZIPs, captured as repeated point-in-time snapshots.

Grain

Key Grain

Key	Grain
`appl_typeappl_noproduct_noexclusivity_codeexclusivity_date_textobserved_period`	1One row per (FDA application type + application number + product number + exclusivity code + exclusivity expiry-date text) per monthly Orange Book snapshot. 2In plain words: one row = one specific exclusivity grant on one drug product, as it appeared in one captured edition of the Orange Book. 3Full table = 36,485 rows. 4Restricting 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).

appl_typeappl_noproduct_noexclusivity_codeexclusivity_date_textobserved_period

1One row per (FDA application type + application number + product number + exclusivity code + exclusivity expiry-date text) per monthly Orange Book snapshot.
2In plain words: one row = one specific exclusivity grant on one drug product, as it appeared in one captured edition of the Orange Book.
3Full table = 36,485 rows.
4Restricting 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 read it

1"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

1is_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.
2appl_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.
3exclusivity_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.
4exclusivity_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).
5Despite 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.

Columns (20)

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

describe_data_source(source_fqn="drug_silver.fact_orange_book_exclusivity")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_orange_book_exclusivity WHERE appl_type = '<value>' AND appl_no = '<value>' AND product_no = '<value>' AND exclusivity_code = '<value>' AND exclusivity_date_text = '<value>' AND observed_period = '<value>' LIMIT 100")

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

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