The interview is a conversation, not a quiz.

Scenario A — Viewing history (the canonical Netflix prompt)

Move 1 — Clarify (out loud)

• "Is a 'view' play-start or qualified-view (≥120s)? Both — qualified is a derived flag."
• "Grain: per session or per heartbeat? Heartbeats land in fact_play_event_raw; I'll roll up to fact_viewing_session."
• "Latency: recs need <5min, finance is monthly. One model, two consumers."
• "Stable IDs: profile_id stable, title_id stable, device_id rotates."

Move 2 — Grain

One row per profile per title per device per session in fact_viewing_session. A session = continuous play with gaps ≤ 30 min.

Move 3 — Conceptual

dim_profile (SCD2)  ─┐
dim_title   (SCD2)  ─┤
dim_device  (SCD1)  ─┼─▶ fact_viewing_session ─▶ fact_royalty_event
dim_geography       ─┤   (one row per session)   (one row per title per studio per period)
dim_date            ─┘

Move 3 — Physical (Snowflake / Iceberg)

CREATE TABLE fact_viewing_session (
  session_sk        BIGINT,                -- surrogate
  session_id        STRING,                -- natural, from client SDK
  profile_sk        BIGINT,                -- FK to SCD2 effective row
  title_sk          BIGINT,
  device_sk         BIGINT,
  geo_sk            BIGINT,
  date_sk           INT,
  session_start_ts  TIMESTAMP_TZ,
  session_end_ts    TIMESTAMP_TZ,
  watch_seconds     INT,
  qualified_view    BOOLEAN,
  bitrate_avg_kbps  INT,
  ingest_ts         TIMESTAMP_TZ,          -- for idempotent MERGE
  source_system     STRING
)
PARTITION BY date_sk
CLUSTER BY (title_sk, profile_sk);

Move 4 — SCD strategy

• dim_profile — Type 2 (plan changes, country moves affect royalty + recs).
• dim_title — Type 2 (rating changes, regional availability changes).
• dim_device — Type 1 (current fingerprint is enough).
• dim_geography — Type 1.

Move 5 — Stress tests (volunteer)

1. Late events: stream-side watermark 60s; daily batch backfills 48h late tail.
2. Time zones: UTC at storage; royalty uses contract TZ.
3. GDPR delete: token mapping table — invalidate token, sessions become un-rejoinable.
4. Schema drift: Avro + Confluent Schema Registry, backward-compat mode.
5. Hot partitions: popular new releases — sub-partition by HASH(title_sk) MOD 16 on release week.

Scenario B — Production lifecycle (Studio/Content)

The trap: This looks like a "status" column on dim_title. It's not — you lose history. The right answer is a factless fact.

dim_title ──┐
dim_stage ──┼──▶ fact_title_stage_transition (factless)
dim_user  ──┤      grain: one row per title per stage entry
dim_date  ──┘
            └──▶ snap_title_stage_daily (daily snapshot for "where is title X now?")

CREATE TABLE fact_title_stage_transition (
  title_sk          BIGINT,
  from_stage_sk     INT,           -- nullable for greenlight (first stage)
  to_stage_sk       INT,
  transition_ts     TIMESTAMP_TZ,
  actor_user_sk     BIGINT,        -- who moved it
  comment           STRING
);

CREATE TABLE snap_title_stage_daily (
  snap_date              DATE,
  title_sk               BIGINT,
  current_stage_sk       INT,
  days_in_stage          INT,
  total_days_in_pipeline INT
)
PARTITION BY snap_date;

The "stuck project" query reduces to:

SELECT title_sk, current_stage_sk, days_in_stage
FROM snap_title_stage_daily s
WHERE snap_date = CURRENT_DATE
  AND days_in_stage > (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY days_in_stage)
    FROM snap_title_stage_daily
    WHERE snap_date = CURRENT_DATE - 7
      AND current_stage_sk = s.current_stage_sk
  );

Scenario C — Talent contracts & royalties (the SCD2 trap)

Why it's hard: Deal terms get renegotiated. A query for "as of 2025-Q1" must use the deal terms current at that moment.

dim_talent (SCD1)

dim_deal_overall (SCD2)         -- talent ↔ Netflix master agreement
  deal_id, talent_sk, term_start, term_end,
  effective_from, effective_to, is_current

dim_deal_show (SCD2)            -- deal under an overall deal, per show
  show_deal_id, overall_deal_id, title_sk,
  base_episode_fee, residual_pct, mfn_flag,
  effective_from, effective_to, is_current

fact_payout_accrued (grain: one row per deal per period)
  payout_id, talent_sk, show_deal_id, accrual_date,
  base_amount_usd, residual_amount_usd, bonus_amount_usd,
  fx_rate_to_usd, source_currency

fact_payout_disbursed (grain: one row per actual payment)
  payout_id, accrual_id, paid_date, amount_usd, status

The "as of date D" query — show this verbatim

SELECT
  d.talent_sk,
  SUM(d.base_episode_fee * episodes_aired_by(D, d.title_sk))
    + SUM(estimated_residual(d, D)) AS total_liability_usd
FROM dim_deal_show d
WHERE d.effective_from <= :as_of_date
  AND (d.effective_to IS NULL OR d.effective_to > :as_of_date)
GROUP BY d.talent_sk;

Things to volunteer

• MFN clauses: If Actor A has MFN, raising Actor B's fee triggers a backfilled adjustment. Need an audit log of fee changes; recompute affected MFN deals.
• Multi-currency: dim_fx_rate is itself SCD2. Lock to accrual date FX rate, not today's.
• Forecast: Monte Carlo on residuals — separate ML model produces forecast_payout_low/mid/high; DE owns the join, not the model.

Scenario D — A/B experimentation at scale

The model is two facts joined late:

fact_assignment (grain: one row per profile per experiment)
  profile_sk, experiment_id, variant, assigned_ts, bucketing_hash
  -- sticky on profile_sk + experiment_id

fact_exposure (grain: one row per profile per experiment per day)
  profile_sk, experiment_id, variant, exposure_date, exposure_count

dim_experiment (SCD2)
  experiment_id, name, primary_metric,
  start_date, end_date, traffic_alloc_pct, mex_layer

Lift compute is metric-agnostic

WITH exposed AS (
  SELECT profile_sk, variant
  FROM fact_assignment
  WHERE experiment_id = :exp
    AND assigned_ts >= :start AND assigned_ts < :end
),
metric AS (
  SELECT profile_sk, SUM(qualified_view_seconds) AS m
  FROM fact_viewing_session
  WHERE session_start_ts BETWEEN :start AND :end
  GROUP BY profile_sk
)
SELECT
  e.variant,
  COUNT(*) AS users,
  AVG(COALESCE(m.m, 0)) AS metric_avg,
  STDDEV(COALESCE(m.m, 0)) AS metric_std
FROM exposed e LEFT JOIN metric m USING (profile_sk)
GROUP BY e.variant;

Scenario E — Renewal scorecard (Sr/Staff differentiator)

This is a data product, not a table. The right answer is to design the layer above the warehouse:

mart_series_renewal_scorecard (one row per series per snapshot_date)
├── viewing_signal     — fact_viewing_session, normalized for catalog effect
├── cost_signal        — fact_production_spend + amortization curve
├── talent_signal      — projected renewal cost from dim_deal
└── benchmark_signal   — k-NN on dim_title features → top 5 comparable

That sentence alone moves you from Senior to Staff signal.

SQL Q1 — Cross-device sessionization (Studio twist)

WITH events AS (
  SELECT profile_id, device_id, ts,
    LAG(ts) OVER (PARTITION BY profile_id ORDER BY ts) AS prev_ts
  FROM fact_play_event
),
flagged AS (
  SELECT *, CASE WHEN prev_ts IS NULL
                  OR TIMESTAMPDIFF('second', prev_ts, ts) > 1800
                THEN 1 ELSE 0 END AS is_new_session
  FROM events
),
sessionized AS (
  SELECT *, SUM(is_new_session) OVER (PARTITION BY profile_id ORDER BY ts) AS session_num
  FROM flagged
)
SELECT profile_id, session_num,
  MIN(ts) AS session_start, MAX(ts) AS session_end,
  COUNT(*) AS event_count,
  COUNT(DISTINCT device_id) AS devices_in_session,
  CASE WHEN COUNT(DISTINCT device_id) > 1 THEN TRUE ELSE FALSE END AS is_handoff
FROM sessionized GROUP BY 1, 2;

SQL Q2 — SCD2 point-in-time join (Studio/Finance)

SELECT t.title_id,
  SUM(s.watch_seconds * p.price_per_second_usd) AS revenue_usd
FROM fact_viewing_session s
JOIN dim_title t ON s.title_sk = t.title_sk
JOIN dim_pricing_plan p
  ON p.plan_id = t.plan_id
 AND s.session_start_ts >= p.effective_from
 AND s.session_start_ts <  COALESCE(p.effective_to, '9999-12-31')
WHERE s.session_start_ts >= '2025-01-01'
  AND s.session_start_ts <  '2025-04-01'
GROUP BY t.title_id;

SQL Q3 — Tiered deduplication with quality preference

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY
        CASE WHEN watch_seconds IS NOT NULL THEN 0 ELSE 1 END,
        ingest_ts DESC
    ) AS rn
  FROM fact_play_event_raw
)
WHERE rn = 1;

SQL Q4 — Density-aware running total with date spine

WITH dates AS (
  SELECT DATEADD('day', seq4(), '2025-01-01') AS d
  FROM TABLE(GENERATOR(ROWCOUNT => 365))
),
title_release AS (
  SELECT title_id, MIN(DATE(session_start_ts)) AS release_date
  FROM fact_viewing_session GROUP BY title_id
),
spine AS (
  SELECT t.title_id, d.d
  FROM title_release t CROSS JOIN dates d
  WHERE d.d >= t.release_date
),
daily AS (
  SELECT s.title_id, s.d,
    COALESCE(SUM(CASE WHEN f.qualified_view_flag THEN 1 END), 0) AS daily_views
  FROM spine s
  LEFT JOIN fact_viewing_session f
    ON f.title_id = s.title_id AND DATE(f.session_start_ts) = s.d
  GROUP BY 1, 2
)
SELECT title_id, d, daily_views,
  SUM(daily_views) OVER (PARTITION BY title_id ORDER BY d) AS cumulative,
  CASE
    WHEN SUM(daily_views) OVER (PARTITION BY title_id ORDER BY d) >= 1000000
     AND COALESCE(SUM(daily_views) OVER (
           PARTITION BY title_id ORDER BY d
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) < 1000000
    THEN TRUE ELSE FALSE
  END AS crossed_1m_today
FROM daily ORDER BY title_id, d;

SQL Q5 — Streaming SQL (Flink tumbling window)

-- Flink SQL
CREATE TABLE play_events (
  profile_id    BIGINT,
  title_id      BIGINT,
  watch_seconds INT,
  event_ts      TIMESTAMP(3),
  WATERMARK FOR event_ts AS event_ts - INTERVAL '60' SECOND
) WITH ('connector' = 'kafka', 'topic' = 'play_events_v1', ...);

SELECT title_id,
  TUMBLE_START(event_ts, INTERVAL '5' MINUTE) AS window_start,
  TUMBLE_END  (event_ts, INTERVAL '5' MINUTE) AS window_end,
  COUNT(*)                   AS qualified_events,
  COUNT(DISTINCT profile_id) AS unique_viewers
FROM play_events
WHERE watch_seconds >= 120
GROUP BY title_id, TUMBLE(event_ts, INTERVAL '5' MINUTE);

Python P1 — Sessionization (the warmup)

from collections import defaultdict

def sessionize(events, gap=1800):
    by_user = defaultdict(list)
    for uid, ts in events:
        by_user[uid].append(ts)

    sessions = []
    for uid, ts_list in by_user.items():
        ts_list.sort()
        start = end = ts_list[0]
        count = 1
        for ts in ts_list[1:]:
            if ts - end <= gap:
                end, count = ts, count + 1
            else:
                sessions.append((uid, start, end, count))
                start = end = ts
                count = 1
        sessions.append((uid, start, end, count))
    return sessions

Python P2 — Top-K from a stream (the Netflix favorite)

from collections import defaultdict, deque
import heapq

class TopKWatched:
    def __init__(self, window_sec=3600):
        self.window = window_sec
        self.events = deque()                 # (ts, title, watch)
        self.totals = defaultdict(int)        # title -> watch in window

    def add_event(self, ts, title, watch):
        self.events.append((ts, title, watch))
        self.totals[title] += watch
        self._evict(ts)

    def _evict(self, now):
        cutoff = now - self.window
        while self.events and self.events[0][0] < cutoff:
            _, t, w = self.events.popleft()
            self.totals[t] -= w
            if self.totals[t] <= 0:
                del self.totals[t]

    def top_k(self, k):
        return heapq.nlargest(k, self.totals.items(), key=lambda x: x[1])

Python P3 — Watermark sessionizer (out-of-order stream)

from collections import defaultdict
import heapq

class WatermarkSessionizer:
    def __init__(self, gap=1800, lag=300):
        self.gap = gap
        self.lag = lag
        self.buf = defaultdict(list)
        self.max_ts = 0

    def add(self, ts, profile, watch):
        heapq.heappush(self.buf[profile], (ts, watch))
        self.max_ts = max(self.max_ts, ts)

    def poll(self):
        watermark = self.max_ts - self.lag
        closed = []
        for pid, heap in list(self.buf.items()):
            start = end = total = None
            while heap and heap[0][0] < watermark:
                ts, w = heapq.heappop(heap)
                if start is None or ts - end > self.gap:
                    if start is not None:
                        closed.append((pid, start, end, total))
                    start, end, total = ts, ts, w
                else:
                    end, total = ts, total + w
            if start is not None:
                heapq.heappush(heap, (start, total))
            if not heap:
                del self.buf[pid]
        return closed

Python P4 — Co-occurrence at scale (the trap)

from collections import defaultdict
from itertools import combinations

def overlap_pairs(views, threshold=3):
    title_to_users = defaultdict(set)
    for uid, tid in views:
        title_to_users[tid].add(uid)

    pair_counts = defaultdict(int)
    for users in title_to_users.values():
        for u1, u2 in combinations(sorted(users), 2):
            pair_counts[(u1, u2)] += 1

    return [pair for pair, c in pair_counts.items() if c >= threshold]

Python P5 — Reservoir sampling

import random

def reservoir_sample(stream, k):
    reservoir = []
    for i, item in enumerate(stream):
        if i < k:
            reservoir.append(item)
        else:
            j = random.randint(0, i)
            if j < k:
                reservoir[j] = item
    return reservoir

Python P6 — Causal event pairing

def pair_events(events, timeout_sec=14400):
    open_starts = {}
    pairs, orphans = [], []

    for ev in sorted(events, key=lambda e: e['ts']):
        key = (ev['profile_id'], ev['title_id'])
        if ev['type'] == 'play_started':
            if key in open_starts:
                orphans.append(open_starts[key])
            open_starts[key] = (ev['session_id'], ev['ts'])
        elif ev['type'] == 'play_ended':
            if key in open_starts:
                sid, sts = open_starts.pop(key)
                if ev['ts'] - sts <= timeout_sec:
                    pairs.append((sid, sts, ev['ts']))
                else:
                    orphans.append((sid, sts))
    orphans.extend(open_starts.values())
    return pairs, orphans

S1 — Event-time vs processing-time

A: "Event-time is when the user pressed play — the client clock embedded in the event. Processing-time is when our pipeline received it. The gap is small on good networks, hours on mobile reconnects. Production analytics always uses event-time — otherwise a deferred sync from someone in airplane mode looks like a traffic spike. Choosing event-time forces us to handle out-of-order arrivals, which is what watermarks are for."

S2 — Watermarks

A: "A watermark is the pipeline's promise: 'I have seen all events with timestamp ≤ W.' Choosing it is a trade-off — too tight, late events drop; too loose, output is delayed. Standard heuristic: max-seen-ts minus the 99th percentile of observed lateness, plus a safety buffer. For Netflix mobile traffic where lateness p99 is around 30s, I'd start with a 60s lag. The metric to monitor is late_event_count per topic per hour."

S3 — Exactly-once

A: "Each event affects state exactly once, even on failure. Flink achieves it with checkpoint barriers — periodic markers in input streams. When a barrier reaches an operator, it snapshots state to S3. Sinks use two-phase commit — pre-commit on checkpoint, commit on next checkpoint. Cost: latency equal to checkpoint interval, plus snapshot CPU. Most Netflix pipelines run at-least-once + idempotent sinks — same observable behavior, half the cost. Exactly-once is reserved for billing where retries change the answer."

S4 — Lambda vs Kappa

A: "Lambda runs separate batch and stream pipelines, reconciles in serving — gives correctness from batch and freshness from stream. Cost: two codebases, drift. Kappa runs only streaming, replays log for backfills — single codebase, but you must own the replay tooling. I'd pick Kappa today. Iceberg/Delta gave us reliable replay-from-log on commodity object store, which used to be the missing piece."

S5 — Schema evolution

A: "Avro or Protobuf, plus a centralized schema registry. Producers register a schema; consumers fetch by ID embedded in messages. Compatibility modes: backward (consumer reads old + new), forward (old consumer reads new producer), full (both). Default to backward. Without this, a renamed field on the producer side silently breaks every consumer at 3 AM. CI test is a registry pre-commit hook. Breaking changes go to a new topic version (play_events_v2) with a sunset window."

S6 — Backpressure

A: "Downstream operator slower than upstream. Without handling, upstream buffer overflows — OOM or data loss. Flink handles it natively: when a downstream task can't keep up, network buffers fill, upstream naturally slows. Diagnostic is the backpressure indicator in the Flink UI — red on a task means it's the bottleneck. Fixes: scale that operator, find skew (one key dominating — salt or sub-partition), or simplify the operator."

S7 — Tumbling vs hopping vs session

A: "Tumbling — fixed, non-overlapping. Use for periodic reports — hourly DAU. Hopping — fixed, overlapping. Use for moving averages — '5-minute trending titles, updated every minute'. Session — gap-defined dynamic. Use for user behavior — sessionization, binge sequences. The mistake is using tumbling for trending — gives stepwise jumps; hopping smooths them."

S8 — The 8-step narrative

Cover when asked: "Walk me through how a play event travels from a user's TV to the executive dashboard." Use the 8 steps above. Memorize; deliver in 90 seconds.

D1 — Real-time recommendation pipeline

Kafka events → Flink for feature aggregation → feature store (Feast/Redis) → model serving (Triton/TF-Serving). Discuss feature freshness SLA (sub-second) vs. batch features (daily). Cold-start handling (use content embeddings + popularity priors for new users).

D2 — Data quality framework

Layered checks: schema (registry), volume (row count vs 7-day average ± stddev), distribution (KS test on key columns), referential integrity (FK orphans). Write to a monitoring topic; route critical failures to PagerDuty. Each check has a severity (block downstream / alert / log).

D3 — GDPR delete-on-request

Token mapping service: replace user_id with token at ingest. On delete, invalidate the token mapping — events become un-rejoinable. Plus a propagation queue for downstream materialized views. SLA: 30 days. For finance/tax (7-year retention), use anonymization (replace name, keep transaction).

D4 — A/B experimentation infra

Assignment service (sticky bucketing on profile_id hash), exposure logging (every served variant logged), metric pipeline (joins exposures to outcome facts), stats engine (CUPED + sequential testing). SRM monitor as a first-class citizen — observed split deviation >0.5% from expected pages on-call.