# FDA Structured Product Labels (SPL) — drug label dimension

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

FDA Structured Product Labels — brand/generic, product NDC, product type, substance, manufacturer, and safety flags (boxed warning, pregnancy, pediatric/geriatric use, REMS). The label-level safety reference per drug.

**Grain.** One row per FDA drug-label DOCUMENT VERSION (one SPL document, keyed by spl_id) within the current snapshot. Verified: on is_current=true, spl_id is unique with 229,807 distinct = 229,807 rows. The full table holds 1,071,488 rows because every prior daily/weekly snapshot vintage is retained (is_current=false), where the same spl_id recurs.

**How to use it.** Use it to resolve a drug's identity/warnings for cost context. Example: SELECT brand_name, generic_name, substance_name, product_ndc, has_boxed_warning FROM drug_silver.dim_drug_label WHERE is_current = true AND generic_name LIKE '%TENOFOVIR%' — returns the current FDA label record(s) for tenofovir products (brand, active substance, product NDC, whether it carries a boxed warning). Always include WHERE is_current = true, and expect the openfda-derived identity columns to be NULL for ~71% of rows.

**Traps.**
- spl_id is the key ONLY on is_current=true (229,807 unique). Over the full 1,071,488-row table the same spl_id repeats across 5 snapshot vintages — spl_id is NOT unique there. ALWAYS filter is_current=true (or one vintage_tag) before treating spl_id as a key.
- is_current is SCD 'last-seen-wins', NOT max(observed_period). The current set is 228,887 rows from the 2026-05-28 snapshot PLUS 920 rows from 05-18/05-21 that dropped out of the latest pull. Filtering by observed_period='2026-05-28' or vintage_tag='drug_label_2026-05-28' silently MISSES ~920 current labels — use is_current=true.
- The identity columns (brand_name, generic_name, product_ndc, product_type, substance_name, manufacturer_name) come from the openFDA 'openfda' enrichment block, which is ABSENT for ~71% of SPL documents — so all six are NULL together for ~163,771 of 229,807 current rows. NULL = no-openfda-metadata, not 'no brand/no NDC'. The has_* flags and effective_time/spl_id are populated regardless and are the reliable columns when openfda is missing.
- No price/cost field exists here — this is a regulatory LABEL DIMENSION (FDA SPL metadata: brand/generic/substance/NDC/warnings), used to ENRICH or join drug-cost facts (ASP/NADAC/MRF), not a cost source itself. And product_ndc is the 9-digit PRODUCT NDC (not 11-digit package NDC) and is non-unique (multiple SPL docs per NDC), so it is unreliable as a join KEY to package-level pricing.
- has_rems is FALSE for 100% of current rows (0 true) — a known gap; do not use it to identify REMS drugs. manufacturer_name is the SPL labeler and is dominated by REPACKAGERS (Bryant Ranch Prepack, A-S Medication Solutions, REMEDYREPACK...), not originator brand owners — which is also why one drug/NDC yields multiple co-current spl_id rows.
- The bitemporal date columns (asserted_at, observed_period, effective_period_start) are all IDENTICAL per row (= the ingest snapshot date), effective_period_end is a constant '2099-12-31' sentinel, and cms_vintage_sequence/parse_revision are constant 1. drug_source_version_id is 100% NULL. The profiler's 'weekly' temporal span (2026-05-16..28) reflects the INGEST cadence (effective_period_start), NOT drug-label history; the real label-revision timeline is in effective_time (1987→2027).

| column | type | meaning |
|---|---|---|
| spl_id | string | PRIMARY KEY (on is_current). UUID of one specific SPL (Structured Product Labeling) label DOCUMENT VERSION from the FDA. e.g. 'a9a21507-d564-4f36-8745-63e77f473978'. Unique only within is_current=true (229,807 distinct); the SAME spl_id repeats across snapshot vintages in the full 1.07M-row table, so it is NOT a key over the whole table. |
| spl_set_id | string | UUID identifying the LABEL FAMILY / drug-product across all its revisions (the stable 'set' that successive spl_id versions belong to). NOT unique even on is_current: 906 sets carry 2-3 distinct spl_id simultaneously (multiple co-current label versions of the same product). 228,894 distinct on is_current. Often equals spl_id for single-version products. |
| effective_time | date | The label's own author-stated effective/revision DATE as printed in the SPL document (date). Fully populated. Ranges 1987-04-03 to 2027-01-27 on is_current — INCLUDES FUTURE-DATED labels. This is the ONLY column carrying the drug's real label-revision timeline (all other date columns collapse to the ingest snapshot date). |
| application_number | string | FDA marketing-application ID, prefixed by type: ANDA (generic, ~26,759), NDA0/NDA2 (brand new-drug), BLA0/1/2/7 (biologic). NULL for ~86.5% of is_current rows (31,031 populated of 229,807) — OTC-monograph and homeopathic products legitimately have no application number. 10,483 distinct. |
| brand_name | string | Proprietary/brand name from the openFDA enrichment block (e.g. 'CIMDUO', 'ACYCLOVIR'). NULL for ~71% of is_current rows (only 66,036 populated) because most SPL documents lack the openfda block. NULL = missing-metadata, not 'no brand'. |
| generic_name | string | Non-proprietary/generic name from the openFDA block (e.g. 'LAMIVUDINE AND TENOFOVIR DISOPROXIL FUMARATE'). For combo/homeopathic products this can be a long comma-joined active-ingredient list. NULL whenever the openfda block is absent (same ~66,036 populated as brand_name). |
| product_ndc | string | 9-digit FDA PRODUCT NDC (labeler-product, e.g. '0338-0190'), NOT the 11-digit PACKAGE NDC. From openFDA block. NULL ~71% (66,036 populated, 64,838 distinct). NOT unique even on is_current — one NDC maps to multiple SPL docs (1,014 NDCs have 2 labels, one has 10), so it cannot be used as a join KEY to package-level pricing. |
| product_type | string | FDA product category from openFDA block. Values on is_current: NULL (163,771, no openfda block), 'HUMAN OTC DRUG' (37,743), 'HUMAN PRESCRIPTION DRUG' (28,276), 'CELLULAR THERAPY' (17). Only 4 distinct values. |
| substance_name | string | Active substance / ingredient name from the openFDA block (e.g. 'EPINEPHRINE' even when brand is 'Adrenalinum'). Slightly less null than brand (64,892 populated on is_current). Represents the active moiety, may differ from generic_name's labeled name. |
| manufacturer_name | string | Labeler/manufacturer name from the openFDA block. GOTCHA: top values are REPACKAGERS not originators (Bryant Ranch Prepack, A-S Medication Solutions, Proficient Rx, REMEDYREPACK, NuCare) — this is the SPL labeler, which explains multiple SPL docs per NDC. NULL ~71% (66,036 populated). |
| has_boxed_warning | boolean | Boolean derived from presence of the boxed-warning SECTION in the label text. TRUE for 28,644 of 229,807 is_current rows. Populated INDEPENDENT of the openfda block (so it is reliable even when brand/NDC are NULL). False = section absent. |
| has_pregnancy_warning | boolean | Boolean: pregnancy-related section present in label text. TRUE for 94,103 is_current rows. Independent of openfda block. |
| has_pediatric_use | boolean | Boolean: pediatric-use section present. TRUE for 59,797 is_current rows. Independent of openfda block. |
| has_geriatric_use | boolean | Boolean: geriatric-use section present. TRUE for 50,599 is_current rows. Independent of openfda block. |
| has_rems | boolean | Boolean: REMS (Risk Evaluation and Mitigation Strategy) section present. GOTCHA: currently FALSE for 100% of is_current rows (0 true) — either a parser gap or REMS text is not captured. Do NOT trust this flag to find REMS drugs. |
| observed_period | date | The DATED SNAPSHOT when the openFDA pull was ingested (date). Drives the snapshot cadence: 2026-05-16, -17, -18, -21, -28 (every few days). On is_current, identical to asserted_at and effective_period_start per row. GOTCHA: is_current is NOT max(observed_period) — 920 current rows come from 05-18/05-21 (last-seen before they dropped out of the 05-28 pull). |
| asserted_at | date | Bitemporal 'as-asserted' date for this row's facts (date). On the current snapshot it always equals observed_period (= snapshot date). Provenance scaffolding; carries no independent meaning here. |
| effective_period_start | date | Bitemporal validity-start date for this row version (date). On is_current always equals the snapshot date (asserted_at/observed_period). The profiler's reported 'temporal' span (2026-05-16..28, weekly) is THIS column = ingest cadence, NOT drug-label history (that's effective_time). |
| effective_period_end | date | Bitemporal validity-end date. ALWAYS '2099-12-31' (open-ended sentinel) on is_current — i.e. the current version has no expiry. Constant column on the current snapshot. |
| cms_vintage_sequence | int | Integer vintage-sequence counter. Constant = 1 on all is_current rows. Provenance/versioning scaffold; not analytically useful here. |
| parse_revision | int | Integer parser-revision number. Constant = 1 on all is_current rows. Bumped if the silver parser logic is re-run with new code; constant currently. |
| parser_artifact_hash | string | SHA-256 of the parser code/artifact that produced the row (e.g. 'fe9e1e25...'). Lineage/reproducibility column; same hash across a build. |
| source_url | string | Origin URL of the raw data. Constant 'https://api.fda.gov/drug/label.json' — confirms the source is the openFDA Drug Label API. |
| sha256_content_hash | string | SHA-256 of the source content batch (raw bronze object). Change-detection / dedup lineage; shared by rows from the same bronze file. |
| source_id | string | Logical source identifier. Constant 'drugsfda_label' — the openFDA drug-label feed registered in the source catalog. |
| is_current | boolean | THE snapshot/dedup flag. TRUE = the latest-known version of that spl_id (SCD 'current' semantics, last-seen-wins per spl_id), 229,807 rows. FALSE = superseded historical snapshot rows, 841,681 rows. ALWAYS filter is_current=true for the live label set; spl_id is unique ONLY under this filter. |
| bronze_object_key | string | S3 key of the raw bronze JSON batch this row was parsed from (e.g. 'drug/bronze/drugsfda_label/2026-05-18/...batch_00006.json'). Per-batch lineage; embeds the ingest date. |
| s3_version_id | string | S3 object version-id of the bronze file (versioned bucket). Pins the exact immutable source object for reproducibility. |
| vintage_tag | string | Human-readable snapshot label, 'drug_label_<YYYY-MM-DD>' (e.g. 'drug_label_2026-05-28'). is_current=true is dominated by drug_label_2026-05-28 (228,887) plus small residual 05-18/05-21 vintages. Useful for picking a specific snapshot. |
| drug_source_version_id | string | Source-version FK into the drug source-version registry. 100% NULL in this table (not yet wired); carries no information currently. |

Tools that read it: ira_drug_label_risk_screen

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