UDS Data Mapping and Validation Workbook for FQHCs
UDS reporting failures rarely begin inside the HRSA Electronic Handbooks. They usually begin months earlier when an encounter type is misclassified, duplicate patient identities remain unresolved, a payer crosswalk becomes outdated, or an EHR report applies the wrong clinical-measure logic.
A reliable Uniform Data System submission therefore needs more than an exported spreadsheet. It needs a governed UDS data mapping and validation workbook that connects HRSA requirements to the health center’s EHR, practice management, dental, behavioral health, payroll, accounting, pharmacy, and population health systems.
Reporting-year note: This guide uses the finalized CY 2025 UDS Manual because it remains the latest detailed, finalized manual published by HRSA. HRSA currently lists CY 2026 changes as proposed. Every production workbook must be updated against the final manual, Program Assistance Letter, table structures, code lists, and measure specifications for the applicable reporting year.
What Is a UDS Data Mapping and Validation Workbook?
A UDS mapping workbook is a controlled source-to-target specification.
For every reportable UDS field, it documents:
- Where the source data originates
- How records qualify for inclusion
- Which records must be excluded
- How local values map to UDS categories
- At what grain the calculation operates
- Which validation rules must pass
- Who owns and approves the result
- What evidence supports the reported value
It should complement, not replace, HRSA’s Offline Excel Mapping Tool. The HRSA tool identifies the Excel locations associated with EHB reporting fields.
The internal FQHC workbook must provide the missing operational layer: local data lineage, transformation rules, source-system dependencies, exceptions, reconciliation results, and approvals.
Why Is the Workbook Important?
HRSA defines a UDS patient as someone who received at least one countable visit during the calendar year. Registered patients, scheduled appointments, claims, laboratory-only encounters, outreach activities, medication administration, and other nonqualifying services cannot automatically be treated as UDS patients. A visit also does not have to be billable when it otherwise meets the UDS countable-visit definition.
These rules create several technical risks:
- One patient may exist under different identifiers across medical, dental, and behavioral health systems.
- One billable transaction may not equal one countable visit.
- Multiple same-day claims may need to collapse into one UDS visit.
- A vendor-generated eCQM result may include patients who are not UDS patients.
- Payer categories used by billing may not align directly with UDS revenue categories.
- Prior-year mappings may continue running after HRSA changes a measure or code list.
The workbook creates traceability between the final UDS cell and the patient, encounter, provider, code, transaction, or general-ledger record that produced it.
Recommended Workbook Architecture
Use separate worksheets rather than maintaining all logic in one large table.
| Worksheet | Required content |
| Reporting Control | Reporting year, UDS Manual version, final PAL, code-set versions, data-freeze date and approvals |
| Source Inventory | System, module, table or report, owner, refresh schedule, identifiers and extraction method |
| Cell Mapping | UDS table, line, column, source field, business rule, grain and target formula |
| Encounter Crosswalk | Local encounter types, service categories, provider categories, modality and countable-visit status |
| Terminology | ICD-10-CM, CPT, HCPCS, CDT, LOINC, SNOMED CT and locally defined codes |
| Payer Mapping | Local plan, financial class, primary insurance category, Table 9D payer and effective dates |
| Validation Rules | Rule ID, expression, tolerance, severity, status, owner and evidence |
| Exceptions | Failed records, reason, remediation, approval and resolution date |
| Reconciliation | Cross-table, source-to-report, year-over-year and financial checks |
| Sign-Off | Data, clinical, finance, operations and executive approvals |
Production SQL, ETL packages, credentials, connection strings, and proprietary integration logic should remain in secured, version-controlled repositories. The workbook should contain business-readable logic and rule references rather than a complete deployable implementation.
Step 1: Lock the Reporting-Year Specification
Before extracting data, create a frozen specification baseline containing:
- Final UDS Manual
- Final Program Assistance Letter
- Reporting tables and forms
- Table 6A code changes
- Table 6B and Table 7 measure criteria
- Qualifying encounter value sets
- Exclusions and exceptions
- Effective terminology versions
Each mapping rule should have an effective start date, end date, reporting year, version, change reason, approver, and corresponding source document.
Never overwrite prior-year logic. Retain it as a separate version so the organization can reproduce a previous submission.
Step 2: Build the Canonical UDS Patient and Visit Universe
Create a canonical patient-year dataset before calculating individual tables.
At minimum, it should include:
reporting_year patient_key source_patient_id source_system encounter_key service_date service_site provider_key provider_category service_category visit_modality scope_of_project_flag grant_population_flag same_day_visit_group countable_visit_flag exclusion_reason mapping_rule_id extract_version
The patient_key must represent the same individual across all contributing systems.
Use enterprise identifiers, documented merge history, or controlled identity-matching rules. Do not deduplicate only within each EHR.
HRSA requires patients on the ZIP Code Table and Tables 3A, 3B, and 4 to be reported once organization-wide. Table 5 patients are instead unduplicated within each applicable service category.
A patient receiving medical and dental care may therefore appear in both Table 5 categories but only once in the organization-wide demographic total.
Step 3: Apply Correct Same-Day Visit Logic
Do not count appointments, claims, procedures, or encounter rows without applying UDS visit consolidation.
For CY 2025, HRSA generally instructs health centers to count one visit per patient, service category, provider, location, and day. Multiple services delivered by providers in the same category at the same site normally collapse into one visit. Separate visits may be countable when qualifying services are provided by different providers at different service delivery sites.
Clinic visits and virtual visits must also remain mutually exclusive:
| total_visits = clinic_visits + virtual_visits clinic_visit_flag + virtual_visit_flag <= 1 |
A virtual visit should not be duplicated as a clinic visit simply because the billing system assigns a physical department or facility location.
Step 4: Map Each UDS Target at the Correct Grain
| UDS target | Calculation grain | Critical mapping control |
| Table 3A, Lines 1–38 | Patient-year | Categorize each unduplicated patient by age on December 31 and sex assigned at birth |
| Table 3A, Line 39 | Aggregate | Sum Lines 1–38; do not define Line 39 as a separate patient grouping |
| Table 5, Column C | Patient-service-year | Count each patient once within every applicable service category |
| Table 5, Columns B/B2 | Countable visit | Apply provider, site, day, category and modality rules |
| Table 6A | Patient or visit, depending on line | Use reporting-year diagnosis and service code definitions |
| Tables 6B and 7 | Patient-measure-year | Apply UDS patient eligibility before initial population, denominator, exclusion, exception and numerator logic |
| Table 9D | Financial transaction | Classify charges and receipts by applicable payer and UDS column |
| Table 9E | Cash receipt | Report non-patient-service receipts using the last-party rule |
Table 3A Line 39 must reconcile with the Patients by ZIP Code total, Table 3B Line 8 Column D, Table 4 Line 6, and Table 4 Line 12 Columns A and B. The age-zero-through-17 and age-18-and-older subtotals must also reconcile with the corresponding Table 4 insurance columns.
Step 5: Separate UDS and eCQM Eligibility
Do not load an EHR’s standard eCQM result directly into Tables 6B or 7. The correct sequence is:
- Confirm the individual is a UDS patient with at least one countable visit.
- Apply the measure’s initial-population requirements.
- Apply qualifying-encounter criteria.
- Apply denominator criteria.
- Remove denominator exclusions.
- Apply denominator exceptions as specified.
- Evaluate numerator compliance.
- Aggregate the final measure result.
HRSA notes that UDS reporting considerations can differ from or expand on the underlying eCQM specifications. Numerators must never exceed their associated denominators.
Step 6: Implement Executable Validation Rules
Every validation should have a defined expression and severity.
Rule ID: VAL-DEMO-001 Description: Table 3A total must equal Table 3B total Expression: T3A_L39_CA + T3A_L39_CB = T3B_L8_CD Tolerance: 0 Severity: Critical Owner: UDS Data Lead Evidence: Reconciliation report reference
Validation should occur at five levels:
- Structural: Required fields, valid dates, unique keys and accepted code systems
- Record-level: Invalid DOBs, excluded encounters, duplicate patients and incompatible flags
- Aggregate: Subtotals, totals, numerator-denominator relationships and Universal-versus-Grant limits
- Cross-table: Demographic, utilization, clinical, staffing and financial consistency
- Analytical: Prior-year, site, provider, service, payer and monthly variance analysis
No Grant Report cell may exceed the corresponding Universal Report cell.
Step 7: Add Financial Reconciliation Controls
Table 9D and Table 9E require different revenue logic.
Table 9D captures patient-service revenue, including charges, collections, retroactive settlements, adjustments, sliding-fee discounts, and self-pay bad debt.
Table 9E captures cash receipts from non-patient-service sources and applies HRSA’s last-party rule, the receipt is classified by the entity from which the health center received it, not necessarily its original funding source. The same receipt must never be reported on both tables.
Include an accounts-receivable roll-forward:
change_in_AR = charges - collections - adjustments - sliding_fee_discounts - self_pay_bad_debt
Review payer reclassifications, wraparound settlements, capitated payments, performance incentives, pharmacy revenue, charge-to-cost ratios, and material year-over-year changes. HRSA also specifies that UDS bad debt currently includes self-pay patient debt, not general payer write-offs.
Step 8: Protect PHI and Preserve Auditability
The workbook should ordinarily contain rules, aggregates, status indicators, and controlled exception references—not unrestricted patient-level PHI. Apply:
- Role-based access
- Encryption in transit and at rest
- Secure version-controlled storage
- Access and change logging
- Restricted patient-level exception files
- Minimum-necessary data handling
- Defined retention and disposal procedures
The HIPAA Security Rule requires appropriate administrative, physical, and technical safeguards for ePHI, including mechanisms appropriate to the organization for controlling and reviewing system activity.
Step 9: Prepare the Workbook for UDS+
Add an optional UDS+ mapping layer containing:
aggregate_uds_target patient_level_data_element fhir_resource uds_plus_profile terminology_binding de_identification_rule submission_validation_status
UDS+ is designed to support electronic submission of de-identified patient-level data. Its implementation guide uses FHIR R4 and maps required data elements to UDS+-specific and QI Core profiles.
Building this crosswalk now reduces future rework, even when the current project focuses on aggregate UDS reporting.
Final Submission Readiness
Load preliminary results into HRSA’s Preliminary Reporting Environment as soon as it becomes available. PRE allows health centers to enter data before the official reporting season, identify errors, run cross-table checks, compare report versions, and use offline Excel or HTML reporting options.
Failed PRE or EHB Data Audit Report checks should be converted into permanent workbook validation rules—not corrected only through manual data entry.
A submission should be approved only when:
- All critical validation rules pass
- Material variances are explained
- Exceptions have documented approval
- Source totals reconcile with reported totals
- Patient samples can be traced in both directions
- Clinical, finance, operations and executive owners sign off
- The final extract, workbook and evidence package are version-locked
Build a Reliable UDS Reporting Process With CapMinds Services
A workbook template alone cannot resolve duplicate identities, incorrect encounter configuration, outdated code mappings, incomplete payer data, eCQM differences, or weak financial reconciliation.
CapMinds helps FQHCs design and implement the full UDS reporting workflow, including:
- UDS data source and workflow assessment
- Patient and encounter mapping
- EHR, billing and financial data integration
- Countable-visit and clinical-measure validation
- Cross-table and year-over-year reconciliation
- PRE and EHB error remediation
- Automated reporting pipelines
- UDS+ and FHIR readiness
- Technical documentation and reporting support
Talk to the CapMinds UDS Reporting Services Team to build a traceable, repeatable, and submission-ready UDS reporting process.
