// healthcare · churn modeling
TL;DR
A churn model on claims is only as honest as its training table. Build one row per member per month, filter every feature on when the data arrived (received_date), not just when the service happened, label only voluntary exits, leave the unobserved tail null, and make leakage a failing dbt test.
The problem
$ select * from churn_model where accuracy = 0.99;// if this returns rows, something is wrong
Churn models on healthcare data have a nasty property: they look amazing in testing and fail in production. The algorithm is rarely the problem. The training table is.
This post is about building that table in dbt: one row per member per month, features computed only from data you actually had at that moment, and a label that means what you think it means.
Context, short version: in Medicare Advantage, a risk-bearing provider gets paid per member, per month. A member who leaves the plan takes that payment with them. So “who is about to leave?” is a question worth answering early.
Everything below uses a generic schema. No real data.
The inputs
Three feeds, all standard:
stg_eligibility member_id, payer, plan_id, is_dual, eff_start, eff_end, term_reason
stg_medical_claims member_id, claim_id, service_date, received_date, pos_code, is_pcp, in_network, allowed_amt
stg_pharmacy_claims member_id, ndc, fill_date, received_date, days_supply, member_oop_amtThe column that matters most here is received_date. Hold that thought.
What I tried
The obvious build: a member-month table, features filtered on service_date <= month_end, every row labeled 0 or 1. It leaks the future in two ways.
1. Post-exit silence. After a member leaves, their claims drop to zero. If any feature can see data after the prediction date, “no claims recently” becomes a near-perfect predictor of “already gone.” The model learns to detect churn, not predict it.
2. Claims lag. A claim for a visit on March 10 might land in your warehouse on May 20. If you build March’s features by filtering on service_date <= '2026-03-31', you’re using data that didn’t exist on March 31. Backtests will look better than production ever will.
What actually worked
The fix for both leaks is the same rule:
-- a source row is visible at month t only if
service_date <= month_end(t)
AND received_date <= month_end(t)service_date alone is not enough. Filter on when the data arrived.
Layer 1: the member-month spine
-- models/intermediate/int_member_months.sql
with months as (
{{ dbt_utils.date_spine(
datepart="month",
start_date="cast('2023-01-01' as date)",
end_date="cast('2026-11-01' as date)"
) }}
),
spans as (
select
member_id,
payer,
plan_id,
is_dual,
date_trunc('month', eff_start) as span_start_month,
coalesce(eff_end, '9999-12-31'::date) as span_end
from {{ ref('stg_eligibility') }}
)
select
s.member_id,
m.date_month as month_start,
last_day(m.date_month) as month_end,
s.payer,
s.plan_id,
s.is_dual,
datediff('month', min(s.span_start_month) over (partition by s.member_id), m.date_month)
as tenure_months
from months m
join spans s
on m.date_month between s.span_start_month and s.span_end
-- overlapping eligibility spans are common; keep one row per member-month
qualify row_number() over (
partition by s.member_id, m.date_month
order by s.span_start_month desc
) = 1That qualify is not optional. Eligibility files routinely have overlapping or duplicated spans, and without it you get two rows for the same member-month and silently double-count.
Layer 2: the label
What we predict: the member voluntarily leaves in the next 1–3 months.
“Voluntarily” matters. Deaths, moves out of the service area and contract terminations are not predictable from care patterns and nobody can act on them. Keeping them in adds noise and inflates the positive rate. CMS excludes them from its own retention measure for the same reason.
-- models/intermediate/int_member_month_label.sql
with voluntary_exits as (
select
member_id,
eff_end as exit_date
from {{ ref('stg_eligibility') }}
where term_reason = 'voluntary'
and eff_end is not null
),
data_as_of as (
select max(received_date) as as_of
from {{ ref('stg_medical_claims') }}
)
select
mm.member_id,
mm.month_start,
case
-- label window not fully observed yet: unknown, not "stayed"
when dateadd('month', 3, mm.month_end) > d.as_of then null
when exists (
select 1
from voluntary_exits e
where e.member_id = mm.member_id
and e.exit_date > mm.month_end
and e.exit_date <= dateadd('month', 3, mm.month_end)
) then 1
else 0
end as left_next_3m
from {{ ref('int_member_months') }} mm
cross join data_as_of dThe null branch is the subtle one. For the last three months of data, you don’t know yet whether the member leaves. Labeling them 0 teaches the model that recent members never churn.
Layer 3: features with an as-of cutoff
Medical utilization, looking back 12 months from each month end:
-- models/intermediate/int_member_month_medical.sql
select
mm.member_id,
mm.month_start,
count_if(c.pos_code = '23'
and c.service_date > dateadd('month', -6, mm.month_end)) as er_visits_6m,
count_if(c.pos_code = '21'
and c.service_date > dateadd('month', -6, mm.month_end)) as inpatient_6m,
count_if(not c.in_network) as oon_claims_12m,
datediff('month',
max(case when c.is_pcp then c.service_date end),
mm.month_end) as months_since_pcp,
sum(c.allowed_amt) as allowed_12m,
max(c.received_date) as max_med_received_date
from {{ ref('int_member_months') }} mm
left join {{ ref('stg_medical_claims') }} c
on c.member_id = mm.member_id
and c.service_date > dateadd('month', -12, mm.month_end)
and c.service_date <= mm.month_end
and c.received_date <= mm.month_end -- the line that prevents lag leakage
group by 1, 2months_since_pcp will be null for members with no PCP visit in 12 months. That’s a signal, not a missing value. Either keep the null if your model handles it, or cap it at 13.
Pharmacy: refill gaps are one of the better adherence signals. First compute the gap per fill, then aggregate.
-- models/intermediate/int_rx_fill_gaps.sql
select
member_id,
ndc,
fill_date,
received_date,
member_oop_amt,
datediff(
'day',
dateadd('day',
lag(days_supply) over (partition by member_id, ndc order by fill_date),
lag(fill_date) over (partition by member_id, ndc order by fill_date)),
fill_date
) as gap_days
from {{ ref('stg_pharmacy_claims') }}-- models/intermediate/int_member_month_rx.sql
select
mm.member_id,
mm.month_start,
count_if(g.gap_days > 15) as rx_gaps_6m,
sum(g.member_oop_amt) as rx_oop_6m,
count(distinct g.ndc) as distinct_drugs_6m,
max(g.received_date) as max_rx_received_date
from {{ ref('int_member_months') }} mm
left join {{ ref('int_rx_fill_gaps') }} g
on g.member_id = mm.member_id
and g.fill_date > dateadd('month', -6, mm.month_end)
and g.fill_date <= mm.month_end
and g.received_date <= mm.month_end
group by 1, 2Note what’s not captured: a fill that should have happened but didn’t, for a member who stopped refilling entirely. If that matters for your population, add “days since last fill” per chronic drug class.
Layer 4: the panel
-- models/marts/fct_member_month_churn_panel.sql
select
mm.member_id,
mm.month_start,
mm.month_end,
mm.payer,
mm.plan_id,
mm.is_dual,
mm.tenure_months,
month(mm.month_start) as calendar_month,
med.er_visits_6m,
med.inpatient_6m,
med.oon_claims_12m,
med.months_since_pcp,
med.allowed_12m,
rx.rx_gaps_6m,
rx.rx_oop_6m,
rx.distinct_drugs_6m,
med.max_med_received_date,
rx.max_rx_received_date,
lbl.left_next_3m
from {{ ref('int_member_months') }} mm
left join {{ ref('int_member_month_medical') }} med using (member_id, month_start)
left join {{ ref('int_member_month_rx') }} rx using (member_id, month_start)
left join {{ ref('int_member_month_label') }} lbl using (member_id, month_start)calendar_month is in there on purpose. In MA, voluntary exits cluster around the Annual Enrollment Period (Oct 15 – Dec 7, effective Jan 1) and the MA Open Enrollment Period (Jan – Mar). Dual-eligible members get more switching windows. A model blind to the calendar will be confused by that.
Tests: make leakage a failing build
The two max_*_received_date columns exist only so the leak check can be a test instead of a hope.
# models/marts/_marts.yml
models:
- name: fct_member_month_churn_panel
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns: [member_id, month_start]
columns:
- name: left_next_3m
tests:
- accepted_values:
values: [0, 1]
quote: false-- tests/assert_no_future_data_in_features.sql
-- fails if any feature row saw data that arrived after its month_end
select member_id, month_start
from {{ ref('fct_member_month_churn_panel') }}
where max_med_received_date > month_end
or max_rx_received_date > month_endIf someone later “simplifies” a join and drops the received_date filter, this test goes red. That’s the point.
Training without fooling yourself
Split by time, not by row. A random split puts March 2025 rows in both train and test for the same member, and the model memorizes members. Train on months up to some cutoff, test on the months after.
create or replace view churn_train as
select * exclude (member_id, month_end, max_med_received_date, max_rx_received_date)
from fct_member_month_churn_panel
where left_next_3m is not null
and month_start < '2025-07-01';
create or replace view churn_test as
select * exclude (max_med_received_date, max_rx_received_date)
from fct_member_month_churn_panel
where left_next_3m is not null
and month_start >= '2025-07-01';If you’re on Snowflake, the built-in classification function keeps everything in the warehouse. With PHI, not exporting member-level rows to a notebook somewhere is a feature, not a limitation.
create or replace snowflake.ml.classification churn_model(
input_data => system$reference('view', 'churn_train'),
target_colname => 'LEFT_NEXT_3M'
);
select member_id, month_start, left_next_3m,
churn_model!predict(input_data => object_construct(*)) as pred
from churn_test;
call churn_model!show_evaluation_metrics();
call churn_model!show_feature_importance();Don’t look at accuracy. Voluntary disenrollment in MA runs around 9% a year industry-wide, so monthly positives are tiny. “Nobody leaves” is 95%+ accurate. The metric that matches how this gets used: of the top 10% of members by predicted risk, how many actually left? Compare that to the base rate. That ratio is your lift, and it’s what tells you whether the care team’s calls will land on the right people.
What this won’t capture
Be honest about the ceiling. Some of the strongest drivers of switching aren’t in claims at all: a competitor’s richer benefits, a premium change, a broker calling the member in October, a complaint. A claims-based panel catches the members whose care pattern is drifting. It won’t catch the ones who are happy with their doctor and leave for a better dental benefit.
That’s fine. The descriptive version of this table (exit rates by payer, clinic, PCP and month) is often more useful on day one than any model, and it falls out of the same panel for free.
$ dbt build --select +fct_member_month_churn_panel
// green tests > good AUCKey takeaways
- Filter every feature on
received_date <= month_end, not justservice_date. Claims lag and post-exit silence both leak the future otherwise. - Label only voluntary exits, and leave the last three months
nullinstead of0. - Carry the max
received_dateper feature row into the panel so leakage is a failing dbt test, not a code review hope. - Split train and test by time, and judge the model by lift in the top decile, not accuracy.
References
- dbt — Add data tests to your DAG
- dbt-utils —
date_spineandunique_combination_of_columns - Snowflake — Classification (ML Functions)
- Snowflake —
QUALIFY - CMS — Medicare Managed Care Manual, Ch. 2: Enrollment and Disenrollment
- CMS / HHS — Part C and D Star Ratings: Members Choosing to Leave the Plan
- What’s Driving High Disenrollment in Medicare Advantage? (Inquiry, 2019)
11 Nov 2026 · #healthcare #dbt #snowflake #machinelearning
GLB_