# NLM RxClass — drug class dimension

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

The NLM RxClass class dimension — drug classes across class systems (ATC, Established Pharmacologic Class, Mechanism of Action, therapeutic, etc.). The vocabulary for rolling drugs up to a clinical/therapeutic class.

**Grain.** One row per class node, uniquely identified by class_id. Verified: 2,847 rows, 2,847 distinct class_id, 0 nulls, and no class_id collides across class_types — so class_id alone is the minimal key (you do NOT need class_type with it). The 2,847 nodes split across 3 classification systems: ATC1-4 = 1,319, MOA = 778, EPC = 750. All 2,847 rows are the single current snapshot (is_current=true for every row).

**How to use it.** Use it to label and group drug classes. Example: SELECT class_id, class_name FROM drug_silver.dim_rxclass_class WHERE is_current = true AND class_type = 'EPC' ORDER BY class_name — returns the 750 Established Pharmacologic Class names with their NUI codes, which you then join (via a separate RxClass membership table on class_id) to drug-level cost facts to report, say, average ASP by pharmacologic class. For ATC therapeutic-area rollups, filter class_type='ATC1-4' and pick the desired depth via length(class_id) (3 = therapeutic subgroup, 5 = chemical subgroup)."

**Traps.**
- class_id is the ONLY key and it is globally unique across all 3 class systems — do NOT assume you must pair it with class_type to dedup. Verified zero class_id collisions across ATC1-4/MOA/EPC.
- class_name is NOT unique and is not a key: e.g. 'Antibiotics' maps to 8 different ATC class_ids and 'Corticosteroids' to 4 (the same label recurs at different points in the ATC tree). Always join/lookup on class_id, never on the name.
- Two different code systems live in one column: ATC1-4 rows use ATC alpha-numeric codes where string LENGTH = hierarchy depth (1/3/4/5 → main/therapeutic/pharmacological/chemical subgroup), while MOA and EPC rows use NUI codes shaped 'N0000NNNNNN'. Parse class_id format by class_type; there is also a synthetic ATC root node class_id='0'.
- This is a single static snapshot: every row is is_current=true, one sha256_content_hash, one vintage_tag ('rxclass_2026-06-03'), effective_period_start=2026-06-03, effective_period_end=2099-12-31 (open-ended sentinel, NOT a real expiry). No history exists yet, so the SCD2/is_current machinery is currently a no-op — but write WHERE is_current = true anyway so queries stay correct once re-pulls add superseded rows.
- drug_source_version_id is 100% NULL here — never join or filter on it. Also note cms_vintage_sequence is a generic template column: despite the 'cms' name the source is NIH RxClass (rxnav.nlm.nih.gov), not CMS.
- This table holds class DEFINITIONS only — it does not contain drug-to-class membership (which RxCUIs/NDCs belong to each class) and contains no cost/price measures. To attach cost data you must join class_id to a separate RxClass membership/mapping table, then to the drug pricing facts (ASP/NADAC/etc.).

| column | type | meaning |
|---|---|---|
| class_id | string | PRIMARY KEY — the class node's code, globally unique across all class systems (2,847 distinct = row count, 0 nulls). Format depends on class_type: ATC1-4 uses hierarchical ATC alphanumeric codes whose LENGTH encodes depth (len 1 = anatomical main group e.g. 'A'; len 3 = therapeutic subgroup e.g. 'A01'; len 4 = pharmacological subgroup e.g. 'A01A'; len 5 = chemical subgroup e.g. 'A01AA'), plus one synthetic root '0'. MOA and EPC use NUI codes of the form 'N0000NNNNNN' (e.g. 'N0000000069', 'N0000175413'). |
| class_type | string | Which RxClass classification system the node belongs to. Exactly 3 values observed: 'ATC1-4' (1,319 rows — WHO Anatomical Therapeutic Chemical hierarchy levels 1-4), 'MOA' (778 rows — Mechanism of Action), 'EPC' (750 rows — Established Pharmacologic Class). 0 nulls. Use this to filter to one taxonomy before traversing/joining. |
| class_name | string | Human-readable label for the class (0 nulls, 0 empty). NOT unique — many class_ids share a name (e.g. 'Antibiotics' = 8 distinct ATC nodes, 'Corticosteroids' = 4). For ATC, casing is a soft hierarchy signal (upper-case names like 'ALIMENTARY TRACT AND METABOLISM' tend to be higher anatomical/therapeutic levels; mixed-case like 'Caries prophylactic agents' tend to be lower chemical levels). Always join on class_id, never class_name. |
| observed_period | date | Date the source content was observed/ingested. Single value: 2026-06-03 for all rows (one snapshot). |
| asserted_at | date | Date this fact was asserted into the silver table. Single value: 2026-06-03. Part of the bitemporal scaffolding (asserted vs effective). |
| effective_period_start | date | Start of the validity window for this row's class definition. Single value: 2026-06-03 (the table's only temporal column with content). Designed for SCD2 versioning across future pulls. |
| effective_period_end | date | End of the validity window. Single value: 2099-12-31 — the open-ended/sentinel 'still active' marker. Every current row carries this; a future re-pull that changes a class would close the old row to the change date. Treat 2099-12-31 as 'currently in effect', not a real expiry. |
| cms_vintage_sequence | int | Monotonic vintage/version counter for the source pull. All rows = 1 (first and only vintage so far). Would increment on each new RxClass ingestion; useful later to order snapshots. Name says 'cms' but the source here is NIH RxClass, not CMS — it is a generic vintage sequence reused from the drug-silver template. |
| parse_revision | int | Revision number of the parser run that produced these rows. All rows = 1. Bumps if the same source is re-parsed with updated logic. |
| parser_artifact_hash | string | SHA-256 of the parser artifact (code/config) that generated the rows — provenance of HOW the data was parsed. Single value across all rows ('26d57599...'). Lets you tie output to an exact parser version. |
| source_url | string | Origin endpoint. Single value: 'https://rxnav.nlm.nih.gov/REST/rxclass' (the NLM RxNav RxClass REST API). |
| sha256_content_hash | string | SHA-256 of the raw downloaded source CONTENT — provenance of WHAT was ingested. Single value ('e898a741...'), confirming all 2,847 rows came from one identical source document. |
| source_id | string | Logical source identifier. Single value: 'nih_rxclass'. The stable handle for this dataset across the drug-silver layer. |
| is_current | boolean | SCD2 current-version flag. TRUE for all 2,847 rows (0 nulls, 0 false) because there is only one snapshot. The intended dedup/filter for serving: always add WHERE is_current = true so that when historical (superseded) rows arrive later, you read only the live definition per class_id. |
| bronze_object_key | string | S3 key of the bronze (raw landed) object this silver row derives from. Single value: 'drug/bronze/nih_rxclass/2026-06-03/e898a741...-nih_rxclass_2026-06-03.json'. Lineage pointer back to the raw JSON. |
| s3_version_id | string | S3 object-version id of that bronze object (bucket versioning). Single value. Pins the exact immutable byte-version of the raw file. |
| vintage_tag | string | Human-readable vintage label. Single value: 'rxclass_2026-06-03'. Convenient snapshot name; will differ per future pull. |
| drug_source_version_id | string | Foreign-key slot to a drug-source-version registry. 100% NULL (2,847/2,847) for this table — present in the shared schema but not populated for the RxClass source. Do NOT join or filter on it; treat as absent. |

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