When working with GenAI / LLM and data one of the first things to focus on is having it return valid SQL

#AgileData

Like most data platform/tool vendors we have been doing some McSpikeys with GenAI / LLM’s to see where it can help reduce the complexity of managing data.

The first one of course was the typical AskAI pattern of Text to SQL that every vendor is doing.

The second was an AssistedAI pattern to assist with the creation of Trust Rules (data quality tests). This is the first pattern we are moving into the core product.

Both of these patterns require the LLM to return SQL that will execute to give the results back to the data magician.

And here is the first challenge to overcome.

Our AgileData product is built on top of Google Cloud, and we make extensive use of BigQuery for data storage and compute (and of course we are using Google Gemini for the LLM)

We provide prompts to Gemini to give it the context that it needs to return SQL that is valid to execute in BigQuery.

And as with all non deterministic patterns, sometimes it does and sometimes it doesn’t.

So what Nigel Vining built to solve this problem is a validation loop. Once Gemini returns the SQL we test it, if it doesn’t execute successfully we loop back and push the request to Gemini again.

This seems to have solved the problem, as we use it more I’m sure we will find some use cases where it doesn’t (as Nigel says I quite good at using our product in a way that finds the edge cases)

Of course the downside of this pattern is it slows down the prompt back to the magician, but the entire process of finding and applying a custom Trust Rule is still way faster than the alternative manual process I used to use outside of the product.

So if your experimenting with the use of LLM’s on data have a go with this pattern if you need SQl that will actually run.