# FDA Orange Book — patents

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

FDA Orange Book patent listings — patents protecting approved drugs by application/product, with patent number, use code, expiry, and substance/product flags. Drives generic-entry timing analysis (when patent protection lapses).

**Grain.** One 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. On 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). Across 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). Note patent_no carries FDA suffixes like *PED (pediatric-exclusivity extension), so the same base USPTO patent can appear as multiple rows (e.g. 7452872 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 use it.** To find the current loss-of-exclusivity timeline for a drug, query the latest snapshot and parse the expiration text. Example: 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. This 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. Join 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. Use the full snapshot history (effective_period_start over 18 quarters) to see when a patent was first listed or dropped/delisted over time.

**Traps.**
- is_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.
- The 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).
- patent_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.
- patent_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.
- The 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.
- All 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).
- Many 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.

| column | type | meaning |
|---|---|---|
| appl_type | string | FDA 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_no | string | FDA 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_no | string | Product 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_no | string | The 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_code | string | FDA 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_text | string | Patent 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_flag | boolean | Boolean 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_flag | boolean | Boolean 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_flag | boolean | Boolean 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_text | string | Text 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_period | date | Date the FDA snapshot represents; equals effective_period_start in every row. One of 18 quarterly snapshot dates 2021-03-01 .. 2026-04-01. |
| asserted_at | date | Date 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_start | date | Snapshot 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_end | date | Snapshot 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_sequence | int | Provenance 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_revision | int | Parser revision number; constant 1 for 100% of rows. Would increment if data were re-parsed with a newer parser. No information here. |
| parser_artifact_hash | string | SHA-256 of the parser code/artifact that produced the row, for reproducibility lineage. Constant per parse generation (single value observed). |
| source_url | string | Origin 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_hash | string | SHA-256 of the source ZIP file content for that snapshot. 18 distinct values = one per snapshot; immutable content fingerprint for provenance/dedup. |
| source_id | string | Logical source identifier; constant 'fda_orange_book' for 100% of rows. |
| is_current | boolean | SCD2 '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_key | string | S3 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_id | string | S3 object version id of the bronze file for immutable lineage. 18 distinct (one per snapshot). |
| vintage_tag | string | Vintage label; constant 'none' for 100% of rows. Unused in this table. |
| drug_source_version_id | string | Drug-spine source version id; NULL for 100% of rows (never populated in this table). Do not rely on it for joins/filters. |

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