Home Credit Default Risk

Query 307,511 loan applications in plain English — or in SQL, or over the Model Context Protocol.

This project loads the Kaggle Home Credit Default Risk dataset into SQLite and puts three interfaces on top of it: a Streamlit app, a set of command-line tools, and an MCP server that lets any LLM client query the data without touching the raw files.

Every path into the data is read-only. The SQLite connection opens with ?mode=ro, and generated SQL passes a keyword guard before it runs.

---

What's in the data

Applications	307,511
Columns	122 (application_train), 130 in the v_application view
Defaults	24,825 — 8.07% of applicants
SK_ID_CURR range	100,002 – 456,255

TARGET is the outcome: 1 means the client had payment difficulties, 0 means they repaid. The class imbalance is the point — 8% means a model that predicts "no default" every time scores 92% accuracy and is worthless.

---

Quickstart

1. Install the dependencies.

py -3.13 -m pip install -r requirements.txt

2. Get the data. The CSVs are not in this repository — they're 2.7 GB and Kaggle's to distribute. Download them from the competition page and put them in the project folder. Only application_train.csv is needed to start.

3. Build the database.

py -3.13 build_db.py

That reads the CSV, infers a SQLite type per column, indexes the columns worth indexing, and creates the v_application view with the derived fields. The result is a 199 MB home_credit.db.

Name tables explicitly to load more, or pass --all to load every CSV in the folder:

py -3.13 build_db.py application_train bureau previous_application
py -3.13 build_db.py --all

4. Run something.

py -3.13 -m streamlit run app.py

The database is all you need for SQL, applicant lookup and the charts. The natural-language features additionally want a Groq API key — free, and read from GROQ_API_KEY in the environment or a .env file beside the scripts. .env is gitignored; no key is ever committed.

---

Three ways to ask

Path	You write	How it works
query.py	SQL	Runs it against a read-only connection. No network, no API key.
nl2sql.py	English	Sends the schema to Groq, gets SQL back, runs it locally.
mcp_server.py	Anything	Publishes 7 tools over MCP; the client's own model drives them.

The middle path is the one worth being precise about. What leaves this machine is column names, column types, and the distinct values of seven categorical columns — enough for the model to write 'Cash loans' rather than guess 'cash_loan'. The applicant rows never leave. The SQL comes back and executes against the database on disk.

The one exception is --explain, which sends the (usually aggregated) result rows back to Groq for a written summary. It's opt-in for that reason.

---

The app

py -3.13 -m streamlit run app.py

Six tabs:

• Agent — tool-calling agent. Reads its tools from tool_calling_prompt.md, so editing that markdown changes the agent's behaviour without touching Python.
• MCP — the same idea, but the tools are discovered over the wire from mcp_server.py. Add a tool to the server and it appears here with no change to app.py.
• Ask in English — one question, one query. Prints the SQL above the result.
• Applicant lookup — one applicant, grouped and readable, placed against the population.
• Explore — prebuilt breakdowns and charts.
• SQL console — write it yourself.

---

Command line

Each script runs standalone and documents itself with --help-style docstrings.

py -3.13 query.py "SELECT COUNT(*) FROM application_train"
py -3.13 query.py --tables
py -3.13 query.py --schema

py -3.13 nl2sql.py "which education level defaults most?"
py -3.13 nl2sql.py --explain "are bigger loans riskier?"
py -3.13 nl2sql.py --sql-only "how many applicants own a car?"
py -3.13 nl2sql.py                    # interactive

py -3.13 customer.py 100002 --compare
py -3.13 customer.py --find "AMT_INCOME_TOTAL > 500000 AND TARGET = 1"
py -3.13 customer.py --random 5

py -3.13 agent.py --trace "what does EXT_SOURCE_1 mean, and how does it relate to default?"
py -3.13 mcp_client.py "break the default rate down by occupation"

---

Checks on generated SQL

Because a model writes the SQL in three of these paths, it doesn't run unchecked:

• Statements containing INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, REPLACE, ATTACH, DETACH, PRAGMA, VACUUM, REINDEX or TRUNCATE are refused. The scan runs on the whole candidate before any prose prefix is trimmed, so it fails closed.
• Multiple statements are refused — ATTACH DATABASE 'x' AS y; SELECT 1 is rejected whole, not trimmed to its harmless tail.
• The connection is opened read-only, so a write fails at the database level regardless.
• Column names reaching default_rate_by are validated against PRAGMA table_info instead of being interpolated into SQL.
• Arithmetic is evaluated from an AST, never eval().

The SQL is printed above every result. Read it before trusting a number — a model can pick a reasonable-looking wrong column and the output will look perfectly plausible.

---

Notes on the data

These are the traps that produce confidently wrong answers if you miss them:

• DAYS_EMPLOYED is 365243 on 55,374 rows (18%). It's a placeholder for pensioners and the unemployed, not 1,000 years of service. Averaging it un-filtered is the single most common mistake on this dataset. v_application.YEARS_EMPLOYED is already NULL for these, and EMPLOYED_ANOMALY flags them.
• All DAYS_* columns are negative offsets from the application date. Age is -DAYS_BIRTH / 365.25.
• AMT_INCOME_TOTAL maxes at 117,000,000 against a mean of 168,798. Use the median (147,150).
• EXT_SOURCE_1 is the strongest single predictor and is missing for 56% of rows.
• CODE_GENDER has 4 rows marked XNA.
• The dataset has no interest rate, loan term, payment history, credit score, names or addresses. If a question needs one of those, it can't be answered here — and the honest answer is to say so rather than substitute a proxy.

---

Files

File	Purpose
build_db.py	CSV → SQLite, derived columns, indexes, the v_application view
query.py	Read-only SQL runner
nl2sql.py	English → SQL via Groq. Owns the connection, clean_sql, the schema card
agent.py	Tool-calling agent over the column dictionary, SQL and a calculator
customer.py	Single-applicant profile with population percentiles
mcp_server.py	MCP server — 7 tools, 4 resources, 1 prompt, over stdio
mcp_client.py	MCP client; also a CLI
app.py	Streamlit front end
tool_calling_prompt.md	System prompt + tool schemas for agent.py
HomeCredit_columns_description.csv	The 220-field Kaggle data dictionary
home_credit_eda.ipynb	Exploratory analysis
home_credit_model.ipynb	Modelling

Guards live in exactly one place and are imported, never reimplemented — nl2sql.py owns SQL validation, agent.py owns the dictionary search and calculator, customer.py owns the percentiles. Validation logic written twice is validation logic that eventually disagrees with itself.

---

MCP server

mcp_server.py exposes the project's tooling to any MCP client — Claude Desktop, Claude Code, an SDK agent. Six of its seven tools need no API key.

py -3.13 mcp_client.py --tools

Setup, the full tool table, design notes and the end-to-end verification results are in README_MCP.md.

---

Stack

Python 3.13 · SQLite · pandas · Streamlit · Altair · mcp 2.0 · Groq (openai/gpt-oss-120b, llama-3.3-70b-versatile)

The Groq calls use urllib from the standard library — there's no vendor SDK in the dependency list.