A Data Quality and Leakage Audit

1. Likely causes and confirming checks

• 118,400 unique of 120,000 application_id. 1,600 duplicated keys — almost certainly a join that fanned out (one application matched several rows in a bureau or payments table) or a pipeline rerun that appended. Check: df[df.duplicated("application_id", keep=False)] .sort_values("application_id") and inspect whether the duplicate rows are identical or differ in a few columns (which columns differ names the offending join).
• Bimodal annual_income at 50k and 50M. A 1000× gap is a units mismatch — one source stored income in cents or in a different currency's minor unit, or a form field was filled monthly-vs-annual. Check: group the histogram by source_system / country / application_date; the high-mode rows will cluster in one group.
• employment_length free text. A string field with inconsistent entry conventions across form versions. Check value_counts(), then parse with a regex to a numeric years value; capture "<1" as 0.5 or 0 and "5+" as 5 (documented), coerce failures to NaN and count them.
• days_past_due_max correlated 0.91 with default. Almost certainly target leakage: the maximum days past due is measured during the loan, i.e. after the decision the model is meant to inform, and it is essentially the definition of default. Check: the data dictionary and the timestamp at which the column is populated; if it is not knowable at application time, it cannot be a feature.
• 3% of dates in 1970. Unix epoch zero — a null or zero timestamp cast to a date. Check whether those rows share a source or period; treat as missing.

2. Which ones fool validation but fail in production

The leakage column is the dangerous one. A random train/validation split keeps the leaky column in both halves, so validation AUC will be spectacular (the model has learned "days past due > 90 ⇒ default"), but at scoring time for a new applicant the column is empty or zero — the model has no signal and collapses. The duplicated keys are the second: if the same application appears in both train and validation, the model is partly evaluated on rows it memorised, inflating validation scores in a way that will not transfer. Units and parsing errors mostly hurt in both environments and are therefore visible; leakage and duplication are the ones that hide.

3. Fixes, in order, and documentation

1. Fix the 1970 dates → NaT (they must not be used to define train/test time windows).
2. Resolve duplicated application_id — find the fan-out join, fix it upstream or deduplicate with an explicit rule (e.g. keep the latest bureau pull); record how many rows were dropped.
3. Normalise annual_income units by source; verify the corrected distribution is unimodal on a log scale; flag any rows that cannot be attributed.
4. Parse employment_length to numeric with a documented mapping; report the residual NaN count and treat it as missing (probably MAR by form version).
5. Drop days_past_due_max (and audit every other column for "known only after origination"); keep a written list of excluded columns and the reason.

Document: the row-count reconciliation (before/after dedup), the unit mapping and how it was inferred, the string-parsing rules, the excluded leaky columns, and the assumption that the remaining columns are all available at application time. Handling of the cleaned features for modelling — scaling, encoding, binning — is the Feature Engineering subject; this audit is what makes that work trustworthy.