HRSA Healthcare Resources Dataset

About the Dataset

The HRSA Healthcare Resources Dataset provides comprehensive, county-level access to US healthcare workforce, shortage area designations, and health center locations from the Health Resources and Services Administration. Four government datasets are combined into a single database: physician and nursing workforce counts (AHRF), medically underserved area designations (MUA/MUP), health professional shortage area scores (HPSA), and 14,000+ federally qualified health center sites.

Get Full Access | Snowflake Marketplace | Free Trial

Quick Access

Tables: AHRF, SHORTAGE_DESIGNATIONS, HPSA, HEALTH_CENTER_SITES Sources: 4 government data sources from data.hrsa.gov Update Frequency: AHRF annual, Shortage Designations monthly, HPSA and Health Center Sites quarterly Geography: All 3,200+ US counties

Overview

The HRSA Healthcare Resources Dataset provides comprehensive access to US healthcare workforce and shortage data including:

• AHRF (Area Health Resources Files) - Physician and nursing workforce counts with per-100k population rates by county

• SHORTAGE_DESIGNATIONS - Medically Underserved Areas and Populations (MUA/MUP) designations by county

• HPSA - Health Professional Shortage Area scores for Primary Care, Dental, and Mental Health by county

• HEALTH_CENTER_SITES - FQHC and Look-Alike service delivery site locations with addresses and coordinates

Metadata Tables

Every Dataplex data product includes these standard metadata tables:

Table	Purpose
FEEDS	Dataset catalog — available tables, descriptions, update dates
FEEDS_FILES	Batch load history with is_latest flag for data freshness
CHANGELOG	Change log — data loads, schema changes, corrections
DATA_DICTIONARY	Column descriptions for all tables

Entity Relationship Diagram

All four data tables join on fips_county for cross-source county-level analysis. Each table links to FEEDS and FEEDS_FILES via feed_id and feeds_files_id for data lineage and freshness tracking.

Data Tables

AHRF (Area Health Resources Files)

Health workforce data by US county — physicians, nurses, and population with per-100k rates. Unpivoted to latest and previous data periods for trend analysis.

Key Features:

• ~3,235 rows per vintage (one per US county)

• Physician and nursing counts with population-adjusted rates

• Latest and previous data years for year-over-year comparison

• Annual updates from HRSA (~Q1 each year)

Column Reference

Column	Type	Description
fips_county	VARCHAR	Five-digit Federal county code (state FIPS + county FIPS)
county_name	VARCHAR	County name with state abbreviation (e.g., New York, NY)
state_name	VARCHAR	Full state name
state_abbrev	VARCHAR	Two-letter USPS state abbreviation
fips_state	VARCHAR	Two-digit state FIPS code
vintage_year	VARCHAR	AHRF release identifier (e.g., '2024-2025'). First four digits = latest data year in that release.
data_year	NUMBER	Calendar year for this data point (e.g., 2024). Query WHERE data_year = 2024 for specific years.
total_mds	NUMBER	Non-federal physicians (MDs) in the county
rn_full_time	NUMBER	Full-time registered nurses
rn_part_time	NUMBER	Part-time registered nurses
rn_total	NUMBER	Total registered nurses (full-time + part-time)
lpnlvn_full_time	NUMBER	Full-time licensed practical/vocational nurses
lpnlvn_part_time	NUMBER	Part-time licensed practical/vocational nurses
lpnlvn_total	NUMBER	Total LPN/LVNs (full-time + part-time)
nursing_workforce_total	NUMBER	All nurses: RN + LPN/LVN, full and part-time
population	NUMBER	County population estimate (NULL for some territories)
mds_per_100k	NUMBER	Physicians per 100,000 population
nurses_per_100k	NUMBER	Total nursing workforce per 100,000 population
feed_id	VARCHAR	FK to FEEDS — identifies which dataset this row belongs to
feeds_files_id	VARCHAR	FK to FEEDS_FILES — identifies which batch loaded this data
created_at	TIMESTAMP	When the source data was loaded into the warehouse
updated_at	TIMESTAMP	When the table was last rebuilt

SHORTAGE_DESIGNATIONS (MUA/MUP)

Medically Underserved Areas and Populations by county — federal shortage area designations with population counts.

Key Features:

• ~3,200 rows (one per US county)

• MUA/MUP designation counts and affected population

• Derived from HRSA shapefiles with geospatial county aggregation

• Monthly updates from HRSA

Column Reference

Column	Type	Description
fips_county	VARCHAR	Five-digit Federal county code
mua_designation_count	NUMBER	Number of MUA/MUP designations in the county
mua_population	NUMBER	Population in Medically Underserved Areas within the county
feed_id	VARCHAR	FK to FEEDS — identifies which dataset this row belongs to
feeds_files_id	VARCHAR	FK to FEEDS_FILES — identifies which batch loaded this data
created_at	TIMESTAMP	When the source data was loaded into the warehouse
updated_at	TIMESTAMP	When the table was last rebuilt

HPSA (Health Professional Shortage Areas)

Health Professional Shortage Areas by county and discipline — Primary Care, Dental, and Mental Health shortage scores aggregated to county level.

Key Features:

• ~18,000 designation records across 3 disciplines

• HPSA scores (0-26 scale, higher = more severe shortage)

• Provider shortage estimates (additional providers needed)

• Quarterly updates from HRSA with snapshot-based history

Column Reference

Column	Type	Description
fips_county	VARCHAR	Five-digit Federal county code
county_name	VARCHAR	County name
state_name	VARCHAR	Full state name
state_abbrev	VARCHAR	Two-letter USPS state abbreviation
discipline	VARCHAR	HPSA discipline: PRIMARY_CARE, DENTAL, or MENTAL_HEALTH
snapshot_month	VARCHAR	Month this data snapshot was captured (e.g., '2026-02'). Use for trend analysis across time periods.
hpsa_score	NUMBER	Worst (highest) HPSA score in this county for this discipline (0-26 scale, higher = more severe shortage)
designation_count	NUMBER	Number of active HPSA designations in this county for this discipline
designated_population	NUMBER	Total population in designated shortage areas within this county
underserved_population	NUMBER	Estimated underserved population in this county for this discipline
provider_shortage	NUMBER	Number of additional providers needed to remove the shortage designation
last_designation_update	DATE	Most recent designation update date for this county and discipline
feed_id	VARCHAR	FK to FEEDS — identifies which dataset this row belongs to
feeds_files_id	VARCHAR	FK to FEEDS_FILES — identifies which batch loaded this data
created_at	TIMESTAMP	When the source data was loaded into the warehouse
updated_at	TIMESTAMP	When the table was last rebuilt

HEALTH_CENTER_SITES (FQHC and Look-Alike Sites)

FQHC and Look-Alike service delivery sites — individual facility locations with addresses, coordinates, and site types.

Key Features:

• ~14,000 individual facility locations

• Addresses, lat/long coordinates, and operating hours

• Site status and health center type classification

• Quarterly updates with snapshot-based history

Column Reference

Column	Type	Description
site_key	VARCHAR	Site identifier (MD5 hash of organization + site name + address)
health_center_type	VARCHAR	Type of health center (e.g., Community Health Center, Migrant Health Center, Look-Alike)
health_center_name	VARCHAR	Organization name operating this site
site_name	VARCHAR	Name of this service delivery site
site_address	VARCHAR	Street address (suppressed for domestic violence shelters)
site_city	VARCHAR	City where the site is located
state_abbrev	VARCHAR	Two-letter USPS state abbreviation
zip_code	VARCHAR	ZIP code (suppressed for domestic violence shelters)
phone	VARCHAR	Site telephone number
operating_hours_per_week	NUMBER	Hours per week the site is open for patient visits
site_status	VARCHAR	Current site status (e.g., Active, Inactive)
fips_county	VARCHAR	Five-digit Federal county code — join key to AHRF, HPSA, and other county-level tables
county_name	VARCHAR	County name
state_name	VARCHAR	Full state name
longitude	NUMBER	Site longitude (WGS84)
latitude	NUMBER	Site latitude (WGS84)
snapshot_month	VARCHAR	Month this data snapshot was captured (e.g., '2026-02'). Use for trend analysis across time periods.
feed_id	VARCHAR	FK to FEEDS — identifies which dataset this row belongs to
feeds_files_id	VARCHAR	FK to FEEDS_FILES — identifies which batch loaded this data
created_at	TIMESTAMP	When the source data was loaded into the warehouse
updated_at	TIMESTAMP	When the table was last rebuilt

Data Quality

Standardization

• Column mapping: AHRF source columns embed year suffixes (e.g., MD_NF_24, MD_NF_23) that change annually. An automated column mapper produces year-agnostic names (total_mds, mds_per_100k).

• Type casting: All raw data arrives as VARCHAR and is cast to appropriate types (NUMBER, DATE) in the staging layer using TRY_TO_NUMBER().

• Geospatial aggregation: Shortage Designation shapefiles are intersected with county boundaries to produce county-level MUA/MUP summaries.

• HPSA aggregation: Component-level HPSA records are aggregated to county level per discipline, taking the worst (highest) score per county.

• Deduplication: Each source publishes full files. The pipeline deduplicates to the latest version per primary key.

Data Freshness

Check when data was last updated:

SELECT source_name, data_period, row_count, created_at, is_latest
FROM DWV.FEEDS_FILES
WHERE is_latest = TRUE;

Getting Started

Platform Schema Reference

This dataset is available on both Snowflake and Databricks. Queries use schema-only references — the database is already set by the share or catalog context:

Platform	Schema	Example
Snowflake	DWV	DWV.AHRF
Databricks	hrsa_dwv	hrsa_dwv.ahrf

Discover Available Data

Start with the FEEDS table to see what's available, and FEEDS_FILES to understand data freshness and load history.

Snowflake

-- Browse the data catalog
SELECT table_name, description, latest_data_period,
       latest_row_count, last_loaded_at, total_batches
FROM DWV.FEEDS
ORDER BY table_name;

Databricks

Snowflake

-- Check data freshness per source
-- is_latest = TRUE marks the current batch for each source
SELECT source_name, data_period, row_count, row_count_delta,
       created_at AS loaded_at, is_latest
FROM DWV.FEEDS_FILES
ORDER BY source_name, created_at DESC;

Databricks

Working with Data Lineage

Every data row links to FEEDS_FILES via feeds_files_id, which tells you exactly which batch loaded that data. Use this to filter to the current data version or trace any row back to its source load.

Snowflake

-- Get current workforce data with batch lineage
-- JOIN to FEEDS_FILES to see when data was loaded and which period it covers
SELECT
    a.fips_county,
    a.county_name,
    a.state_abbrev,
    a.data_year,
    a.total_mds,
    a.mds_per_100k,
    a.nurses_per_100k,
    ff.data_period,
    ff.created_at AS data_loaded_at
FROM DWV.AHRF a
JOIN DWV.FEEDS_FILES ff ON a.feeds_files_id = ff.id
WHERE ff.is_latest = TRUE
  AND a.data_year = (SELECT MAX(data_year) FROM DWV.AHRF)
ORDER BY a.mds_per_100k DESC NULLS LAST
LIMIT 20;

Databricks

Healthcare Workforce Analysis

Snowflake

-- Counties with critical workforce shortages
-- Cross-source analysis: workforce + HPSA scores + MUA designations
SELECT
    a.fips_county,
    a.county_name,
    a.state_abbrev,
    a.total_mds,
    a.mds_per_100k,
    a.nurses_per_100k,
    h.hpsa_score,
    h.provider_shortage AS providers_needed,
    s.mua_designation_count
FROM DWV.AHRF a
JOIN DWV.FEEDS_FILES ff_a ON a.feeds_files_id = ff_a.id AND ff_a.is_latest = TRUE
JOIN DWV.HPSA h ON a.fips_county = h.fips_county AND h.discipline = 'PRIMARY_CARE'
JOIN DWV.FEEDS_FILES ff_h ON h.feeds_files_id = ff_h.id AND ff_h.is_latest = TRUE
LEFT JOIN DWV.SHORTAGE_DESIGNATIONS s ON a.fips_county = s.fips_county
WHERE a.data_year = (SELECT MAX(data_year) FROM DWV.AHRF)
  AND h.hpsa_score >= 15
ORDER BY h.hpsa_score DESC
LIMIT 50;

Databricks

Snowflake

-- State-level workforce summary for grant writing
-- Aggregate county data to state level for federal funding applications
SELECT
    a.state_abbrev,
    a.state_name,
    COUNT(DISTINCT a.fips_county) AS counties,
    SUM(a.population) AS total_population,
    SUM(a.total_mds) AS total_physicians,
    ROUND(SUM(a.total_mds) * 100000.0 / NULLIF(SUM(a.population), 0), 1) AS state_mds_per_100k,
    SUM(h.provider_shortage) AS total_providers_needed,
    COUNT(CASE WHEN h.hpsa_score >= 15 THEN 1 END) AS critical_shortage_counties
FROM DWV.AHRF a
JOIN DWV.FEEDS_FILES ff ON a.feeds_files_id = ff.id AND ff.is_latest = TRUE
LEFT JOIN DWV.HPSA h ON a.fips_county = h.fips_county AND h.discipline = 'PRIMARY_CARE'
    AND h.feeds_files_id IN (SELECT id FROM DWV.FEEDS_FILES WHERE source_name = 'hpsa' AND is_latest = TRUE)
WHERE a.data_year = (SELECT MAX(data_year) FROM DWV.AHRF)
GROUP BY a.state_abbrev, a.state_name
ORDER BY total_providers_needed DESC NULLS LAST;

Databricks

FQHC Network Analysis

Snowflake

-- Health centers in high-shortage counties
-- Site-level facility data joined with county shortage context
SELECT
    hc.health_center_name,
    hc.site_name,
    hc.site_city,
    hc.state_abbrev,
    hc.operating_hours_per_week,
    h.hpsa_score,
    h.designated_population,
    a.mds_per_100k
FROM DWV.HEALTH_CENTER_SITES hc
JOIN DWV.FEEDS_FILES ff_hc ON hc.feeds_files_id = ff_hc.id AND ff_hc.is_latest = TRUE
JOIN DWV.HPSA h ON hc.fips_county = h.fips_county AND h.discipline = 'PRIMARY_CARE'
JOIN DWV.FEEDS_FILES ff_h ON h.feeds_files_id = ff_h.id AND ff_h.is_latest = TRUE
JOIN DWV.AHRF a ON hc.fips_county = a.fips_county
    AND a.data_year = (SELECT MAX(data_year) FROM DWV.AHRF)
WHERE h.hpsa_score >= 20
  AND hc.site_status = 'Active'
ORDER BY h.hpsa_score DESC, hc.health_center_name
LIMIT 25;

Databricks

Complete County Health Profile

Snowflake

-- Full cross-source county profile: workforce + shortages + facilities
-- Joins all 4 data tables on fips_county using is_latest for current data
SELECT
    a.fips_county,
    a.county_name,
    a.state_abbrev,
    a.total_mds,
    a.mds_per_100k,
    a.nurses_per_100k,
    h.hpsa_score AS pc_shortage_score,
    h.provider_shortage AS pc_providers_needed,
    s.mua_designation_count,
    s.mua_population,
    COUNT(hc.site_key) AS health_center_count,
    ff_a.data_period AS ahrf_period,
    ff_h.data_period AS hpsa_period
FROM DWV.AHRF a
JOIN DWV.FEEDS_FILES ff_a ON a.feeds_files_id = ff_a.id AND ff_a.is_latest = TRUE
LEFT JOIN DWV.HPSA h ON a.fips_county = h.fips_county AND h.discipline = 'PRIMARY_CARE'
LEFT JOIN DWV.FEEDS_FILES ff_h ON h.feeds_files_id = ff_h.id AND ff_h.is_latest = TRUE
LEFT JOIN DWV.SHORTAGE_DESIGNATIONS s ON a.fips_county = s.fips_county
LEFT JOIN DWV.HEALTH_CENTER_SITES hc ON a.fips_county = hc.fips_county
    AND hc.site_status = 'Active'
    AND hc.feeds_files_id IN (SELECT id FROM DWV.FEEDS_FILES WHERE source_name = 'health_center_sites' AND is_latest = TRUE)
WHERE a.data_year = (SELECT MAX(data_year) FROM DWV.AHRF)
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13
ORDER BY h.hpsa_score DESC NULLS LAST
LIMIT 50;

Databricks

Payer Network Adequacy

Snowflake

-- Shortage gap analysis for Medicare Advantage network adequacy
-- Counties where provider supply falls short of HPSA-designated need
SELECT
    a.state_abbrev,
    a.county_name,
    a.fips_county,
    a.mds_per_100k,
    a.population,
    h_pc.hpsa_score AS pc_score,
    h_pc.provider_shortage AS pc_gap,
    h_dent.hpsa_score AS dental_score,
    h_dent.provider_shortage AS dental_gap,
    h_mh.hpsa_score AS mh_score,
    h_mh.provider_shortage AS mh_gap
FROM DWV.AHRF a
JOIN DWV.FEEDS_FILES ff ON a.feeds_files_id = ff.id AND ff.is_latest = TRUE
LEFT JOIN DWV.HPSA h_pc ON a.fips_county = h_pc.fips_county AND h_pc.discipline = 'PRIMARY_CARE'
    AND h_pc.feeds_files_id IN (SELECT id FROM DWV.FEEDS_FILES WHERE source_name = 'hpsa' AND is_latest = TRUE)
LEFT JOIN DWV.HPSA h_dent ON a.fips_county = h_dent.fips_county AND h_dent.discipline = 'DENTAL'
    AND h_dent.feeds_files_id IN (SELECT id FROM DWV.FEEDS_FILES WHERE source_name = 'hpsa' AND is_latest = TRUE)
LEFT JOIN DWV.HPSA h_mh ON a.fips_county = h_mh.fips_county AND h_mh.discipline = 'MENTAL_HEALTH'
    AND h_mh.feeds_files_id IN (SELECT id FROM DWV.FEEDS_FILES WHERE source_name = 'hpsa' AND is_latest = TRUE)
WHERE a.data_year = (SELECT MAX(data_year) FROM DWV.AHRF)
  AND (h_pc.hpsa_score >= 10 OR h_dent.hpsa_score >= 10 OR h_mh.hpsa_score >= 10)
ORDER BY COALESCE(h_pc.hpsa_score, 0) + COALESCE(h_dent.hpsa_score, 0) + COALESCE(h_mh.hpsa_score, 0) DESC
LIMIT 25;

Databricks

Tracking Data Changes Over Time

FEEDS_FILES records every batch load with row_count_delta showing what changed. Use this to monitor source data updates and understand how the dataset evolves.

Snowflake

-- Track how HPSA data has evolved across batches
-- row_count_delta shows net new designations per load
SELECT
    source_name,
    data_period,
    row_count,
    row_count_delta,
    created_at AS loaded_at,
    is_latest
FROM DWV.FEEDS_FILES
WHERE source_name = 'hpsa'
ORDER BY created_at;

Databricks

Ready to access HRSA data?

Platform	Action
Snowflake	Get on Marketplace
Databricks	Free Trial

Questions? Contact our team for a walkthrough.

HRSA Data Sources

• HRSA Data Downloads

• Area Health Resources Files (AHRF)

• HPSA Find

• Health Center Program