DoorDash Staff Data Scientist Interview Preparation Guide

Situation: Marketing asks for a 10% conversion uplift from a predictive model; Legal mandates model explainability and forbids using demographic features. My goal: reconcile business impact with compliance and deliver an actionable roadmap.

Decomposition

• Technical requirements
  • Target metric: +10% conversion (baseline, measurement window)
  • Data: event-level conversion labels, behavioral and product features, A/B test instrumentation
  • Models: interpretable baseline models (logistic regression, GAMs), and higher-capacity models (tree ensembles, neural nets) with post-hoc explainability
  • Evaluation: holdout/test AUC, calibration, business uplift (incremental conversions), offline causal proxies and online A/B testing
  • Instrumentation: logging, feature lineage, model monitoring
• Policy / compliance requirements
  • No demographic features in training or inference pipelines
  • Explainability standard: feature-level attributions, human-readable rationale, documentation for audits
  • Data governance: provenance, retention, access controls, privacy checks

Negotiation questions to stakeholders

• To Marketing: How is the 10% measured (absolute vs relative)? What is baseline and timeline? Are there acceptable interim goals?
• To Legal: Define acceptable explainability standards (local vs global, fidelity thresholds). Are proxy features or derived embeddings acceptable if not correlated with demographics? What audit artifacts satisfy compliance?
• To both: Are controlled experiments (A/B tests) acceptable to validate uplift? What risk tolerance exists for false positives/negatives?

Prioritized implementation plan (phases + timelines)

1. Week 0–2: Align & define success
   • Confirm baseline conversion, metric, acceptable timelines, and Legal’s explicit explainability requirements.
2. Weeks 2–6: Data & guardrails
   • Build feature catalog; implement policy checks to block demographic fields and flag correlated proxies. Create logging & lineage.
3. Weeks 4–10: Interpretable baseline + monitoring
   • Train interpretable models (logistic regression, GAM) with business features; produce feature importance, SHAP-lite explanations; run offline evaluation against uplift proxy.
4. Weeks 8–16: High-capacity models with constrained features
   • Train XGBoost/NN with explainability pipeline (SHAP, LIME, counterfactuals) and fairness checks. Compare to baseline.
5. Weeks 12–20: Experimentation & deployment
   • Run A/B tests measuring incremental conversions. Only deploy models meeting Legal explainability criteria and passing fairness checks.
6. Ongoing: Monitoring & audits
   • Drift, performance, and periodic re-evaluation; maintain audit docs for Legal.

Trade-offs

• Speed vs rigor: Interpretable models give faster compliance but may yield smaller uplift; complex models can boost performance but require stronger explainability and validation.
• Feature utility vs policy: Blocking demographics may reduce predictive power; mitigate via richer behavioral/product features and causal signals.
• Explainability fidelity vs model accuracy: Post-hoc explanations may not perfectly reflect complex models—Legal may prefer inherently interpretable models.

Expected timeline: measurable uplift signal in A/B within 3–5 months; full production with monitoring and audit trail by ~5 months. If Legal requires only interpretable models, expect smaller immediate uplift but faster deployment (6–8 weeks). If complex models are approved with robust explainability, potential to reach 10% within 4–6 months.

I would start by aligning stakeholders on definitions and acceptable trade-offs, deliver an interpretable baseline quickly to build trust, and iterate toward higher-performing models under Legal’s documented explainability guardrails.

Direct answer: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) is the exact, standard way to compute a median in engines that support ordered-set aggregates (Postgres, Snowflake, BigQuery, DuckDB): it sorts the group and interpolates between the two middle values. Engines without it (MySQL, SQLite, older warehouses) need a manual ROW_NUMBER() workaround that ranks each group and averages the middle row (even count) or takes the single middle row (odd count). Switch to an approximate, sketch-based quantile function once the exact sort-per-group computation becomes the bottleneck at scale, and tell the stakeholder plainly: "this number is very close but not exact, and it will stay fast as the data grows."

Structured elaboration

Approach 1: exact, ordered-set aggregate

PERCENTILE_CONT is an ordered-set aggregate function, not a plain window function. It always needs WITHIN GROUP (ORDER BY ...) and a GROUP BY (or no GROUP BY at all for one global median). PostgreSQL's documentation is explicit that ordered-set aggregates cannot be used as window functions with OVER (...); DuckDB rejects the same syntax outright. On PostgreSQL and DuckDB specifically, if you need a per-partition median attached to every row (window-style), you compute the aggregate in a subquery or CTE (common table expression, a named, reusable subquery introduced with WITH) and join it back, since neither engine lets you bolt OVER (PARTITION BY ...) directly onto PERCENTILE_CONT. That restriction is not universal: Snowflake and Redshift both document PERCENTILE_CONT(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) as valid windowed syntax, so on those two engines the per-partition median can be a single expression with no join-back step.

Approach 2: manual ROW_NUMBER() workaround (portable, works anywhere)

Rank each row within its group by the target column, count the group size, and average the row(s) sitting at the middle rank(s):

WITH numbered AS (
  SELECT
    category_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY amount) AS rn,
    COUNT(amount) OVER (PARTITION BY category_id) AS cnt
  FROM transactions
  WHERE amount IS NOT NULL
)
SELECT category_id, AVG(amount) AS median_amount
FROM numbered
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEILING((cnt + 1) / 2.0))
GROUP BY category_id;

For an odd group size the two FLOOR/CEILING picks land on the same rank, so AVG of one value is just that value; for an even group size they land on the two middle ranks and AVG interpolates the way PERCENTILE_CONT does. NTILE(2) is sometimes mentioned in the same breath as this workaround, but it is a coarser tool: it splits a group into equal-sized buckets and tells you which bucket a row falls in, it does not interpolate a boundary value, so it is the right primitive for quartile/decile bucketing, not a precise substitute for a point-estimate median.

Approach 3: approximate, sketch-based quantile estimation

Once a partition is large enough that sorting it (the O(n log n) step both approaches above rely on) means spilling to disk or holding the whole group in memory, switch to a probabilistic sketch such as t-digest, a compact, mergeable summary structure that approximates quantiles instead of computing them exactly. These structures process the data in one streaming pass, keep a small, bounded amount of state regardless of how many rows feed into it, and answer a quantile query with a small, known error bound instead of an exact sort. Every major analytical warehouse ships a built-in function for this: DuckDB's APPROX_QUANTILE, Snowflake's APPROX_PERCENTILE (built on a t-digest variant), BigQuery's APPROX_QUANTILES (returns an array of boundary values), and Redshift's APPROXIMATE PERCENTILE_DISC.

When to switch, and how to say it to a stakeholder

Switch when the exact computation is measurably the bottleneck: a group so large that the sort spills to disk, a high-cardinality GROUP BY key producing thousands of small sorts, or a dashboard that needs to stay responsive as the underlying table grows unbounded. Do not switch pre-emptively on a small or medium table just because "approximate" sounds more scalable; exactness is free until it isn't. When a stakeholder asks for "the median" and you plan to serve an approximate one, say what changes in plain terms: the number will be extremely close to the true median (typically within a fraction of a percent for standard sketch sizes) and it will stay fast no matter how much data accumulates, versus an exact number that is guaranteed correct but gets slower, and eventually a background batch job, as volume grows. If the number feeds a regulated or audited report, keep it exact; if it feeds a live operational dashboard, approximate is almost always the right trade.

Extension: exact-vs-approximate distinct counts

The same tension shows up one level over, on COUNT(DISTINCT user_id) for a daily active-users metric. An exact distinct count forces the engine to track every value it has already seen (a hash set or a sort-and-dedupe pass), so memory and compute scale with the number of distinct values, not just the row count, and a single query with several COUNT(DISTINCT ...) metrics can be especially expensive because each one needs its own tracking structure. HyperLogLog-style sketches solve it the same way t-digest solves quantiles: hash each value, keep a small fixed-size register array (kilobytes, not megabytes, regardless of whether the true distinct count is a thousand or a billion), and estimate the cardinality from the distribution of leading zero-bits observed. The error shrinks as the sketch's register count grows, and standard sizes comfortably keep the relative error to a low single-digit percentage, which is normally far tighter than a stakeholder needs for a trend line. DuckDB exposes this as APPROX_COUNT_DISTINCT; BigQuery, Snowflake, and Redshift ship equivalents (BigQuery additionally exposes the raw HLL_COUNT.* sketch functions so you can store and merge partial sketches across days instead of recomputing from scratch).

Worked example

Ran against a real transactions(category_id, amount) table in DuckDB, category_id = '1' has an odd count of five rows (10, 20, 30, 40, 100), category_id = '2' has an even count of four rows (5, 15, 25, 35):

-- Approach 1: exact, ordered-set aggregate
SELECT category_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM transactions GROUP BY category_id;

Category 1's median is the literal middle value (30). Category 2 has no single middle value, so PERCENTILE_CONT interpolates between the two middle values 15 and 25, giving (15+25)/2 = 20. The manual ROW_NUMBER() query from Approach 2, run against the same table, returned identical results (30 and 20), confirming the two exact methods agree. APPROX_QUANTILE(amount, 0.5) also returned 30 and 20 on this table, because the demo dataset is far too small for a sketch's approximation error to show up. The whole point of switching to a sketch only pays off at the row counts where an exact sort would actually hurt, which a nine-row demo table cannot honestly simulate; the sketch's real behavior is a bounded-error, bounded-memory summary, not a different answer on small data.

For the distinct-count extension, an active_users(event_date, user_id) table with 3 distinct users on day one and 4 distinct users on day two returned identical results from COUNT(DISTINCT user_id) and APPROX_COUNT_DISTINCT(user_id) for the same reason: at single-digit cardinality there is nothing for a sketch to approximate away.

Key points

• PERCENTILE_CONT is an ordered-set aggregate: exact, needs WITHIN GROUP and GROUP BY, and (verified against PostgreSQL's own documentation and by direct execution) cannot take an OVER (PARTITION BY ...) clause the way a true window function can.
• PERCENTILE_CONT silently drops NULL values from the ordering before computing the median (verified: a group of 10, 20, NULL, 30 returned a median of 20, the same as if the NULL were never there).
• NTILE buckets rows into N groups; it is a bucketing tool, not an interpolating median calculator.

Complexity

Both exact approaches require a full sort of each group: O(n log n) per group, and the whole group must be materialized before the middle value(s) can be identified, so memory scales with the largest group's size. Sketch-based approximate methods process each row once, O(n) total, using a fixed, small amount of memory per sketch regardless of group size, which is exactly what makes them viable when the exact sort would spill to disk.

Edge cases

• Empty group: both PERCENTILE_CONT and the ROW_NUMBER() approach return NULL (no rows to rank).
• NULL values in the target column: excluded from the ordering before the median is computed; make sure cnt in the manual approach is a NULL-excluding count (COUNT(amount), not COUNT(*)) or the rank-matching math drifts.
• Ties at the middle rank(s) are handled correctly by both exact approaches since they operate on sorted position, not on distinct values.

Trade-offs & pitfalls

The trap most candidates miss is reaching for NTILE as if it were a median shortcut; it answers a different question (which bucket) and does not interpolate. The trap on the approximate side is presenting a sketch-based number as if it were exact without saying so: a dashboard KPI silently switching from exact to approximate after a "performance fix" is the kind of change that erodes stakeholder trust in the numbers, even when the approximation itself is well within an acceptable error band. State the switch and its error characteristics explicitly rather than letting it hide inside a query rewrite.

Direct answer

In a regulated industry the story changes in three ways before you ever open a slide deck: what you're allowed to show gets filtered by regulation first, every number needs a documented trail back to its source, and the audience usually includes someone whose job is to say no. The craft is presenting a defensible, compliant insight that is still genuinely persuasive, not a watered-down one.

Structured elaboration

1. Filter the insight through the regulatory constraint before you design the story.
Start by asking what you are legally or contractually allowed to surface, not what would make the best slide. In healthcare this typically means de-identification requirements in the style of HIPAA (the Health Insurance Portability and Accountability Act, the US healthcare privacy law) (removing or generalizing direct identifiers, applying k-anonymity style aggregation so no small cell size can be re-identified); in finance it often means restrictions on disclosing individual customer positions, fair-lending constraints on which variables can drive a decision, and model-risk-management documentation requirements. The constraint is not a formatting afterthought, it determines which findings you can even lead with. A finding that is only compelling at the individual-customer level may need to be re-cut at a cohort or segment level to be shshowable at all.

2. Choose an anonymization or aggregation method proportionate to the risk, and say so explicitly.
Common options, roughly in order of how much detail they preserve: generalization/binning (age becomes a 10-year band), suppression of small cells (any group below a stated threshold, for example n<10, is not reported individually), k-anonymity (restructuring the data so every individual is indistinguishable from at least k-1 others) or differential privacy (adding carefully calibrated statistical noise so no single record can be reverse-engineered from the released numbers) for released datasets, and full aggregation to segment or cohort level for anything leaving the compliance boundary. State which one you used and why in the deck itself, not just in a footnote; a compliance-literate audience will ask, and pre-empting the question builds trust.

3. Build the documentation trail the regulator or auditor would expect.
At minimum: a data lineage note (where the data came from, what was excluded and why), the exact aggregation/anonymization method applied, the population definition, and any known limitations or exclusions. In a bank this is close to what model-risk-management documentation already requires; in healthcare it is close to what a compliance or privacy officer would ask for before approving external use of a dataset. Producing this alongside the insight, not after someone asks for it, is what separates a defensible story from an accidental disclosure.

4. Present the compliance-versus-insight trade-off to the executive directly, instead of hiding it.
An executive who wants the fuller, more granular picture needs to understand that the constraint is not analyst caution, it is a hard requirement with real penalties for the organization. Frame it as: here is the insight we can show at the compliant aggregation level, here is what more granular view would add, and here is why we cannot show that view without additional legal/privacy sign-off (and what that sign-off would require, e.g., a data use agreement, approval from an institutional review board (the ethics body that approves research involving people's data), legal review). This turns a limitation into a scoped, honest recommendation rather than a vague 'we can't share that.'

Worked example

A healthcare analytics team wants to show a hospital system that a proposed care-pathway change reduces 30-day readmissions. The raw finding is a 2.1 percentage point reduction (from a baseline of 15.0% to 12.9%) in a cohort of 640 patients. Because the cohort includes some very small subgroups (for example, a specific rare-diagnosis subgroup of 6 patients), the team cannot report readmission rates by that subgroup without violating a small-cell suppression rule (commonly a minimum reportable cell size, e.g. n>=11, used across many healthcare reporting standards). The story that ships: the top-line reduction at the full-cohort level (which is well above the suppression threshold and safe to report), a note that subgroup-level results are directionally consistent but suppressed below n=11 per data governance policy, and an explicit statement that a follow-up analysis with a larger sample is planned before subgroup-level claims can be made. The executive sees the real result, understands exactly why the subgroup cut is withheld, and knows what it would take to get it.

Trade-offs and pitfalls

• The biggest pitfall is aggregating so heavily to stay 'safe' that the insight becomes too vague to act on; the discipline is finding the least aggregated view that is still compliant, not the most conservative one available.
• A second common mistake is treating the regulatory constraint as something to mention once in an appendix; a compliance-savvy stakeholder will judge you on whether the constraint shaped the analysis from the start, not whether you disclosed it at the end.
• Do not let 'the regulation requires it' become an excuse for skipping normal storytelling discipline (headline, evidence, recommendation); the compliant version of the insight still needs to lead with the so-what, it just has a narrower evidentiary base.
• When in doubt about whether a cut of the data is disclosable, the right escalation path is your privacy/compliance/legal function, not an individual judgment call, and that escalation itself is worth naming as part of your process when a stakeholder pushes for more granularity.

Direct answer

GROW is a four-stage, question-led coaching structure: Goal (what success looks like), Reality (the current state), Options (possible paths forward), and Way forward (specific commitments). Applied to a 1:1 with someone who wants to grow a specific skill, it turns a vague aspiration into a concrete next step, and the same question-led habit also works inside a work review, not only a scheduled conversation.

Walking through the four stages

• Goal. Get specific: "What would 'better at this' actually look like, concretely, and how would you know it happened?"
• Reality. Surface the current state without judgment: "Tell me about a recent situation where this was hard, what made it hard?"
• Options. Generate paths rather than prescribing one: "What could you try next, and who or what could help?"
• Way forward. Get a specific, small commitment: "Which one thing will you actually do before we talk again, and what support do you need from me?"

Socratic questioning is the companion technique that runs through all four stages: instead of stating the answer, ask a question that leads the person to notice the gap themselves ("what did you expect to happen there, versus what actually happened?"). It works well when there's time to let someone arrive at the insight; it works poorly when someone is genuinely blocked and just needs the direct answer.

Extending this into reviewing someone's work

The same question-led approach makes a review of someone's work (code, a document, a design, an analysis) constructive rather than purely corrective. Concrete techniques: a review template that separates "must fix" from "worth considering" from "just for your awareness," so feedback doesn't read as one undifferentiated pile of criticism; annotated examples that show a better version alongside the original with a short reason, not just a comment naming the problem; and a Socratic question left in the review itself ("what happens here if this is empty?") instead of stating the bug outright, when the goal is teaching and there's no urgency forcing a direct fix.

Worked example

In a 1:1, a mentee said they wanted to get better at making structural decisions independently instead of always checking first. Goal: they described what "independent" would look like in practice (making a defined class of calls without asking). Reality: walking through a recent case, they could explain their reasoning but hadn't trusted it enough to act without confirmation. Options: they proposed trying it on a low-stakes decision first and reviewing the reasoning after the fact rather than before. Way forward: they committed to making the next reversible decision on their own and bringing the reasoning to the following session, with an explicit offer of support if it went wrong.

Trade-offs and pitfalls

A common mistake is treating GROW as a rigid script and marching through all four stages regardless of what the person actually needs that day. A stronger approach holds the structure loosely: skip Reality if it's already obvious, compress stages under time pressure, and know when the moment calls for direct answers instead of more questions, especially if something is safety-critical or urgent. Inside reviews specifically, overusing Socratic questions when someone is genuinely stuck can read as withholding rather than teaching, so it's worth pairing questions with a clear direct answer once the teaching moment has been made.

Direct answer

Batch, stochastic, and mini-batch gradient descent all use the same update rule, they only differ in how many examples' gradients get averaged before each step. Batch GD averages over the entire dataset (low noise, expensive per step), stochastic GD uses one example at a time (high noise, cheap per step), and mini-batch splits the difference. Feature scaling matters for all three because an unscaled feature distorts the shape of the loss surface itself, which no choice of batch size fixes.

Structured elaboration

$w \leftarrow w - \eta\, \nabla_w L(w)$

is the shared update rule; what changes between the three variants is what $\nabla_w L(w)$ is computed from:

Why feature scaling matters for all three: the gradient of a linear model's loss with respect to a weight is proportional to that feature's magnitude, so a feature with a much larger numeric range dominates the gradient direction, making the loss surface elongated and ill-conditioned. This slows convergence (forcing a smaller learning rate) regardless of whether you're averaging over the full dataset, one example, or a mini-batch, the batch size changes the noise in the gradient estimate, not the shape of the underlying loss surface.

Worked example

Simple linear model $\hat{y} = wx$ with per-example squared loss, gradient $\nabla_w L_i(w) = 2 x_i (w x_i - y_i)$. Four training points: $(1,3), (2,5), (3,7), (4,9)$, and current weight $w = 1$.

Batch gradient (average of all four): $(-4 -12 -24 -40)/4 = -20$

Single-example (SGD) gradients range from -4 (point 1) to -40 (point 4), a 10x spread depending purely on which point got sampled.

Mini-batch of size 2: points {1,2} average to $(-4-12)/2 = -8$; points {3,4} average to $(-24-40)/2 = -32$, a 4x spread, narrower than single-example SGD but still noisier than the fixed batch value of -20.

With learning rate $\eta = 0.01$, the batch update moves $w$ from 1 to $1 - 0.01 \times (-20) = 1.2$, a single-example update using only point 1 would instead move it to $1 - 0.01 \times (-4) = 1.04$, a visibly smaller and less representative step, which is exactly the variance/noise trade-off in numbers.

Trade-offs & pitfalls

• SGD's noise is a feature, not just a bug: it can help escape shallow local minima or saddle regions, but it requires a decaying learning rate or momentum/Adam to actually settle near a minimum rather than bouncing around it indefinitely.
• Mini-batch size interacts with hardware, not just statistics: very small batches underutilize GPU parallelism, very large batches need learning-rate scaling and warmup to avoid degraded generalization.
• Feature scaling is not optional for any of the three variants, without it, you're forced into a smaller learning rate than the well-scaled problem would need, which slows every variant proportionally.
• Pitfall: tuning batch size as if it were purely a speed knob. It also changes the effective noise in the optimization, which affects both convergence speed and, for non-convex problems, the character of the minimum you land in.

Direct answer. Make deletion tractable by isolating personal identifiers into a single dedicated table keyed by a pseudonymized ID from the start, so every other system, including the data lake, the warehouse, streaming topics, derived and denormalized tables, and ML (machine learning) training sets, references the pseudonym instead of storing the real identifier. A deletion request then becomes deleting or crypto-shredding one row plus fanning out a tombstone event, instead of hunting through every table in the company. For systems that can't practically delete in place, such as backups and some ML training artifacts, apply irreversible pseudonymization or crypto-shredding, and produce an auditable receipt for every system confirming completion, not just a log line saying the job ran.

Schema pattern that makes this tractable. Assign every subject a stable pseudonymized ID, a random or hashed identifier with no reversible mapping exposed outside the identity system, at first contact. Store the mapping between the pseudonym and any real identifying attributes (name, email, phone, address) in exactly one isolated table, access-controlled separately from everything else. Every other table, topic, and derived dataset references the pseudonym, never the real identifier directly; if a downstream system needs to display a real name, it looks it up from the isolated table at render time rather than copying the name into its own storage. Deletion then reduces to deleting or crypto-shredding (discarding the encryption key so the ciphertext becomes permanently unreadable) the one row in the isolated table, after which the pseudonym everywhere else becomes an opaque, non-personal identifier.

Identifying every place the data lives, and how each is handled

• Data lake, raw and curated zones: tag every dataset containing the isolated PII (personally identifiable information) table or a denormalized copy of it at ingestion; a deletion event triggers a rewrite of affected partitions, not a soft flag.
• Warehouse: derived tables should already reference the pseudonym only, so the mapping table gets the delete; any warehouse table that denormalized real attributes directly needs its own remediation job.
• Backups and snapshots: cannot practically be edited in place. Either let old backups expire naturally within a documented retention window, disclosed as part of the deletion SLA (service-level agreement), meaning the data becomes irretrievable in production but persists in an inaccessible backup until it rolls off, or encrypt the isolated PII table with a per-subject key and crypto-shred the key so the backup contains only permanently unreadable ciphertext.
• Streaming topics: publish a deletion or tombstone event keyed by the pseudonym to a compacted topic so consumers apply the same removal; topics that already stored real attributes directly need a targeted purge or key rotation, which is exactly why the isolation pattern should be enforced at the producer level going forward.
• Derived, aggregated, and denormalized tables (the absorbed hard case): an aggregate that already summed or counted a subject's contribution can't be un-summed for one individual without recomputation. Aggregates where the subject's contribution isn't separable, for example "total monthly signups" among thousands, generally don't need per-subject reconstruction since no personal data is retrievable from them; but any denormalized table that copied the subject's raw attributes directly, the exact anti-pattern the isolated-table design avoids, must be identified and remediated as its own class of hard case, because the schema pattern didn't protect it in the first place.
• ML training sets (the other absorbed hard case): a model trained on data containing the subject's attributes is the hardest case, since deleting the raw training row doesn't remove what the model already learned. If the training pipeline referenced the pseudonym and looked up attributes at training time, deletion cascades the same way as any other pseudonym-keyed dataset. If raw PII was baked directly into stored training files, those artifacts must be identified and deleted, and the affected model scheduled for retraining on its normal cadence; exact "unlearning" from an already-trained model without retraining isn't something that can be reliably promised, so a scheduled retrain, not an in-place model edit, is the honest answer.

Verifiable proof for an auditor. A central deletion service issues a deletion event with a unique request ID and records which systems must acknowledge. Each downstream system, lake job, warehouse job, streaming consumer, backup rotation, writes a signed completion receipt back (system name, request ID, timestamp, rows or partitions affected) to an audit log the service owns. That audit log, not each team's word, is what's shown to an auditor: request R, acknowledged complete by N of N required systems, with receipts. Periodically, not just per request, a scan across systems for the deleted pseudonym confirms it doesn't resurface, for example from a delayed backfill job re-ingesting an old file, and any resurfacing is treated as its own incident.

flowchart TD
    A[Deletion request: subject S] --> B[Deletion service]
    B --> C[Isolated PII table: delete or crypto-shred row]
    B --> D[Tombstone event: pseudonym P]
    D --> E[Data lake jobs]
    D --> F[Warehouse jobs]
    D --> G[Streaming consumers]
    D --> H[ML training pipeline: flag for retrain]
    E --> I[Signed completion receipt]
    F --> I
    G --> I
    H --> I
    I --> J[Audit log: request R, N/N acknowledged]

Worked example. Subject S requests deletion. The deletion service deletes S's row from the isolated pii_identity table (keyed by pseudonym P) and publishes a tombstone for P to a compacted topic. A warehouse job that joined orders to pii_identity for display purposes now resolves no name for P, and needs no rewrite, since the orders table itself only ever stored P, never the name. A denormalized legacy export table that copied customer_name directly two years ago, before the isolation pattern was enforced, is flagged separately by the catalog tag and requires a one-off remediation job. The ML training pipeline had ingested pii_identity fields directly into a stored feature file for a churn model; that file is identified, the row purged, and the churn model queued for its next scheduled retrain rather than promised an immediate in-place fix. Each of the four affected systems, warehouse, streaming, legacy export, and ML pipeline, writes a completion receipt, and the deletion service shows 4/4 acknowledged in the audit log as the auditor-facing proof.

Trade-offs and pitfalls

• The isolated-PII-table pattern only works going forward and for systems built to reference the pseudonym; retrofitting it onto years of tables that denormalized real attributes directly is a real migration project, not a policy memo.
• Backups are the honest limitation almost every design has to disclose: either accept a documented backup-expiry window as part of the deletion SLA, or invest in per-subject encryption keys so backups can be crypto-shredded, which adds real key-management overhead.
• The most common wrong turn is declaring deletion done when the primary systems are clean but derived, denormalized copies and ML artifacts were never inventoried; a completion receipt from every known system is only as good as the catalog's completeness in knowing which systems exist.

For a video streaming product, the right engagement metrics differ by time horizon because a single number can't distinguish a good single session from a habit, so tracking one metric per horizon (daily, weekly, monthly) catches problems a single aggregate would hide.

Three engagement metrics by horizon

Why one horizon alone misleads

Daily minutes watched can spike from a single viral release without indicating any lasting change in habit; weekly days-active can look healthy from repeat short check-ins that never lead to satisfying, completed viewing; only the monthly completion metric would catch a catalog that hooks people briefly but rarely delivers a satisfying full watch, which is the actual thing a streaming business needs for long-term retention.

Trade-offs and pitfalls

Using only the daily metric invites over-reacting to noise (weekend viewing patterns, one big release) as if it were a durable trend; using only the monthly metric is too slow to catch a real-time regression from a broken recommendation algorithm. The three horizons are meant to be read together, not to replace each other.

Redesign the input path
I would separate expensive preprocessing from the training loop and make the input stream sharded, cached, and deterministic.

Main changes

• Compact many small object-store files into larger shards so reads are sequential instead of chatty.
• Precompute expensive transforms once, then store the transformed output or an intermediate cache.
• Use a manifest that records shard order, sample IDs, and versioned transform code.
• Prefetch data to local disk or memory so GPUs do not wait on network reads.
• Keep lightweight, deterministic transforms in the training worker so results are reproducible.

Worked example
If the dataset is 10,000 files of 2 MB each, that is 20 GB spread across many requests. Repacking into 200 shards of 100 MB keeps the same 20 GB, but cuts file open and listing overhead dramatically.

Why this helps
The GPUs stay busy because decoding and network latency happen ahead of time. Reproducibility comes from versioned manifests and fixed seeds. Debuggability comes from keeping raw sample IDs so a bad example can be replayed exactly.

This is usually the highest leverage change when preprocessing time dominates training time.

Direct answer: Churn and segmentation feature design at scale needs to combine behavioral counts/rates/recency, cross-source interactions, and time-based signals computed from usage logs, support tickets, and demographics, with an explicit leakage check before trusting any offline lift, since churn labels are especially prone to accidentally encoding future information.

Structured elaboration: Eight concrete engineered features, with intuition and how each is computed at scale (roughly two million rows of mixed numeric, high-cardinality categorical, timestamp, and free-text data):

1. Days since last login/usage event (recency) - intuition: a lapsing user's engagement gap widens before they formally churn. Computed via a vectorized groupby-max on the timestamp column per customer, not a row-by-row loop, which is essential at two million rows.
2. Login/event frequency over trailing 7/30/90-day windows - intuition: a declining frequency trend is often more predictive than any single-point count. Computed via grouped, windowed aggregation (e.g. groupby(customer_id).rolling() or a windowed SQL aggregate).
3. Usage-intensity trend (slope of a simple linear fit or week-over-week ratio of event counts) - intuition: distinguishes "still active but declining" from "steady at a low level," which behave differently for churn.
4. Support ticket count and recency - intuition: a recent spike in support contact correlates with dissatisfaction, but must be computed carefully to avoid the leakage case below.
5. Support ticket sentiment or category distribution (from free-text ticket bodies, e.g. via a lightweight TF-IDF or keyword-tagging pass) - intuition: ticket TONE (frustrated vs. neutral) often separates at-risk from routine contacts even at similar ticket counts.
6. Plan/product tenure (time since signup or since last plan change) - intuition: churn risk is often U-shaped or front-loaded relative to tenure.
7. High-cardinality categorical encoding of plan or product identifier - using target encoding (with proper out-of-fold computation to avoid leakage) or frequency encoding rather than naive one-hot, since a high-cardinality plan/product ID field would otherwise blow up the feature space.
8. Cross-source interaction: declining usage AND a recent support contact (an explicit interaction feature, e.g. a flag or product of the two normalized signals) - intuition: the combination is a stronger churn signal than either usage decline or ticket volume alone, since users who complain but keep using the product, or who go quiet without complaining, behave differently than users doing both.

At roughly two million rows, computing these efficiently means vectorized, grouped aggregation (pandas groupby/window functions, or an equivalent SQL/Spark aggregation) rather than row-by-row processing, and encoding the high-cardinality categoricals (plan or product identifiers) with target or frequency encoding rather than naive one-hot encoding, which would otherwise create an unmanageably wide sparse matrix.

Whether to standardize or bin a behavioral signal depends on the downstream model and the signal's distribution: a heavily skewed count (most customers have very few support tickets, a small tail has many) often benefits from binning or a log-style transform for a linear model, while a tree-based model is largely indifferent to the raw scale and doesn't need standardization at all.

Detecting leakage specifically for churn: check whether any candidate feature (feature 4 and 5 above are the highest-risk ones) is computed using data from AFTER the point the churn decision would have actually been observable. A support ticket opened in response to a cancellation the customer had already decided on is a classic hidden leak, since it looks predictive but is really a symptom of the outcome, not a cause.

Worked example: A "days until account closure" style feature (however indirectly encoded, e.g. a support-ticket category that only exists post-cancellation) would show enormous offline lift and be completely unusable in production, since at prediction time you don't yet know whether the customer will churn; catching this requires explicitly auditing what information was genuinely available BEFORE the churn decision point for every candidate feature, not just checking whether the feature "looks reasonable."

Trade-offs and pitfalls: A common trap at this scale is trusting an offline metric improvement without an out-of-time evaluation; a feature that looks powerful on a random split can be capturing something that's specific to the historical period (a promotion that ran during part of the data) rather than a durable churn signal.

Overfitting: a model captures noise or idiosyncrasies in the training data and performs much better on training than on unseen data. Underfitting: a model is too simple to capture the underlying signal and performs poorly on both training and validation.

Learning curves: plot model performance (e.g., accuracy or RMSE) on training and validation sets versus training set size.

• Overfitting signature: training performance is high, validation performance is much lower; gap persists as data grows. Validation may improve slowly with more data. Worked example (train/validation accuracy at increasing training-set size n): n=100: train=99%, val=58%; n=500: train=98%, val=61%; n=1000: train=97%, val=62%; n=2000: train=97%, val=63%. The training curve sits near-flat and high the whole way, the validation curve creeps up only slowly, and the gap between them (roughly 35-40 points) barely narrows even as n quadruples: that persistent, wide, flat gap is exactly the "training error stays low while validation error stays high and roughly flat" pattern the question describes.
• Underfitting signature: both training and validation performance are poor and close to each other; adding more data doesn't help much. Worked example: n=100: train=64%, val=61%; n=500: train=66%, val=64%; n=1000: train=67%, val=65%; n=2000: train=67%, val=66%. Both curves sit low from the start, stay within a couple of points of each other throughout, and neither one climbs meaningfully as n grows: the model has hit a capacity ceiling that more data can't fix, unlike the overfitting case where the gap (not the level) was the problem.

Practical BI pipeline changes

• If I detect overfitting: add regularization or simplify the model used in automated reports (e.g., switch from a high-cardinality decision tree to a regularized logistic regression or apply L1 feature selection). Also enforce cross-validation in model training and reduce dimensionality (aggregate categorical levels, drop redundant features) before publishing predictions to dashboards. Applied to the worked example above, adding L2 regularization and dropping redundant features would be expected to pull training accuracy down from the high-90s toward the mid-80s while lifting validation accuracy from the low 60s toward the low-to-mid 70s, narrowing the gap rather than closing it in one step.
• If I detect underfitting: enrich features and increase model capacity: add derived features (time-based aggregations, interaction terms), loosen regularization, or use a more flexible model (e.g., gradient boosted trees) in the modeling stage. Re-run validation and update ETL to include the new features so dashboards reflect improved predictions. Applied to the worked underfitting example above, swapping in gradient boosted trees and adding derived features would be expected to lift both curves together, e.g. training accuracy from ~67% toward the mid-80s and validation from ~66% toward the high-70s, since the ceiling here was capacity, not overfitting.

These changes balance predictive accuracy with interpretability and operational constraints typical in BI deliverables.