Healthcare Claims Snowflake Schema

Healthcare claims snowflake schema

This healthcare claims snowflake schema places billed and paid amounts in FactClaim. The claim references date, patient, provider and diagnosis dimensions. Provider details roll through specialty to department, while diagnosis codes roll through disease group. The normalized structures prevent the same specialty, department or disease group text from being repeated on every dimensional row. Analysts can aggregate claims by date, clinical department, specialty or disease group while retaining a clear relationship back to the individual claim. This is useful where reporting hierarchies are governed separately from transaction data. It can also make changes to clinical groupings visible and reusable across reports.

種類 ERD規格 Chen 1976 / Everest 1976 (crow's foot)エンジン schematex-erd更新日 2026/9/24

依頼内容

“A healthcare claims snowflake schema: FactClaim linked to Date, Patient, Provider, and Diagnosis; normalize Provider through Specialty to Department and Diagnosis through Disease Group.”

次はこちらAdd an insurance plan dimensionAdd a facility table below Department

この図に含まれるもの

図の背後にある意思決定を読み解く。

01Claim grain

Store one row per submitted claim.

02Provider hierarchy

Separate specialty and department.

03Diagnosis grouping

Keep diagnosis groups independent of codes.

Use this for claims reporting that rolls providers and diagnoses into managed hierarchies.

ERDのテンプレートをすべて見る →

よくある質問

What does FactClaim represent?

It represents one claim and its billed and paid measures.

Why separate specialty from provider?

A specialty can be shared by many providers and managed once.

What is a disease group used for?

It groups individual diagnosis codes for higher-level analysis.