Credit features in SQL

Building the feature layer of a credit scorecard in SQL, the way it is built in production: from dated event tables, with every feature constrained to what was knowable on the day of the decision.

Companion to credit-risk-scorecard, which models. This repository prepares.

---

The rule that governs the whole project

A feature attached to an application may only use facts that were knowable before that application's decision date.

Break it and the model reads the future. It will look excellent in backtest — often suspiciously excellent — and collapse in production, because in production the future is not available.

This is the most expensive mistake in credit modelling, and it is invisible unless you look for it. It is why none of the joins in sql/03_features.sql is a plain equality join: every one of them carries a date condition.

---

The tables

Not a tidy matrix — the five tables a lender actually stores.

Table	Rows	Grain
applicants	20,000	one per person
applications	25,152	one per loan request, with its decision date
repayments	389,388	one per instalment, with the date it was actually paid
bureau_inquiries	44,163	one per credit check, by any lender
telco_events	450,304	one per monthly top-up

Three properties make it realistic, and each creates real work for the SQL: facts are dated, many applicants have no history at all, and a person can apply more than once.

---

The output

25,152 rows, 34 columns, one row per application.

application_id	decision_date	approved	debt_to_income	n_prior_loans	prior_worst_dpd	n_inquiries_6m	topup_trend	default_flag
1	2023-12-17	0	0.4469	0	—	0	1.267	—
2	2024-07-17	1	0.2057	0	—	0	0.967	0
7	2024-06-26	1	0.0361	1	8	0	1.295	0
8	2024-10-31	0	0.6200	2	8	0	—	—

Note rows 1 and 8: a refused application has no default_flag, because a loan that was never granted has no outcome. Inventing one is precisely the mistake that reject inference exists to address.

---

What the SQL does

Point-in-time joins, not equality joins

The repayment block is the valuable one and the dangerous one. An instalment is admissible only if it fell due before the current application was decided — not an instalment of the same loan, not one of a later loan, not one falling due next month.

FROM base b
LEFT JOIN applications pa
       ON pa.applicant_id   = b.applicant_id
      AND pa.application_id <> b.application_id
      AND pa.decision_date  < b.decision_date      -- an EARLIER loan
LEFT JOIN repayments r
       ON r.application_id = pa.application_id
      AND r.due_date       < b.decision_date       -- already due at the time

LEFT JOIN, not JOIN: a first-time applicant has no history and must survive the join with NULLs rather than disappear from the table.

Window functions for the applicant's own history

Framed to end on the preceding row, so an application never counts itself and never counts the ones that came after:

COUNT(*) OVER w AS n_prior_applications,
DATE_DIFF('day', LAG(decision_date) OVER (
             PARTITION BY applicant_id ORDER BY decision_date),
          decision_date) AS days_since_last_application
...
WINDOW w AS (PARTITION BY applicant_id ORDER BY decision_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

FILTER for several time windows in one pass

Rather than joining the same table three times:

COUNT(i.inquiry_id) FILTER (WHERE i.inquiry_date >= b.decision_date - INTERVAL 90 DAY)  AS n_inquiries_3m,
COUNT(i.inquiry_id) FILTER (WHERE i.inquiry_date >= b.decision_date - INTERVAL 180 DAY) AS n_inquiries_6m

A target that is a definition, not a column

"Default" is nowhere in the data. The industry convention is 90 days past due, and it has to be computed:

COALESCE(DATE_DIFF('day', due_date, paid_date), 999) AS days_past_due

An instalment never paid has paid_date NULL, not a large delay. A plain subtraction would silently drop exactly the worst cases — the loans that never paid at all.

---

A bug the checks caught

The coverage check reported prior_worst_dpd populated for 100 % of applications, while only 20 % of applicants had ever applied before. That is impossible, and it exposed a real trap:

-- wrong
COALESCE(DATE_DIFF('day', r.due_date, r.paid_date), 999)

Inside a LEFT JOIN, this cannot tell "instalment never paid" from "no instalment at all". Both arrive as NULL, and both come out as 999 — so every first-time applicant was being scored as the worst payer in the book.

-- right
CASE WHEN r.application_id IS NULL THEN NULL
     ELSE COALESCE(DATE_DIFF('day', r.due_date, r.paid_date), 999) END

Coverage then fell to 13.5 %, which matches the share of applicants with a prior loan whose instalments had already fallen due. A feature that is populated too often is as suspicious as one populated too rarely.

---

Checks

Five queries, each answering a question with an obviously wrong answer.

1. Leakage — demonstrated, not asserted

Counting rows that could leak proves nothing. The honest test builds the same feature twice, with and without the date filter, and compares:

Version	With history	Average worst DPD
Point-in-time correct	13.5 %	57.7
Leaky, no date filter	13.6 %	130.6
Applications whose value changed	6.7 %

The leaky version is not subtly different. Its average delinquency is more than twice as bad, because it is reading instalments that had not yet fallen due — including the ones that went bad after the decision. A model fed that feature would look excellent and be worthless.

2. Invariants

Test	Offending rows
Target on a refused application	0
First-time applicant with a repayment history	0

3. Coverage

Feature	Populated
prior_worst_dpd	13.5 %
days_since_last_delinquency	1.7 %
topup_trend	73.7 %
days_since_last_application	20.5 %

A feature missing for most applicants is not necessarily bad — thin files are a real population, and is_thin_file marks them explicitly. But it must be a decision, not a surprise.

4. Signal, before any model

If the joins are right, the default rate must move across a feature's bins:

Debt-to-income quintile	n	Average DTI	Default rate
1	3,196	0.050	11.33 %
2	3,195	0.104	14.77 %
3	3,195	0.165	15.40 %
4	3,195	0.243	18.72 %
5	3,195	0.361	23.26 %

Monotone across all five bins. This is the cheapest possible sanity check and it catches a broken join immediately.

5. Stability over time

Volume, approval rate and default rate by quarter. A jump usually means a policy change, not a change in borrowers — and a model trained across the break learns the policy instead of the risk.

---

Running it

git clone https://github.com/al-fayed1998/credit-features-sql
cd credit-features-sql
pip install duckdb pandas numpy

python3 data/make_raw_tables.py   # generates data/raw/*.csv
python3 run_sql.py                # runs sql/*.sql, prints every check

DuckDB needs no server and its dialect is close to BigQuery and ClickHouse, so the same SQL transfers with minimal change.

sql/01_load.sql        views over the raw tables
sql/02_target.sql      90+ days past due, computed from instalment dates
sql/03_features.sql    the feature layer — the substance of the project
sql/04_checks.sql      leakage, invariants, coverage, signal, stability
run_sql.py             sequencing only; the SQL stands on its own

---

Limits

• The data is generated, by data/make_raw_tables.py. The schema and the SQL are what the project is about; the numbers describe a portfolio I simulated.
• DuckDB, single machine. On a real warehouse the same logic would run in BigQuery or ClickHouse; the point-in-time joins are the part that would need care at scale, since they are the expensive ones.
• No incremental refresh. A production feature store would recompute only the applications since the last run, and would need a backfill strategy.

---

Stack

SQL (DuckDB) · Python · pandas · NumPy

Author

Mouhammad Thahir OUSMANE

License

MIT