Extracting 817,000 Rows of Hospital Data from an API That Doesn't Want You To

Every hospital in the United States has a star rating from CMS — the Centers for Medicare & Medicaid Services. One to five stars. You've probably seen them. They show up on hospital websites, in news articles, in the quiet panic of someone Googling "best hospital near me" at 2 AM.

What most people don't know is that the methodology behind those ratings is publicly documented. Five domains — mortality, safety, readmission, patient experience, and timely & effective care — each scored from public data, weighted, z-scored, and clustered into star buckets using k-means. It's not a black box. It's a transparent box that nobody opens because the documentation is 47 pages of statistical methodology written by people who think "winsorized z-score" is a phrase that needs no introduction.

I decided to open it. Extract the raw data from CMS's public API, land it in a data lake, transform it through a warehouse, and score it myself. Then compare my stars to CMS's published stars and see how close I can get.

This is the first post in a series covering the full pipeline. This one's about the unglamorous beginning: getting 817,000 rows of healthcare data out of a government API and into a place where I can actually work with it.

The Data Source

CMS publishes hospital quality data through the Provider Data Catalog, a public REST API with no authentication. Eleven datasets cover everything from infection rates to patient survey responses to death rates. They range from 2,455 rows (HVBP summary tables) to 325,652 rows (HCAHPS patient surveys). All told, about 817,000 rows of data describing the performance of roughly 5,400 US hospitals.

The first six are scoring inputs. The last five are validation datasets — CMS's own computed scores that I'll use later to check my work. This distinction matters: if you accidentally include the validation data in your scoring pipeline, you're not computing star ratings, you're plagiarizing them.

The API That Moved

The first lesson CMS teaches you is humility. I scaffolded my extraction code against the documented API endpoint, pressed run, and got 404s across the board.

CMS had migrated their API. The old endpoint was dead. The Socrata-style endpoint returned 410 Gone. No deprecation notice, no redirect, no "hey, we moved." Just a 404 and the quiet assumption that you'd figure it out.

The new endpoint is:

https://data.cms.gov/provider-data/api/1/datastore/query/{uuid}/0

Key differences from the old API: - Pagination parameter changed from size to limit - Response is {"results": [...], "count": N} instead of a bare JSON array - The /0 suffix refers to the first distribution of the dataset - Maximum page size is somewhere between 1,000 and 2,000 (request 5,000 and you get a 400)

The UUIDs are still valid. Every dataset has a stable identifier like 77hc-ibv8 (HAI) or dgck-syfz (HCAHPS). These don't change across API versions, which is the only thing that saved this from being a complete rewrite.

Lesson: Government APIs change without notice. The data.json DCAT catalog at data.cms.gov/data.json is the authoritative source for discovering current endpoints — not blog posts, not Stack Overflow answers, and definitely not whatever you bookmarked six months ago.

The Extraction Client

The extraction code is deliberately simple. httpx for HTTP, manual pagination, and a content hash for change detection:

CMS_API_BASE = "https://data.cms.gov/provider-data/api/1/datastore/query"
PAGE_SIZE = 1000

def fetch_dataset(uuid: str, client: httpx.Client) -> list[dict]:
    all_rows: list[dict] = []
    offset = 0

    while True:
        url = f"{CMS_API_BASE}/{uuid}/0"
        resp = client.get(url, params={"limit": PAGE_SIZE, "offset": offset})
        resp.raise_for_status()
        page = resp.json().get("results", [])

        if not page:
            break

        all_rows.extend(page)
        offset += PAGE_SIZE

    return all_rows

No async. No parallelism. No retry library. Each API call takes about a second, and with 817 pages total, the whole extraction finishes in roughly 15 minutes. It's not fast, but it runs once and I have other things to do.

The one piece of cleverness is the content hash. Before writing anything to storage, I hash the entire dataset and compare it against the last extraction:

def content_hash(rows: list[dict]) -> str:
    h = hashlib.sha256()
    for row in rows:
        h.update(json.dumps(row, sort_keys=True).encode())
    return h.hexdigest()

If the hash matches, skip the write. CMS updates their data quarterly, so most runs find nothing new. This keeps the bronze layer clean — you don't end up with 90 identical copies of the same data just because you ran the pipeline every day.

That row-by-row hashing isn't how I wrote it the first time. The original version serialized the entire dataset into a single JSON string, then hashed it. This works fine for 2,455-row datasets and not at all for 325,652-row HCAHPS. The process ate 780 MB of RAM and got OOM-killed. Streaming the hash row by row dropped peak memory from 780 MB to 280 MB. Same hash semantics (deterministic via sort_keys), different hash values (which means one forced re-extraction after the fix), and no more death by serialization.

The Bronze Layer

Extracted data lands in MinIO — a self-hosted, S3-compatible object store running on my app server. The bucket structure mirrors the pagination:

cms-bronze/
  bronze/
    hai/
      20260315T153940Z/
        page_000.json
        page_001.json
        ...
        page_172.json
        _metadata.json
      _latest_hash.json
    hcahps/
      20260315T154215Z/
        page_000.json
        ...

Each extraction gets a timestamped prefix. Data is immutable — I never overwrite or modify a previous extraction. If CMS updates their data, a new timestamped directory appears next to the old one. This is the "bronze" in the bronze/silver/gold medallion architecture: raw data, exactly as the API returned it, with just enough metadata to reconstruct what happened.

The _metadata.json file records the extraction timestamp, row count, page count, and content hash. The _latest_hash.json file at the dataset root is the change detection pointer — it's what the next extraction run checks before deciding whether to proceed.

Why MinIO instead of just writing files to disk? Two reasons. First, S3 semantics are industry-standard — if this ever moves off my homelab, the extraction code doesn't change. Second, object storage forces immutability by convention. There's no sed -i on an S3 object. You can overwrite it, but you have to be intentional about it. Disk files invite casual mutation.

Loading the Warehouse

The bronze layer stores raw JSON. That's great for auditability but useless for analysis. The staging load reads the latest extraction from MinIO and bulk-loads it into PostgreSQL:

def _bulk_load(conn, table_name, columns, rows):
    buf = StringIO()
    for row in rows:
        values = []
        for col in columns:
            val = str(row.get(col, ""))
            val = val.replace("\\", "\\\\").replace("\t", "\\t").replace("\n", "\\n")
            values.append(val)
        buf.write("\t".join(values) + "\n")

    buf.seek(0)
    with conn.cursor() as cur:
        with cur.copy(f'COPY staging."{table_name}" ({quoted_cols}) FROM STDIN') as copy:
            while data := buf.read(8192):
                copy.write(data.encode("utf-8"))

Every column is TEXT. No type casting, no null handling, no transformation. The staging tables are a faithful reproduction of what CMS sent — all the garbage included.

This is intentional. The CMS API returns everything as strings, and those strings contain a rich taxonomy of nonsense that I don't want to deal with at load time:

Five different representations of "we don't have this data," plus categorical labels hiding in numeric columns. If I tried to cast types at load time, I'd be playing whack-a-mole with every new variant CMS invents. Instead, the staging layer stores everything as-is, and a dbt transformation layer handles the cleanup with macros that are designed to expect this chaos.

The key macro is safe_numeric, which uses a regex gate instead of trying to enumerate every possible non-numeric value:

{% macro safe_numeric(column) -%}
    case when {{ column }} ~ '^-?[0-9]*\.?[0-9]+$'
         then {{ column }}::numeric
    end
{%- endmacro %}

If it looks like a number, cast it. If it doesn't, it's NULL. No exceptions, no "but what about," no nullif chain that's always one sentinel value behind.

What I Learned About Government Data

A few observations after ingesting 817,000 rows of CMS data:

Everything is a string. Every value. The API returns "5426" not 5426, "true" not true, "2024-01-01" not an actual date. This is load-bearing laziness — it means CMS can put anything in any field, and they do.

"Not Available" and "Not Applicable" mean different things. The first means "we don't have this data." The second means "this metric doesn't apply to this hospital" — maternity care scores for hospitals that don't deliver babies, for instance. If you NULL them both without preserving the distinction, you'll corrupt your scoring downstream.

Facility IDs are not integers. They're zero-padded six-character codes like 010001 where the first two digits encode the state. Cast them to int and you lose the leading zeros and the embedded structure. They're identifiers, not quantities.

Wide tables and tall tables don't mix. Five of the eleven datasets use a tall format — one row per hospital per measure, with a measure_id column. Four use a wide format — one row per hospital with dozens of measure-specific columns. You can't UNION ALL these shapes together without an unpivot step, which is why the HVBP tables need their own transformation path.

PostgreSQL silently truncates column names. The 63-character identifier limit means unweighted_normalized_efficiency_and_cost_reduction_domain_score quietly becomes unweighted_normalized_efficiency_and_cost_reduction_domain_scor. No warning, no error. Just a silent truncation that you'll discover when your column alias doesn't match anything.

The Stack So Far

The extraction and staging load are each a single Prefect flow with one task per dataset. Prefect gives me @task(retries=3, retry_delay_seconds=30) for free — if a CMS API call fails mid-extraction, the task retries from the top. The flow-level orchestration is sequential (no reason to slam a government API with parallel requests), and the whole thing runs from a single python -m extract.extract_all invocation.

What's Next

The data is in the warehouse. Every column is a string. There are five flavors of NULL. The numbers are hiding behind text labels. This is where dbt enters the picture.

The next post covers the transformation layer: staging views that tame the chaos, a star schema with dim_hospital, dim_measure, and fact_hospital_measure, and the question of how you UNION ALL five datasets with different schemas into a single fact table without losing your mind.

This is part 1 of a series on rebuilding CMS's 5-Star Hospital Quality Rating from scratch.