Who Is Churning and Why

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain: Pipeline Design
Difficulty: medium
Seniority: L6

Problem

Subscription records for 200,000 accounts on monthly and annual plans across several tiers live in the application database, which serves the product and has no read capacity for analytics. About 30,000 plan changes, cancellations and reactivations land there daily. Design the pipeline and data model for MRR by tier, cohort churn and renewal risk.

Subscribers churn. The pipeline cannot.

Business requirements

Finance reconciles MRR against the billing provider's dashboard; today the two diverge by enough that finance can't sign off.
Mid-cycle upgrades and downgrades change MRR on the day they happen; the intraday dashboard has to reflect them within the next refresh.
Some cancellations are immediate and some are end-of-period; MRR drops on the day for the first, continues to period end for the second, and the warehouse has to model both.
When a churned account comes back, cohort retention curves have to keep treating them as the original cohort, not as a new acquisition.

Worked solution and explanation

What this really is

This is event sourcing presented as a dashboard request. Every metric finance and growth want (MRR by tier, cohort churn, renewal risk) is a fold over subscription lifecycle events, and the application database only stores the latest state of each row. Anyone can draw Postgres flowing into a warehouse. What separates candidates is whether the history survives the copy. Snapshot the subscriptions table nightly and three things break: a mid-cycle upgrade is invisible until the next snapshot, a cancellation has one end date and no kind, and a reactivated account overwrites its signup date and turns up as a brand-new acquisition.

Walk the requirements

Step 1: Capture changes without touching production

CDC reads the database's change log, so analytics never queries the primary. Polling updated_at can miss a row that changed twice between polls, which is exactly how an upgrade followed by a downgrade disappears. Dedupe on event_id in fact_subscription_events, because the connector delivers at least once.

Step 2: Refresh MRR hourly from the event log

Plan-change events reach the lake within minutes. The hourly dbt run rebuilds today's partition of fact_mrr_daily, so an upgrade at 10:00 is on the dashboard by 11:00 and not at month end. Rebuilding the whole partition keeps reruns from double counting.

Step 3: Put `cancellation_kind` on the event

An 'immediate' cancellation drops MRR on the event date. An 'end_of_period' cancellation keeps MRR flowing until period_end. Any single rule gets one of the two wrong, and finance sees that as unexplained drift.

Step 4: Freeze `cohort_date` in `dim_account_scd2`

SCD2 versions the account's tier and status, but cohort_date is written once at first signup and never updated. A reactivation opens a new version with the original cohort, so retention curves show a return and not a new logo.

The reference design

CDC lands lifecycle events in the lake. dbt builds the event fact, the SCD2 account dimension and an hourly `fact_mrr_daily`. Reconciliation against the billing provider gates the finance publish.

subscription_event_cdc (source, CDC)
cdc_stream (queue, Kafka)
raw_events_lake (storage, Delta Lake)
fact_subscription_events (transform, dbt)
dim_account_scd2 (transform, dbt)
fact_mrr_daily (transform, dbt); freshness: < 1h
analytics_warehouse (storage, warehouse)
billing_provider (source, API)
mrr_reconciliation (quality gate, Great Expectations); alerting: Warehouse MRR differs from billing provider past tolerance
churn_risk_mart (transform, dbt)
finance_dashboard (consumer, dashboard)
growth_dashboard (consumer, dashboard)

subscription_event_cdc -> cdc_stream
cdc_stream -> raw_events_lake
raw_events_lake -> fact_subscription_events
fact_subscription_events -> dim_account_scd2
fact_subscription_events -> fact_mrr_daily
dim_account_scd2 -> fact_mrr_daily
fact_subscription_events -> analytics_warehouse
dim_account_scd2 -> analytics_warehouse
fact_mrr_daily -> analytics_warehouse
analytics_warehouse -> mrr_reconciliation
billing_provider -> mrr_reconciliation
mrr_reconciliation -> finance_dashboard
analytics_warehouse -> churn_risk_mart
churn_risk_mart -> growth_dashboard

Capture: subscription_event_cdc, cdc_stream, raw_events_lake
Model: fact_subscription_events, dim_account_scd2, fact_mrr_daily, analytics_warehouse
Verify and serve: billing_provider, mrr_reconciliation, churn_risk_mart, finance_dashboard, growth_dashboard

Nightly snapshot: One row per subscription with the latest plan_id and canceled_at. Intraday changes are lost, the cancellation kind is guessed, and reactivation overwrites created_at.
CDC event log: One row per lifecycle event with event_ts and cancellation_kind. Any day's MRR can be rebuilt, both cancellation kinds are modeled, and the original cohort survives.

Common follow-up questions

A customer cancels end of period and reactivates the day after `period_end`. What do MRR and the cohort show? (MRR drops on `period_end` and resumes the next day. The account stays in its original cohort and counts as returned.)
A CDC event for a plan change arrives six hours late. How does `fact_mrr_daily` correct the past day? (Tests late data handling: the job reprocesses affected partitions by `event_ts` and overwrites them so reruns never double count.)