RAG, Embeddings and Vector Search with Google BigQuery

Andreas Nigg
Andreas Nigg · Co-Founder, Pondhouse Data
Published Updated 12 min read
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

1pip install google-cloud-bigquery langchain==0.2 langchain-community==0.2 langchain-text-splitters==0.2 pypdf

Preparation: creating a BigQuery table with sample text data

First, let's create a dataset for this demo.

  1. Download our demo PDF document from our website

  2. 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.

    1from langchain_community.document_loaders import PyPDFLoader
    2from langchain.text_splitter import RecursiveCharacterTextSplitter
    3loader = PyPDFLoader("public/demo-docs/HypotheticalMachine.pdf")
    4text_splitter = RecursiveCharacterTextSplitter(
    5 chunk_size=1500,
    6 chunk_overlap=200,
    7 )
    8documents = loader.load_and_split(text_splitter)
  3. 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".

  4. 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.

    1 import csv
    2 from datetime import datetime
    3 import os
    4 current_timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    5
    6 content = [doc.page_content for doc in documents]
    7
    8 with open("internalonly__blog_bq_test.csv", 'w', newline='', encoding='utf-8') as csvfile:
    9 csv_writer = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
    10 for string in content:
    11 csv_writer.writerow([current_timestamp, string])
  5. 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

  6. 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:

In most cases, the LLM-based models give better quality and are easier to use, so we stick with them.

  1. In your BigQuery console, click the "+Add" button on the top left. Then click "Connections to external data sources".

    Create external connection

  2. 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

  3. In the grey popup, click "Go to connection". Note down the service account id for this connection.

    Connection service account id

  4. 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

  5. Finally, run the following query in your BigQuery console:

    1CREATE OR REPLACE MODEL `blog_bg_vector.embedding_model`
    2REMOTE WITH CONNECTION `eu.blog-bq-external-model`
    3OPTIONS (ENDPOINT = 'text-embedding-004');

    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-004 is 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:

1-- Update our table. Change dataset.table according to your configuration
2UPDATE `blog_bg_vector.blog_bq_embeddings` t
3-- We just want to change the embeddings column
4SET t.embeddings = e.ml_generate_embedding_result
5FROM (
6
7 -- Call the BigQuery ML embedding generation
8 SELECT
9 -- ml_generate_embedding_result is the actual array of floats as result
10 -- of the embedding process
11 ml_generate_embedding_result,
12 -- we also get the actual text content back, so we can use it for
13 -- updating our table.
14 content
15 FROM ML.GENERATE_EMBEDDING(
16 -- select the previously created model, by dataset.model_name
17 MODEL `blog_bg_vector.embedding_model`,
18 -- We need to pass the text column from our original table
19 (
20 SELECT
21 -- GENERATE_EMBEDDING requires the actual text column to be called 'content'
22 text as content
23 FROM `blog_bg_vector.blog_bq_embeddings`
24 -- we only want to update embeddings for rows which don't have embeddings already
25 WHERE LENGTH(text) > 0 AND ARRAY_LENGTH(embeddings) = 0
26 )
27 )
28 WHERE LENGTH(ml_generate_embedding_status) = 0
29) e
30WHERE t.text = e.content;

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

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.

1VECTOR_SEARCH(
2 {TABLE base_table | base_table_query_statement},
3 column_to_search,
4 TABLE query_table
5 [, query_column_to_search => query_column_to_search_value]
6 [, top_k => top_k_value ]
7 [, distance_type => distance_type_value ]
8 [, options => options_value ]
9)

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:

1SELECT *
2FROM VECTOR_SEARCH(
3 (SELECT * from `blog_bg_vector.blog_bq_embeddings`
4 -- You can pre-filter your query here, eg. for rows of specific users
5 -- WHERE some-clause
6 ),
7 'embeddings',
8 (SELECT ml_generate_embedding_result, content AS query
9 FROM ML.GENERATE_EMBEDDING(
10 MODEL `blog_bg_vector.embedding_model`,
11 (SELECT 'How to maintain the machine' AS content))
12 ),
13 top_k => 4,
14 distance_type => 'COSINE')

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:

1CREATE VECTOR INDEX blog_bq_vector_index ON
2
3-- dataset.table(column_with_embeddings)
4blog_bg_vector.blog_bq_embeddings(embeddings)
5
6OPTIONS(distance_type='COSINE', index_type='IVF', ivf_options='{"num_lists": 100}');

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:

1SELECT table_name, index_name, index_status,
2coverage_percentage, last_refresh_time, disable_reason
3-- project_id.dataset.INFORMATION_SCHEMA.VECTOR_INDEXES
4FROM `<your-project-id>.blog_bg_vector.INFORMATION_SCHEMA.VECTOR_INDEXES`

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.

1CREATE OR REPLACE MODEL `blog_bg_vector.blog_bq_text_model`
2 REMOTE WITH CONNECTION `eu.blog-bq-external-model`
3 OPTIONS (ENDPOINT = 'gemini-1.5-flash-001');

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:

  1. Retrieves texts that are similar to our search query
  2. Sends these texts as context to our LLM
  3. Lets the LLM give a final answer, based on this context and the search query

All three steps fit into a single SQL statement:

1-- 3. Invoke the LLM and return the generated text as well as the prompt for further inspection
2SELECT ml_generate_text_llm_result AS generated, prompt
3FROM ML.GENERATE_TEXT(
4 MODEL `blog_bg_vector.blog_bq_text_model`,
5 (
6 -- 2. Create a prompt for the LLM
7 SELECT CONCAT(
8 'You get a user question as well as some context to answer this question. Please provide an answer from the provided context',
9 'Only use the data from the context to answer. If you dont know the answer, answer with "I dont know"',
10 STRING_AGG(
11 FORMAT("Context: %s", base.text),
12 ',\n'),
13 FORMAT("User question: %s", max(query.query))
14 ) AS prompt,
15 FROM VECTOR_SEARCH(
16 (SELECT * from `blog_bg_vector.blog_bq_embeddings`
17 -- You can pre-filter your query here, eg. for rows of specific users
18 -- WHERE some-clause
19 ),
20 'embeddings',
21 (
22 -- 1. Retrieve similar vectors, using the embedding model
23 SELECT ml_generate_embedding_result, content AS query
24 FROM ML.GENERATE_EMBEDDING(
25 MODEL `blog_bg_vector.embedding_model`,
26 (SELECT 'How to maintain the machine?' AS content)
27 )
28 ),
29 top_k => 5,
30 distance_type => "COSINE",
31 options => '{"fraction_lists_to_search": 0.01}')
32 ),
33 -- define options for LLM text generation
34 STRUCT(2048 AS max_output_tokens, TRUE AS flatten_json_output));

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.

  1. 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.

  2. 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.

  3. Finally, the ML.GENERATE_TEXT function sends the prompt to the LLM. The answer comes back as ml_generate_text_llm_result, which we rename to generated. 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:

  1. 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

  2. You pay for every BigQuery query, at the normal BigQuery analytics pricing

  3. You pay for each AI invocation. The AI models are priced as follows:

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

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
Andreas Nigg

About the author

Andreas Nigg

Co-Founder, Pondhouse Data

Andreas has spent more than eight years building data applications. At Pondhouse Data he designs and ships AI systems and integrates them into complex, real-world environments.