Pharmacy Data Model: Prescription & Dispensing Schema Guide

Why Pharmacy Data Is Uniquely Complex

Pharmacy data sits at the intersection of clinical care, regulatory compliance, and financial reconciliation. A data engineer building a pharmacy data warehouse must simultaneously handle NDC code normalization (the same drug arrives in three different formats from different sources), PBM adjudication (real-time at the point of dispensing through a chain involving pharmacy, PBM, and payer), DEA controlled substance compliance, and days supply validation (PBM feeds frequently contain clinically impossible values that corrupt adherence metrics).

This guide covers the core tables, reference data, and data quality logic for a production-grade pharmacy data warehouse on Snowflake or Databricks.

---

NDC Code: The Drug Identifier

The NDC code (National Drug Code) is the FDA's universal drug product identifier. It is an 11-digit number with three segments: labeler (manufacturer), product, and package. The format problem is that NDC codes appear in multiple forms across different systems:

Always normalize to 11-digit no-dash format at ingestion. This is the canonical form used in CMS reference files and drug crosswalks.

-- NDC normalization — BigQuery implementation
CREATE OR REPLACE FUNCTION util.normalize_ndc(raw_ndc STRING)
RETURNS STRING AS (
  CASE
    WHEN REGEXP_CONTAINS(REPLACE(raw_ndc, '-', ''), r'^\d{10,11}$')
      THEN LPAD(REPLACE(raw_ndc, '-', ''), 11, '0')
    ELSE NULL  -- return null for truly invalid NDCs
  END
);

-- Apply at ingestion — flag unnormalizable NDCs for review
SELECT
    raw_ndc,
    util.normalize_ndc(raw_ndc) AS ndc_11,
    COUNT(*) AS occurrences
FROM staging.pbm_claims_raw
GROUP BY raw_ndc, ndc_11
HAVING ndc_11 IS NULL
ORDER BY occurrences DESC;

---

Core Pharmacy Data Model

Drug Reference Table

The drug reference table is your foundational lookup. Load it from the FDA NDC directory (published weekly) and supplement with therapeutic class data from your formulary vendor.

CREATE TABLE pharmacy.drug_ref (
  ndc_11              VARCHAR(11)    NOT NULL,
  proprietary_nm      VARCHAR(255)   NOT NULL,
  nonproprietary_nm   VARCHAR(255),
  dosage_form         VARCHAR(50),
  route               VARCHAR(50),
  strength            VARCHAR(100),
  labeler_nm          VARCHAR(255),
  dea_schedule_cd     VARCHAR(5),                -- CII, CIII, CIV, CV, or null
  gpi_cd              VARCHAR(14),               -- Generic Product Identifier
  therapeutic_class   VARCHAR(100),
  is_generic_ind      BOOLEAN        DEFAULT FALSE,
  fda_approval_dt     DATE,
  market_dt           DATE,
  discontinued_dt     DATE,
  ndc_refresh_dt      DATE           NOT NULL,
  PRIMARY KEY (ndc_11)
);

Pharmacy Claims (Dispensing) Fact Table

CREATE TABLE pharmacy.rx_claim (
  rx_clm_id           VARCHAR(50)    NOT NULL,
  mbr_id              VARCHAR(50)    NOT NULL,
  pharmacy_npi        VARCHAR(10)    NOT NULL,
  prescriber_npi      VARCHAR(10)    NOT NULL,
  prescriber_dea_nbr  VARCHAR(9),               -- required for controlled substances
  ndc_11              VARCHAR(11)    NOT NULL,
  fill_dt             DATE           NOT NULL,
  written_dt          DATE,
  days_supply         SMALLINT       NOT NULL,
  qty_dispensed       NUMBER(10,3)   NOT NULL,
  refill_nbr          SMALLINT       DEFAULT 0,
  daw_cd              SMALLINT,                  -- Dispense As Written code (0-9)
  billed_amt          NUMBER(12,2)   NOT NULL,
  ingredient_cost     NUMBER(12,2),
  dispensing_fee      NUMBER(6,2),
  copay_amt           NUMBER(8,2),
  plan_paid_amt       NUMBER(12,2),
  clm_sts_cd          VARCHAR(20)    NOT NULL,   -- PAID, REVERSED, REJECTED
  reject_cd           VARCHAR(5),
  rx_nbr              VARCHAR(20),
  plan_id             VARCHAR(20)    NOT NULL,
  formulary_tier      SMALLINT,
  pbm_id              VARCHAR(20),
  PRIMARY KEY (rx_clm_id)
);

Prescription (Written Rx) Table

This is distinct from the dispensing fact. A prescription may be written but never filled, or filled multiple times as refills.

CREATE TABLE pharmacy.rx_prescription (
  rx_nbr              VARCHAR(20)    NOT NULL,
  mbr_id              VARCHAR(50)    NOT NULL,
  prescriber_npi      VARCHAR(10)    NOT NULL,
  prescriber_dea_nbr  VARCHAR(9),
  ndc_11              VARCHAR(11)    NOT NULL,
  written_dt          DATE           NOT NULL,
  qty_prescribed      NUMBER(10,3)   NOT NULL,
  days_supply_ord     SMALLINT       NOT NULL,
  refills_authorized  SMALLINT       DEFAULT 0,
  sig_text            VARCHAR(500),
  PRIMARY KEY (rx_nbr)
);

---

DEA Number Validation

The DEA number identifies prescribers authorized to prescribe controlled substances. Format: 2 letters + 7 digits. The 7th digit is a check digit: sum digits 1, 3, 5 of the numeric suffix; sum digits 2, 4, 6 and double; add both results; the last digit of the total must equal digit 7.

-- DEA check-digit validation (Snowflake)
CREATE OR REPLACE FUNCTION util.validate_dea(dea VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
  if (!/^[A-Za-z]{2}\d{7}$/.test(DEA)) return false;
  const n = DEA.slice(2);
  const sum = (parseInt(n[0]) + parseInt(n[2]) + parseInt(n[4]))
            + 2 * (parseInt(n[1]) + parseInt(n[3]) + parseInt(n[5]));
  return sum % 10 === parseInt(n[6]);
$$;

-- Flag invalid DEA numbers on controlled substance fills
SELECT
    r.rx_clm_id,
    r.prescriber_dea_nbr,
    r.ndc_11,
    d.dea_schedule_cd
FROM pharmacy.rx_claim r
JOIN pharmacy.drug_ref d USING (ndc_11)
WHERE d.dea_schedule_cd IS NOT NULL
  AND r.prescriber_dea_nbr IS NOT NULL
  AND NOT util.validate_dea(r.prescriber_dea_nbr);

---

Days Supply Validation and Medication Adherence

Days supply is critical for adherence analytics (PDC, MPR, HEDIS measures) — and one of the most commonly corrupted fields. PBM feeds contain values of 999, 0, and negative numbers. Build validation into ingestion.

-- Days supply outlier analysis by drug class
SELECT
    d.therapeutic_class,
    MIN(r.days_supply)     AS min_days,
    MAX(r.days_supply)     AS max_days,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY r.days_supply) AS p99_days,
    COUNT(*) FILTER (WHERE r.days_supply > 365) AS gt_365_count,
    COUNT(*) FILTER (WHERE r.days_supply <= 0)  AS invalid_count
FROM pharmacy.rx_claim r
JOIN pharmacy.drug_ref d USING (ndc_11)
WHERE r.clm_sts_cd = 'PAID'
GROUP BY d.therapeutic_class
ORDER BY gt_365_count DESC;

Standard caps by drug class: maintenance medications (antihypertensives, statins, diabetes agents) should be capped at 90 days supply per fill. Specialty drugs are typically 30 days. Any value above 365 should be treated as a data error and excluded from adherence calculations.

---

Medication Adherence (PDC) Calculation

Proportion of Days Covered (PDC) is the standard HEDIS adherence metric. It requires a timeline approach, not a simple average of days supply, because fills can overlap.

-- Build coverage timeline, then calculate PDC
WITH coverage AS (
    SELECT
        mbr_id,
        ndc_11,
        fill_dt                                    AS start_dt,
        DATEADD('day', days_supply - 1, fill_dt)   AS end_dt
    FROM pharmacy.rx_claim
    WHERE clm_sts_cd = 'PAID'
      AND fill_dt BETWEEN '2025-01-01' AND '2025-12-31'
),
covered_days AS (
    SELECT
        mbr_id,
        ndc_11,
        DATEDIFF('day',
            GREATEST(start_dt, '2025-01-01'),
            LEAST(end_dt,      '2025-12-31')
        ) + 1 AS days_covered
    FROM coverage
    WHERE end_dt >= '2025-01-01'
      AND start_dt <= '2025-12-31'
)
SELECT
    mbr_id,
    ndc_11,
    SUM(days_covered)                                  AS total_covered_days,
    365                                                AS measurement_period,
    ROUND(LEAST(SUM(days_covered), 365) / 365.0, 3)   AS pdc
FROM covered_days
GROUP BY mbr_id, ndc_11
ORDER BY pdc;

---

Frequently Asked Questions

What is the correct format for NDC codes in a data warehouse?

Always store NDC codes as 11-digit no-dash strings, left-padded with zeros. This is the canonical format used by the FDA NDC directory, CMS drug reference files, and NCPDP standards. Normalizing at ingestion prevents duplicate drug records caused by the same NDC appearing as 10-digit, 11-digit, and dash-formatted variants from different source systems.

What does the DAW code mean on a pharmacy claim?

DAW (Dispense As Written) is a single-digit code indicating whether the prescriber authorized generic substitution. DAW 0 means no instruction — the pharmacist may dispense a generic. DAW 1 means the prescriber specifically requested the brand product. DAW 2 means the patient requested brand. DAW codes 3–9 cover regulatory and pharmacy-specific reasons. This field matters for formulary analytics, generic substitution rates, and PBM contract performance.

How should I handle pharmacy claim reversals?

Store reversals as separate records with clm_sts_cd = 'REVERSED' linked to the original paid claim via rx_clm_id. For adherence calculations, exclude reversed fills from the coverage timeline. For financial reporting, net reversals against paid amounts rather than deleting original records. Pharmacy reversals are far more frequent than medical claim reversals because real-time adjudication errors are caught and corrected within days.

What is the difference between a prescription and a pharmacy claim?

A prescription (rx_prescription) is the written order from a prescriber authorizing a dispensing. A pharmacy claim (rx_claim) is the adjudicated record of a specific fill at a specific pharmacy on a specific date. One prescription generates multiple claims: the original fill plus each authorized refill. A prescription may also be written but never filled — these exist only in the prescription table, which makes linking them via rx_nbr essential for medication non-adherence analysis.