The provider directory archive, on Databricks

Reference architecture

One ingest, two paths. The audit runs on BigQuery and publishes to the web. The archive runs on Databricks and publishes to consumers. They share the ingest and nothing else, which is why the MCP server keeps answering when the warehouse is asleep.

What is in it

Six tables, one per FHIR resource type, each partitioned by release_date. The current CMS release is 2026-08-20.

Each row keeps the full FHIR resource as JSON alongside flattened columns for the fields worth querying directly, so a question the flattening did not anticipate is still answerable from the same row.

Get it

Two ways in, and the second needs no Databricks account at all.

From Databricks Marketplace

Find CMS National Provider Directory: Release Archive, take Get instant access, and the tables mount into a catalog in your own workspace. Then query them like any other table.

SELECT release_date, count(*) AS practitioners
FROM ainpi_release_archive.ainpi.practitioner
GROUP BY release_date
ORDER BY release_date;

Without a Databricks account

The share is open Delta Sharing, so any client that speaks the protocol can read it from a credential file. Request one at gene@fhiriq.com.

pip install delta-sharing

import delta_sharing
url = "ainpi.share#ainpi-ndh-archive.ainpi.practitioner"
df = delta_sharing.load_as_pandas(url, limit=1000)

Filter on release_date before anything else. Partition pruning is a hint to the client, not a guarantee from the server, so a query without it can pull every release when you wanted one.

Read this before you write a diff

CMS regenerates Endpoint and Location resource ids on every export. Join two releases on id and you get 100% churn every time, which is an artifact of id minting rather than anything that happened in the world.

• Practitioner ids are stable, at 100% across releases, because the id is derived from the NPI. Stable ids do not mean unchanged records: of 20,000 practitioners present in two releases, zero were byte-identical.
• Endpoint rejoins on address at 100.0%. Use that, not the id.
• Location has no reliable cross-release key. The best composite matches 73.5% after normalising case and punctuation. Any Location diff has to state its match rate.

The table comments carry these warnings too, so they travel with the data rather than living only here. Full method in the methodology.