Data Model

RxNorm drug hierarchy enriched with engineered therapy behavior classification. Therapy attributes are derived at the clinical drug form level and propagated to all strength and branded variants. Primary drug table for most analytics — it combines drug hierarchy with therapy context in a single row.

RxNorm ingredient concepts (TTY = IN or MIN). Anchor for ATC classification and ingredient-level economics.

ICD-10-CM diagnosis code reference with full classification hierarchy from chapter down to the most specific subcategory. Covers all codes including non-billable parent categories. Filter is_billable = true to restrict to codes that can appear on medical or pharmacy claims.

WHO Anatomical Therapeutic Chemical (ATC) classification system. All five levels of the hierarchy are denormalized onto each row. Level 5 is the individual drug substance level; levels 1–4 represent progressively broader therapeutic groupings.

Crosswalk between ATC level-5 drug classification codes and RxNorm ingredient identifiers. One ingredient may map to multiple ATC codes (e.g., a drug with multiple approved therapeutic uses) and one ATC code may map to multiple ingredient RxCUIs (e.g., salt variants of the same molecule).

Additional ingredient-to-ATC mappings sourced from Health Canada's Drug Product Database, which classifies every marketed product with a WHO ATC code natively. Covers ingredients the primary crosswalk leaves unclassified, most often where the primary source binds the ATC code to a salt or ester concept while RxNorm builds its drug products on the base ingredient.

Drug-level ATC classification: the resolved therapeutic class for each drug product, combining the ingredient-based crosswalk with product-level classifications from Health Canada. This is the recommended starting point for ATC lookups because it is the only table that classifies combination products, which the WHO assigns a single dedicated combination code rather than one code per ingredient.

Multi-source NDC-to-drug_id resolution. Combines RxNav ndc2rxcui, FDA NDC directory, and supplemental manual overrides. source_rank encodes provenance confidence.

Every NDC TwinFyRx has ever mapped, including NDCs whose drug concept RxNorm has since retired. ndc_crosswalk is the active subset. Rows are never removed, so historical pharmacy claims still join to a drug.

Drug concepts that historical NDCs point at but that are no longer in drug_profile. Same hierarchy columns as drug_profile, plus why the concept left and its RxNorm replacement.

CDC vaccine codes (CVX) — the code set every US immunization information system (IIS) uses to record what was administered. Refreshed from the CDC IIS standards files.

Vaccine NDCs from the CDC IIS lookup files, each with its CVX code and, where it resolves, the TwinFyRx drug_id. Covers both the carton sold (unit of sale) and the vial or syringe administered (unit of use), including new-season products before RxNorm lists them.

CVX codes for current drug_profile products — the bridge between prescription/medical claims (drug_id, NDC) and immunization records (CVX).

Estimated share of each drug's prescriptions written for each ICD-10-CM diagnosis code. Each row reads as P(diagnosis code | a fill of this drug) — e.g. finasteride 1 mg: L64.9 androgenic alopecia 96%; finasteride 5 mg: N40.1 BPH 87%. Derived from the indication layer: each indication's share goes to the ICD-10 code(s) of its primary condition — or, when the primary condition has no code of its own (a procedure or a broad class), to the code of what the drug treats (chemotherapy-induced nausea and vomiting → R11.2). Covers chronic-use and specialty drugs plus the top 300 clinical drug forms by US prescription volume.

Estimated share of each drug's prescriptions written for each TwinFyRx Indication. Each row reads as P(indication | a fill of this drug) — e.g. finasteride 1 mg: male pattern hair loss 91%; finasteride 5 mg: benign prostatic hyperplasia 77%. The entry point of the indication layer: drug_id (or an NDC via ndc_crosswalk) → drug_indication → indication → indication_condition.

The TwinFyRx Indication vocabulary — indications as analysts group a market (e.g. 'Stroke prevention in nonvalvular atrial fibrillation'), independent of any drug. Each indication records what the patient has (primary_condition), how the drug relates to it (relationship), what the drug acts on (target), any qualifier, and a parent for roll-ups.

The conditions each indication implies the patient has, with their ICD-10-CM codes and condition-layer mapping. The primary condition is the diagnosis on the claim for the fill; associated conditions are other conditions the use implies (e.g. dyslipidemia in chronic kidney disease: primary hyperlipidemia E78.5, associated chronic kidney disease N18.9). When the primary condition has no code of its own (a procedure or a broad class), a target row carries the code the claim uses for what the drug treats (chemotherapy-induced nausea and vomiting: primary cancer receiving emetogenic chemotherapy, class; target R11.2).

TwinFyRx condition layer: clinically coherent patient conditions built on AHRQ's Clinical Classifications Software Refined (CCSR), with categories merged or split where CCSR groups conditions a prescriber would treat differently, plus five broad classes (cancer of any site, atherosclerotic cardiovascular disease, bacterial infection, chronic pain, psychotic or bipolar disorder on antipsychotic therapy).

ICD-10-CM → condition crosswalk. Every ICD-10-CM code, billable and category (header) codes, assigned to exactly one condition — the join from a diagnosis on a medical claim to the condition layer.

Weekly CMS NADAC rates per NDC. National average drug acquisition cost for retail pharmacy — what pharmacies actually pay. Sample contains the most recent price per NDC.

Quarterly Medicare Part B ASP+6% payment limits per HCPCS code. Used for medical-channel drug cost benchmarking (physician-administered biologics, injectables).

CMS crosswalk mapping NDC-level package data to HCPCS codes used in Medicare Part B ASP pricing. Supports unit conversion between pharmacy packages and billing units.

HCPCS J-code to RxNorm product crosswalk for Medicare Part B drug billing codes. Resolves each HCPCS code to its corresponding RxNorm SCD/SBD concept, enabling joins between ASP payment limits and the drug intelligence pipeline.

Deterministic package size and billing unit normalization for every NDC11 with FDA package data. Parses free-text package descriptions into structured total_quantity and billing_unit fields.

NDC-level cost economics. Combines NADAC acquisition cost and ASP Part B payment limits with package normalization to yield price per billing unit and price per package.

NDC-level therapy cost mart adding WHO DDD-derived daily, 30-day, and 90-day cost estimates to mart_ndc_economics (WHO ATC/DDD Index 2026). Only applicable where a meaningful daily dose can be derived.

Drug-level cost aggregates. Min/median/max cost statistics across all NDCs; daily, 30-day, and 90-day therapy cost where dose is derivable.

Ingredient-level cost rollup across all clinical drug forms for the ingredient. Useful for class benchmarking where brand/generic distinctions are unimportant.

ATC 4th-level therapeutic class benchmarks. 30-day cost min/median/max across drugs in the class with applicable therapy economics.

Drug-level Medicare Part D formulary restriction prevalence. Captures prior auth, step therapy, quantity limit, and tier placement across all covering plans for a given year.

Composite payer access barrier score (0–1) per drug and year. Weighted combination of PA prevalence (35%), step therapy (25%), quantity limits (15%), tier position (10%), and non-coverage rate (15%).

Medicare Part D and MA-PD plan dimension. One row per plan year × CMS contract × plan benefit package, with sponsor, marketing name, plan type, and market segment.

PBM attribution derived from observed Medicare plan artifacts (BIN/PCN routing, plan documents). Captures the primary PBM managing pharmacy benefits for a plan/year with supporting evidence.

Historical PBM attribution per Medicare contract/PBP across all CMS plan years. Built from Medicare plan artifact inference (primary) and BIN/PCN backfill (secondary). 96.9% attribution coverage.

Pharmacy claim routing identifiers (RxBIN, RxPCN, RxGroup, M3P overrides) per Medicare plan/year. Use to route claims data to the correct plan and PBM.

US pharmacy roster filtered from NPPES organizations with pharmacy taxonomy and CMS Part D network presence. Includes chain attribution, PSAO affiliation, type classification, and geocoded location.

Marketed human drug products from the Health Canada Drug Product Database (DPD), each crosswalked to the canonical RxNorm drug_id where an equivalent exists. Products with no RxNorm equivalent at their marketed strength resolve instead to a stable Canada-only identifier. See match_method for how each row was resolved.

Canada-only drug strengths: an ingredient + dose form that exists in RxNorm, at a strength RxNorm carries no product for. Gives those products a stable identifier so they can be grouped and joined even though no RxCUI exists.

Drug to risk-adjustment condition category. Answers: given a dispensed drug, which CMS-HCC or RxHCC categories does it point at, how much of the drug's prescription volume supports each one, and how much confidence that carries. The starting point for pharmacy-based diagnosis suspecting: a member fills the drug, no diagnosis in the category is on file this year, and confidence_tier says how strong a suspect that is.

CMS's name for each risk-adjustment condition category in CMS-HCC V24, CMS-HCC V28 and RxHCC V08. Names any category number carried on icd10 (hcc_v24, hcc_v28, rxhcc_v08) or drug_hcc_suspect (cc).

CMS's hierarchies among condition categories in CMS-HCC V24, CMS-HCC V28 and RxHCC V08. Answers: when a member already has a more severe category on record, which less severe categories no longer count? Diagnosis suspecting uses it so a category covered by a higher one is not listed as a gap.

One row per Medicare Part D prescriber: who they are (name, location, best-available specialty), how much they prescribe, how broad their prescribing is, and where they rank by volume.

Prescribing volume per prescriber in each WHO ATC class at levels 2, 3 and 4, with the prescriber's rank among everyone who prescribes the class. Answers: who are the top prescribers of a class, and what does a given prescriber's class mix look like.

Prescribing volume per prescriber per ingredient (RxNorm IN / MIN), with the prescriber's rank among everyone who prescribes the ingredient.

Who prescribes each ATC class: prescribers, claims and share of the class's claims by CMS prescriber type.