Learn / Data guide
Credit-risk data guide: the tables and columns behind every tool
Every Bucketwise tool asks for table and column names. This guide explains what those tables hold, what each column means, and where to find the names in your own systems. Your names will differ from the examples. The meaning will not. You never need to share customer data: names and definitions are enough.
How to find your table and column names
Three ways, from easiest to most reliable. 1) Open your data dictionary or MIS documentation if one exists. 2) Ask the data or MIS team for the table that holds loans, the one that holds instalments due, and the one that holds payments received. 3) List the columns yourself with a schema query. It returns names and types only, no customer rows.
| Database | Query that lists tables and columns |
|---|---|
| PostgreSQL, MySQL, SQL Server, Snowflake, Redshift | SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'your_schema' ORDER BY table_name, ordinal_position; |
| Oracle | SELECT table_name, column_name, data_type FROM all_tab_columns WHERE owner = 'YOUR_SCHEMA' ORDER BY table_name, column_id; |
| BigQuery | SELECT table_name, column_name, data_type FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS` ORDER BY table_name, ordinal_position; |
Replace the schema, owner, project and dataset with your own. Paste the result into the Bucketwise schema profile tool, and it fills the other tools for you.
Loan master: one row per loan
The loan master is the list of every loan you have given. Most other tables join back to it through the loan id. It should have exactly one row per loan.
| Column (typical names) | What it holds | Where to look | Watch out |
|---|---|---|---|
| loan_id, acct_no | The unique id of the loan | The first column of the loan master or LMS loan table | Must be identical in every table you join. Check for duplicates before anything else. |
| product | Personal loan, business loan, salary advance and so on | Product or scheme code in the LMS | Codes may be numbers. Ask for the code list. |
| disb_dt | Date the money was disbursed | Disbursal or booking date | Not the sanction date, and not the first EMI date. |
| sanction_amt, disb_amt | Amount sanctioned, amount actually disbursed | Loan amount fields | They can differ after deductions or partial disbursal. Pick one and say which. |
| tenure_m | Number of monthly instalments | Tenure or term | Some systems store days or years. |
| roi_pct | Annual interest rate | Rate of interest | Check if it is flat or reducing balance. |
| emi_amt | The monthly instalment | EMI or instalment amount | May be revised after restructuring. |
| status | Active, closed or written off | Account status | Status values differ by system. List them before you filter. |
| closure_dt, writeoff_dt | Date the loan closed or was written off | Closure and write-off fields | Empty for live loans. A closed loan should have nothing overdue. |
Typical problems: the same loan appearing twice, a zero or negative sanction amount, and loans marked closed that still show overdue amounts.
Repayment schedule: what was due and when
One row for every instalment that falls due on a loan. It answers: how much was due, and on what date.
| Column (typical names) | What it holds | Where to look | Watch out |
|---|---|---|---|
| loan_id | The loan this instalment belongs to | Same column as in the loan master | Joins many rows to one loan. |
| inst_no | Instalment number: 1, 2, 3 and so on | Instalment or sequence number | Gaps (1, 2, 4) mean a missing instalment. |
| due_dt | Date the instalment was due | Due date or EMI date | Never use the payment date here. Watch for empty due dates. |
| due_amt | Amount due, principal plus interest | Instalment or EMI amount | Some tables split principal and interest into two columns. Add them. |
Typical problems: the same instalment listed twice, so a join multiplies rows, instalments missing from the sequence, and empty due dates.
Payment transactions: what was actually received
One row for every payment event, including failed presentations and reversals in many systems. It answers: how much money actually came in, and when.
| Column (typical names) | What it holds | Where to look | Watch out |
|---|---|---|---|
| txn_id | Unique id of the payment event | Transaction or receipt number | Not unique if the system reposts a payment. |
| loan_id | The loan the payment was for | Loan or account reference | Some payments reference loans that are not in the loan master. |
| pay_dt | Date the money was received | Receipt, value or collection date | Not the date it was presented to the bank. A date after your as-of date must be excluded. |
| pay_amt | Amount received | Amount or credit amount | Reversals are often negative. |
| mode | How it was paid: auto-debit, UPI, cash, cheque | Payment mode or channel | Useful for collections analysis. |
| txn_status | Whether the payment actually succeeded | Status, presentation result or return code | Failed and bounced rows sit in the same table. If you leave them in, loans look better than they are. |
Typical problems: bounced rows counted as payments, reversals that cancel an earlier payment, the same payment posted twice, payments dated before disbursal, and payments dated after the as-of date.
Monthly snapshot: the book at each month-end
One row per loan per month-end, frozen at the time. It is what you use for roll rates, vintages and trends, because you cannot go back and recompute an old month from today's data.
| Column (typical names) | What it holds | Where to look | Watch out |
|---|---|---|---|
| loan_id | The loan | Same id as everywhere else | One row per loan per month. |
| month_end | The snapshot date | Reporting or snapshot date | Always the last day of the month. |
| dpd | Days past due on that date | DPD field in the monthly MIS | Check it against a recomputed value. Stored snapshots can disagree with the payments. |
| bucket | Current, 1-30, 31-60, 61-90 or 91+ | Bucket or delinquency band | Confirm the boundary: 91+ is more than 90 days. |
| overdue_amt | Amount overdue at that date | Arrears or overdue field | Zero for current loans. |
| pos_amt | Principal outstanding | POS or principal balance | Not the same as total outstanding, which includes interest. |
Applications: who applied and what was decided
| Column (typical names) | What it holds | Where to look | Watch out |
|---|---|---|---|
| app_id | Unique id of the application | Application number in the LOS | Links to the bureau pull and, if approved, to the loan. |
| app_dt | Application date | Created or login date | Use one date consistently for monthly funnels. |
| income | Declared or verified monthly income | Income field in the LOS | Say which one. Declared and verified differ. |
| requested_amt | Amount the customer asked for | Requested loan amount | Not the sanctioned amount. |
| foir | Fixed obligations as a share of income | Computed field, or EMI plus obligations divided by income | Different lenders include different obligations. |
| decision | Approved or declined | Final decision field | Check what happens to cases still pending. |
| decline_reason | Why it was declined | Reason or reject code | Often free text. The same reason can be spelled several ways. |
Typical problems: declined applications with no reason, and one reason written in many spellings, which splits one reason across many counts.
Bureau pulls: what the bureau said at application
| Column (typical names) | What it holds | Where to look | Watch out |
|---|---|---|---|
| pull_id | Unique id of the pull | Bureau response id | One application can have more than one pull. |
| app_id | The application it belongs to | LOS reference | The same pull stored twice inflates counts. |
| pull_dt | Date the bureau was queried | Enquiry date | A pull long before the application is stale. |
| bureau_score | The score returned | Score field in the bureau response | Check the valid range for your bureau. Some use a special value for no history. |
| enquiries_6m | Credit enquiries in the last six months | Bureau summary | A count, not a balance. |
| max_dpd_12m | Worst DPD in the last 12 months | Bureau summary | Bureau DPD can fall the moment a payment posts. |
Terms that appear in many tools
| Term | Meaning | Note |
|---|---|---|
| As-of date | The date every figure is measured on | Pick one date for the whole query. The last day of the month is usual. |
| Grace days | Days allowed after the due date before a loan counts as late | Use 0 if your policy has none. |
| Overdue amount | What is due and unpaid on the as-of date | Not the full outstanding balance. |
| POS | Principal outstanding | Excludes accrued interest. |
| Bucket | A band of DPD: Current, 1-30, 31-60, 61-90, 91+ | The last bucket starts above 90 days. |
| Bad flag | A loan that reached a stated delinquency within a stated window | For example 91+ within 12 months of disbursal. Write the definition down and keep it fixed. |
What goes wrong in real tables
We tested the prompts on loan data that contains each of these problems. The same patterns turn up in real extracts, which is why the tools ask about them.
- A loan appears twice in the loan master.
- Instalments are listed twice, or missing from the sequence.
- Bounced presentations sit among the payments.
- A reversal cancels an earlier payment.
- A payment is dated before disbursal or after the as-of date.
- A payment refers to a loan that is not in the loan master.
- The stored monthly DPD disagrees with the payments.
- A bureau score is outside the valid range.
- A declined application has no reason.
The data-quality tool writes a query for each of these checks. Run it before any other tool.
Try it on your own tables
The free tools give you the prompt, the checks and how to present the result. You enter table names only.
See the free toolsFrequently asked questions
Do I need to share customer data to use the tools?
No. The tools only ask for table and column names and your definitions. Customer rows never need to be pasted.
My column names are different from the examples. Does that matter?
No. Type your own names over the samples. The meaning of the column is what counts.
Which tables do I need to start?
For DPD and buckets you need the repayment schedule and the payment transactions. For roll rates you need the monthly snapshot.
Keep reading
What is DPD (days past due) and how to calculate it
DPD counts how many days a loan is overdue. Learn the rule, a worked example with several instalments, and the mistakes that make DPD wrong.
Using AIHow to check AI-written credit-risk SQL before you trust it
AI tools write SQL that runs but may be wrong. Ten checks to run on any credit-risk query: row counts, duplicates, DPD edges, bucket totals and a hand trace.