VladaOmega
Sign inStart free
Drugs and formularies

NLM RxClass — drug class dimension

drug_silver.dim_rxclass_class·live·dim·static (single snapshot; SCD2 scaffolding present but no history yet)·Drug Classification (RxClass)
Rows
N/A
Columns
18
History
2026-06-03 to 2026-06-03
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A reference dimension that defines drug-classification CLASSES from NIH/NLM RxClass (RxNav).
  2. 2Each row is one class node — its code (class_id), the classification system it belongs to (class_type: ATC1-4, MOA, or EPC), and its human-readable label (class_name).
  3. 3It is a lookup/join spine: it lets the agent translate a drug-class code into a name and roll drug-level cost data (ASP, NADAC, pricing) up to a therapeutic or mechanism-of-action grouping.
  4. 4The table holds the class definitions themselves, not the drug-to-class memberships (that is a separate mapping table).

Grain

KeyGrain
class_id
  1. 1One row per class node, uniquely identified by class_id.
  2. 2Verified: 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).
  3. 3The 2,847 nodes split across 3 classification systems: ATC1-4 = 1,319, MOA = 778, EPC = 750.
  4. 4All 2,847 rows are the single current snapshot (is_current=true for every row).

How to read it

  1. 1Use it to label and group drug classes.
  2. 2Example: 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.
  3. 3For 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

  1. 1class_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.
  2. 2class_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.
  3. 3Two 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'.
  4. 4This 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.
  5. 5drug_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.
  6. 6This 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.).

Columns (18)

ColumnTypeMeaning
class_idstringPRIMARY 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_typestringWhich 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_namestringHuman-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_perioddateDate the source content was observed/ingested. Single value: 2026-06-03 for all rows (one snapshot).
asserted_atdateDate this fact was asserted into the silver table. Single value: 2026-06-03. Part of the bitemporal scaffolding (asserted vs effective).
effective_period_startdateStart 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_enddateEnd 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_sequenceintMonotonic 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_revisionintRevision 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_hashstringSHA-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_urlstringOrigin endpoint. Single value: 'https://rxnav.nlm.nih.gov/REST/rxclass' (the NLM RxNav RxClass REST API).
sha256_content_hashstringSHA-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_idstringLogical source identifier. Single value: 'nih_rxclass'. The stable handle for this dataset across the drug-silver layer.
is_currentbooleanSCD2 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_keystringS3 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_idstringS3 object-version id of that bronze object (bucket versioning). Single value. Pins the exact immutable byte-version of the raw file.
vintage_tagstringHuman-readable vintage label. Single value: 'rxclass_2026-06-03'. Convenient snapshot name; will differ per future pull.
drug_source_version_idstringForeign-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

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

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