Hands-on with the PromptQL Playground

Suppose you had like at least 10 bigs of data (so much big data!!!!) across multiple databases. Even in a really well-designed data warehouse, you could easily spend half a day trying to answer a very simple question, such as "How many Type-A widgets did I sell to BigCo last month?" Typically, orders would be in one table, the mapping of customerID (included in the order) to customerName is in another table, and the mapping of productID to ProductName is in yet another table. Then you find out that that BigCo acquired smallCo a few years ago so your SQL join on the customerId creates all these random duplicates and you spend the other half of the day trying to re-remember which SQL join is the 'bad' one. It sucks and I hate it.

What I usually end up doing is deciding to ask ChatGPT to write the SQL query for me. This usually involves me creating a simpler schema than what the three tables actually use, mentally mapping the actual table column names to the new "simple" schema, asking ChatGPT how to write a query that gives me roughly what I want, then mentally mapping the response example back to the "real" column names. It is usually pretty close on the first or second try and the error messages from the SQL server get me the rest of the way to a mostly working query. This approach would still take forever and had me juggling these column names in my head during the whole process. It sucks and I hate it.

If you're reading this and thinking "just dump your databases into the ChatGPT context and ask it the question directly", please don't ever touch my data! Hallucinations by Large Language Models make them completely unsuited for that approach. Best case? You anger your boss. Wost case? You anger the SEC because you did that for a quarterly financial report and it hallucinates a few million extra revenues.

The Hasura team gave me a preview of their new PromptQL feature last week and it had me awe-struck with both how clever and how obvious it is. PromptQL takes your natural language query, creates a plan for how it would solve it, then tries to implement the plan in code, fixing errors based on the error messages, and then shows the result from actually querying the database. It's relying on the language model to write the SQL query, but not trying to 'force' the model to do actual reasoning about the data. This drastically limits where hallucinations can occur to places that can be systematically checked (via the SQL query engine rejecting incorrect syntax).

Let's walk through my first use of the feature without someone from Hasura coaching me :D

I used their e-commerce sample data

I open the PromptQL feature and ask it a pretty simple question that spans multiple tables in the database. Who are my top cusotmers by revenue that have submitted at least one review?

and it returns the correct result, but in a way that I describe as "how my brother, the history major, would have done it". It makes a couple queries, saves the results of both locally, then uses python to smash the two together. It works, it gives me the right answer, but I can't really hand my boss this amalgamation of SQL and Python and say "here you go! oh, by the way, can we talk about that raise I've been asking about?"

I noticed that the reviews table included text embeddings and tried to make the question a bit more challenging by asking it to limit the responses to customers who have created critical reviews. I was thinking it would somehow include those embeddings, but instead it was much more straight-forward and just looked at the review score. That's fair, I guess. It still gave the right answer based on limiting the answers to ratings of 2 or lower. It clearly states that the assumption it's using is that the rating value is used as the deciding factor for what consitutes a 'critical' review. I like that!

I check the results it gives against the database and the answer looks correct.

Now, I'm going to try to get it to address that problem I mentioned earlier that I can't just hand it to my boss and pretend I did all this hard work all by myself. I ask PromptQL "can you combine that into a single query?"

Well, that didn't work!

The PromptQL Playground shows the actual results of the query on right-hand side of the page in a section titled "Memory Artifacts". This section shows the returned results from each query and here we can see that the "Total Revenue" amounts for each customer are all higher than they were in the previous queries. It appears to be multiplying how much the customer spent by the number of reviews they've submitted.

Being able to see the actual query that was run is super helpful here. That join statement doesn't look right.

So I tell PromptQL what it's doing wrong and it creates a new plan that ..... results in the same query.

Now I'm ready to fight someone and I let PromptQL know. Instead of agreeing to meet me in the parking garage, PromptQL asks what specifically it got wrong. I give it a concrete example from the first row in the results table and that looks like enough info to convince PromptQL that the current approach isn't working.

This is where I get really impressed with PromptQL. There was a syntax error in the SQL query and PromptQL was able to recognize the error message and adjust the query to avoid it.

that was enough feedback to get PromptQL to write the correct query.

I have a single SQL query that will return the right results. That raise is in the bag!!

In the early days of things like ChatGPT, people were lamenting the end of sites like Stack Overflow because ChatGPT could provide the same answers. I saw someone on Twitter say something along the lines of "ChatGPT never calls me a moron and tells me to RTFM, good riddance!"

I like to think of PromptQL as this infinitely patience Stack Overflow expert who wants nothing more than to help me learn. We've all encountered someone like this. You ask a question and not only do they answer it, but they provide sample code to demonstrate the answer and clearly explain what each step is doing so you don't just have an answer but you understand the answer too.

The world needs more tools that make you feel the way PromptQL does.