CMS Medicare
claims analytics.

Hospital charges vs Medicare reimbursement.

Medicare publishes provider-level charge and payment data — but raw, in 10 separate yearly tables, with inconsistent column names between inpatient and outpatient settings. To analyze the cost-vs-payment gap — what hospitals bill compared to what's actually paid — you need to harmonize the schemas, parse procedure codes from natural-language strings, and join against a clean dimensional model.

Without that scaffolding, every analytical question becomes a one-off SQL adventure.

dbt-bigquery, Kimball-style.

Built a 13-model dbt project with the standard staging → intermediate → marts layering, plus a star schema (4 dimensions + 1 fact) under the marts layer. All transformations are SQL-only, materialized to BigQuery tables for fast dashboard reads.

-- fact_provider_procedure_year.sql
-- Generates surrogate keys matching the dim formulas — joins line up by hash equality.
SELECT
  {{ dbt_utils.generate_surrogate_key(['s.provider_id']) }}                  AS provider_sk,
  {{ dbt_utils.generate_surrogate_key(['s.procedure_code', 's.setting']) }}  AS procedure_sk,
  {{ dbt_utils.generate_surrogate_key(['s.provider_state']) }}               AS geo_sk,
  s.year, s.total_services, s.avg_covered_charges, s.avg_total_payments,
  {{ cost_payment_ratio('s.avg_covered_charges', 's.avg_total_payments') }} AS cost_payment_ratio
FROM {{ ref('int_provider_procedure_unioned') }} s;

• Sources: 10 public CMS Medicare tables (inpatient + outpatient × 5 years, ~1.1M fact rows after union)
• Staging unions years and normalizes column names; intermediate parses the embedded procedure codes
• Tests: surrogate-key uniqueness, referential integrity to dims, no-negative-charges, ratio sanity (64 tests total, all green)
• CI: GitHub Actions runs dbt build on every push

The gap, quantified.

Hospitals charge an average 3.92× what Medicare pays — and that ratio varies dramatically by procedure (outpatient APC 0336 tops the list at 8.52×) and by state (New Jersey at 6.30×, with Nevada and Florida close behind). The dashboard at the bottom of the portfolio homepage embeds the full Looker Studio report.

The whole pipeline ships as a clean dbt project — lineage docs, tests, CI all in place. Code in the repo below.

Stack.

References.