FDA Structured Product Labels (SPL) — drug label dimension

What it is

1. 1A drug-label dimension table sourced from the openFDA Drug Label API (SPL / Structured Product Labeling documents).
2. 2Each row is one FDA label document version with its identity metadata (brand/generic/substance names, 9-digit product NDC, manufacturer/labeler, FDA application number, product type) and boolean flags for the presence of key label sections (boxed warning, pregnancy, pediatric, geriatric, REMS).
3. 3In a cost-data catalog it is the regulatory ENRICHMENT/join dimension for drugs — it supplies what a drug IS and its warnings, to attach context to drug-cost facts (ASP/NADAC/MRF); it contains no prices itself.

Grain

How to read it

1. 1Use it to resolve a drug's identity/warnings for cost context.
2. 2Example: 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).
3. 3Always include WHERE is_current = true, and expect the openfda-derived identity columns to be NULL for ~71% of rows.

Traps

1. 1spl_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.
2. 2is_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.
3. 3The 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.
4. 4No 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.
5. 5has_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.
6. 6The 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).

Columns (30)

Query it

describe_data_source(source_fqn="drug_silver.dim_drug_label")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.dim_drug_label WHERE spl_id = '<value>' LIMIT 100")