VladaOmega
Sign inStart free
Drugs and formularies

FDA Orange Book — patents

drug_silver.fact_orange_book_patent·live·silver·quarterly·FDA Orange Book
Rows
N/A
Columns
25
History
2021-03-01 to 2026-04-01
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A point-in-time-snapshotted mirror of the FDA Orange Book "patent" file (the patent.txt inside the Electronic Orange Book ZIP): every patent and method-of-use code that a brand-drug (NDA) sponsor has listed against an approved drug product, with the patent's expiration date and FDA listing flags.
  2. 2It is the legal/IP layer of the drug spine — it tells you which patents protect a given drug and when they expire, i.e. when generic/biosimilar competition (and therefore price erosion) can begin.
  3. 3Used for loss-of-exclusivity timing and brand-vs-generic cost analysis.

Grain

KeyGrain
appl_noproduct_nopatent_nopatent_use_codeeffective_period_start
  1. 1One row per listed patent-use entry per FDA snapshot: the natural key is (appl_no, product_no, patent_no, patent_use_code) within a single effective_period_start.
  2. 2On is_current=true that natural key is unique and yields 28,168 rows (each = one patent + one use-code listed against one approved product, frozen at its last-observed FDA values).
  3. 3Across the full table, (appl_no, product_no, patent_no, patent_use_code, effective_period_start) is unique = exactly 318,983 rows (the same key repeated across 18 quarterly snapshots).
  4. 4Note patent_no carries FDA suffixes like *PED (pediatric-exclusivity extension), so the same base USPTO patent can appear as multiple rows (e.g.
  5. 57452872 and 7452872*PED) with different expiration dates; and patent_use_code='' (empty string, 42% of rows) is a real value meaning "drug-substance/drug-product patent with no method-of-use claim", not missing data.

How to read it

  1. 1To find the current loss-of-exclusivity timeline for a drug, query the latest snapshot and parse the expiration text.
  2. 2Example: SELECT appl_no, product_no, patent_no, patent_use_code, patent_expire_date_text, date_parse(patent_expire_date_text, '%b %e, %Y') AS expire_date, delist_flag FROM drug_silver.fact_orange_book_patent WHERE effective_period_start = DATE '2026-04-01' AND appl_no = '020610' ORDER BY expire_date DESC.
  3. 3This answers 'which patents protect this drug product and when does the last one (including any *PED pediatric extension) expire?' — i.e. the earliest date generic competition / price erosion can begin.
  4. 4Join appl_no(+product_no) to the drug-product/NDC spine to attach drug names and prices; aggregate max(expire_date) per product for the headline LOE date.
  5. 5Use the full snapshot history (effective_period_start over 18 quarters) to see when a patent was first listed or dropped/delisted over time.

Traps

  1. 1is_current=true does NOT mean 'currently in the Orange Book.' It is SCD2 last-seen state: 28,168 rows = every patent-use entry ever observed, frozen at its last snapshot. 7,056 of them (incl. long-expired/delisted patents) are absent from the newest file. To get TODAY'S actual Orange Book patents, filter effective_period_start = (SELECT max(effective_period_start)) — i.e. DATE '2026-04-01' (21,112 rows), not is_current.
  2. 2The grain is the 4-part FDA natural key (appl_no, product_no, patent_no, patent_use_code) per snapshot — NOT patent_no (8,828 distinct, heavily repeated), patent_use_code, or any single column. The profiler failed to find the key because it never tested these natural-key columns; verified here as exactly unique (28,168 on is_current, 318,983 with effective_period_start across all 18 snapshots).
  3. 3patent_no is NOT a clean USPTO number: FDA appends suffixes, most importantly '*PED' for a 6-month pediatric-exclusivity extension. The same base patent appears as two rows (e.g. '7452872' expiring Aug 24 2026 and '7452872*PED' expiring Feb 24 2027). Strip/parse the suffix before joining to external patent data, and use the *PED row for the true latest exclusivity date.
  4. 4patent_use_code is never NULL but uses empty string '' (42% of rows) as a real value meaning 'no method-of-use code' (a drug-substance or drug-product patent). Don't treat '' as missing. The U-#### codes reference indications defined in a SEPARATE FDA use-code dictionary that is NOT in this table.
  5. 5The three boolean flags (drug_substance_flag, drug_product_flag, delist_flag) are TRUE-or-NULL only (never false). NULL means 'not flagged' (effectively false), not 'unknown'. Counting NULLs as missing data will mislead — e.g. delist_flag is true for only 223 rows and NULL for the other 27,945.
  6. 6All dates that matter (patent_expire_date_text, submission_date_text) are stored as 'Mon D, YYYY' TEXT, not date types, and submission_date_text is null for ~5K older rows. Parse to real dates before any expiration/sorting logic. effective_period_end always equals effective_period_start (snapshots, not ranges).
  7. 7Many columns are constant filler in this table and carry no signal: appl_type='N', cms_vintage_sequence=1, parse_revision=1, vintage_tag='none', source_id='fda_orange_book', single source_url, and drug_source_version_id is 100% NULL. The 'cms_' prefix on cms_vintage_sequence is a schema-template misnomer — this is FDA, not CMS, data.

Columns (25)

ColumnTypeMeaning
appl_typestringFDA application type. Constant 'N' (New Drug Application / NDA = brand) for 100% of rows in this table — ANDAs (generics, type 'A') do not list patents, so only NDAs appear. Not useful as a filter here.
appl_nostringFDA NDA application number, fixed 6-char zero-padded string (e.g. '020610', '206073'). Joins to the drug-product / approval spine. Part of the natural key. 1,537 distinct applications on is_current.
product_nostringProduct number within the application, fixed 3-char zero-padded string (e.g. '001','002'). Distinguishes strengths/dosage forms under one NDA. appl_no+product_no identifies one approved drug product (3,044 distinct on is_current). Part of the natural key.
patent_nostringThe listed patent, as printed by FDA — usually a USPTO number string (e.g. '7625884') but MAY carry FDA suffixes, notably '*PED' for a 6-month pediatric-exclusivity extension (e.g. '7452872*PED'). The *PED variant is a separate row with a LATER patent_expire_date_text than the base patent. Do NOT treat as a clean integer patent id. 8,828 distinct values on is_current.
patent_use_codestringFDA Patent Use Code identifying the specific approved indication/method-of-use the patent claims, formatted 'U-####' (e.g. 'U-141'); the code maps to an indication phrase in FDA's separate use-code dictionary (not in this table). Empty string '' (NEVER null) = no use code = a drug-substance or drug-product (compound/formulation) patent rather than a method-of-use patent; '' accounts for 11,807 of 28,168 is_current rows (42%). Part of the natural key.
patent_expire_date_textstringPatent expiration date as a human text string in 'Mon D, YYYY' format (e.g. 'Aug 24, 2026'); never null/empty. THIS IS THE KEY ECONOMIC FIELD — it dates when patent protection ends (loss-of-exclusivity). Stored as text, so must be parsed to a real date for comparisons. *PED rows carry the extended (later) date. Already-past dates appear because expired patents are retained via the last-seen is_current logic.
drug_substance_flagbooleanBoolean presence flag: true = FDA flagged this as a drug-substance (active-ingredient/compound) patent. TRUE-or-NULL only (4,622 true / 23,546 NULL / 0 false on is_current). NULL means 'not a substance patent' (effectively false), NOT unknown/missing.
drug_product_flagbooleanBoolean presence flag: true = FDA flagged this as a drug-product (formulation/composition) patent. TRUE-or-NULL only (12,940 true / 15,228 NULL / 0 false on is_current). NULL = 'not a product patent' (effectively false), not missing. A patent can be flagged both substance and product.
delist_flagbooleanBoolean presence flag: true = the sponsor has REQUESTED that this patent be delisted from the Orange Book. TRUE-or-NULL only and rare (223 true / 27,945 NULL / 0 false on is_current). NULL = no delist request. Material signal: a delist-requested patent may no longer block generics.
submission_date_textstringText date ('Mon D, YYYY') the patent/use-code information was submitted to FDA; null for 5,365 of 28,168 is_current rows (older listings predate FDA's capture of this field). Stored as text.
observed_perioddateDate the FDA snapshot represents; equals effective_period_start in every row. One of 18 quarterly snapshot dates 2021-03-01 .. 2026-04-01.
asserted_atdateDate Vlada parsed/loaded that snapshot (a few days to ~5 weeks after observed_period; e.g. 2026-04-01 snapshot asserted 2026-05-08). One value per snapshot. Use for lineage, not as the data effective date.
effective_period_startdateSnapshot effective date — the PRIMARY time dimension and partition. Equals observed_period. 18 distinct quarterly values 2021-03-01 .. 2026-04-01. Filter to the MAX value (2026-04-01) to get the actual current Orange Book.
effective_period_enddateSnapshot end date; ALWAYS equals effective_period_start (0 rows differ). These are point-in-time snapshots, not validity ranges — do not interpret as an interval.
cms_vintage_sequenceintProvenance sequence integer; constant value 1 for 100% of rows. The 'cms_' prefix is a misnomer inherited from the drug-silver schema template — this is FDA data, not CMS. Carries no information here.
parse_revisionintParser revision number; constant 1 for 100% of rows. Would increment if data were re-parsed with a newer parser. No information here.
parser_artifact_hashstringSHA-256 of the parser code/artifact that produced the row, for reproducibility lineage. Constant per parse generation (single value observed).
source_urlstringOrigin URL of the source file; constant 'https://www.fda.gov/media/76860/download' (the FDA Electronic Orange Book ZIP) for 100% of rows.
sha256_content_hashstringSHA-256 of the source ZIP file content for that snapshot. 18 distinct values = one per snapshot; immutable content fingerprint for provenance/dedup.
source_idstringLogical source identifier; constant 'fda_orange_book' for 100% of rows.
is_currentbooleanSCD2 'latest-known-state' flag, NOT 'in the current Orange Book file'. true marks the most recent snapshot in which each natural key was last seen, so is_current=true = the union of every patent-use entry frozen at its last-observed values (28,168 rows). 7,056 of those are NOT in the latest 2026-04-01 file (delisted/dropped patents retained at last-seen). The latest snapshot (2026-04-01, 21,112 rows) is 100% is_current. Keys are globally unique on is_current=true.
bronze_object_keystringS3 key of the raw bronze ZIP this row was extracted from (e.g. 'drug/bronze/fda_orange_book/2026-04-01/...EOBZIP_2026_04.zip'). 18 distinct = one source file per snapshot.
s3_version_idstringS3 object version id of the bronze file for immutable lineage. 18 distinct (one per snapshot).
vintage_tagstringVintage label; constant 'none' for 100% of rows. Unused in this table.
drug_source_version_idstringDrug-spine source version id; NULL for 100% of rows (never populated in this table). Do not rely on it for joins/filters.

Query it

describe_data_source(source_fqn="drug_silver.fact_orange_book_patent")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_orange_book_patent WHERE appl_no = '<value>' AND product_no = '<value>' AND patent_no = '<value>' AND patent_use_code = '<value>' AND effective_period_start = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keypatent_no
Joinhow_to_join(source_fqn="drug_silver.fact_orange_book_patent")
Raw doorAthena (read-only via query_substrate): drug_silver.fact_orange_book_patent

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