Dimensional Modeling for Healthcare Data Warehouses
The definitive guide to dimensional modeling for healthcare data engineers and architects — star schema design, fact and dimension tables, SCD Type 2, conformed dimensions, and production-ready DDL for claims, clinical, and member data in Snowflake, BigQuery, and Databricks.
Why Dimensional Modeling for Healthcare?
Healthcare data warehouses contain some of the most complex analytical data in any industry — claims with dozens of diagnosis codes, members changing plans mid-year, providers affiliated with multiple facilities, and quality measures requiring longitudinal patient histories. Dimensional modeling, pioneered by Ralph Kimball, provides the structural framework that makes this complexity analytically tractable.
The star schema — a central fact table surrounded by dimension tables — is the near-universal data warehouse design for healthcare analytics because its simple join structure enables fast aggregation queries in columnar cloud warehouses, its denormalized dimension tables eliminate complex multi-table joins for common analytics patterns, and its consistent design makes it understandable to analysts and data scientists without deep data engineering expertise.
This guide covers the complete dimensional modeling toolkit for healthcare — fact table grain selection, dimension table design, slowly changing dimension strategies, conformed dimension architecture, and production DDL examples you can adapt for your Snowflake, BigQuery, or Databricks environment.
Healthcare Star Schema — Core Pattern
The healthcare dimensional model centers on claims as the primary fact, surrounded by conformed dimensions shared across all subject areas.
-- ════════════════════════════════════════════════ -- HEALTHCARE STAR SCHEMA — Core Tables -- ════════════════════════════════════════════════ -- Central fact table: one row per claim CREATE TABLE fact_claim_header ( clm_key INTEGER NOT NULL, -- surrogate key -- Dimension foreign keys mbr_key INTEGER NOT NULL, -- → dim_member rndrng_prvdr_key INTEGER NOT NULL, -- → dim_provider billng_prvdr_key INTEGER NOT NULL, -- → dim_provider fac_key INTEGER, -- → dim_facility payer_key INTEGER NOT NULL, -- → dim_payer prim_diag_key INTEGER, -- → dim_icd10 sec_diag_key INTEGER, -- → dim_icd10 svc_dt_key INTEGER NOT NULL, -- → dim_date paid_dt_key INTEGER, -- → dim_date -- Degenerate dimensions (no lookup needed) clm_nbr VARCHAR(20) NOT NULL, clm_type_cd VARCHAR(20), auth_nbr VARCHAR(30), -- Measures bld_amt DECIMAL(12,2) NOT NULL, alwd_amt DECIMAL(12,2), paid_amt DECIMAL(12,2), ded_amt DECIMAL(10,2), copay_amt DECIMAL(10,2), coins_amt DECIMAL(10,2), -- Metadata rec_creat_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, rec_updt_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- Service line fact: one row per claim line CREATE TABLE fact_claim_service_line ( svc_line_key INTEGER NOT NULL, clm_key INTEGER NOT NULL, -- → fact_claim_header proc_key INTEGER NOT NULL, -- → dim_procedure svc_dt_key INTEGER NOT NULL, -- → dim_date line_nbr INTEGER NOT NULL, proc_cd VARCHAR(10), units_qty DECIMAL(8,2), bld_amt DECIMAL(12,2), paid_amt DECIMAL(12,2) );
Need to convert this DDL for Snowflake, BigQuery, or Databricks? Use the free DDL Converter →
Core Healthcare Dimension Tables
DIM_MEMBER — SCD Type 2
The most important conformed dimension — shared across claims, pharmacy, encounters, and quality measures
CREATE TABLE dim_member ( mbr_key INTEGER PRIMARY KEY, -- surrogate key mbr_id VARCHAR(20) NOT NULL, -- natural key -- Demographics mbr_first_nm VARCHAR(100), -- PHI mbr_last_nm VARCHAR(100), -- PHI mbr_dob DATE, -- PHI mbr_age_band VARCHAR(10), -- derived: 0-17, 18-34... mbr_gndr_cd VARCHAR(1), mbr_state_cd VARCHAR(2), -- Enrollment plan_cd VARCHAR(20), grp_nbr VARCHAR(20), sbscrbr_id VARCHAR(20), rltnshp_cd VARCHAR(10), -- SCD Type 2 columns eff_dt DATE NOT NULL, exp_dt DATE, -- NULL = current record curr_rec_flg BOOLEAN NOT NULL DEFAULT TRUE, -- Metadata rec_creat_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_dim_mbr_id ON dim_member(mbr_id); CREATE INDEX idx_dim_mbr_curr ON dim_member(mbr_id) WHERE curr_rec_flg = TRUE;
DIM_PROVIDER — SCD Type 2
Provider dimension — used as both rendering and billing provider via role-playing
CREATE TABLE dim_provider ( prvdr_key INTEGER PRIMARY KEY, npi_nbr VARCHAR(10) NOT NULL, -- natural key npi_type_cd VARCHAR(1), -- 1=Individual, 2=Org prvdr_first_nm VARCHAR(100), prvdr_last_nm VARCHAR(100), prvdr_org_nm VARCHAR(300), prvdr_taxnmy_cd VARCHAR(10), prvdr_spclty_desc VARCHAR(200), prvdr_st_cd VARCHAR(2), npi_sts_cd VARCHAR(1), -- A=Active, D=Deactivated pecos_enrl_flg BOOLEAN DEFAULT FALSE, -- SCD Type 2 eff_dt DATE NOT NULL, exp_dt DATE, curr_rec_flg BOOLEAN NOT NULL DEFAULT TRUE, rec_creat_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );
DIM_DATE — Pre-populated Calendar
Date dimension with healthcare-specific attributes — populate for 20+ years
CREATE TABLE dim_date ( dt_key INTEGER PRIMARY KEY, -- YYYYMMDD format: 20260101 full_dt DATE NOT NULL, -- Calendar attributes cal_yr INTEGER, cal_qtr INTEGER, -- 1-4 cal_mo INTEGER, -- 1-12 cal_mo_nm VARCHAR(10), -- January...December cal_wk_of_yr INTEGER, day_of_wk INTEGER, -- 1=Sunday, 7=Saturday is_weekend BOOLEAN, -- Fiscal attributes (adjust to your fiscal calendar) fisc_yr INTEGER, fisc_qtr INTEGER, -- Healthcare-specific cms_msrmt_yr INTEGER, -- CMS measurement year hedis_yr INTEGER, -- HEDIS measurement year ma_plan_yr INTEGER, -- Medicare Advantage plan year -- Metadata is_last_day_of_mo BOOLEAN, is_last_day_of_qtr BOOLEAN );
SCD Type 2 — Managing Member Changes Over Time
Members change plans, addresses, and demographics throughout the year. Without SCD Type 2, joining a claim from January to a member record updated in December returns December attributes — making historical population analytics incorrect.
SCD Type 2 Merge Pattern — Snowflake
-- SCD Type 2 merge for DIM_MEMBER
-- Expire changed records and insert new versions
MERGE INTO dim_member tgt
USING (
SELECT
mbr_id,
mbr_first_nm, mbr_last_nm, mbr_dob,
plan_cd, grp_nbr, sbscrbr_id, rltnshp_cd,
mbr_gndr_cd, mbr_state_cd,
CURRENT_DATE as eff_dt
FROM stg_member_current
) src
ON tgt.mbr_id = src.mbr_id
AND tgt.curr_rec_flg = TRUE
WHEN MATCHED AND (
tgt.plan_cd != src.plan_cd OR
tgt.mbr_state_cd != src.mbr_state_cd OR
tgt.rltnshp_cd != src.rltnshp_cd
) THEN UPDATE SET
tgt.exp_dt = CURRENT_DATE - 1,
tgt.curr_rec_flg = FALSE,
tgt.rec_updt_ts = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (
mbr_id, mbr_first_nm, mbr_last_nm, mbr_dob,
plan_cd, grp_nbr, sbscrbr_id, rltnshp_cd,
mbr_gndr_cd, mbr_state_cd,
eff_dt, exp_dt, curr_rec_flg
) VALUES (
src.mbr_id, src.mbr_first_nm, src.mbr_last_nm, src.mbr_dob,
src.plan_cd, src.grp_nbr, src.sbscrbr_id, src.rltnshp_cd,
src.mbr_gndr_cd, src.mbr_state_cd,
src.eff_dt, NULL, TRUE
);
-- Insert new versions for changed records
INSERT INTO dim_member (
mbr_id, mbr_first_nm, mbr_last_nm, mbr_dob,
plan_cd, grp_nbr, sbscrbr_id, rltnshp_cd,
mbr_gndr_cd, mbr_state_cd, eff_dt, exp_dt, curr_rec_flg
)
SELECT
src.mbr_id, src.mbr_first_nm, src.mbr_last_nm, src.mbr_dob,
src.plan_cd, src.grp_nbr, src.sbscrbr_id, src.rltnshp_cd,
src.mbr_gndr_cd, src.mbr_state_cd,
CURRENT_DATE, NULL, TRUE
FROM stg_member_current src
JOIN dim_member tgt
ON tgt.mbr_id = src.mbr_id
AND tgt.curr_rec_flg = FALSE
AND tgt.exp_dt = CURRENT_DATE - 1;ON f.mbr_id = d.mbr_id AND f.svc_dt BETWEEN d.eff_dt AND COALESCE(d.exp_dt, '9999-12-31')Conformed Dimensions — Cross-Domain Analytics
Conformed dimensions enable consistent analytics across subject areas. The same DIM_MEMBER joined to claims, pharmacy, encounters, and quality measures ensures member attributes are defined identically everywhere.
| Dimension | Used By Fact Tables | SCD Type | Key Design Note |
|---|---|---|---|
| DIM_MEMBER | Claims, Pharmacy, Encounters, Quality, Eligibility | Type 2 | Point-in-time join essential for historical accuracy |
| DIM_PROVIDER | Claims (rendering + billing), Encounters, Credentialing | Type 2 | Role-playing dimension — same table, different FK aliases |
| DIM_FACILITY | Claims, Encounters, ADT Events | Type 1 | Facility attributes rarely change; Type 1 overwrite acceptable |
| DIM_PAYER | Claims, Eligibility, Contracts | Type 1 | Payer name/ID changes are rare; overwrite is fine |
| DIM_ICD10 | Claims (multiple DX), Quality Measures, Risk Adjustment | Type 1 + annual snapshot | Reload annually in October; keep prior year versions |
| DIM_PROCEDURE | Claims, Encounters, Orders | Type 1 + annual snapshot | CPT codes update annually; version by effective year |
| DIM_DATE | All fact tables | Static | Pre-populate 20 years; never changes once loaded |
| DIM_PLAN | Eligibility, Claims, Quality | Type 2 | Plan design changes annually — history critical for HEDIS |
dbt Patterns for Healthcare Dimensional Models
models/marts/dim_member.sql — SCD Type 2 in dbt
{{ config(
materialized='incremental',
unique_key='mbr_key',
incremental_strategy='merge',
schema='marts',
tags=['dimension', 'member', 'scd2']
) }}
with source as (
select * from {{ ref('stg_member_eligibility') }}
),
-- Identify changed records using hash comparison
with_hash as (
select
mbr_id,
mbr_first_nm, mbr_last_nm, mbr_dob,
plan_cd, grp_nbr, rltnshp_cd, mbr_state_cd,
md5(concat_ws('|',
plan_cd, grp_nbr, rltnshp_cd, mbr_state_cd
)) as row_hash
from source
),
final as (
select
{{ dbt_utils.generate_surrogate_key(['mbr_id', 'eff_dt']) }} as mbr_key,
mbr_id,
mbr_first_nm, mbr_last_nm, mbr_dob,
plan_cd, grp_nbr, rltnshp_cd, mbr_state_cd,
row_hash,
current_date as eff_dt,
cast('9999-12-31' as date) as exp_dt,
true as curr_rec_flg,
current_timestamp as rec_creat_ts
from with_hash
)
select * from finalmodels/marts/dim_member.yml
version: 2
models:
- name: dim_member
columns:
- name: mbr_key
tests: [not_null, unique]
- name: mbr_id
tests: [not_null]
- name: eff_dt
tests: [not_null]
- name: curr_rec_flg
tests:
- accepted_values:
values: [true, false]
- name: plan_cd
tests: [not_null]Validate your column naming against ISO-11179 standards using the free Naming Auditor →
Healthcare Dimensional Modeling Best Practices
- →Define grain before writing DDL: State the grain of every fact table explicitly in a comment before writing any column — one row per claim, per service line, per encounter, per member per month. Grain violations cause incorrect aggregations that are difficult to debug.
- →Use surrogate keys everywhere: Never use natural keys (NPI, member ID, claim number) as dimension primary keys. Surrogate keys protect fact tables from source system key changes and support SCD Type 2 history without changing fact table foreign keys.
- →Conform DIM_MEMBER and DIM_PROVIDER first: Build your conformed member and provider dimensions before any fact tables. Every subject area needs them, and inconsistent member definitions across claims and quality measures create trust-destroying discrepancies.
- →Store degenerate dimensions in fact tables: Claim number, authorization number, and line number have no descriptive attributes worth a dimension table. Store them directly in the fact table as degenerate dimensions — they are useful for drilling to source records without a JOIN overhead.
- →Always include PHI sensitivity metadata: Tag PHI columns in your dbt schema YAML with meta: {phi: true} so data catalog tools and access control systems can automatically enforce appropriate protections across your dimensional model.
- →Pre-aggregate slowly for performance: For common aggregations like PMPM cost, member months, and quality measure rates, create aggregate fact tables at the month-member-plan grain alongside atomic claim-level facts to accelerate dashboard queries without full table scans.
Frequently Asked Questions
What is dimensional modeling in healthcare?
Dimensional modeling is a data warehouse design technique that organizes data into fact tables containing quantitative measures and dimension tables containing descriptive attributes, connected by foreign keys. In healthcare, fact tables represent measurable events such as claims, encounters, pharmacy dispensings, and quality measure results, while dimension tables provide context such as member demographics, provider specialty, facility type, and diagnosis codes. The resulting star schema enables fast analytical queries across large healthcare datasets in Snowflake, BigQuery, and Databricks.
What is the difference between a fact table and a dimension table in healthcare?
A fact table stores quantitative, measurable events — in healthcare these are typically claims (FACT_CLAIM_HEADER), encounters (FACT_ENCOUNTER), pharmacy dispensings (FACT_PHARMACY_CLAIM), and quality measure results (FACT_QUALITY_MEASURE). A dimension table provides descriptive context for those events — DIM_MEMBER contains member demographics, DIM_PROVIDER contains provider specialty and NPI, DIM_FACILITY contains facility type and location, and DIM_DATE contains calendar and fiscal period attributes. Fact tables are large and append-mostly; dimension tables are smaller and change slowly over time.
What is SCD Type 2 and when should I use it in healthcare?
Slowly Changing Dimension Type 2 handles attribute changes by inserting a new row with updated values and setting expiration dates on the prior row, preserving full history. In healthcare, SCD Type 2 is essential for DIM_MEMBER where members change plans, addresses, and eligibility status, and DIM_PROVIDER where providers change affiliations, specialties, or credentialing status. Without SCD Type 2, joining a current member record to a historical claim will reflect the member's current attributes rather than what was true at time of service, causing incorrect population health analytics.
What are conformed dimensions and why do they matter in healthcare?
Conformed dimensions are dimension tables shared across multiple fact tables, ensuring consistent definitions for common entities. In healthcare, DIM_MEMBER and DIM_PROVIDER are the canonical conformed dimensions — the same DIM_MEMBER table should be joined to FACT_CLAIM_HEADER, FACT_PHARMACY_CLAIM, FACT_ENCOUNTER, and FACT_QUALITY_MEASURE, so that member attributes such as age band, plan type, and risk tier are defined consistently across all analytics domains. Without conformed dimensions, the same member may be characterized differently in claims versus clinical versus quality measure analytics.
Should I use a star schema or snowflake schema for healthcare?
For most healthcare analytical workloads, use a star schema with denormalized dimensions. The slight storage overhead of denormalization is negligible in columnar cloud warehouses like Snowflake and BigQuery, while the simpler join structure makes queries faster and easier to write. Snowflake schema normalization of dimensions is occasionally justified for very large provider or facility hierarchies where taxonomy and specialty data changes frequently and maintaining multiple normalized tables reduces update overhead, but this is the exception rather than the rule in modern healthcare data warehouse design.
How many grain levels should a healthcare fact table have?
Each fact table should have exactly one grain — the most atomic level of detail the fact represents. FACT_CLAIM_HEADER should be one row per claim, not per service line (use a separate FACT_CLAIM_SERVICE_LINE for line-level detail). FACT_ENCOUNTER should be one row per encounter. FACT_PHARMACY_CLAIM should be one row per dispensing. Mixing grains in a single fact table causes incorrect aggregations — a common mistake is including both claim-level and service-line-level measures in the same fact table, causing double-counting when summed.
How do I handle ICD-10 diagnosis codes in dimensional modeling?
Build a DIM_ICD10 reference dimension containing all valid ICD-10-CM codes with descriptions, chapter classifications, HCC mappings, and billable indicators, stored as VARCHAR(7) — never INTEGER. In FACT_CLAIM_HEADER, store up to 12 diagnosis code foreign keys (prim_diag_key, sec_diag_key through diag_key_12) that join to DIM_ICD10. For quality measure and population health analytics that require flexible diagnosis code filtering, also maintain a separate FACT_CLAIM_DIAGNOSIS bridge table with one row per claim-diagnosis combination, enabling efficient diagnosis-based cohort identification without multi-column OR conditions.
What is a date dimension and how should I design it for healthcare?
A date dimension (DIM_DATE) contains one row per calendar date with pre-computed attributes enabling flexible time-based filtering and grouping without date function calculations at query time. For healthcare, include calendar year, quarter, month, and week; fiscal year and quarter aligned to your organization's fiscal calendar; CMS measurement year for HEDIS and Star Ratings reporting; Medicare Advantage plan year; and a is_weekend flag for utilization pattern analysis. Pre-populate DIM_DATE for 20+ years to cover historical claims data and future projection periods.