Churn Feature Pipeline: Skew, Leakage, and Feature Governance
A retail bank trains a 90-day churn model on 60 million customer-month rows stored in Delta Lake. The feature notebook joins a 2.8 TB events table to a 4 million-row account dimension, then creates 350 features. The nightly job takes seven hours and fails intermittently with FetchFailedException; Spark UI shows one partition processing 41% of events because a single test account generates synthetic load. The team uses a random 80/20 split and reports AUC 0.81, but a backtest on the most recent month scores 0.62. Several features aggregate event history over the full window, including events after the label date. A data scientist proposes a Python UDF that calls an external risk API once per row to add a score, and wants to skip the feature table because 'notebooks are faster.'
1. Diagnose the intermittent job failure and propose a concrete Spark plan. Include how to handle the skewed account and the proposed per-row external API call.
Read the worked answer
FetchFailedException with one partition at 41% is a classic skew symptom, not proof of storage corruption. Confirm in the Spark UI: max task duration, shuffle read size, spill, and retry count. The hot test account should first be filtered or isolated if it is synthetic and not legitimate production behavior. If it must remain, use AQE skew join handling, but do not assume it fully solves a 41% partition. Salt the hot account key, replicate the small side, join, then aggregate back. Broadcasting the 4 million-row account dimension may exceed executor memory; check size after column pruning and compression before forcing a broadcast. For the external API, a Python UDF called once per row serializes Python and makes 60 million network calls, worsening runtime and failures. Instead, enrich a small dimension table in batch, cache or broadcast it, or use a vectorized pandas UDF with bounded batch size and retries if row-level enrichment is unavoidable. Persist intermediate features to Delta and checkpoint long lineages.
2. Explain why random-split AUC 0.81 collapses to 0.62 on the recent month, and describe the validation and feature-timing changes you would make.
Read the worked answer
The gap points to both temporal distribution shift and label leakage. A random split lets future events inform past predictions and does not mimic deployment. Worse, features that aggregate the full event history include events after the label date, so they encode information unavailable at scoring time. Rebuild the split chronologically: train on months T-6 through T-1, validate on T, hold out T+1. For every feature, define an event cutoff and only aggregate events before the label timestamp. Use point-in-time joins rather than recomputing aggregates over the full table. Re-run the backtest and report confidence intervals. If recent AUC remains below 0.70, inspect changes in customer mix, label definition, and missingness before concluding the model is bad. Do not repeatedly tune against the recent month; keep it as a final holdout and use a validation window for decisions.
3. The team asks whether to keep notebook-computed features or adopt Unity Catalog feature tables. Recommend an approach that supports training and online serving without leakage.
Read the worked answer
Notebooks are faster only for one-off exploration; they do not provide a governed feature contract. Use Unity Catalog feature tables: Delta tables with a declared primary key and event timestamp, permissions, lineage, and point-in-time lookup. Materialize features in a scheduled job, not inside each training notebook. Create the training set with FeatureLookup so each label receives feature values as of the label time. Publish only the subset needed for real-time scoring to the online store. If the external risk score is required, batch-enrich it into a feature table keyed by entity and effective date, with cached responses; a per-row API call creates latency, cost, and availability risk. Version feature definitions and log the training set lineage so a model version can be traced back to the exact feature values.