Credit-risk data engineering

Entity keys and source-system mapping

Create traceable obligor, facility, application, and collateral relationships before modeling.

A visual overview of Create traceable obligor, facility, application, and collateral relationships before modeling. See the guide for assumptions and implementation details.

Purpose

Set up a governed data model that links source records without multiplying exposures or losing lineage.

Key terms

Hover over or focus a term for a quick definition, or open its glossary entry.

Key definitions

Entity resolution links records that refer to the same real-world obligor, facility, or account.
Lineage records where a field came from and how it was transformed.

Formula

Join invariant: output row count and key uniqueness match the declared analytical grain.

Worked example

Before joining monthly balances to one borrower row, confirm whether the expected result is one row per borrower-month or facility-month.

Detailed explanation

Credit risk data often lives across origination, servicing, collections, bureau, collateral, and finance systems. Build a field dictionary that identifies source, business definition, grain, effective date, refresh cadence, and transformation. Preserve immutable source keys and map them through effective-dated crosswalks.

Test uniqueness and row counts before and after joins. A one-to-many relationship may be correct, but only if the downstream grain accounts for it. Reconcile balances and default flags to trusted control totals, retain data lineage, and version mapping rules so a score or ECL result can be reproduced later.

Try a reproducible Python example

These examples use synthetic inputs and run locally in a browser worker. Edit the code, run it, and compare its output with the published example.

Runnable Python · local in your browser

Detect a point-in-time feature leak

A miniature data preparation check compares each feature's observation date with the prediction date.

Editable Python example

Output

Run the code to see the output.

Runtime loads only after you press Run. Code runs in a separate browser worker with a 10-second limit and a 20,000-character output cap. Use synthetic examples only; browser execution is not a secure environment for secrets or sensitive records.

Runnable Python · local in your browser

Engineer numeric credit-risk features

Use a training-only median, explicit missingness flags, a log transform, a payment-to-income ratio, and a delinquency indicator. Amounts are synthetic CAD; transforms are illustrative and should be validated for the intended population.

Editable Python example

from math import log1p

training = [
    {"income": 48000, "monthly_payment": 420, "utilization": 0.35, "dpd": 0},
    {"income": 72000, "monthly_payment": 900, "utilization": 0.72, "dpd": 12},
    {"income": 56000, "monthly_payment": 650, "utilization": 0.51, "dpd": 0},
]
scoring = [
    {"account": "A", "income": 60000, "monthly_payment": 750, "utilization": 0.60, "dpd": 0},
    {"account": "B", "income": None, "monthly_payment": 500, "utilization": None, "dpd": 35},
]

# Fit the imputation value on training data only; reuse it unchanged at scoring time.
income_values = sorted(row["income"] for row in training if row["income"] is not None)
training_income_median = income_values[len(income_values) // 2]
utilization_values = sorted(row["utilization"] for row in training if row["utilization"] is not None)
training_utilization_median = utilization_values[len(utilization_values) // 2]

for row in scoring:
    income_missing = row["income"] is None
    utilization_missing = row["utilization"] is None
    income = row["income"] if not income_missing else training_income_median
    utilization = row["utilization"] if not utilization_missing else training_utilization_median
    features = {
        "log_income": round(log1p(income), 4),
        "income_missing": int(income_missing),
        "payment_to_monthly_income": round(row["monthly_payment"] / (income / 12), 4),
        "utilization": utilization,
        "utilization_missing": int(utilization_missing),
        "dpd_30_plus": int(row["dpd"] >= 30),
    }
    print("{}: {}".format(row["account"], features))

Output

Run the code to see the output.

Assumptions

Crosswalk keys are effective-dated, reviewed, and retain source-system identifiers.

Limitations

False merges or splits can corrupt default labels and exposure totals.
Master-data rules can change over time and require versioning.

References

pandas user guide — pandas documentation
Guidance on credit risk and accounting for expected credit losses — Basel Committee on Banking Supervision

Educational material only. Methods and examples are simplified; state and validate the assumptions required for your use case.