By Zeel Patel, Data & AI Platform Engineer at Prodigal
Part 2 of 2. Part 1: Why “just connect the AI agent to the data” is the hard part.
Nobody pours a foundation before the floor plan is signed. Concrete is expensive to undo. A floor plan is cheap to argue about.
Data pipelines deserve the same order of operations. A wrong assumption about what a row means is cheap to fix on paper. Buried in a few hundred lines of generated SQL, it's expensive even to find.
We built the PIE-mapping agent, which onboards a new lender's data, around the same idea. Before it writes any SQL, it writes down what it thinks each source field means, where it should go in our model, and what it isn't sure about. Someone on the team reviews that, and only after it's approved does the agent write code.
Models are already good at SQL. That's the problem.
Given an ambiguous dataset, a model will write clean SQL for whichever interpretation it assumed. Asked for tests, it will write tests that agree with that assumption. In Part 1, a joint loan with a balance of 500 became 1,000 because each person's row was read as a separate loan. A model would reproduce that error fluently, with a passing test attached.
The hard part of onboarding isn't writing code. It's establishing what the data means. Done by hand, onboarding a new lender took eight to ten days of clarifying, building, checking and reviewing. The agent takes on parts of the investigation and the pipeline work.
LangGraph orchestrates the workflow. The inputs are the lender's name, the product use case, a mapping sheet describing the source fields, and the source data, already landed in our raw data layer.
The agent also gets a little business context to start from. For a lender it will usually look for loans first. For a law firm handling collections, the file number tends to be the account number. It checks either guess against the mapping sheet and the data before relying on it.
First the agent runs queries against the data itself. For F-17, counting rows per file number turns up two rows and two consumer IDs:
SELECT
file_number,
COUNT(*) AS source_rows,
COUNT(DISTINCT consumer_id) AS distinct_consumer_ids
FROM source_export
WHERE file_number IS NOT NULL
GROUP BY file_number
HAVING COUNT(*) > 1;That rules out file_number as a unique key for rows. It doesn't explain why the number repeats. Two people on one loan, two transactions, monthly snapshots and plain duplicates all produce the same result.
Which of those it is, only the people who own the source system can say, so the agent writes it down as a question for them. It also looks at key candidates, null rates, which values each column really contains, and whether two IDs that look alike in different systems point to the same thing. It does this part better than we did by hand. We never compared every row. The agent does, and when a column the customer called a primary key turns out not to be unique, it lists the rows that break it and the likely reason. That often becomes our first question back to the customer.
Simple mappings are handled by fixed rules. The rest end up as questions in a review sheet, like this one about F-17:
"Does each row represent a separate loan, or a person's connection to a loan? Is the balance repeated for each person?"
Someone from the team types the answer in the next column and the agent runs again. We stuck with a spreadsheet because the onboarding team already reviewed mappings in one.
| Source field | Proposed target | Status | Agent's question | Reviewer's answer |
|---|---|---|---|---|
| balance | loan.balance | Needs clarification | Is the balance repeated for each person on the file? | Yes. One balance per file number. |
| role | link.role | Proposed |
Each field also carries a data-quality and confidence score. These work like traffic lights: they decide what moves forward and what gets another look. They aren't probabilities of being correct. That judgment stays with a person.
Once the mapping is approved, the agent writes the dbt models: staging, intermediate and final layers, plus reference data, incremental models, snapshots and tests where needed. It builds them, tries to fix failures within a fixed number of attempts, opens a pull request, and updates it based on review comments.
Passing builds and structural tests confirm that code runs and constraints hold. They don't confirm the interpretation. The query that turned 500 into 1,000 passes a uniqueness test.
The answer is to take the expected result from a source the generator didn't write: the reviewer's answer. A dbt unit test is a natural fit, because the test data is the clarified example itself:
unit_tests:
- name: joint_loan_keeps_one_balance
model: int_accounts
given:
- input: ref('stg_source_export')
rows:
- {file_number: F-17, consumer_id: C-101, role: primary, balance: 500}
- {file_number: F-17, consumer_id: C-202, role: joint, balance: 500}
expect:
rows:
- {account_key: F-17, balance: 500}If a test like this is wrong, it's because the reviewer's answer was wrong, and that's much easier to check than a few hundred lines of generated SQL.
The fixes the agent makes to its own failing builds need a careful look too. Suppose F-17's rows had said 500 and 450. Changing SUM to MAX would get the build passing, but nobody would have decided which balance is right. Dropping duplicates, picking one of the values or loosening a test can do the same thing. In the agent's pull requests, those are the diffs we read first.
Two of the issues we hit while building it had nothing to do with SQL.
First, early versions of the agent didn't follow the mapping sheet we gave them. They looked at the raw data, came up with mappings on their own, and then asked reviewers questions the sheet already answered. That wasted reviewers' time, and this whole setup depends on that time.
We changed the instructions so the agent reads the sheet first and then checks the data against it. If the two disagree, for example a field the sheet says is always blank actually has values, the agent raises it as a question.
The second issue was in the instructions. Some rules sat in the system prompt and others in skill files, and a few of them contradicted each other. There was no error to catch. Depending on the run, the agent went with one rule or the other, so the same input could produce two slightly different mappings. We noticed it in manual review, and getting the prompt and the skills to agree took two or three rounds.
Since then we try to keep each rule in one place, and we review changes to the instructions the way we review code.
Mapping sheets don't always come in a format the agent can read, so someone still has to reorganize them by hand.
There's also no speedup figure here, by design. Agent runtime is easy to measure. The full job also includes reshaping inputs, answering questions, reviewing code and reworking what the agent got wrong. A number that leaves those out would flatter the agent. The measurement worth publishing is the end-to-end one.
Splitting the mapping from the code means there are two reviews instead of one. The first checks whether we understood the data correctly. The second checks whether the code does what the mapping says. They go wrong for different reasons, and when both are mixed into one review, it's easy to miss either.
For F-17, profiling finds the repeated file number, a reviewer explains why it repeats, the approved mapping puts the balance on the loan, and the pipeline is tested against the reviewer's answer: one loan, a balance of 500, two people and two links.
The floor plan gets signed before the concrete is poured. And at the far end of the pipeline, an agent on a phone call says 500.
Zeel Patel is a Data & AI Platform Engineer at Prodigal, where he works on PIE, the data platform behind Prodigal's AI agents, and on the agent that onboards new customer data. Before Prodigal, he built data pipelines and BI for a bank's corporate digital banking group. He studied at IIT Kharagpur. Prodigal's engineering team in Bengaluru is hiring: prodigaltech.com/careers.