We built an AI assistant into a client’s ERP so managers could ask for sales, receivables, inventory and order figures without waiting on a report. The first version worked often enough to be interesting and failed often enough that nobody relied on it.
The instinct in that situation is to blame the model, change the prompt, or try a different one. We did some of that. It helped least. Almost everything we eventually fixed was a data problem or a prompt-input problem that we had created ourselves.
This is what was actually wrong.
The symptoms
The failures were not random nonsense, which is what made them confusing. They looked plausible.
A question about a brand would come back empty, when the brand clearly existed. A question about open orders would return a number that did not match the ERP screen anyone could open themselves. Quantities would appear formatted as dollars. A query would run correctly and then the answer would say no sales were found.
Worst of all, some questions were slow enough to time out, and a user who waits a long time for a wrong answer stops asking.
Cause one: the model was guessing column names
We had given the model a list of table and column names. Nothing else. No types, no join keys, no indication of what values a column actually holds, and naming conventions that differed from table to table.
Faced with a gap, a language model does what it is built to do: it produces the most plausible continuation. If the schema it was shown has no obvious column for what the question needs, it invents a column that sounds right. From the model’s point of view this is not an error. It had no way to know the column did not exist.
That reframed the problem for us. We were not dealing with a model that lied. We were dealing with a model working from an incomplete brief.
Cause two: some of our business rules were wrong
We had written rules to steer the model: how to calculate margin, what counts as an open order, which table holds brands. Some of those rules were wrong, and because they were wrong in the prompt, the model followed them faithfully.
One rule pointed brand searches at a field that did not exist on the inventory table. Another defined open orders using a flag that was always zero, so the correct answer under our own rule was always nothing.
This is the uncomfortable category. The model was not hallucinating. It was obeying.
Cause three: correct SQL, wrong presentation
A third group of failures had nothing to do with the query at all. The SQL was right, the rows came back right, and the answer was still wrong.
Quantities were rendered as currency. Columns showing whether an invoice was paid were being hidden before the user saw them. In one case a discount of zero was treated as a missing value, which made the assistant announce that there were no sales when the sales were there.
Users do not distinguish between a bad query and a bad rendering. They just see a wrong answer.
Cause four: the data underneath
The last group was the data itself, and it is the one most teams skip. A reporting refresh could leave key tables empty, which meant an entirely correct query returned nothing at all. Inventory was reading from a source that had drifted out of date.
No amount of prompt work fixes that. The assistant was accurately reporting what was in the tables.
What we changed
A schema with meaning, not just names. We rebuilt what the model sees: every column with its type, its meaning, its join keys and its real allowed values, read from the live database rather than written by hand. Columns that were always empty or actively misleading were removed. Business rules were rewritten by domain and the contradictions taken out. This single change cut invented columns to near zero, because there were no longer gaps to fill.
Golden examples that run on every build. We wrote 40 verified question-to-SQL examples and made them execute against the real database on every build. That matters more than the count. An example that is never run is documentation; an example that runs is a test. When the data mart changes underneath, the example fails immediately rather than quietly becoming wrong.
A validation layer instead of trust. We stopped trying to make the model write only safe SQL and made unsafe SQL unable to run. Every query is parsed with Microsoft’s T-SQL parser and bound against an allow-list of tables, columns, aliases and functions. Only read-only statements execute. If validation fails, or SQL Server throws at runtime, the query gets exactly one schema-aware repair attempt using the closest valid column names. If that also fails, the assistant says so rather than guessing.
The assistant also reads the live schema at startup and switches off any rule or example referring to something the database no longer has. The system tells the truth about itself as the database changes.
Fixing the data mart. Refreshes now load into staging tables and swap in a single transaction behind a row-count guard, so a failed refresh cannot leave a table empty. Inventory moved to the live item source. Fields that staff could already see on ERP screens, such as tracking numbers, shipment cost, carrier, rep names and return reasons, were added to the data mart so the assistant was no longer being asked about data it did not have.
Measuring it instead of sampling it
The change that made the rest provable was the evaluation harness. We assembled 601 end-to-end test questions and replay them, asserting on both the generated SQL and the answer.
599 of those 601 pass, at 99.7 percent. The two failures were fixed the same day we found them. Deterministic code checks went from 106 to 247. Repeated questions across 9 test sets returned identical totals every time, which is the property that actually earns trust: the same question has to produce the same number today and next week.
Speed changed too. Short and aggregate answers now return in 2 to 5 seconds, or 6 to 11 seconds when a repair runs, against a previous path that could take up to 1.5 to 2 minutes before timing out.
Before the harness existed, our evidence was anecdotes. Someone would try a question, it would work, and we would feel better. Anecdotes cannot tell you whether today’s prompt change broke last week’s working question.
What we would tell another team
Check the data before blaming the model. In our case the majority of apparent model errors were missing columns, wrong rules or stale tables.
Give the model meaning, not just names. Types, join keys and real allowed values are what stop invented columns.
Do not rely on the model to be safe. Parse and validate its output, allow only read-only statements, and let the system refuse rather than improvise.
Build the evaluation before the next prompt change. You cannot improve what you are not measuring, and an assistant that answers confidently and wrongly is worse than no assistant.
The full build, including the fourteen business modules and the validation layer, is written up in our ERP AI assistant case study. If you are weighing up an assistant over your own business data, we cover that work on AI integration services and the systems it runs against on custom ERP development. Get in touch with what your data looks like today and we will tell you honestly whether it is ready.
Common questions
Was the model the problem?
Mostly not. Nearly everything we traced started as a missing column type, a wrong business rule or a display decision. The model was filling gaps we had left in what we gave it.
Can you stop a language model writing bad SQL?
You cannot stop it writing bad SQL, so we stopped bad SQL reaching the database. Every query is parsed and bound against the real schema, and only read-only statements run.
How do you know an answer is right?
We run a fixed set of questions end to end and assert on both the generated SQL and the answer, on every build. A number nobody has tested is a number nobody should trust.