Healthcare Revenue Cycle (RCM) Analytics data model
Kimball warehouse for the provider revenue cycle: a charge-capture fact, the core claim-line fact (837 reconciled with 835), a remittance fact at one-row-per-CARC so denial dollars stay additive, and a periodic AR snapshot for aging and days-in-AR — over conformed patient, payer, provider, coverage, encounter, diagnosis and procedure dimensions. Wired to FHIR/HL7/X12 so EHR and clearinghouse feeds conform into one model. Optimized for clean claim rate, first-pass denial rate, days in AR, and net collection rate.
Provider-side healthcare revenue cycle management: charge capture, claim submission (X12 837), remittance & payment posting (X12 835), denials management (CARC/RARC), and accounts-receivable aging — a Kimball star schema over conformed patient, payer, provider, coverage, encounter, ICD-10-CM diagnosis and CPT/HCPCS procedure dimensions, mapped to HL7 FHIR R4, HL7 v2 and X12 EDI, with PHI classified and masked for HIPAA. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 15
- tables
- 156
- columns
- 25
- relationships
- 10
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Service-line grain for the core claim fact
The 837 service line (CPT/HCPCS) reconciled against the 835 service payment is the atomic unit of RCM. Modeling at line grain lets every financial measure (billed/allowed/paid/adjustment/patient-responsibility) roll up to claim, encounter, provider, payer and procedure without losing detail.
Separate remittance fact at the adjustment-reason grain
An 835 can attach multiple CARC/RARC adjustments to a single line. One-row-per-adjustment keeps denial and adjustment amounts additive so 'denials by CARC' and write-off analysis are correct, instead of forcing multiple reasons into one claim-line row.
AR aging modeled as a periodic snapshot
Days-in-AR and aging buckets are point-in-time balances that cannot be reconstructed from transactional facts after the fact. A daily/weekly snapshot of outstanding balance per claim makes AR aging trends and DSO trivially queryable.
Standards mapping embedded on every entity (FHIR / HL7 v2 / X12)
Each table documents the FHIR R4 resource, HL7 v2 segment and X12 EDI loop it derives from, so EHR feeds (FHIR/HL7) and clearinghouse feeds (X12) land in one conformed model — the interoperability backbone an RCM operation needs.
SCD2 on patient, coverage and provider
Eligibility, plan, COB order and provider specialty change over time and must be evaluated as-of the date of service for correct claim attribution and denial root-cause; SCD2 preserves the version true when the service was rendered.
HIPAA Safe Harbor masking on PHI; NPI/code-sets in clear
Patient identifiers (name, DOB, SSN, address, MRN, member id) are HIPAA identifiers and are masked/hashed. Provider NPIs and ICD/CPT/HCPCS code sets are public reference data and stay unmasked so analytics and joins work without re-identification risk.
Tables in the Healthcare Revenue Cycle (RCM) Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fact_charge
Grain: one row per charge-capture line (one service rendered, pre-claim)
Charge capture before claims. Maps to FHIR ChargeItem and HL7 v2 DFT^P03. Gross charges originate here and flow into fact_claim_line.
| Column | Type | Keys |
|---|---|---|
| charge_key | bigint | PK |
| service_date_key | bigint | |
| patient_key | bigint | |
| encounter_key | bigint | |
| provider_key | bigint | |
| procedure_key | bigint | |
| organization_key | bigint | |
| charge_id | varchar | |
| revenue_code | varchar | |
| units | decimal | |
| charge_amount | decimal | |
| charge_status | varchar |
fact_claim_line
Grain: one row per claim service line (837 service line, adjudicated by 835)
The core RCM fact: one row per claim service line with billed/allowed/paid/adjustment/patient-responsibility. Maps to FHIR Claim.item + EOB.item.adjudication and X12 837 loop 2400 reconciled with 835 SVC.
| Column | Type | Keys |
|---|---|---|
| claim_line_key | bigint | PK |
| claim_key | bigint | |
| service_date_key | bigint | |
| submission_date_key | bigint | |
| patient_key | bigint | |
| coverage_key | bigint | |
| payer_key | bigint | |
| provider_key | bigint | |
| encounter_key | bigint | |
| procedure_key | bigint | |
| primary_diagnosis_key | bigint | |
| line_number | integer | |
| procedure_modifiers | varchar | |
| units | decimal | |
| billed_amount | decimal | |
| allowed_amount | decimal | |
| paid_amount | decimal | |
| adjustment_amount | decimal | |
| patient_responsibility_amount | decimal | |
| copay_amount | decimal | |
| coinsurance_amount | decimal | |
| deductible_amount | decimal | |
| line_status | varchar | |
| is_denied | boolean |
fact_remittance
Grain: one row per remittance adjustment detail (per claim line per CARC on an 835)
Payment posting & adjustments from electronic remittance advice. Maps to FHIR PaymentReconciliation + ClaimResponse.adjudication and X12 835 (BPR/CLP/SVC/CAS). One row per adjustment reason so denials-by-CARC stay additive.
| Column | Type | Keys |
|---|---|---|
| remittance_key | bigint | PK |
| claim_key | bigint | |
| payment_date_key | bigint | |
| patient_key | bigint | |
| payer_key | bigint | |
| denial_reason_key | bigint | |
| check_eft_number | varchar | |
| claim_line_number | integer | |
| payment_amount | decimal | |
| adjustment_amount | decimal | |
| adjustment_group_code | varchar | |
| payment_method | varchar |
fact_ar_snapshot
Grain: one row per open claim per snapshot date (periodic snapshot)
Periodic snapshot of outstanding AR per claim for aging buckets and days-in-AR trending. Derived from claim, charge and remittance facts.
| Column | Type | Keys |
|---|---|---|
| ar_snapshot_key | bigint | PK |
| snapshot_date_key | bigint | |
| claim_key | bigint | |
| patient_key | bigint | |
| payer_key | bigint | |
| billed_amount | decimal | |
| paid_amount | decimal | |
| adjustment_amount | decimal | |
| outstanding_balance | decimal | |
| ar_age_days | integer | |
| ar_aging_bucket | varchar | |
| responsible_party | varchar |
Dimension tables (8)
dim_date
Grain: one row per calendar day
Conformed calendar used by every fact (service / submission / payment / snapshot date).
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| year | smallint | |
| quarter | smallint | |
| month | smallint | |
| month_name | varchar | |
| day_of_month | smallint | |
| day_of_week | smallint | |
| is_weekend | boolean | |
| fiscal_year | smallint | |
| fiscal_period | smallint |
dim_patient
Grain: one row per patient per SCD2 version
Patient master. Maps to FHIR Patient and HL7 v2 PID. PHI masked for HIPAA; MRN is the durable business key across SCD2 versions.
SCD type 2
| Column | Type | Keys |
|---|---|---|
| patient_key | bigint | PK |
| mrn | varchar | BK · PII |
| fhir_patient_id | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | |
| ssn | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| zip_code | varchar | PII |
| phone | varchar | PII |
| varchar | PII | |
| race | varchar | |
| ethnicity | varchar |
dim_payer
Grain: one row per payer / insurance company
Insurance payer master. Maps to FHIR Organization (payor) and X12 payer identifier (Loop 1000B / N1*PR).
SCD type 1
| Column | Type | Keys |
|---|---|---|
| payer_key | bigint | PK |
| payer_id | varchar | BK |
| payer_name | varchar | |
| payer_type | varchar | |
| claim_filing_indicator | varchar | |
| fhir_organization_id | varchar |
dim_coverage
Grain: one row per patient coverage per SCD2 version
Patient insurance coverage. Maps to FHIR Coverage, X12 270/271 eligibility, and HL7 v2 IN1/IN2. SCD2 because plan, COB order and member status change over time.
SCD type 2
| Column | Type | Keys |
|---|---|---|
| coverage_key | bigint | PK |
| member_id | varchar | BK · PII |
| payer_key | bigint | |
| fhir_coverage_id | varchar | |
| plan_name | varchar | |
| group_number | varchar | |
| coverage_type | varchar | |
| subscriber_relationship | varchar | |
| coordination_of_benefits_order | smallint | |
| coverage_start_date | date | |
| coverage_end_date | date |
dim_provider
Grain: one row per provider per SCD2 version
Rendering / billing / referring / attending provider. Maps to FHIR Practitioner & PractitionerRole and X12 NM1 provider loops. Keyed by NPI (public registry id - not PHI).
SCD type 2
| Column | Type | Keys |
|---|---|---|
| provider_key | bigint | PK |
| npi | varchar | BK |
| fhir_practitioner_id | varchar | |
| provider_first_name | varchar | |
| provider_last_name | varchar | |
| taxonomy_code | varchar | |
| specialty | varchar | |
| provider_type | varchar |
dim_organization
Grain: one row per facility / service location
Billing facility / service location. Maps to FHIR Organization & Location and X12 service-facility loop 2310C.
SCD type 1
| Column | Type | Keys |
|---|---|---|
| organization_key | bigint | PK |
| facility_id | varchar | BK |
| facility_name | varchar | |
| facility_npi | varchar | |
| facility_type | varchar | |
| state | varchar |
dim_encounter
Grain: one row per encounter / visit
Clinical encounter / visit context. Maps to FHIR Encounter and HL7 v2 PV1 (ADT).
SCD type 1
| Column | Type | Keys |
|---|---|---|
| encounter_key | bigint | PK |
| encounter_id | varchar | BK |
| encounter_type | varchar | |
| admission_date | date | |
| discharge_date | date | |
| discharge_disposition | varchar | |
| place_of_service | varchar |
dim_claim
Grain: one row per submitted claim (header)
Claim header. Maps to FHIR Claim / ExplanationOfBenefit (header) and X12 837 claim loop 2300 (CLM).
SCD type 1
| Column | Type | Keys |
|---|---|---|
| claim_key | bigint | PK |
| claim_control_number | varchar | BK |
| payer_claim_control_number | varchar | |
| fhir_claim_id | varchar | |
| claim_type | varchar | |
| bill_type | varchar | |
| claim_frequency_code | varchar | |
| claim_status | varchar | |
| is_clean_claim | boolean | |
| submission_method | varchar |
Lookup tables (3)
dim_diagnosis
Grain: one row per ICD-10-CM diagnosis code
ICD-10-CM diagnosis code reference. Maps to FHIR Condition.code / Claim.diagnosis and X12 837 HI segment.
| Column | Type | Keys |
|---|---|---|
| diagnosis_key | bigint | PK |
| icd10_code | varchar | BK |
| code_description | varchar | |
| code_chapter | varchar | |
| is_hcc | boolean | |
| icd_version | varchar |
dim_procedure
Grain: one row per procedure code (CPT / HCPCS / ICD-10-PCS)
Procedure / service code reference. Maps to FHIR Procedure.code / ChargeItem.code and X12 837 SV101 / SV202. Carries RVUs.
| Column | Type | Keys |
|---|---|---|
| procedure_key | bigint | PK |
| procedure_code | varchar | BK |
| code_system | varchar | |
| code_description | varchar | |
| code_category | varchar | |
| revenue_category | varchar | |
| rvu_total | decimal |
dim_denial_reason
Grain: one row per CARC code (with optional RARC pairing)
Adjustment / denial reason reference. Maps to X12 835 CAS (CARC) + RARC remark codes and FHIR ClaimResponse.adjudication.
| Column | Type | Keys |
|---|---|---|
| denial_reason_key | bigint | PK |
| carc_code | varchar | BK |
| carc_description | varchar | |
| rarc_code | varchar | |
| rarc_description | varchar | |
| adjustment_group_code | varchar | |
| denial_category | varchar | |
| is_appealable | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dim_coverage.payer_key → dim_payer (many-to-one)
- fact_charge.service_date_key → dim_date (many-to-one)
- fact_charge.patient_key → dim_patient (many-to-one)
- fact_charge.encounter_key → dim_encounter (many-to-one)
- fact_charge.provider_key → dim_provider (many-to-one)
- fact_charge.procedure_key → dim_procedure (many-to-one)
- fact_charge.organization_key → dim_organization (many-to-one)
- fact_claim_line.claim_key → dim_claim (many-to-one)
- fact_claim_line.service_date_key → dim_date (many-to-one)
- fact_claim_line.patient_key → dim_patient (many-to-one)
- fact_claim_line.coverage_key → dim_coverage (many-to-one)
- fact_claim_line.payer_key → dim_payer (many-to-one)
- fact_claim_line.provider_key → dim_provider (many-to-one)
- fact_claim_line.encounter_key → dim_encounter (many-to-one)
- fact_claim_line.procedure_key → dim_procedure (many-to-one)
- fact_claim_line.primary_diagnosis_key → dim_diagnosis (many-to-one)
- fact_remittance.claim_key → dim_claim (many-to-one)
- fact_remittance.payment_date_key → dim_date (many-to-one)
- fact_remittance.patient_key → dim_patient (many-to-one)
- fact_remittance.payer_key → dim_payer (many-to-one)
- fact_remittance.denial_reason_key → dim_denial_reason (many-to-one)
- fact_ar_snapshot.snapshot_date_key → dim_date (many-to-one)
- fact_ar_snapshot.claim_key → dim_claim (many-to-one)
- fact_ar_snapshot.patient_key → dim_patient (many-to-one)
- fact_ar_snapshot.payer_key → dim_payer (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.