The Plan That Changed Twice This Month

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5
Asked in
Netflix

Problem

A subscription streaming service is building a warehouse to measure churn and downgrade rates and content engagement, and it needs to reconstruct exactly which plan any subscriber was on at any past date even when someone starts, downgrades, and cancels all inside the same billing period, so each of those changes has to survive as its own timestamped record. Viewing has to be analyzed on its own, including ad-break exposure for the ad-supported tier, and churn and downgrades must be sliceable by the channel a subscriber was acquired through and the month they signed up. When a subscriber asks to be forgotten, their personal details must be removable while the historical viewing and subscription records stay intact under a stable, non-identifying key.

Subscribers come, go, downgrade, and share. The schema has to keep up.

Worked solution and explanation

What this really is

This is an event log dressed up as a subscriber table. The real question is grain: if one subscriber starts, downgrades and cancels inside the same billing month, how many rows exist? Anyone can draw a star. What separates candidates is where the plan change lives. The trap is SCD Type 2 on dim_plan, or a plan column on dim_subscriber. Either one holds a single plan state per period, so the second change in the month overwrites the first. Downgrade and churn counts undercount, and net MRR stops being an additive sum.

Building it

Step 1: Declare fact_subscriptions at event grain

Use one row per lifecycle event, keyed by subscription_event_id, with event_type ('start', 'downgrade', 'cancel'), event_ts and mrr_delta. Three changes in one month become three rows. The plan held at any past instant is the plan_sk on the latest event at or before it, so nothing is stored twice.

Step 2: Keep dim_plan flat

tier, has_ads and monthly_price describe the product. Version it to track customer moves and every downgrade by any subscriber mints a new plan row: the dimension turns into a disguised fact table.

Step 3: Give viewing its own fact

fact_viewing stores one row per playback, with watch_seconds and ad_break_count. It has a different grain and orders of magnitude more volume. Fold it into the subscription fact and half the columns on every row are null.

Step 4: Key both facts to dim_date with real date columns

Add event_date_key and view_date_key as FKs to dim_date.date_key. A calendar edge has to start from a column that means a day; drawing it off subscriber_sk joins customers to dates by coincidence.

Step 5: Separate identity from history for erasure

The facts carry only the surrogate subscriber_sk. A forget request scrubs subscriber_nk and any personal fields on the dim_subscriber row, while the meaningless subscriber_sk keeps every event and playback joined. acquisition_channel and signup_ts stay for cohort slicing.

Event-grain `fact_subscriptions` and playback-grain `fact_viewing`, both keyed to a conformed `dim_date`
  • dim_subscriber: subscriber_sk BIGINT PK, subscriber_nk TEXT, country TEXT, acquisition_channel TEXT, signup_ts TIMESTAMP
  • dim_plan: plan_sk BIGINT PK, plan_code TEXT, tier TEXT, has_ads BOOLEAN, monthly_price DECIMAL
  • dim_content: content_sk BIGINT PK, title TEXT, content_type TEXT, duration_sec INT
  • dim_date: date_key INT PK, calendar_date DATE, month TEXT, is_month_end BOOLEAN
  • fact_subscriptions: subscription_event_id BIGINT PK, subscriber_sk BIGINT FK, plan_sk BIGINT FK, event_date_key INT FK, event_ts TIMESTAMP, event_type TEXT, mrr_delta DECIMAL
  • fact_viewing: view_id BIGINT PK, subscriber_sk BIGINT FK, content_sk BIGINT FK, view_date_key INT FK, view_ts TIMESTAMP, watch_seconds INT, ad_break_count INT
  • fact_subscriptions.subscriber_sk references dim_subscriber.subscriber_sk
  • fact_subscriptions.plan_sk references dim_plan.plan_sk
  • fact_subscriptions.event_date_key references dim_date.date_key
  • fact_viewing.subscriber_sk references dim_subscriber.subscriber_sk
  • fact_viewing.content_sk references dim_content.content_sk
  • fact_viewing.view_date_key references dim_date.date_key

This is the payoff of event grain. The monthly report is a plain GROUP BY. Churn and downgrades are filtered counts on event_type, and net MRR is SUM(mrr_delta). No window function, no reconciling snapshots.

Monthly churn, downgrades and net MRR by tier
SELECT
    d.month,
    p.tier,
    COUNT(*) FILTER (WHERE e.event_type = 'cancel') AS churns,
    COUNT(*) FILTER (WHERE e.event_type = 'downgrade') AS downgrades,
    SUM(e.mrr_delta) AS net_mrr
FROM fact_subscriptions e
JOIN dim_plan p ON p.plan_sk = e.plan_sk
JOIN dim_date d ON d.date_key = CAST(TO_CHAR(e.event_ts, 'YYYYMMDD') AS INT)
GROUP BY d.month, p.tier

Derives the day key from event_ts; with event_date_key stored it becomes a plain equijoin

Plan column or SCD Type 2
Stores one plan state per subscriber per period. A start, a downgrade and a cancel in one month collapse to 'cancelled', so the downgrade vanishes from the count. mrr_delta has nowhere to live.
Event-grain fact
Every change is a row with its own event_ts and mrr_delta. Counts are filters, MRR is a sum, and point-in-time plan is the latest event at or before the date.

Common follow-up questions