FDA Warning Letters

What it is

1. 1A silver-layer mirror of the FDA's public Warning Letters dataset (the official FDA "datatables" export): one record per FDA warning letter issued to a company for a regulatory violation.
2. 2Despite living in the drug catalog, it spans ALL FDA centers (tobacco, drugs, devices, food, veterinary, biologics), so it is a regulatory-enforcement signal — useful for flagging drug manufacturer / compounder / API-maker GMP and unapproved-drug actions that can disrupt supply, not a price or cost table itself.

Grain

How to read it

1. 1Surface drug-supply-disruption / enforcement risk for a manufacturer or compounder.
2. 2Example: SELECT company_name, letter_issue_date, subject, issuing_office, closeout_letter_date FROM drug_silver.fact_fda_warning_letter WHERE (lower(issuing_office) LIKE '%cder%' OR lower(issuing_office) LIKE '%pharmaceutical quality%' OR lower(subject) LIKE '%pharmaceutical%' OR lower(subject) LIKE '%compounding%' OR lower(subject) LIKE '%api%') ORDER BY letter_issue_date DESC; — returns recent FDA drug-side warning letters (GMP/adulteration/unapproved-drug actions). closeout_letter_date IS NULL flags violations not yet formally resolved.
3. 3Join to cost/manufacturer tables is name-based only (no shared code).

Traps

1. 1NOT a cost/price table and NOT drug-only. It holds zero prices, NDCs, NPIs, or labeler codes. It spans ALL FDA centers — tobacco (868 letters) is actually the single biggest bucket, ahead of drugs. Drug-relevant rows are a large minority: ~773 by CDER/pharma-quality issuing_office, ~687 by drug-related subject. Always filter by subject/issuing_office (with LIKE, never equality) before treating it as a drug signal.
2. 2letter_id looks like a content hash but IS the verified primary key (16-char hex, 2,571 unique = row count). Do not assume it's an unstable surrogate — the natural key (company+issue_date+subject) collapses to the exact same 2,571, so it is a deterministic 1:1 letter identifier.
3. 3Single snapshot only (observed_period = asserted_at = vintage_tag date = 2026-05-16; cms_vintage_sequence, parse_revision, source_id, source_url, sha256_content_hash, s3_version_id are ALL single constants; is_current is trivially all-true). There is no history/time series here despite the bitemporal columns — periodicity is effectively static until the next pull. Do NOT read effective_period_start as a snapshot dimension: it just mirrors letter_issue_date 1:1.
4. 4Three columns are null-as-meaning / dead: response_letter_date is 100% null (1 stray row), closeout_letter_date is 89% null where non-null = FDA formally resolved the violation (and null does NOT reliably mean 'still open' — it may just be un-closed-out publicly), and drug_source_version_id is 100% null. effective_period_end is always the 2099-12-31 open sentinel.
5. 5issuing_office is unnormalized free text with 120+ values and heavy spelling drift for the same office (e.g. CDER appears as 'Center for Drug Evaluation and Research (CDER)', '... | CDER', 'Center for Drug Evaluation and Research', 'Drug'). Bucketing requires fuzzy/LIKE logic; an exact GROUP BY will badly fragment counts. company_name has the same risk for entity joins (free text, no identifier).

Columns (23)

Query it

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