What credit data taught me about data modeling

I spent a few years building anomaly detection and risk models over credit and financial data at a capital firm. Most of my modeling since has been over product and marketing data, where the cost of a wrong number is an awkward meeting. The costs were arranged differently in credit. Two habits got beaten into me there, and I still apply them in places that don’t strictly require it.

A number without an “as of” is half a number.

In most analytics, a table is a picture of the world right now. A customer’s plan, their segment, their current lifetime value, whether they’ve churned. Someone updates the row when the fact changes, and everyone gets on with their day.

Credit data does not allow this. The question was rarely about today. It was about the day the decision got made, and those are different questions with different answers, and only one of them holds up when somebody asks you to explain the decision six months later. Limits move. Ratings get revised. A field called “current status” is a landmine if you ever want to reconstruct why anything happened.

I learned this the expensive way, which is the only way I seem to learn things. One of my early risk features came off a field that the upstream system overwrote whenever an account got manually reviewed. In backtest the feature looked wonderful. Of course it did. It was partly encoding the outcome, because reviews happened to accounts that were already in trouble, and by the time I saw the field it had been updated to reflect what the reviewer found. The model had been quietly reading the last page. In production it was ordinary.

Nobody caught this in review. I caught it because the live scores didn’t match the backtest, and I spent two days assuming the scoring code was broken.

So: effective-from and effective-to on anything a decision depends on, and no overwriting in place. The reframe that made it click was giving up on rows as facts. A row is a claim that was true during a window. Modeling gets easier once you accept that history is data and not clutter.

A definition is a piece of infrastructure.

Financial data gets audited, which means every number in a report eventually has to survive a stranger asking where it came from. Not “roughly what does this measure.” Where it came from: which rows, which filters, whose rule, and which version of the rule.

Part of my job there was writing down business definitions and KPI logic for the warehouse, and I went in treating it as a documentation chore. It was modeling work in disguise. “Delinquent” had at least three definitions in active use across the firm, all defensible, all producing different totals, and nobody had noticed because the groups using them rarely compared numbers in the same room. Writing the definitions down didn’t cause the disagreement. It exposed one that had been sitting there for years. Then the modeling could actually start, because now there was a thing to build.

I write definitions before I write models now, even for a Power BI semantic model that nobody plans to audit. It costs an hour. It prevents the meeting where two people argue for forty minutes before discovering they agree.

And a third thing, harder to name. Financial data is produced by parties with an interest in how they look. Not fraud, mostly. Just the ordinary gravity of incentives. Missing values are not missing at random. A field that’s blank on the risky accounts is telling you something, and the reflex of imputing the median throws that away and replaces it with a comfortable fiction. In sensor data a gap is usually a gap. In credit data a gap has a reason, and sometimes the reason is the most useful column you have.

The anomaly models that came out of all this surfaced potential issues three to four weeks before the old process caught them, though as I’ve written before, the model was the easy part and getting anyone to work the queue was not.

At my last job I built effective dating into the customer dimension without anyone asking for it. It cost some extra complexity in the dbt models and roughly one confused code review, where I did not have a great answer to “who needs this.” Then Growth asked what the segment mix looked like back in March, and the answer existed, and I got to be very casual about it in a way I had absolutely rehearsed.

Product data forgives a lot. It will let you overwrite a dimension for three years and never once bring it up. That forgiveness is what makes it dangerous, because you find out you needed the history on the day someone asks for it, which is reliably the day it’s gone.