RAG, Embeddings and Vector Search with Google BigQuery

On this page
Google BigQuery supports vector search natively. You store embeddings as a
simple array of floats, create them with ML.GENERATE_EMBEDDING through a
remote Vertex AI embedding model, and find similar texts with the
VECTOR_SEARCH function. Add a remote Gemini model and ML.GENERATE_TEXT,
and you get a complete Retrieval Augmented Generation (RAG) pipeline in a
single SQL statement, with no infrastructure outside BigQuery.
This post walks through every step: loading sample text into BigQuery, connecting BigQuery to Vertex AI models, creating embeddings, querying them, adding a vector index, running RAG, and what it all costs.
Update (November 2026): Google retired gemini-1.5-flash-001 on May 24,
2025 and recommends gemini-2.5-flash-lite as the upgrade; text-embedding-004
retires on April 1, 2027 (see Vertex AI model
versions).
Gemini 2.0 and later models are billed per token rather than per 1,000
characters, see Vertex AI pricing.
TreeAH indexes now support pre-filtering on stored columns, see vector
indexes.
Why vector search in BigQuery?
Google BigQuery is an excellent Data Warehouse. With its serverless architecture and Google's scaling capabilities, it's a great choice for storing and querying large datasets.
At the same time, vector databases have been all the rage, because you need vectors to implement semantic search. Our take: vector databases are great, but you only need a specialized database for vectors if you don't have a database at all. If you already use a database, use its vector extension. Almost every database has one. Postgres has pgvector, CrateDB has vector support, so does Elasticsearch, and even DuckDB has them.
Google added vector support to BigQuery as well. Below, we show the vector data type, how to create vectors from your texts and how to query them.
BigQuery can also call AI models directly from the database, without additional infrastructure. That's very similar to what Timescale built with their pgai extension for postgres. We'll use this feature for both embeddings and text generation.
Preparation: install the required dependencies
Preparation: creating a BigQuery table with sample text data
First, let's create a dataset for this demo.
-
Download our demo PDF document from our website
-
Use LangChain to extract the text from the PDF and split it into smaller chunks of about 1500 characters each. Each chunk becomes a row in BigQuery.
-
Next, open the Google BigQuery console in your browser and create an empty dataset and table.
Create a new dataset
Give your dataset a name, select the region you want to deploy the dataset to and click "Create Dataset".
-
For now, we only add a timestamp and the text. The embeddings come later. To prepare the upload, we reformat our documents and save them as CSV.
-
In the new BigQuery dataset, add a new table:
Create a new table
For the table, select "Upload" in the "Create table from" field, give it a name, and add the following columns in the "Schema" section:
- timestamp:
TIMESTAMP - text:
STRING
Create table form with CSV upload and timestamp and text columns
In the "Select file" section, upload the CSV file you created in the previous step.
Optionally, set partitioning and clustering as for any other BigQuery table.
In the "Advanced options" section, make sure to select "Quoted newlines". Otherwise, line breaks in your text will break the import.
Table advanced options
Click "Create Table" to finish. BigQuery uploads the CSV file and inserts the data into the table.
Note: The BigQuery CSV upload is quite sensitive. Make sure your CSV file has no headers and uses the "quote all" option as shown above.
Open the "Preview" tab of the table to check whether the import was successful.
Preview imported data
- timestamp:
-
With the data in BigQuery, let's add an empty column for the embeddings.
That's the important part: embeddings in BigQuery are simply stored as an array of floats. This makes them quite versatile, as you are not limited to a specific vector size.
In the "Schema" tab, click "Edit Schema" and add a "REPEATED" "FLOAT" column called "embeddings", as shown below:
Add embeddings column
Preparation: connecting BigQuery to remote Vertex AI models
Now that we have data, we want to create embeddings.
You could use any embedding model, for example the OpenAI
embedding models
(see our post on OpenAI's v3 embeddings),
to create the vectors and insert them into the embeddings column.
Instead, we use a more convenient method: BigQuery
ML lets us
call an embedding model right from the BigQuery console, using plain SQL.
BigQuery currently supports the following embedding models:
- The Google Generative AI embedding models
- Pretrained TensorFlow models NNLM, SWIVEL and BERT
In most cases, the LLM-based models give better quality and are easier to use, so we stick with them.
-
In your BigQuery console, click the "+Add" button on the top left. Then click "Connections to external data sources".
Create external connection
-
On the next screen, select "Vertex AI remote models, remote functions and BigLake" as "Connection type". Set a connection id, select your region and click "Create Connection".
Create connection to a remote model
-
In the grey popup, click "Go to connection". Note down the service account id for this connection.
Connection service account id
-
Next, give this service account the permissions it needs. Go to the IAM page and click "GRANT ACCESS". On the next screen, add the service account id you noted down in the "New principals" field and select "Vertex AI User" as the "Role". Click "Save".
Grant Permissions to service account
-
Finally, run the following query in your BigQuery console:
Change the following names to match your configuration:
-
blog_bg_vector: Name of the dataset (not table!) -
eu: Name of the location of your external data source connection -
blog-bq-external-model: Name of the external data source connection
Note:
text-embedding-004is the name of the Google embedding model to use.After running this query, the model is ready to use. It appears as a sub-object under your BigQuery dataset:
Embedding Model in BigQuery console
-
Creating embeddings in BigQuery with ML.GENERATE_EMBEDDING
With the embedding model in place, let's add embeddings to the text data we imported. All of this happens inside the BigQuery console.
BigQuery ML lets you interact with models using only SQL. We want to
UPDATE the embeddings column with our text embeddings, so we write an
UPDATE statement that does exactly that:
Note: ml_generate_embedding_status contains errors if there are any.
An empty ml_generate_embedding_status means no errors. That's why we
check for LENGTH(ml_generate_embedding_status)=0
After running the query, check whether the embeddings were updated as planned in the "Preview" pane of your table.
Preview of created embeddings
Querying embeddings with BigQuery VECTOR_SEARCH
Next, let's use vector similarity search to find texts that are semantically similar to a search query.
BigQuery provides the VECTOR_SEARCH
function for this.
Say we want to:
- search for "How to maintain the machine"
- get the 4 most similar docs
- use cosine similarity
Our query looks as follows:
You can pre-filter your data by adding a WHERE clause to the very first function parameter.
The query returns the embedding of the search query, the distance to each
of the matching rows and every column included in the SELECT statement
of the first parameter. So you get not only the distance and text of the
rows, but also any metadata that comes with them.
Results of vector search
Creating a BigQuery vector index (IVF or TreeAH)
The retrieval query above didn't use an index, which can be slow in production.
BigQuery offers two types of indexes :
-
IVF index: an inverted file index. It uses a k-means algorithm to cluster the vector data, and then partitions the vector data based on those clusters.
-
TreeAH index: a vector index that uses Google's ScaNN algorithm.
ScaNN is one of the fastest and most efficient search algorithms out there. But it has one significant drawback: at the time of writing, TreeAH indexes don't support pre-filtering. If you want to filter your data with a WHERE clause before running the vector search, you are out of luck.
We suggest using IVF indexes for use cases below 10 million rows, and TreeAH indexes for larger datasets.
Creating an index is, once again, just one SQL query:
The num_lists parameter defines how many lists the IVF algorithm
creates. IVF divides the whole data space into num_lists lists, and data
points that are close to each other are more likely to end up in the same
list. A small value gives you fewer lists with more data points each, a
larger value more lists with fewer data points.
As a rule of thumb: if you have many vectors that are rather
similar to each other, you have many larger lists. Set num_lists to a
smaller value. If you have many vectors that are rather different from
each other, you have many smaller lists. Set num_lists to a larger value.
Creating an index can take some time. You can check its progress with, you guessed it, another SQL statement:
One caveat: BigQuery only populates a vector index on tables of at least 10 MB. For small tables, brute-force search is fast enough, and vector search falls back to it automatically. If your table is smaller than that, don't bother with an index.
If your table is larger, do create an index, as it speeds up your queries significantly.
How do you use the index at query time? You don't have to do anything: BigQuery uses the index automatically if it exists.
Retrieval augmented generation with BigQuery
So far, we've embedded our text data, retrieved it with vector similarity search and added an index to make retrieval fast.
That's almost everything we need for a simple but quite powerful Retrieval Augmented Generation (RAG) process that runs entirely on BigQuery, without any additional infrastructure for the indexing and retrieval part of RAG.
Creating a remote Gemini text generation model
The only missing part of the RAG pipeline is the "Generation" part: a text generation LLM reads the search results and answers the question based on them.
Just as we created a remote embedding model, let's create a remote text generation model.
Change blog_bg_vector to your dataset name, and
eu.blog-bq-external-model to your location and external connection id,
as in the sections above.
The ENDPOINT option defines which model to use. When we wrote this post,
we were big fans of Gemini 1.5 flash
because of its very attractive pricing, extremely fast inference and
overall quite good quality.
After the query finishes, you have a new remote model named
blog_bg_vector.blog_bq_text_model.
Putting it all together: a full RAG pipeline in one BigQuery SQL statement
Now we have everything we need. To recap, a RAG query:
- Retrieves texts that are similar to our search query
- Sends these texts as context to our LLM
- Lets the LLM give a final answer, based on this context and the search query
All three steps fit into a single SQL statement:
The query is a little more complex, so let's break it down, following the numbered comments in the SQL statement. Read it from the inside out: the innermost parts run first.
-
We retrieve similar vectors using the embedding model, exactly as in the sections above. After this step, we have 5 documents that are semantically similar to our user query,
How to maintain the machine?.We set the following options:
-
top_k: the number of similar documents to retrieve, ordered by similarity. -
distance_type: the distance metric for the vector search. -
fraction_lists_to_search: a number between 0 and 1 that defines the share of index lists to search. A higher value gives higher recall and slower performance, and vice versa.
-
-
Next, we build a prompt from the retrieved documents. For that, we use the BigQuery SQL functions STRING_AGG, which combines the string values of all returned rows and separates them by
\n, and CONCAT, which concatenates strings. -
Finally, the
ML.GENERATE_TEXTfunction sends the prompt to the LLM. The answer comes back asml_generate_text_llm_result, which we rename togenerated. The query also returns the prompt, which helps when you debug the model's answer.We set the following options:
-
max_output_tokens: how many answer tokens the LLM may produce. Important for cost control. -
flatten_json_output: if set to true, returns plain text extracted from the LLM response. We recommend setting it to true.
-
After running the query, we get these results:
BigQuery RAG results
To be honest, this looks very promising. The SQL query looks a bit convoluted, but it implements a basic RAG pipeline in about 30 lines of code, without any additional infrastructure. Before you rely on it, measure the answer quality, for example with the approach from our post on evaluating RAG pipelines with Ragas.
BigQuery vector search and RAG pricing
The final price has several components:
-
The good news: index creation costs nothing extra. You only pay for index storage, which is billed by bytes stored at the normal BigQuery storage rates
-
You pay for every BigQuery query, at the normal BigQuery analytics pricing
-
You pay for each AI invocation. The AI models are priced as follows:
-
Embedding model: Google Vertex AI pricing 0.000025$ per 1000 characters
-
Gemini 1.5 Flash Text generation model: Google Vertex AI pricing 0.00001875$ per 1000 characters
-
So what's the real cost? As always with BigQuery, it's hard to tell upfront. The best approach is to check the current storage costs in the "Details" tab of your BigQuery table, and use the query run estimate in the BigQuery console for analytics costs.
BigQuery analytics cost estimate
Note: Calculating actual BigQuery costs is always a hassle, but the vector search pricing and the AI model pricing are highly competitive. In our experience, the total costs are remarkably low.
Conclusion
With vector support, BigQuery covers the whole retrieval side of a RAG
application: embeddings live in a float array column, ML.GENERATE_EMBEDDING
creates them through a remote Vertex AI model, VECTOR_SEARCH finds similar
rows (with an IVF or TreeAH index once your table passes 10 MB), and
ML.GENERATE_TEXT with Gemini generates the answer. All of it is plain SQL,
so you don't need a separate vector database or extra infrastructure.
Costs consist of normal BigQuery storage and query pricing plus a small per-character fee for the AI models, and in our experience they stay low. If your data already lives in BigQuery, it's a solid place to start with AI applications. For other ways to put AI on top of BigQuery, see how to query BigQuery with natural language or how to connect it to AI agents with an MCP server for BigQuery.
Further reading
- Using AI directly from your PostgreSQL database
- Create knowledge graphs using Neo4j LLM knowledge graph builder
- Use GPT-4o to parse pdf, docx and xlsx
- Vertex AI Agent Builder: no-code RAG search on Google Cloud
- Deploying open models with the Vertex AI Model Garden
- The Azure alternative: RAG with Azure AI Search
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



