Beyond Chatbots: Introducing AI Assist, a SQL Generator for Analysts

When I first joined Mode back in 2016, I was struck by how deeply ingrained the motto “by analysts, for analysts” was within our culture. It was a guiding force for everything, from the direction of our features to our marketing voice and tone. And as we expanded and evolved the product over the years, as more people began to learn SQL and perform visual analysis, we always continued to believe in the value of BI, centered around analysts. So last summer when we were acquired by ThoughtSpot and the AI wave gained even more momentum, we started to think about how we might bring AI functionality to Mode. Perhaps unsurprisingly, that led us to one question: “What would an AI SQL generator look like if it was built for people who knew and loved SQL?” 

In terms of inspiration, the market had a few options to look to. There were code-complete solutions for software developers, and conversational chatbots for everyone else. But we had an instinct and idea that didn’t fall neatly into either camp. So we talked to tons of customers to better understand how they were working with ChatGPT and other ChatGPT alternatives already, and how they felt AI could make their jobs easier. The thing we heard every analyst say was they were spending the majority of their time in Mode on edits or revisions to the same, or similar, queries. 

If they could lean on AI to expedite these SQL edits, or even encourage others to attempt them on their own, they could spend more of their time on impactful work and be able to respond to even more questions, unlocking additional insights. Our proposed solution to this need resonated during our conversations so we decided to double down, and laser-focus our efforts to help analysts iterate through SQL edits more effectively.

There are many reasons we decided not to build another chat interface to address this particular problem (though ThoughtSpot is certainly cooking up some exciting ideas in this camp too). And to clarify, there are plenty of use cases for chatbots. I’m also a firm believer that great artists steal. When a mental model or framework catches on, it can be immensely beneficial to simply leverage what's working, to stick with a concept that broadly resonates and feels immediately intuitive. There are tons of studies proving the value of connecting and conversing with AI as if it were human. And frankly, the idea that you could simply pause what you were doing to chat with AI as though you were talking to a fellow analyst sounded awfully compelling. 

But we didn’t need AI to replace conversations; we needed it to replace writing the tedious, repetitive aspects of queries that our customers kept telling us about. We needed something that would let you talk to machines. Something kind of like…SQL? It seemed almost obvious in hindsight. Writing SQL with AI assistance could feel a lot like writing SQL. It’s already declarative in nature. It's a language that doesn't just perform tasks, it communicates with databases. It allows you to succinctly state what you want. And we could leverage that declarative power to offer an incredibly efficient and flexible solution. Lean on SQL keywords and valid syntax when it feels more natural, switch to English shorthand when it feels more natural. Think Mad Libs, for queries.

With an integrated approach like this, the jump between SQL and natural language could be fast. It could also sidestep all of the conversational lingo that tends to come along with catching a colleague up to speed, as your code would provide loads of that context for you. You’d simply add natural language requests or placeholders as needed, allowing you to stay in the flow of your work and ensure that any AI-generated output would more accurately reflect your business’s complexity and style.

This idea of query outlines quickly hit home. As a designer, you can move extremely fast when you’re wireframing. But if those outlines and sketches could be automatically converted into pixel-perfect mockups the moment you were ready to move forward with an idea, the time it would take to get to a shared solution would be exponentially faster. We wanted to bridge that same gap for analysts. 

So we built AI Assist. And with it, you can quickly map out a query structure before diving into the details. You can define a clear vision for most of it, while leaving some areas fuzzy. Or, you can simply prompt AI to make improvements to existing code, or take over work that feels cumbersome and tedious, perhaps finishing that LAG function you can’t quite recall. 

So how does it work?

Let’s imagine we need to analyze account manager performance, which will require joining a few tables together and writing some long-winded expressions. It's not hard to do, but it will take some time to type out by hand. Now let's imagine how we’d write it if we were simply outlining the query to the database. We might start by saying something like: 

SELECT --! total sales by rep this week

Notice that the majority of this statement is written in plain English. We aren’t specifying how to calculate total sales, or referring to an explicit column name. These are aspects AI Assist will fill in on our behalf.  Mode also treats these natural language prompts like special AI comments so that they can be used for AI placeholders and requests, but similar to traditional SQL comments, will be ignored if the SQL is run against the warehouse.

Next, we’ll add a FROM statement to provide some context about the data. By referencing the tables I want to include, Mode will provide all of the schema and column meta-data as context to AI Assist automatically. 

SELECT --! total sales by rep this week

FROM mode.dunder_mifflin_paper_sales ps

We’ll also join in a second table, but let’s be lazy about the join key. Both of these tables include a column for account reps, but we’ll look to AI Assist to locate the correct column names and define the join more explicitly.

SELECT --! total sales by rep this week

FROM mode.dunder_mifflin_paper_sales ps

JOIN novaksam.dm_employees e ON --! account manager

After, we’ll add an ORDER BY and update the SELECT statement to describe a few of the columns we’d like to add from the second, joined table. 

SELECT --! total sales by rep this week, title, years at the company

FROM mode.dunder_mifflin_paper_sales ps

JOIN novaksam.dm_employees e ON --! account manager

ORDER BY --! best manager

Finally, we’ll sprinkle in an ask to format the final query so it’s a bit easier to parse. SELECT --! total sales by rep this week, title, years at the company

FROM mode.dunder_mifflin_paper_sales ps

JOIN novaksam.dm_employees e ON --! account manager

ORDER BY --! best manager

--! and format the code to be more legible

After submitting this draft to AI Assist, we can review the AI-suggested SQL alongside our original edits, to quickly compare the differences and validate the output before using it. Here’s what we get back.

SELECT

  e.account_manager,

  e.title,

  e.years_at_dunder_mifflin,

  SUM(ps.price * ps.quantity) AS total_sales

FROM

  MODE.dunder_mifflin_paper_sales ps

  JOIN novaksam.dm_employees e ON ps.account_manager = e.account_manager

WHERE

  purchased_at >= CURRENT_DATE - INTERVAL '7 days'

GROUP BY

  e.account_manager,

  e.title,

  e.years_at_dunder_mifflin

ORDER BY

  total_sales DESC;

That was only one fairly simplistic query, touching on a few of the many supported use cases.  And though I primarily used natural language to write placeholder code, I also leveraged it to leave a quick contextual request, think “--! update all columns to be aliased in Title Case.” The potential for AI Assist is bound only by your creativity, and we know we’ll be blown away by the ideas y’all come up with.

Will this approach resonate with you? Hopefully. Will you find the concept of writing SQL shorthand or leaving AI notes in the margins uncomfortable, particularly at first? Maybe. But will you quickly discover that it enables you to work even faster? We think so, and that’s what’s been driving us over the last few months. 

Mode’s goal isn’t to replace analysts with simplistic chatbots that can write basic queries. We want you to be able to write all the queries you want to— just in less time, with less headache, taking shortcuts wherever you've always wished you could take them. And though we built AI Assist with the goal of making analysts more efficient, we also see tremendous value in up-leveling more novice writers by providing a bridge to mastering SQL. If that sounds like something you’d be interested in, you can request access here!

Be sure to check out our onboarding video and interactive guides as you get started. Query on. 🤘