# FDA Drug Master Files (DMF)

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

FDA Drug Master Files — confidential filings (type, status, holder, subject, submit date) referenced by drug applications. Supply-chain / API-source reference for a drug.

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

**How to use it.** Use 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. Example: 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. Always include WHERE is_current = true.

**Traps.**
- The 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.
- is_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.
- The 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.
- This 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.
- dmf_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).

| column | type | meaning |
|---|---|---|
| dmf_number | int | FDA-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. |
| status | string | DMF 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_type | string | FDA 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_date | date | Date 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. |
| holder | string | Name 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. |
| subject | string | Free-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_period | date | The 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_at | date | Bitemporal '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_start | date | SCD-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_end | date | SCD-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_sequence | int | Pipeline vintage sequence number for the source vintage. Constant value 1 in all observed rows. |
| parse_revision | int | Parser revision counter for this row's extraction. Constant value 1 in all observed rows. |
| parser_artifact_hash | string | SHA-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_url | string | FDA 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_hash | string | SHA-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_id | string | Logical source identifier. Constant 'fda_dmf' for the whole table. |
| is_current | boolean | Boolean 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_key | string | S3 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_id | string | S3 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_tag | string | Human-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_id | string | Intended cross-source drug lineage/version id, but 100% NULL in this table — unused placeholder. Do not rely on it for joins. |

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