Healthcare Data Model Guide: Dimension Mapping, Units and SCDs

Updated October 2026

A healthcare data model defines how patient, clinical, financial and operational records are structured and related, so that analysis means the same thing across systems. For analytics, the most useful kind is usually a dimensional model: fact tables that record events, joined to shared dimensions for patient, provider, facility, code and date.

This guide is for CIOs, CTOs and heads of data funding or reviewing that work. Our view: dimension mapping is where a healthcare data model succeeds or fails, because the hard part is deciding that two codes, units, names or facilities are the same thing, and keeping that decision over time. Our healthcare data engineering team reviews this through a fixed-price Healthcare Data Platform Assessment.

Which Healthcare Data Model Do You Need?

You need the model that matches the job, and most organizations will need more than one. Exchange, research and reporting pull in different directions, so one shape forced onto all three tends to serve each of them badly.

Buying a vendor model can be reasonable, and it does not remove the work below. A packaged model gives you tables, and your local codes, units and facilities still need mapping into them. If you already run Epic, its Caboodle dimensional model may give you a starting point. Check how your other sources would join to it before you assume it can be the enterprise model.

Start With Grain, Then Build the Dimensional Model

Start by stating the grain, meaning what one row in the fact table represents. The Kimball four-step process is still the right order: choose the business process, declare the grain, identify the dimensions, then identify the facts.

Grain mistakes are hard to catch, because the numbers still look plausible.

The last row needs a bridge table, Kimball's method for multivalued dimensions. An encounter has many diagnoses and many care team members, and joining them straight to the fact table multiplies your counts with no error message.

What Is Dimension Mapping in Healthcare?

Dimension mapping is the work of converting each source's local values into one shared dimension, through a dated crosswalk that keeps the original. It applies to four areas: codes, units, names and facilities. Each one fails in a different way, and we cover each below.

Four rules apply to all of them:

• Never overwrite the source value. Store it beside the standard one, because mappings get corrected.
• Date every map. Codes and organizations change, so a map needs a valid period.
• Treat a match as a candidate. A text or name match needs a named person to approve it before it counts.
• Load failures visibly. Report the unmapped share per source each month. Silent drops make every downstream rate wrong.

Mapping Codes: Crosswalks, Versions and Local Codes

Code mapping turns local and standard codes into a single dim_code through a dated crosswalk. Use the standard that fits each domain: ICD-10-CM for diagnoses on US claims, SNOMED CT for clinical problems, LOINC for lab tests, RxNorm for drugs and CPT or HCPCS for billed services. We explain each in our guide to healthcare data standards, and operating them in terminology services in production.

Mapping Units: Normalize, but Keep the Original

Unit mapping converts each result to one standard unit per test, using UCUM codes and a conversion rule approved by lab staff. UCUM exists "to facilitate unambiguous electronic communication of quantities together with their units", and it matters most at the point where feeds merge.

A mapped code with an unmapped unit is the most dangerous combination, because the column looks clean. A1c can arrive as a percent or in mmol/mol. The NGSP master equation converts them: NGSP % = (0.09148 x IFCC) + 2.152, so 42 mmol/mol is about 6.0%. Without conversion, that patient appears to have an A1c of 42.

# Normalize a lab result. Keep the original value and unit on the row.
# NGSP master equation: NGSP % = (0.09148 * IFCC mmol/mol) + 2.152
RULES = {
    ("4548-4", "%"): lambda v: (v, "%"),
    ("4548-4", "mmol/mol"): lambda v: (0.09148 * v + 2.152, "%"),
    ("29463-7", "kg"): lambda v: (v, "kg"),
    ("29463-7", "[lb_av]"): lambda v: (v * 0.45359237, "kg"),
}

def normalize(loinc, value, ucum_unit):
    rule = RULES.get((loinc, ucum_unit))
    if rule is None or value is None:
        # Unknown pair: never guess. Route to review and leave the normalized value empty.
        return {"value_std": None, "unit_std": None, "status": "review",
                "value_raw": value, "unit_raw": ucum_unit}
    std, unit = rule(value)
    return {"value_std": round(std, 2), "unit_std": unit, "status": "ok",
            "value_raw": value, "unit_raw": ucum_unit}

This sketch is for illustration and is not a clinical tool. We checked it and the SQL below on synthetic rows, and a lab director should approve any conversion you run.

Three warnings. UCUM says arbitrary units cannot be converted or compared, so a result in international units can have no safe conversion. Text values such as "<5" or "positive" need their own columns, because forcing them to numbers drops them. A method change can shift results even when the unit stays the same, so conversion alone does not make two tests comparable.

Mapping Names: Patients and Providers

Name mapping should link records to a stable identifier wherever one exists, and treat every name match as a candidate. A wrong merge can put one person's history on another person's chart or analysis, so the cost of a false match is far higher than a missed one.

• Providers: use the NPI from the NPPES registry as the anchor, and keep local provider IDs in a crosswalk. A name lookup in NPPES returns candidates, and shared names are common, so a person confirms the match. Specialty and group membership change, so keep their history.
• Patients: build on your master patient index, keep the source MRN, and record merges and unmerges as events, so analysis can be replayed. See our guide to patient matching beyond demographics.
• Payers and plans: the same payer name appears across lines of business, and contract level is what finance needs. Build a dim_payer hierarchy instead of cleaning spellings.

Mapping Facilities: A Hierarchy, Not a List

A facility dimension needs your own stable key, a crosswalk from every source identifier, and a hierarchy that keeps its history. Facility looks like simple cleanup, yet it decides every site comparison you report.

Mergers make this urgent. When a clinic moves to a new group, last year's numbers either stay with the old group or restate under the new one, and leaders need to choose. That choice is what slowly changing dimensions record.

Slowly Changing Dimensions in Healthcare

A slowly changing dimension (SCD) records how you handle a dimension attribute that changes, such as a patient's address or a provider's group. The choice decides whether a report shows the world as it was then or as it is now, so decide it per attribute, with the report's owner.

A date that sits in the wrong place causes most errors here. Join to the version valid on the event date, and test the boundary. Our sample treats the end date as exclusive, so a boundary date matches one version. Also test that no two versions of a facility overlap, because the join cannot prevent that:

-- As-was: which facility group did this encounter belong to at the time?
SELECT d.facility_group, COUNT(*) AS encounters
FROM fact_encounter f
JOIN dim_facility d
  ON d.facility_id = f.facility_id
 AND f.admit_date >= d.valid_from
 AND f.admit_date <  d.valid_to
GROUP BY d.facility_group;

-- As-is: restate all history under today's structure.
SELECT cur.facility_group, COUNT(*) AS encounters
FROM fact_encounter f
JOIN dim_facility cur
  ON cur.facility_id = f.facility_id
 AND cur.is_current = 1
GROUP BY cur.facility_group;

Late-arriving records are the other trap. A claim or a corrected result can arrive months after the event, and it needs the dimension version from its event date. Our guide to change data capture for healthcare warehouses covers how updates and deletes reach these tables. Once dimensions are right, a healthcare semantic layer can define metrics on top, and it is where as-was and as-is should be named explicitly.

Who Owns Mapping Decisions

Mapping decisions belong to named stewards in the business and clinic, with engineers building what they approve. Without that, engineers end up making clinical and financial decisions in code, and nobody can explain them later.

Set measures you can review monthly: the unmapped share by source, the age of the oldest open mapping task, and how many maps changed in the last release. For the wider picture see governance, lineage and PHI access. Do this before you add layers such as the medallion architecture, which organizes the work without deciding meaning for you.

What a CIO or Head of Data Should Ask Before Hiring

Here are the questions we would ask any data modeling vendor, ourselves included. The answers show whether a team has thought about what happens after the first load.

Risks and how to control them

Governance and PHI handling

Keep identifiers in restricted dimensions, and give most users de-identified or hashed keys. Address history is valuable and sensitive, so restrict it the same way. Early design can run on schemas, code lists and field profiles with no patient rows. If row-level PHI is needed, a Business Associate Agreement comes first. Nirmitee is ISO 27001:2022 certified and signs Business Associate Agreements. Neither of those makes a given dataset compliant, so your own access rules and audit still apply.

Who owns the code and the data

Once paid, you own the custom model code, every crosswalk and the documentation we write for you, and all of your data. We keep our reusable tools and open-source parts. Crosswalks are expensive to rebuild, which makes them one of the most valuable things you pay for. Licensed code sets such as CPT and SNOMED CT come with their own licence terms, so check yours.

What the team looks like

Expect these named roles: a data architect, an analytics engineer, a data steward, a clinical or coding reviewer, lab staff for unit rules, and a domain owner for each area. Stewards and reviewers usually sit on your side, and their time is the cost most plans leave out.

What you receive

From a build: grain statements, tested dimension and fact models, dated crosswalks with approvers, an SCD decision per attribute, reconciliation reports, an unmapped-values report and a runbook for code updates.

How to judge a vendor

1. Show me the grain statement for each fact table, before any code.
2. What happens to a lab result with an unknown unit?
3. Who approves a patient merge, and how do you reverse one?
4. How does a report show a facility before and after a merger?
5. How do late-arriving claims find the right dimension version?
6. Where do crosswalks live if we stop working with you?

Start with a Healthcare Data Platform Assessment

Before funding a model, you need to know how many codes, units and facilities your sources really hold, and who will approve their mapping. The Healthcare Data Platform Assessment is a fixed $3,500 review that takes 2 to 3 weeks and is credited against the build. It gives you written evidence for that decision before you commit a build budget.

• Source inventory: each system, its extract method, its access owner and its known gaps.
• Target architecture: a recommended design, with the options we rejected and why.
• Data model outline: facts and grain, shared dimensions, and the mapping effort for each source.
• Risk list: what could stop the work, who owns each risk and how to reduce it.
• Costed build plan: phases, roles and an estimate you can compare with other vendors.

It does not include building pipelines, loading production data, software or cloud licences, a compliance opinion or a fixed price for the build. You can take the output to any team, including your own. Ask for a Healthcare Data Platform Assessment.

You can also read how we approach healthcare data pipelines, or talk to our team about one dataset.