An insurance claims analytics warehouse combines claim header and line-item transaction facts with an accumulating snapshot fact to monitor adjudication lifecycles. Clinical procedure and diagnosis codes link to patient, provider, and payer dimensions, using bridge tables for multi-diagnosis claims alongside rigorous HIPAA privacy controls.
Healthcare claim lifecycle and grains
Claims processing spans two fundamental structures: financial line items and lifecycle tracking:
fact_claim_lines (transaction grain: one row per billed service line) claim_id (degenerate dimension) claim_line_number patient_key provider_key payer_key procedure_key (CPT / HCPCS code) service_date_key billed_charge_amount allowed_amount paid_amount copay_amount coinsurance_amount fact_claim_lifecycle (accumulating snapshot: one row per claim submission) claim_id patient_key provider_key payer_key submitted_date_key adjudicated_date_key payment_date_key denial_date_key claim_status (submitted, in_review, approved, paid, denied, appealed) denial_reason_code total_billed_amount total_adjudicated_amount adjudication_lag_days
A review of the functional separation:
fact_claim_linescaptures individual medical procedures, units of service, and contracted insurance reimbursement rates. Professional outpatient claims bill on discrete procedure lines, while institutional hospital claims group services into Diagnosis-Related Groups (DRG).fact_claim_lifecyclecaptures the operational pipeline, measuring processing lag between submission and payment while isolating denial rates across insurance payers.- Claim adjustments and re-adjudications generate revision sequence numbers, preserving the exact audit trail of payer decisions over time.
- Denial reason codes distinguish clinical necessity disputes from technical billing omissions like missing physician credentials.
Diagnosis bridges and provider dimensions
Claims capture complex clinical relationships that require specialized dimensional designs:
dim_diagnosis: Stores standardized International Classification of Diseases (ICD-10) diagnosis codes. Because a single claim can involve multiple diagnoses, usebridge_claim_diagnoseswith adiagnosis_prioritycolumn (primary, secondary, tertiary) to model the relationship without cartesian explosions.dim_provider: Captures physician National Provider Identifier (NPI), medical specialty, and clinic network affiliation.dim_procedure: Normalizes Current Procedural Terminology (CPT) codes and service categories.
The diagnosis bridge table also includes an ICD version indicator to distinguish legacy ICD-9 historical data from modern ICD-10 codings, allowing clinical analysts to slice disease patterns across multi-year studies without mixing coding systems.
HIPAA compliance and sensitive attributes
Healthcare models must adhere strictly to HIPAA regulations protecting Protected Health Information (PHI). Patient names, social security numbers, and contact details must be segregated into a secure dimension table with column-level encryption and dynamic data masking. Under the HIPAA Safe Harbor method, dates must be truncated and geographic codes restricted to protect patient privacy. Analysts querying healthcare claims work with synthetic patient_key identifiers, while access to re-identifiable demographic attributes requires audited, role-based database privileges.