VladaOmega
Sign inStart free
Drugs and formularies

FDA Drug Master Files (DMF)

drug_silver.dim_dmf·live·dim·other·FDA Drug Master Files (DMF)
Rows
N/A
Columns
21
History
2026-05-16 (snapshot/ingest date; filings' original submit_date goes back to 1939-07-18) to 2026-05-18 (current snapshot; latest submit_date 2026-03-31)
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A cleaned, bitemporal snapshot of the public FDA Drug Master File (DMF) list — every confidential filing that an API/excipient/packaging manufacturer has on file with FDA to support drug applications.
  2. 2In the drug COST substrate it is a supply-chain / manufacturer-context DIMENSION (who makes the drug substances, excipients, and packaging behind branded/generic drugs), used to enrich and join to the drug cost facts; it contains no prices itself.

Grain

KeyGrain
dmf_numberobserved_period
  1. 1One row per FDA DMF filing per ingest snapshot.
  2. 2The table holds 80,730 rows = exactly 2 full snapshots of the same FDA file.
  3. 3On the live slice (WHERE is_current = true) the minimal verified key is dmf_number alone: 40,365 rows = 40,365 distinct dmf_number.
  4. 4Across ALL rows the minimal key is (dmf_number, observed_period): 80,730 distinct = the full row count.
  5. 5Always filter is_current = true (or pick one observed_period) before treating dmf_number as a primary key.

How to read it

  1. 1Use it to identify the manufacturers and substances behind a drug's supply chain (active-ingredient/API makers, excipient and packaging suppliers) and join that context to the drug cost facts.
  2. 2Example: SELECT holder, COUNT(*) AS active_filings FROM drug_silver.dim_dmf WHERE is_current = true AND status = 'A' AND dmf_type = 'II' GROUP BY holder ORDER BY active_filings DESC — answers 'which companies hold the most active drug-substance (API) master files', a proxy for who supplies the most APIs.
  3. 3Always include WHERE is_current = true.

Traps

  1. 1The raw table is DOUBLE-COUNTED: 80,730 rows = two identical snapshots of the same FDA file. ALWAYS filter WHERE is_current = true (or pick one observed_period) — otherwise every dmf_number appears twice and counts/joins double. On the live slice it is 40,365 rows.
  2. 2is_current is the ONLY trustworthy current-version flag. effective_period_end is 2099-12-31 (open) for BOTH the live and the superseded snapshot, so the older version was never end-dated — do NOT use effective_period_end to pick the current row.
  3. 3The 2026-05-16 → 2026-05-18 'update' carries NO data change: identical sha256_content_hash, 0 rows changed/added/removed. It is just a re-ingest/re-parse (parser_artifact_hash differs, s3_version_id newly populated). Don't read it as new FDA data; temporal range (2026-05-16→05-18) reflects ingest dates, not real-world change cadence.
  4. 4This is a manufacturer/supply-chain DIMENSION, not a cost/price table — it has NO NDC, no drug product code, and no price. It does not directly key to the drug spine (dim_drug) or to the cost facts; holder/subject are free text. Joining it to cost data requires fuzzy substance/manufacturer matching, not a clean ID.
  5. 5dmf_number = 0 is a legitimate filing (HEXCEL CHEMICAL PRODUCTS / 'CPC'), not a null/sentinel placeholder — keep it. status uses single letters A=Active, I=Inactive; dmf_type uses Roman numerals II/III/IV/V (Type I discontinued by FDA, absent here).

Columns (21)

ColumnTypeMeaning
dmf_numberintFDA-assigned Drug Master File number; the natural primary key on the is_current slice (40,365 distinct, range 0–44,003). NOT globally unique in the raw table because each filing appears once per snapshot. dmf_number = 0 is a REAL filing (HEXCEL CHEMICAL PRODUCTS / 'CPC'), not a sentinel — do not filter it out.
statusstringDMF lifecycle status, single-letter code. Only two values observed: 'A' = Active (22,039 on is_current) and 'I' = Inactive/closed (18,326). No nulls.
dmf_typestringFDA DMF type (Roman numeral). Observed values: 'II' = drug substance / API / drug intermediate (28,872, the bulk), 'III' = packaging material (7,305), 'IV' = excipient, colorant, flavor or other additive (3,616), 'V' = FDA-accepted reference information (572). (Type I was discontinued by FDA and is absent.) No nulls.
submit_datedateDate the DMF was originally submitted to FDA. Real historical depth: ranges 1939-07-18 to 2026-03-31. Some early dates are Jan-1 placeholders (e.g. 1940-01-01, 1950-01-01) reflecting year-only original records. No nulls.
holderstringName of the DMF holder — the manufacturer/company that owns the filing (e.g. EASTMAN CHEMICAL CO, MONSANTO CO). 8,722 distinct holders on the live slice. Free-text, not normalized to an entity ID. No nulls.
subjectstringFree-text description of what the DMF covers — the substance, material, excipient, or (for Type V) the reference topic (e.g. 'PENICILLIN', 'HYDROXYPROPYL METHYLCELLULOSE'). 27,155 distinct values. Not a coded field and carries no NDC. No nulls.
observed_perioddateThe snapshot/as-of date the FDA list was observed (ingested). Two values: 2026-05-16 and 2026-05-18. This is the second half of the grain key across all rows; together with dmf_number it uniquely identifies every row (80,730).
asserted_atdateBitemporal 'assertion' / knowledge date — when this snapshot's facts were asserted by the pipeline. Equals observed_period in all rows (2026-05-16 or 2026-05-18).
effective_period_startdateSCD-2 valid-from date for the row's version; equals observed_period (2026-05-16 or 2026-05-18). The temporal column the profiler keys on.
effective_period_enddateSCD-2 valid-to date. TRAP: it is 2099-12-31 (open/infinity) for BOTH snapshots, including the superseded 2026-05-16 one — the older version was never end-dated. So effective_period_end CANNOT be used to find the current record; use is_current instead.
cms_vintage_sequenceintPipeline vintage sequence number for the source vintage. Constant value 1 in all observed rows.
parse_revisionintParser revision counter for this row's extraction. Constant value 1 in all observed rows.
parser_artifact_hashstringSHA-256 of the parser artifact/build that produced the row. DIFFERS between the two snapshots (1f5e8223… for 2026-05-16 vs 8589ceaa… for 2026-05-18) even though the underlying FDA file content is identical — i.e. the 05-18 snapshot is a re-parse of the same file with a newer parser build.
source_urlstringFDA download URL of the source file. Constant: https://www.fda.gov/media/192069/download?attachment (the public FDA DMF list). Confirms origin = FDA, single file.
sha256_content_hashstringSHA-256 of the raw source file content. IDENTICAL across both snapshots (efed3bd6…) — proving the 2026-05-16 and 2026-05-18 snapshots are byte-for-byte the same FDA file (0 rows changed, 0 added, 0 removed between them).
source_idstringLogical source identifier. Constant 'fda_dmf' for the whole table.
is_currentbooleanBoolean SCD flag marking the live snapshot. Exactly half the rows (40,365) are true (the 2026-05-18 snapshot) and half false (2026-05-16). This is the ONLY reliable way to dedup to the current version (effective_period_end is unreliable). Filter is_current = true for the live dimension.
bronze_object_keystringS3 key of the bronze landing object for the snapshot (e.g. drug/bronze/fda_dmf/2026-05-18/…-fda_dmf_2026-05-18.xlsx). Provenance lineage; one key per snapshot.
s3_version_idstringS3 object version id of the bronze file. Populated for the current 2026-05-18 snapshot but NULL for the superseded 2026-05-16 snapshot — a null that signals the older/non-current vintage.
vintage_tagstringHuman-readable vintage label, pattern fda_dmf_<observed_period> (e.g. fda_dmf_2026-05-18). One per snapshot; encodes the same date as observed_period.
drug_source_version_idstringIntended cross-source drug lineage/version id, but 100% NULL in this table — unused placeholder. Do not rely on it for joins.

Query it

describe_data_source(source_fqn="drug_silver.dim_dmf")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.dim_dmf WHERE dmf_number = '<value>' AND observed_period = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keydmf_number
Joinhow_to_join(source_fqn="drug_silver.dim_dmf")
Raw doorAthena (read-only via query_substrate): drug_silver.dim_dmf

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