By Zeel Patel, Data & AI Platform Engineer at Prodigal

Part 1 of 2. Part 2: Don’t let the agent write SQL first.

In September 1999, NASA lost the Mars Climate Orbiter. The navigation software worked. The thruster software worked. One system reported thrust in pound-force seconds, the other read the numbers as newton-seconds, and the spacecraft flew too close to Mars.

Neither piece of software had a bug in the usual sense. The two teams had simply assumed different units.

Data pipelines can fail the same way. In our case the output goes to AI agents that talk to people about their loans, so a mistake like this can end up being said to someone on a call.

Correct code, wrong meaning

Prodigal builds AI agents that help lenders and collection teams talk to people about their loans: what's due, what's overdue, and what options are available. Before an agent can say any of that, it needs the lender's data, mapped into a shared model inside the Prodigal Intelligence Engine (PIE).

That sounds like plumbing. Connect the tables, map the columns, ship.

In practice, it's translation. Every lender's data comes from its own loan system, its own CRM and years of its own conventions. Each has a different idea of what a row, an ID or a status means, and that idea is rarely written down.

The stakes are higher than in most software. A wrong number on a dashboard gets questioned eventually. A wrong number from an agent is a statement to a person about their money. In the US, misstating the amount of a debt is also something the law specifically prohibits.

So the first job isn't making the agent fluent. It's making sure the numbers underneath it mean what they appear to mean.

One loan, two rows

Some loans have two people on them. Think of a couple with a joint home loan. Either person can be asked to pay, but there's one loan and one balance.

Here's a synthetic export for one of those loans:

How much does F-17 owe? The owner of the source system confirms: 500. Each row isn't a loan. It's one person's connection to the loan, and the balance is copied onto both.

Group by file_number, sum the balance, and F-17 owes 1,000. Anyone who has summed an order total that repeats on every line item has written this bug before. As SQL, there's nothing wrong with it:

SELECT
    file_number  AS account_key,
    SUM(balance) AS balance
FROM source_export
GROUP BY file_number;

It runs, account_key comes out unique, there are no nulls, and the usual tests pass. The number is still double what's owed.

What the export actually holds is three different kinds of record:

This is the question of grain: what does one row actually represent? It has to be settled before any code is written, because once an agent has retrieved 1,000, nothing downstream can recover the 500. Even the keys a source system declares need checking. A field described as a primary key sometimes stops being unique once every row is examined, usually because of a handful of edge cases.

Who is this, really?

Identity is the second trap. Some systems issue a new ID every time a person is attached to a loan. One person with three loans gets three IDs.

Map those IDs directly to people and one person becomes three, each holding a third of their history. Correct for it by matching on name, birth date and postcode, and two different people who happen to share all three become one.

The two errors carry different costs. A false split means an agent doesn't know someone already agreed to a payment plan on another loan, and asks again. A false merge means an agent could discuss one person's loan with someone else. The second is far more serious, which is why a similarity score is never the final word.

What we do now is slower. First we find out what the ID identifies and in which system. Then we pick a person-level key and check candidate matches for both kinds of mistake. Whoever we merge still has to end up linked to all of their loans.

Two columns pretending to be one

Status is the third trap. A typical column might hold Active, Closed, Settled, Deceased and Bankrupt.

Closed and Settled describe a loan. Deceased describes a person. Bankrupt depends on the source system: it may be recorded against a person while its legal effect applies to specific loans.

Keeping these in one column works as long as each person has one loan and each loan has one person. Once that changes, it breaks. Say someone closes one of their three loans and Closed is stored against the person: their other two loans now look closed too. Or the primary borrower on a joint loan dies and Deceased is stored against the loan: the data now says the other person has died as well.

No test fails. Something downstream simply acts on the wrong entity, and in this setting, that something makes phone calls.

Every value needs an owner: the loan, the person or the link between them. When the data doesn't make the owner clear, the answer comes from someone who knows the source system. Bankrupt almost always needs that conversation.

Part of what makes lending data hard is that a single loan can pass through several hands: the original lender, a loan servicer, a collection agency, sometimes a law firm. Each describes the same loan from its own vantage point.

Not all blanks are equal

Three birth-date problems can look identical in a data profile. They need three different responses.

• Impossible values. A birth date later than the date of the export. It turns up more often than you'd expect. A simple date check catches it.
• Placeholders. 1900-01-01 on thousands of rows. It probably means "unknown." It might be an old default that meant something to someone. Converting it to NULL tells downstream logic something specific, so the meaning gets confirmed first.
• The wrong kind of entity. No birth date because the record is a business, not a person. Filling in a birth date would make a company slightly more human and the model more wrong. The fix belongs in the entity type, not the column.

Four questions before the first query

For F-17, the answers fit in one sentence: one loan, a balance of 500, two people, two links.

That sentence is the most valuable artifact in the pipeline. It's what the output gets checked against. Code that compiles is the easy part.

The Mars Climate Orbiter didn't need better software. It needed both teams to agree on what a number meant before anyone relied on it. Part 2 describes the agent built to reach that agreement, and how the sentence becomes a test.