---
title: "Healthcare"
canonical: "https://www.owox.com/data-models/healthcare"
updated: "2026-09-26"
---

# Healthcare Data Model

8 data marts56 fields[Vlad Flaks](https://github.com/vladflaks)[Rus Obolonsky](https://github.com/Obolrus)

Patients move from a booked appointment through the clinical encounter to the insurance claim that pays for it, while daily bed census tracks capacity alongside providers, payers and departments.

## Overview

A hospital system modeled end to end — from a patient booking an appointment, through the clinical encounter that follows, to the insurance claim that pays for it. Patients, providers, payers and departments anchor the model, while appointments capture scheduling and no-show behavior, encounters capture diagnoses, length of stay and readmissions, claims capture the revenue cycle (billed, allowed and paid amounts, denials, days in accounts receivable), and daily bed census tracks capacity and patient flow across inpatient units. Together they tell the operational and financial story of running a health system: who is being seen, how care converts into cash, and where capacity is tight.

## Example Questions

*   How does no-show rate vary by booking lead time and insurance type, and what would tightening scheduling windows save in lost capacity?
*   What is the `claim` denial rate by `payer`, and how much longer do denied claims sit in accounts receivable than clean claims?
*   Which `departments` are running closest to `bed capacity`, and how does occupancy relate to admissions and discharges over time?

[Explore on canvas →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/healthcare)

## Appointments

One row per scheduled appointment, capturing how a visit was booked and whether the patient actually showed up. Each row links a patient, a provider and a department, records the scheduled date and time, how far in advance it was booked (lead time), how long the patient waited past that time, whether they were a no-show, and the appointment's final status. This is the front door of the clinical and revenue cycle — every encounter and claim downstream starts as a booked appointment here.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `appointment_id` | STRING | Appointment ID | PK. Unique appointment identifier. |
| `patient_id` | STRING | Patient ID | Patient who booked the appointment. FK to [Patient](#mart-patient) |
| `provider_id` | STRING | Provider ID | Provider seeing the patient. FK to [Provider](#mart-provider) |
| `scheduled_at` | TIMESTAMP | Scheduled At | Scheduled date and time of the appointment. |
| `department_id` | STRING | Department ID | Department where the appointment takes place. FK to [Department](#mart-department) |
| `status` | STRING | Status | Appointment status (e.g. booked, completed, cancelled). |
| `is_no_show` | BOOLEAN | Is No Show | Whether the patient failed to show up. |
| `wait_minutes` | INTEGER | Wait Minutes | Door-to-provider wait. |
| `lead_time_days` | INTEGER | Lead Time Days | Booking-to-visit lead time — no-show driver. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Department](#mart-department) | `department_id = department_id` | N:1 | The department the appointment was booked into. |
| [Patient](#mart-patient) | `patient_id = patient_id` | N:1 | The patient the appointment was booked for. |
| [Provider](#mart-provider) | `provider_id = provider_id` | N:1 | The clinician the appointment was booked with. |

## Bed Census (daily)

One row per department per day, tracking inpatient bed capacity and patient flow over time. Each row records how many beds were staffed and how many were occupied that day, alongside the day's admissions and discharges — the numbers that drive occupancy rate and reveal whether a department is filling up or emptying out. This is the operational pulse of capacity management across the health system.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `census_id` | STRING | Census ID | PK. Unique identifier for the daily census record. |
| `department_id` | STRING | Department ID | Department the census covers. FK to [Department](#mart-department) |
| `census_date` | DATE | Census Date | Calendar day of the census. |
| `staffed_beds` | INTEGER | Staffed Beds | Beds staffed and available that day. |
| `occupied_beds` | INTEGER | Occupied Beds | Beds occupied at census time — utilization numerator. |
| `admissions` | INTEGER | Admissions | Admissions during the day. |
| `discharges` | INTEGER | Discharges | Discharges during the day. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Department](#mart-department) | `department_id = department_id` | N:1 | The department these staffed beds belong to. |

## Claims

One row per claim submitted to a payer for a clinical encounter — the revenue-cycle record that turns care into cash. Each claim tracks the billed, payer-allowed and actually paid amounts, its submission and payment dates, current status, the denial reason when one applies, and the number of days it has sat in accounts receivable. This is the mart that answers how well the health system gets paid for the care it delivers, and how quickly.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `claim_id` | STRING | Claim ID | PK. Unique claim identifier. |
| `encounter_id` | STRING | Encounter ID | Encounter the claim is billed for. FK to [Encounters](#mart-encounters) |
| `payer_id` | STRING | Payer ID | Payer responsible for the claim. FK to [Payer](#mart-payer) |
| `submitted_at` | DATE | Submitted At | Date the claim was submitted. |
| `paid_at` | DATE | Paid At | Date the claim was paid. |
| `billed_amount` | NUMERIC | Billed Amount | Amount billed to the payer. |
| `allowed_amount` | NUMERIC | Allowed Amount | Payer-allowed amount. |
| `paid_amount` | NUMERIC | Paid Amount | Amount actually paid. |
| `status` | STRING | Status | Claim status (e.g. submitted, paid, denied). |
| `denial_code` | STRING | Denial Code | CARC/RARC denial reason, when denied. |
| `ar_days` | INTEGER | Ar Days | Days in accounts receivable — revenue-cycle speed. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Encounters](#mart-encounters) | `encounter_id = encounter_id` | N:1 | The encounter being billed. |
| [Payer](#mart-payer) | `payer_id = payer_id` | N:1 | The insurer billed for this claim. |

## Department

Reference list of the clinical departments that make up the health system, each with the clinical specialty it serves and its staffed-bed capacity. Departments are the organizational unit behind appointments, provider assignments and daily bed census — staffed beds is the denominator for every occupancy and capacity calculation in the model.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `department_id` | STRING | Department ID | PK. Unique department identifier. |
| `name` | STRING | Name | Department name. |
| `specialty` | STRING | Specialty | Clinical specialty the department serves. |
| `staffed_beds` | INTEGER | Staffed Beds | Number of staffed beds — the utilization denominator. |

## Encounters

One row per clinical encounter — the actual visit that followed a scheduled appointment. Each encounter records its type (outpatient, inpatient or emergency department), the primary diagnosis, admission and discharge timestamps, the resulting length of stay, and whether it was an unplanned readmission within 30 days. This is where the clinical story lives: what patients are being treated for, how long they stay, and how often they come back unexpectedly.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `encounter_id` | STRING | Encounter ID | PK. Unique clinical encounter identifier. |
| `appointment_id` | STRING | Appointment ID | Appointment that led to this encounter. FK to [Appointments](#mart-appointments) |
| `patient_id` | STRING | Patient ID | Patient seen in the encounter. FK to [Patient](#mart-patient) |
| `provider_id` | STRING | Provider ID | Provider who delivered care. |
| `admit_ts` | TIMESTAMP | Admit Time | Admission date and time. |
| `discharge_ts` | TIMESTAMP | Discharge Time | Discharge date and time. |
| `encounter_type` | STRING | Encounter Type | outpatient / inpatient / ED. |
| `primary_diagnosis` | STRING | Primary Diagnosis | Primary ICD-10 code. |
| `length_of_stay_days` | FLOAT | Length Of Stay Days | Length of stay in days. |
| `is_readmission_30d` | BOOLEAN | Is Readmission 30d | Unplanned readmission within 30 days. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Appointments](#mart-appointments) | `appointment_id = appointment_id` | N:1 | The booking that brought the patient in. |
| [Patient](#mart-patient) | `patient_id = patient_id` | N:1 | The patient treated in this encounter. |

## Patient

One row per patient, the demographic and coverage anchor for every appointment, encounter and claim in the system. Each patient carries a birth year and gender for age-banded analysis, a postal code for geographic reach, an insurance type (commercial, Medicare, Medicaid or self-pay) that determines how their care gets billed, a risk-stratification tier used by care management to flag patients who need closer follow-up, and the date they first registered with the system.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `patient_id` | STRING | Patient ID | PK. Unique de-identified patient identifier. |
| `birth_year` | INTEGER | Birth Year | Year of birth, used for age banding. |
| `gender` | STRING | Gender | Patient gender. |
| `postal_code` | STRING | Postal Code | Patient postal/ZIP code for geographic analysis. |
| `insurance_type` | STRING | Insurance Type | commercial / Medicare / Medicaid / self-pay. |
| `risk_tier` | STRING | Risk Tier | Risk-stratification band for care management. |
| `registered_at` | DATE | Registered At | Date the patient was first registered. |

## Payer

Reference list of the insurance payers and plans that reimburse the health system for care, each identified by name and plan type — HMO, PPO, EPO or government (Medicare/Medicaid). Every claim is billed to one of these payers, so this mart is the lookup behind any payer-mix or reimbursement analysis.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `payer_id` | STRING | Payer ID | PK. Unique payer identifier. |
| `name` | STRING | Name | Payer / insurance plan name. |
| `plan_type` | STRING | Plan Type | HMO / PPO / EPO / government. |

## Provider

One row per clinician, the roster behind every appointment and encounter. Each provider is identified by name, clinical specialty, home department, and National Provider Identifier (NPI) — the standard identifier used to credential and bill for a clinician's services. This is the lens for looking at care delivery by clinician: who is seeing patients, in what specialty, and out of which department.

### Fields

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `provider_id` | STRING | Provider ID | PK. Unique provider identifier. |
| `full_name` | STRING | Full Name | Provider's full name. |
| `specialty` | STRING | Specialty | Clinical specialty of the provider. |
| `department` | STRING | Department | Department the provider belongs to. |
| `npi` | STRING | NPI | National Provider Identifier. |

## Apply to your project

1.  1

    ### Install the Import Model plugin

    One plugin, installed once, in your own OWOX workspace.

    [Get the plugin →](https://github.com/OWOX/import-model)

2.  2

    ### Import this model

    Point it at this bundle and it creates every data mart above, joins and all.

    [Open the model →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/healthcare)

3.  3

    ### Plug in your data and destinations

    Connect your own sources and send the results where your team already works.

    [Browse connectors →](/connectors)

**4\. Optional — customize as you wish.** Rename a column, drop a mart, add your own: once it is imported it is yours, and nothing here syncs back.

## References

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Browse connectors →](https://www.owox.com/connectors) — /connectors.md