# NLM RxClass — drug → class membership

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

RxClass membership edges — maps each RxNorm concept (RxCUI + name + term type) to its drug class(es), with the relationship + source. Bridges NDC/drug (via RxCUI) to clinical/therapeutic classes for class-level analysis.

**Grain.** One row per (class_id, rxcui) pair — i.e. one row for each membership of a single RxNorm drug ingredient in a single drug class. 35,389 rows = 35,389 distinct class_id×rxcui pairs (verified). A drug appears in as many rows as it has classes (up to 16+); a class appears in as many rows as it has member drugs.

**How to use it.** To roll drug prices up to a therapeutic class or mechanism: join a price fact (e.g. ASP/NADAC by rxcui) to this table on rxcui, then GROUP BY class_id/class_type. Example — list all RxNorm ingredients in ATC subgroup L01 (antineoplastics): SELECT DISTINCT rxcui, rxnorm_name FROM fact_rxclass_membership WHERE class_type='ATC1-4' AND class_id='L01'; this returns the 277 drugs that define the 'chemo' cohort you would then price. Use rela='DIRECT' to restrict to primary assignments. Remember the rxcui join fans out across classes, so deduplicate/aggregate prices before summing.

**Traps.**
- MINIMAL GRAIN is just (class_id, rxcui) — 35,389 distinct = full row count. The profiler's reported 6-column key (adding source_id, bronze_object_key, s3_version_id, drug_source_version_id) is a SUPERSET inflated by 4 constant/NULL provenance columns; they add zero discrimination. Do not key or join on them.
- drug_source_version_id is 100% NULL — never use it as a key or join column despite its name and its appearance in the auto-profiler key.
- This is a single static snapshot: every temporal/provenance column (observed_period, asserted_at, effective_period_start='2026-06-03', effective_period_end='2099-12-31', cms_vintage_sequence=1, vintage_tag) holds exactly one value, and is_current is true for 100% of rows (no is_current=false rows exist). There is NO time series and is_current-filtering is currently a no-op — but it is bitemporally structured so future reloads will add vintages.
- Many-to-many fan-out is intentional and large: a drug (rxcui) can be in 16+ classes and one class can have thousands of drugs. Joining this table to a price/fact table on rxcui MULTIPLIES rows by the number of classes — aggregate (e.g. price first, then roll up by class_id) or you will double-count spend.
- class_id is a MULTI-SCHEME code: ATC1-4 rows use short ATC alphanumeric codes (incl. pseudo-code '0' = the 'all drugs' root bucket holding all 3,112 ATC drugs) while EPC/MOA rows use NDF-RT identifiers like 'N0000175413'. Always interpret class_id together with class_type; the same string space is not shared across schemes. Note 'ATC1-4' means levels 1-4 only (no level-5 chemical-substance codes).
- rela_source is fully redundant with class_type (ATC1-4->ATC, EPC/MOA->DAILYMED) and class_type is fully determined by class_id — treat class_type/rela_source as derived labels, not independent filter dimensions. Use rela='DIRECT' to get only primary class assignments vs the larger INDIRECT (hierarchy-inferred) set.

| column | type | meaning |
|---|---|---|
| class_id | string | The drug-class identifier (the class side of the membership). Code SYSTEM depends on class_type: for ATC1-4 it is an ATC code — 1 char = anatomical main group (e.g. 'A','N','L','J','C'), 3 chars = therapeutic subgroup (e.g. 'L01'), 4-5 chars = deeper ATC level (no level-5 substance codes present), plus a pseudo-code '0' = the ATC root/'all drugs' bucket (3,112 members, the single largest class); for EPC and MOA it is an NDF-RT/NUI identifier of the form 'N0000175413'. 2,295 distinct classes. NOT unique on its own. |
| class_type | string | Which classification system the class belongs to. Exactly 3 values: 'ATC1-4' (WHO Anatomical Therapeutic Chemical, levels 1-4; 17,783 rows / 1,039 classes), 'EPC' (FDA Established Pharmacologic Class; 10,584 rows / 677 classes), 'MOA' (Mechanism of Action; 7,022 rows / 579 classes). Strictly functionally determined by class_id (one class_id maps to exactly one class_type) — it is a derived attribute, not part of the key. |
| rxcui | string | RxNorm Concept Unique Identifier of the drug (the drug side of the membership), stored as a numeric string e.g. '2555'=cisplatin. 4,159 distinct drugs. These are ingredient-level concepts (see rxnorm_tty: IN/PIN/MIN), the join key to other drug_silver price/identity tables. NOT unique on its own. |
| rxnorm_name | string | Human-readable RxNorm name of the rxcui, e.g. 'cisplatin', 'doxorubicin', 'dutasteride / tamsulosin'. Functionally determined by rxcui (1:1). Convenience label, not a key. |
| rxnorm_tty | string | RxNorm term type of the rxcui — the granularity of the drug concept. 3 values: 'IN' = ingredient (32,404 rows, the bulk), 'PIN' = precise ingredient (2,149), 'MIN' = multiple ingredient / combination drug (836). Determined by rxcui. Tells you the membership is at ingredient level, not packaged-product (NDC) level. |
| rela | string | Relationship directness of the drug-to-class link as reported by RxClass. 2 values: 'INDIRECT' (27,568) = the drug is assigned to the class via the RxNorm hierarchy/transitive relationship; 'DIRECT' (7,821) = the class is asserted directly on the drug. Functionally determined by (class_id, rxcui). Filter to rela='DIRECT' if you want only primary/explicit class assignments. |
| rela_source | string | Source pipeline RxClass used to derive the membership. 2 values, strictly determined by class_type: 'ATC' (17,783) for all ATC1-4 rows; 'DAILYMED' (17,606) for all EPC and MOA rows (FDA structured-product-label / DailyMed source). Redundant with class_type — not an independent dimension. |
| observed_period | date | Date the data was observed/snapshotted. Constant '2026-06-03' for every row (single snapshot). Provenance/bitemporal field, not analytical. |
| asserted_at | date | Date this assertion was recorded into the silver table. Constant '2026-06-03'. Provenance. |
| effective_period_start | date | Start of the bitemporal validity window. Constant '2026-06-03'. Provenance; despite being the profiler's 'time column', it carries no real history (1 distinct value). |
| effective_period_end | date | End of the validity window. Constant '2099-12-31' = open/current sentinel (this snapshot is the live one). Provenance. |
| cms_vintage_sequence | int | Vintage ordinal for this source. Constant 1 (only one vintage loaded). Provenance; would increment on a re-load. |
| parse_revision | int | Parser revision number. Constant 1. Provenance. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced these rows. Single constant value. Provenance/lineage. |
| source_url | string | Origin endpoint. Constant 'https://rxnav.nlm.nih.gov/REST/rxclass' (NIH/NLM RxNav RxClass REST API). Confirms the authoritative source. |
| sha256_content_hash | string | SHA-256 of the raw bronze source content. Single constant value (one source file). Provenance/dedup key. |
| source_id | string | Logical source identifier. Constant 'nih_rxclass'. Provenance; the profiler listed it in the 'unique key' only because it is constant (1 distinct), not because it discriminates rows. |
| is_current | boolean | Snapshot-currency flag. Constant true for ALL 35,389 rows; zero rows have is_current=false. The table holds only the current vintage — superseded snapshots are not retained here — so filtering on is_current is a no-op today (but keep it for forward compatibility). |
| bronze_object_key | string | S3 key of the bronze source object, e.g. 'drug/bronze/nih_rxclass/2026-06-03/...json'. Single constant value. Provenance; the profiler put it in the 'unique key' only because it is constant. |
| s3_version_id | string | S3 object version id of the bronze file. Single constant value. Provenance. |
| vintage_tag | string | Human vintage label, constant 'rxclass_2026-06-03'. Provenance; the readable handle for this snapshot. |
| drug_source_version_id | string | 100% NULL across all 35,389 rows (0 distinct). An unpopulated lineage passthrough. TRAP: the profiler included it in its 'verified unique key', but a column that is entirely NULL cannot be part of a real grain — ignore it. |

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