How to Model Social Determinants of Health (SDOH) Data in Your Warehouse

Introduction

Social determinants of health data is no longer optional. CMS now requires SDOH screening for several Medicare and Medicaid quality programs. NCQA has added SDOH measures to [HEDIS](/terms/HEDIS). Value-based care contracts increasingly include social needs as risk factors and care gap drivers. Yet in most healthcare data warehouses, SDOH data is scattered across free-text fields, one-off Z-code queries, and disconnected screening tool outputs that nobody has modeled into a usable analytical structure.

This guide provides a concrete data model for SDOH — including the standard code sets, the logical entity design, and the SQL logic for flagging members with active social needs.

---

SDOH Domains

The five core SDOH domains, as defined by Healthy People 2030 and aligned with CMS and NCQA requirements:

---

Standard Code Sets for SDOH

ICD-10-CM Z Codes

The Z55–Z65 code range captures social circumstances. Key codes:

Z55.x — Problems related to education and literacy
Z56.x — Problems related to employment
Z57.x — Occupational exposure to risk factors
Z58.x — Problems related to physical environment
Z59.0 — Homelessness
Z59.1 — Inadequate housing
Z59.4 — Lack of adequate food
Z59.6 — Low income
Z59.7 — Insufficient social insurance and welfare support
Z60.2 — Problems related to living alone
Z60.4 — Social exclusion and rejection
Z63.x — Problems related to primary support group

LOINC SDOH Codes

The Accountable Health Communities (AHC) Health-Related Social Needs (HRSN) screening tool is the CMS standard. Key LOINC codes:

71802-3 — Housing instability risk
88122-7 — Food insecurity risk
93030-5 — Transportation barrier
89555-7 — Interpersonal safety
76513-1 — Financial resource strain

---

Logical Data Model

-- SDOH screening event: one row per screening instance
CREATE TABLE sdoh.screening_event (
  screening_id         VARCHAR(36)   NOT NULL,
  enterprise_member_id VARCHAR(36)   NOT NULL,
  screening_tool       VARCHAR(50)   NOT NULL,  -- AHC_HRSN, PRAPARE, WE_CARE
  screened_at          TIMESTAMP     NOT NULL,
  screened_by_npi      CHAR(10),
  encounter_id         VARCHAR(36),
  screening_location   VARCHAR(100),
  PRIMARY KEY (screening_id)
);

-- Individual SDOH need: one row per identified need per screening
CREATE TABLE sdoh.social_need (
  need_id              BIGINT GENERATED ALWAYS AS IDENTITY,
  screening_id         VARCHAR(36)   NOT NULL,
  enterprise_member_id VARCHAR(36)   NOT NULL,
  sdoh_domain          VARCHAR(30)   NOT NULL,  -- HOUSING, FOOD, TRANSPORT, SOCIAL, FINANCIAL
  loinc_code           VARCHAR(20),             -- screening question LOINC
  icd10_z_code         VARCHAR(10),             -- mapped Z code
  need_present_flag    BOOLEAN       NOT NULL,
  severity             VARCHAR(10),             -- MILD, MODERATE, SEVERE
  identified_at        TIMESTAMP     NOT NULL,
  resolved_at          TIMESTAMP,
  resolution_type      VARCHAR(50),             -- REFERRAL_COMPLETED, SELF_RESOLVED, DECLINED
  PRIMARY KEY (need_id),
  FOREIGN KEY (screening_id) REFERENCES sdoh.screening_event(screening_id)
);

-- Resource referral: when a need is addressed with a community resource
CREATE TABLE sdoh.resource_referral (
  referral_id          BIGINT GENERATED ALWAYS AS IDENTITY,
  need_id              BIGINT        NOT NULL,
  enterprise_member_id VARCHAR(36)   NOT NULL,
  resource_name        VARCHAR(200),
  resource_type        VARCHAR(50),  -- FOOD_BANK, HOUSING_SHELTER, TRANSPORT_VOUCHER
  referred_at          TIMESTAMP     NOT NULL,
  referral_status      VARCHAR(20),  -- PENDING, ACCEPTED, COMPLETED, DECLINED
  closed_at            TIMESTAMP,
  PRIMARY KEY (referral_id),
  FOREIGN KEY (need_id) REFERENCES sdoh.social_need(need_id)
);

---

Linking SDOH to Claims and Member Records

SDOH data is most analytically valuable when it is linked to the member record and to claims. The enterprise member ID is the join key:

-- Members with active food insecurity and ED utilization in the last 90 days
SELECT
  m.enterprise_member_id,
  m.last_name,
  m.first_name,
  m.risk_segment,
  COUNT(c.claim_id)    AS ed_visits_90d,
  sn.sdoh_domain,
  sn.severity
FROM mdm.member_master m
JOIN sdoh.social_need sn
  ON m.enterprise_member_id = sn.enterprise_member_id
  AND sn.sdoh_domain = 'FOOD'
  AND sn.need_present_flag = TRUE
  AND sn.resolved_at IS NULL
JOIN claims.claim_header c
  ON m.enterprise_member_id = c.enterprise_member_id
  AND c.service_from_date >= CURRENT_DATE - 90
  AND c.place_of_service_code = '23'  -- Emergency Room
GROUP BY 1, 2, 3, 4, 6, 7
HAVING COUNT(c.claim_id) >= 2
ORDER BY ed_visits_90d DESC;

---

SDOH Flag Logic for Analytics

Many HEDIS and risk models use a simple SDOH flag — a boolean that indicates whether a member has any active social need. Here is the flag logic:

-- Build SDOH summary flag table for analytics layer
CREATE TABLE analytics.member_sdoh_summary AS
SELECT
  enterprise_member_id,
  MAX(CASE WHEN sdoh_domain = 'HOUSING'   AND need_present_flag AND resolved_at IS NULL THEN 1 ELSE 0 END) AS housing_instability_flag,
  MAX(CASE WHEN sdoh_domain = 'FOOD'      AND need_present_flag AND resolved_at IS NULL THEN 1 ELSE 0 END) AS food_insecurity_flag,
  MAX(CASE WHEN sdoh_domain = 'TRANSPORT' AND need_present_flag AND resolved_at IS NULL THEN 1 ELSE 0 END) AS transport_barrier_flag,
  MAX(CASE WHEN sdoh_domain = 'SOCIAL'    AND need_present_flag AND resolved_at IS NULL THEN 1 ELSE 0 END) AS social_isolation_flag,
  MAX(CASE WHEN sdoh_domain = 'FINANCIAL' AND need_present_flag AND resolved_at IS NULL THEN 1 ELSE 0 END) AS financial_strain_flag,
  MAX(CASE WHEN need_present_flag AND resolved_at IS NULL THEN 1 ELSE 0 END)                               AS any_sdoh_flag,
  COUNT(DISTINCT CASE WHEN need_present_flag AND resolved_at IS NULL THEN sdoh_domain END)                 AS active_need_count,
  MAX(identified_at) AS most_recent_screening_date
FROM sdoh.social_need
GROUP BY enterprise_member_id;

---

Analytics Use Cases

Risk stratification: Members with 2+ active SDOH needs have 40–60% higher ED utilization than similar clinical-risk members with no SDOH needs. Include any_sdoh_flag in your risk model feature set.

Care gap identification: HEDIS measures like Controlling Blood Pressure and Comprehensive Diabetes Care have significantly lower compliance rates among members with food insecurity. SDOH-stratified gap analysis enables targeted interventions.

Value-based contract performance: Some VBC contracts now include SDOH screening rates as a quality measure. Track screening_completion_rate by population segment and care manager assignment.

---

Key Takeaways

• SDOH data requires a dedicated schema — do not bury it in claims or encounter tables. The need-level granularity is what makes it analytically useful.
• ICD-10 Z codes in claims capture SDOH retrospectively; LOINC-coded screening tools capture it prospectively. Use both.
• The enterprise member ID is the join key. SDOH is only valuable when linked to the same member across claims, clinical, and risk data.
• Build a summary flag table for analytics consumers — most downstream models need SDOH as a boolean or count, not individual need rows.
• Use the DDL Converter to translate the SDOH schema DDL to your target platform dialect (Snowflake, BigQuery, Redshift) without manual syntax adaptation.