Building AI agents to query your databases • Hacker News Board

Re: Building AI agents to query your databases

#11

Post by troupo » Tue, Mar 18, 2025, 11:14 AM UTC

ritz_labringue wrote: post #6

bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

It does require writing good instructions for the LLM to properly use the tables, and it works best if you carefully pick the tables that your agent is allowed to use beforehand. We have many users that use it for every day work with real data (definitely not toy problems).

> It does require writing good instructions for the LLM to properly use the tables

--- start quote ---

prompt engineering is nothing but an attempt to reverse-engineer a non-deterministic black box for which any of the parameters below are unknown:

- training set

- weights

- constraints on the model

- layers between you and the model that transform both your input and the model's output that can change at any time

- availability of compute for your specific query

- and definitely some more details I haven't thought of

https://dmitriid.com/prompting-llms-is-not-engineering

--- end quote ---

Re: Building AI agents to query your databases

#12

Post by ozim » Tue, Mar 18, 2025, 11:22 AM UTC

bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

A bit of a joke here:

It solves 100% cases where some manager requests dashboard never to look at it again after one day.

Re: Building AI agents to query your databases

#13

Post by abirch » Tue, Mar 18, 2025, 11:24 AM UTC

spolu wrote: post #8

bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

Hi, you are right that things can go sideways fast. In practice, the data that the typical employee needs is also quite simple. So there is definitely a very nice fit for this kind of product with a large number of use-case that we do see provide a lot of value internally for employees (self access to data) and data scientist (reducing loads). For complex queries/use-cases, we generally instead push our users to crea…

This works well until it doesn’t. As long as there is someone who is responsible for the data correctness. E.g. the cardinality of two joining tables maintained cardinality instead of: there’s currently no one in the system in with two locations in the employee_to_location table so it works right now. One it happens there will be the wrong employee count from this query

Re: Building AI agents to query your databases

#14

Post by gavinray » Tue, Mar 18, 2025, 11:27 AM UTC

Interesting -- I work on a similar tool [1], and the JSON IR representation for your query is similar to the internal IR we used for our data connectors.

[1] https://promptql.hasura.io

Re: Building AI agents to query your databases

#15

Post by yonl » Tue, Mar 18, 2025, 11:58 AM UTC

mritchie712 wrote: post #10

We ( https://www.definite.app/ ) solved this a bit differently. We spin up a data lake and pipelines (we support 500+ integrations / connectors) to populate the data lake for you then put DuckDB on top as a single query engine to access all your data.

This is really interesting. At my previous company, I built a data lakehouse for operational reporting with recency prioritization (query only recent data, archive the rest). While there was no LLM integration when I left, I've learned from former colleagues that they've since added a lightweight LLM layer on top (though I suspect Dustt's implementation is more comprehensive).

Our main requirement was querying recent operational data across daily/weekly/monthly/quarterly timeframes. The data sources included OLTP binlogs, OLAP views, SFDC, and about 15 other marketing platforms. We implemented a datalake with our own query and archival layers. This approach worked well for queries like "conversion rate per channel this quarter" where we needed broad data coverage (all 17 integrations) but manageable depth (reasonable row scanned).

This architecture also enabled quick solutions for additional use cases, like on-the-fly SFDC data enrichment that our analytics team could handle independently. Later, I learned the team integrated LLMs as they began dumping OLAP views inside the datalake for different query types, and eventually replaced our original query layer with DuckDB.

I believe approaches like these (what I had done as in house solution and what definite may be doing more extensively) are data and query-pattern focused first. While it might initially seem like overkill, this approach can withstand organizational complexity challenges - with LLMs serving primarily as an interpretation layer. From skimming the Dustt blog, their approach is refreshing, though it seems their product was built primarily for LLM integration rather than focusing first on data management and scale. They likely have internal mechanisms to handle various use cases that weren't detailed in the blog.

Re: Building AI agents to query your databases

#16

Post by LaGrange » Tue, Mar 18, 2025, 12:00 PM UTC

I mean this is generally repulsive, but please I beg of you, run this exclusively against a read only replica. I mean, you should have one for exploratory queries _anyway_, but nobody ever does that.

"Validating the query to ensure it's safe and well-formed" all I can say to that is "ROFL. LMAO."

Re: Building AI agents to query your databases

#17

Post by aargh_aargh » Tue, Mar 18, 2025, 12:05 PM UTC

ozim wrote: post #12

bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

A bit of a joke here: It solves 100% cases where some manager requests dashboard never to look at it again after one day.

There's no smoke without fire.

Re: Building AI agents to query your databases

#18

Post by mritchie712 » Tue, Mar 18, 2025, 12:17 PM UTC

bob1029 wrote: post #5

> This abstraction shields users from the complexity of the underlying systems and allows us to add new data sources without changing the user experience. Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast. I think it might be possible to have a human in the loop during the SQL authoring pha…

Using a semantic layer is the cleanest way to have a human in the loop. A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU.

With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much more consistent at writing a small JSON vs. hundreds of lines of SQL.

We[0] use cube[1] for this. It's the best open source semantic layer, but there's a couple closed source options too.

0 - https://www.definite.app/

1 - https://cube.dev/

Re: Building AI agents to query your databases

#19

Post by gcanyon » Tue, Mar 18, 2025, 12:17 PM UTC

My general method for things like this is to:

1. Get a .dot file of the database. Many tools will export this. 2. Open the .dot in a tool I built for the purpose. 3. Select the tables I'm interested in, and export a subset of the .dot file representing just those tables and relationships. 4. Hand that subset .dot file to the LLM and say, "given this schema, write a query -- here's what I want: "

That gets the job done 60% of the time. Sometimes when there's an incorrect shortcut relationship resulting in the wrong join I'll have to redirect with something like, "You need to go through to relate to " That gets my success rate up above 95%. I'm not doing ridiculous queries, but I am doing recursive aggregations successfully.

Re: Building AI agents to query your databases

#20

Post by gergely » Tue, Mar 18, 2025, 12:24 PM UTC

How do you solve the following problems? 1. Whenever you run a natural language question the result will be the same for the same question? 2. How do you teach an agent/LLM for the datamodel of the application that stores the data in the databases?