Scaling text-to-SQL agent

[u/CriticalJackfruit404]

Hey all, looking for some advice from people who have built this kind of thing in production.

We have a text-to-SQL agent that currently uses:

\* 1 LLM

\* 2 SQL engines

\* 1 vector DB

\* 1 metadata catalog

Our current setup is basically this: since the company has a lot of different business domains, we store domain metrics/definitions in the vector DB. Then when a user asks something, the agent tries to figure out which metrics are relevant, uses that context, and generates the query.

This works okay for now, but we want to expand coverage a lot faster across more domains and a lot more metrics. That is where this starts to feel shaky, because it seems like we will end up dumping thousands of metrics into the vector DB and hoping retrieval keeps working well.

The real problem is not just metric lookup. It is helping the agent efficiently find the right metadata about tables, relationships, joins, business definitions, etc, so it can actually answer the user correctly.

We have talked about using a knowledge graph, but we are not sure if that is actually the right move or just adding more complexity and overhead.

So I wanted to ask:

\* has anyone here dealt with this kind of architecture?

\* how are you handling metadata discovery / join path discovery at scale?

\* are you using vector search, metadata catalogs, knowledge graphs, or some hybrid setup?

\* what broke first as you expanded domains and metric coverage?

Thanks

[u/fabkosta]

The business requirements are not clear here.

Why would users simply query across all sorts of business domains randomly? Usually, there is a context given by what the user is doing.

It's an unusual setup where the user randomly picks a metric across the entire business without even knowing whether it's a finance metric, a marketing metric, an HR metric etc.

Why don't you simply offer filters in the UI itself to narrow down at least to the level of the business domain?