BigQuery Text-to-SQL: Query Your Database in Natural Language

On this page
You can chat with your BigQuery data by putting a small AI agent between the user and the database. The agent reads the table schema, asks a large language model (here OpenAI's GPT-4o) to translate the question into BigQuery SQL, runs the query and, if BigQuery returns an error, sends the error back to the model to fix the query. On the telecom churn demo dataset used below, this simple loop answers questions like "How many churns are in the churn_table?" (1869) without anyone writing SQL.
Large language models are very good at writing SQL for a variety of databases, including Google BigQuery. That matters, because BigQuery datasets tend to be complex and often hold some of a company's most relevant business data. Instead of first searching for the right data model and then writing a query, you ask "How many users signed up last month?" in plain language and get an answer in seconds.
In this post we show you how to build such a conversational interface for Google BigQuery with GPT-4o. We cover setting up the development environment, connecting to BigQuery, using the OpenAI API to generate the SQL queries and executing them against your BigQuery database.
As a bonus, we show how to use RAG - Retrieval Augmented Generation to improve the quality of the generated SQL queries. If you would rather connect BigQuery to existing AI agents and chat clients, have a look at our guide on running an MCP server for BigQuery.
Update (November 2026): OpenAI has deprecated GPT-4o. The gpt-4o
snapshots used below shut down on January 14, 2027, according to OpenAI's
deprecations page. The
agent works the same with any newer chat model that supports function
calling, so only the model parameter needs to change. The embedding code
uses text-embedding-ada-002, which still works; OpenAI's current
embedding models
are text-embedding-3-small and text-embedding-3-large. If you switch,
check the similarity threshold in query_chromadb again, because the
distances change with the model. The ChromaDB snippet uses
get_or_create_collection, which works across ChromaDB versions (the
return type of list_collections() changed between releases).
Architecture of a chat interface for BigQuery
A "chat with your BigQuery" solution needs three elements:
- A way for the user to enter a question in natural language.
- A layer that translates the natural language question into SQL.
- A runtime to execute the SQL query against the BigQuery database.
Elements 1 and 3 are straightforward. The main logic and complexity lies in element 2, translating natural language into SQL.
High-quality SQL generation needs the following sub-steps:
- Get the structure of the database, the database schema.
- Get a description of the individual columns, so that the AI can understand the context of the data.
- Use retrieval augmented generation to find the most relevant BigQuery tables and columns for the user query.
- Use a large language model to generate the SQL query.
- Automatically correct errors in the generated SQL query, as LLMs are not perfect and produce erroneous queries from time to time.
Put together, a robust conversational chat interface for BigQuery has the following high-level architecture:
High-level architecture of a conversational chat interface for BigQuery
Looking at the architecture in more detail, it becomes clear that we don't just "connect" BigQuery to an AI model. We need an AI Agent.
In short, AI agents are AI models that take a user input and then independently use their knowledge, external tools (like database access) and memory to solve the user query.
Our agent is small. Its task is to take the user query and translate it into a working SQL query that answers it. As tools, it gets BigQuery database access and a Python runtime. The knowledge it needs, SQL generation, is part of the AI model itself.
AI agent combining user input, database access and a Python runtime
Note: As the set of tasks we need the AI to solve on its own is limited, our AI agent will be quite small. AI agents can handle a wide variety of tasks, but here we only need a very limited implementation. All our agent needs to do is generate SQL queries, execute them, decide whether to execute them again on error and return the result to the user.
Step by step: how to build a text-to-SQL agent for BigQuery
With the architecture in place, let's go through the implementation. We build the whole AI agent loop from scratch, without any of the existing LLM libraries. (For a broader walkthrough of this approach, see how to build AI agents without frameworks.)
Why not use LangChain or LlamaIndex?
Both LangChain and LlamaIndex provide SQL AI Agents as part of their feature set.
Both are a great way to get started with text-to-SQL agents, but their abstractions make it hard to understand what happens underneath. More importantly, their implementations are rather generic, built to cover many SQL databases. After many hours and days of building SQL AI agents, we found that every use case needs tweaking in how the SQL is generated and how errors are handled. The high-level interfaces of LangChain and LlamaIndex don't give you that flexibility.
Preparation
First, make sure that you have an active Google Cloud project as well as activated billing for said project.
Next, enable the following Google Cloud APIs:
Next, set up the Google Cloud BigQuery authentication as follows:
-
Create a service account for your project. Add the following BigQuery roles:
- BigQuery User
- BigQuery Data Viewer
- BigQuery Job User
-
Download the service account key of the newly created service account.
Finally, let's import some demo data into BigQuery. Alternatively, you can use your own data.
-
Download the demo dataset. It contains data from a well-known Kaggle challenge about customer churn at a telecom company.
-
Import the file into BigQuery in a new dataset
aiagent_testand tablechurn_table.
Implementing the AI agent
-
Create a new venv or conda environment and activate it.
-
Install the required Python packages:
-
Import the modules, define the BigQuery dataset and table, and set the OpenAI API key.
-
Create a SQLAlchemy engine for the BigQuery database.
-
Let's test our database connection:
The results should be:
-
With the connection working, retrieve the database schema.
This provides a list of all the tables and columns in the provided BigQuery dataset.
With the preparation done, we can implement the AI agent itself. We use the OpenAI API to generate the SQL queries and execute them against the BigQuery database.
Implementing the agent loop
At its core, an AI agent is nothing more than a while loop that takes a task or query and invokes an AI model to solve it. The loop (the agent) runs until a stop condition is met.
Our agent is a simple one. In pseudo-code it looks like this:
while:
- Use AI to generate SQL query based on user input and using the database schema
- Execute the generated SQL query on the BigQuery database
- Validate, if there were any errors, if yes, use AI to correct the query
- Optional: If no errors, use AI to validate, if the results answer the user query. If not, repeat
- Stop if the user query is answered
Let's implement this in Python. First, we create a function that generates the query. We use OpenAI function calling for that, because it returns robust JSON output that is easy to parse.
NOTE: In many experiments, AI models tend to be less intelligent when using function calling. This has not been a problem for our SQL generation so far, but keep it in mind. If your AI model keeps producing bad queries, try the "normal" chat endpoint and parse the output yourself.
This function runs the first query generation attempt. We execute the resulting query against BigQuery and check for errors. If there are any, the AI corrects the query.
Let's create that error handling function next. It is just another LLM query generation, but this time we also send the error message to the model so it can correct the query.
We only change the system prompt to include the error and ask the model to correct it. Otherwise, it is the same request and function as before.
Finally, bring it all together in the agent loop:
Now run the agent with a question:
The agent generates the SQL query, executes it, fixes errors on its own and returns the correct result.
Now something more complicated: the percentage of male and female customers among those who churned.
Output:
Not bad for a few dozen lines of Python.
Improving the BigQuery SQL agent
The agent is already fun to play with, but it produces a fair number of garbage queries. It is surprisingly good at fixing these errors on its own, but two methods reduce the number of failed attempts.
-
Add descriptions to the database columns. So far we only give the AI model the table name and the column names as context. The model then has to "guess" which columns are relevant for the user's question. This is error-prone, and column descriptions improve it.
There are 2 ways to do this: a. Manually add a description to each column. This is a little tedious, but it is the most accurate and reliable way to give the model context. b. Send a
head(10)of each column to GPT-4o and ask for a description. The model will most likely come up with a reasonable description.Both methods are simple, so we leave the implementation to the reader. For this post, I used GPT-4o to rewrite our schema as follows:
-
Use RAG to first find potentially relevant tables and columns, which reduces the search space for the AI model.
This works because most user queries relate to some of the available columns. Searching for relevant columns and tables before asking the AI to write a query can drastically improve the quality of the generated SQL.
Using RAG to find relevant columns for text-to-SQL
To show what a RAG-assisted text-to-SQL system looks like, let's extend the agent we just built.
The RAG concept is always the same: take a set of texts (here, column descriptions), create a vector index for them, and use that index to find the texts that are semantically closest to the user query.
Specifically, we create an index on our column descriptions and store the
other keys (table_name, column_name and column_type) as metadata.
We use ChromaDB as the vector store in this example, but any other vector store works as well. (BigQuery itself can also store embeddings and run vector search, see RAG, embeddings and vector search with BigQuery.)
Note: ChromaDB has a handy feature
where you pass only the "document" (the text you want to index) to
collection.add(), without any embeddings. ChromaDB then creates the
embeddings for you. We create the embeddings manually in this example
so the code is easier to port to other vector stores. If you use ChromaDB
anyway, feel free to use this feature.
With the embeddings in place, let's create a function that retrieves the most relevant columns.
query_chromadb finds the 10 columns most relevant to the user query.
Adjust this number as needed.
ChromaDB has no "threshold" parameter, so we filter the results manually to keep only columns that are reasonably relevant.
NOTE: If your vector store provides a threshold feature, you can skip the manual filtering step.
Now let's use this function in our agent:
The only change is the call to query_chromadb before create_query.
Instead of the whole table schema, the model only gets the subset of columns
that might be relevant for the question.
Let's try it once more:
The agent with RAG still returns the correct result.
Conclusion
We built a simple AI agent that translates natural language questions into SQL, executes them against a BigQuery database and handles errors automatically. It uses OpenAI's GPT-4o for SQL generation, Google BigQuery for storage and querying, and Retrieval Augmented Generation (RAG) to pick the relevant columns.
Two measures improve the accuracy of the generated SQL:
- Adding descriptions to database columns
- Using RAG to find relevant tables and columns before query generation
A further step is a semantic layer that maps business terms to tables, join paths and SQL expressions. We tested a tool that generates such a layer automatically in Text-to-SQL with a semantic layer: NVIDIA Auto Ontology tested.
Because the implementation is custom, you can tune the prompts and the error handling for BigQuery and your particular use case, which is the main reason we didn't use LangChain or LlamaIndex.
We think natural language interfaces like this can change how organizations work with their data. People without SQL skills can get answers from complex databases without relying on the data team, and data professionals can spend more time on analysis and less on data retrieval.
The system is not infallible, though. Users should always verify critical results, especially for complex queries or when they base important business decisions on the output.
NOTE: If you need help with implementing such a system for your organization, feel free to reach out to us - we are happy to help!
Further reading
- Using AI directly from your PostgreSQL database
- How to test your RAG pipeline?
- How to host your own LLM - including HTTPS?
- Creating an MCP server with FastMCP to expose your own database tools
- Building knowledge graphs for RAG with the Neo4j LLM Knowledge Graph Builder
- What is the Google Vertex AI Agent Builder?
Interested in building high-quality AI agent systems?
We prepared a comprehensive guide based on cutting-edge research for how to build robust, reliable AI agent systems that actually work in production. This guide covers:
- Understanding the 14 systematic failure modes in multi-agent systems
- Evidence-based best practices for agent design
- Structured communication protocols and verification mechanisms



