How to build a secure Text-to-SQL analytics?

Why Can't You Just Ask Your ERP a Question?

Somewhere in your SAP or Oracle instance, there's a table that already has the answer to "why did margin dip in Region 3 last week." A senior engineer knows it. Everyone else has to file a ticket and wait.

That gap — between data that technically exists and people who can reach it without help — is the real bottleneck in most ERP-run enterprises today. Text-to-SQL and conversational analytics tools promise to close it by letting anyone type a question in plain English and get a real answer, no SQL required. The demand is real. So is the reason most attempts at it quietly fail once they hit a live ERP.

What is text-to-SQL?

Text-to-SQL is the class of systems that convert a natural language question, "what were our top five SKUs by margin last quarter" into a working SQL query, run it against a database, and return the result in plain language. Conversational analytics is the broader product experience built on top of it: a chat interface over your data warehouse instead of a dashboard you have to know how to filter.

In theory, any large language model (LLM) can write SQL. In practice, pointing an LLM straight at an enterprise ERP is where things break down.

Why ERPs fight back against natural language search

ERP schemas weren't designed to be asked questions in English. They were designed for the application logic running on top of them, which means:

Cryptic naming: Tables like MARA, BSEG, or T001 mean nothing to an LLM, or to most humans outside the SAP team. Any system that maps these by hand ends up with brittle, hardcoded logic.

Schema drift: A routine ERP upgrade or config change can silently break a hardcoded mapping, and the failure usually shows up as a wrong number in a report, not an error message.

No inherent business meaning: A column can hold a number without the schema telling you whether it should be summed, averaged, or run through some business-specific formula. Structure alone can't answer that.

This is why most "just ask your data a question" pilots work fine in a demo on clean sample data and then stall the moment they meet a real ERP.

The architecture that actually holds up

Making this reliable takes more than a bigger model. It takes an architecture that assumes the ERP schema is messy on purpose and works with that, not around it.

Adapter-driven, pushdown execution: Rather than pulling operational data out into a separate processing engine, a database-agnostic adapter layer lets computation run natively inside the client's existing database—SAP, Oracle, whatever it is. Nothing sensitive gets copied out just to be queried.

A trimmed-down medallion architecture: Raw ERP data lands in a Bronze layer untouched. An ingestion layer then standardizes it into a Silver layer that consolidates what would normally be split into Silver and Gold, one analytical layer instead of two, less storage overhead, fewer pipeline stages to maintain.

In-flight virtualization instead of renaming anything: Instead of physically renaming MARA to materials_master, which is destructive and hard to reverse—helper metadata tables translate physical names into logical, readable ones during the Bronze-to-Silver step. The original schema stays untouched underneath; the readable version is what gets queried. Nothing gets migrated, nothing gets locked in.

Between them, this means schema chaos gets absorbed by the pipeline instead of becoming everyone's daily problem.

Structure gets you standardization. It doesn't get you accuracy.

Here's the part that's easy to skip past: even a perfectly standardized warehouse still can't tell an LLM whether "revenue" should include tax, whether a metric needs a business-specific rollup instead of a plain SUM, or which of three similarly named fields is the one finance actually trusts.

That's a semantic problem, not a structural one, and no amount of schema discovery solves it on its own. It has to be answered by people who know the business — which is why a semantic layer, populated by domain experts against generated templates, sits on top of the standardized warehouse. It's the difference between a system that can technically run a query and one that runs the query your finance team would have written. Leaning on this layer instead of pure LLM inference is also what keeps answers deterministic instead of "probably right most of the time."

The part most conversational analytics tools skip: security

Ask most vendors how their AI analytics tool works, and the honest answer is: the LLM sees your data. It has to, to answer questions about it. For anyone with PII, financial records, or basic governance obligations in that ERP, that's not a small caveat.

A more defensible approach is metadata-isolated execution: the LLM never touches the actual rows. It only ever sees structural metadata — table and column descriptions, relationships, business definitions — never customer records or transaction values. A deterministic routing layer figures out which slice of metadata is relevant to the question, hands only that to the model, and the model returns SQL. That SQL gets validated as read-only before it's ever allowed to run against the real warehouse.

The result: the LLM writes a query it's never actually allowed to see the output of until the query has already been checked. Business data never enters the prompt. That one design choice does more for enterprise security posture than any amount of access control bolted on afterward.

Follow-up questions shouldn't reset the conversation

A one-shot question-and-answer bot gets old fast. Real analysis is a back-and-forth: "what about the last three days," "break that down by region," "now just show me the outliers." A bounded rolling conversation history lets the system carry that context forward without the user re-stating everything from scratch each time — closer to how you'd actually talk to an analyst than to a search box.

Why this matters more than it sounds like

None of this is really about chatbots. It's about how long it takes an enterprise to get an answer out of a system it already owns. Right now, that answer usually runs through a data engineering team, a ticket queue, and a few days of turnaround for something that should take thirty seconds.

A modular, metadata-driven approach to text-to-SQL doesn't just make querying feel more conversational. It removes the dependency on custom middleware, keeps sensitive data out of prompts by design, and gets non-technical stakeholders unstuck without waiting on a specialist every time a question comes up. For any enterprise mid-way through modernizing its ERP stack, schema readiness and metadata quality are the real starting point, before anyone talks about which LLM to plug in.

FAQs

1. Is text-to-SQL accurate enough for enterprise reporting?

Accuracy depends far more on the semantic layer than on the LLM itself. Systems that rely on curated business metadata for query generation, rather than pure model inference, produce far more consistent, auditable results than "ask the AI and hope" approaches.

2. Does the LLM need direct access to our ERP data?

No, and it shouldn't. A well-architected system routes only structural metadata to the LLM, generates SQL from that, validates it as read-only, and executes it separately. The model never sees the underlying business data.

3. What's the difference between text-to-SQL and a BI dashboard?

A dashboard answers the questions someone already anticipated. Text-to-SQL answers the question you have right now, in your own words, without needing a pre-built report or a data team on standby.

4. Can this work with SAP or Oracle without renaming our tables?

Yes. In-flight virtualization translates physical object names to logical ones on the fly, during the pipeline transformation step, without touching or renaming anything in the source schema.

5. How does this handle schema change over time?

Because logical names are generated dynamically from metadata rather than hardcoded, routine ERP schema changes are far less likely to silently break existing queries and reports.

6. Who needs to be involved to set this up?

Data engineers handle the pipeline and adapter layer; domain experts populate the semantic templates. Neither piece works well without the other — structure and business meaning both have to be in place.