From raw claims to RAF: what the data pipeline actually looks like

A patient’s RAF score is a number between roughly 0.2 and 10 that CMS uses to determine how much money a Medicare Advantage plan receives for that patient per year. The higher the score, the more complex and expensive the patient is expected to be.

Getting from a raw claims file to a meaningful RAF score involves multiple transformation layers. Here’s what each one actually does.

Layer 1: Raw claims ingestion

Raw Medicare claims come in 837 format (EDI transactions) or as CMS standard files for FFS data. The fields that matter for RAF are:

diagnosis_code_1 through diagnosis_code_25 (ICD-10-CM codes)
claim_type (professional vs. institutional)
service_start_date and service_end_date
patient_id
rendering_provider_npi
bill_type_code (for institutional claims)

In this project, the raw data is 167k synthetic Medicare medical claims and 2.3k eligibility rows, loaded from S3 into DuckDB using the httpfs extension:

INSTALL httpfs;
LOAD httpfs;

CREATE TABLE raw_medical_claims AS
SELECT * FROM read_parquet('s3://bucket/claims/*.parquet');

Layer 2: Tuva Core mart — normalization

The Tuva Core mart normalizes claims into a consistent structure:

Encounter grouping: professional claim lines grouped into encounters by patient, provider, and service date proximity
Claim type classification: institutional claims sub-typed as inpatient, outpatient, or skilled nursing based on revenue codes and bill type
Diagnosis unpivoting: ICD-10 codes extracted from the 25-column wide format into long format — one row per diagnosis code per claim

The output is core__medical_claim, core__patient, and core__condition. The core__condition table is the one that feeds RAF — it contains one row per (patient, diagnosis_code, service_date) combination.

Layer 3: ICD-10 to HCC mapping

CMS publishes an annual crosswalk from ICD-10-CM codes to HCC categories. Tuva includes the v28 mapping as a seed table. The join is straightforward:

SELECT
    c.patient_id,
    c.diagnosis_code,
    h.hcc_code,
    h.hcc_description,
    h.hcc_coefficient
FROM core__condition c
INNER JOIN hcc_mapping h
    ON c.diagnosis_code = h.diagnosis_code
WHERE EXTRACT(YEAR FROM c.service_date) = EXTRACT(YEAR FROM CURRENT_DATE)

The complication is the “hierarchical” rule: within each disease hierarchy group, only the most specific (highest-severity) HCC counts. Tuva handles this with a ROW_NUMBER() window function partitioned by patient and hierarchy group.

Layer 4: RAF score calculation

Each HCC category has an age-sex-specific coefficient from CMS. The patient’s RAF score is the demographic base plus the sum of coefficients for their active HCCs.

For CMS HCC model v28, a simplified calculation:

RAF = demographic_base + SUM(active_hcc_coefficients)

Where demographic_base for a 72-year-old male community-dwelling patient is approximately 0.31.

Example:

Demographic base: 0.31
HCC 85 (congestive heart failure): +0.32
HCC 22 (morbid obesity): +0.27
HCC 136 (unsepcified renal failure): +0.18

RAF = 0.31 + 0.32 + 0.27 + 0.18 = 1.08

This 1.08 is multiplied by the county-level benchmark (around $900-$1200/month for Medicare Advantage) to produce the monthly capitation payment.

Layer 5: HCC suspecting and recapture

The RAF calculation above is based only on diagnoses that have appeared in current-year claims. The suspecting layer adds a forward-looking dimension: which HCCs from prior year haven’t appeared yet this year?

The hcc_suspecting__summary table computes suspecting_gaps per patient — the count of HCCs documented in prior year that are absent from current-year claims so far. The hcc_recapture mart tracks which of those gaps get closed over time.

On the 167k claim synthetic dataset, running from raw ingestion through seeds and the full Tuva pipeline to the suspecting mart takes approximately 3 minutes on a mid-range laptop with DuckDB 1.10.

What the full pipeline looks like in dbt

The dbt DAG has this structure:

raw sources
    → staging (light renaming and typing)
        → core__* (encounter grouping, claim typing, normalization)
            → hcc_mapping seed join
                → hcc_suspecting__* (gap analysis)
                    → hcc_recapture__* (year-over-year closure tracking)

Each layer is a set of dbt models. The total model count for Tuva 0.17.2 is 879. On a clean run, 875 pass and 4 fail due to DuckDB 1.10 assertion errors in the recapture models.

The output data products

After a successful run, the analytics layer has:

Patient-level RAF estimates from the HCC suspecting summary
Suspecting gap counts per patient for outreach prioritization
HEDIS quality measure rates at the population level
Per-member-per-month spend summaries for financial analysis
DQI fill rates to assess input data quality

These are the tables the Streamlit analytics app and the scikit-learn risk model sit on top of.