Getting Started with Vector Search in BigQuery

Vector search in BigQuery lets you find rows by meaning rather than by exact match: the support tickets most similar to this one, the products closest to a free-text description, the document chunks that best answer a question. The pieces are all SQL — ML.GENERATE_EMBEDDING to turn text into vectors, CREATE VECTOR INDEX to make search fast, and VECTOR_SEARCH to query — and the data never leaves the warehouse. This tutorial builds a working semantic search over a public dataset and finishes with a minimal retrieval-augmented generation (RAG) query.

Prerequisites

• A Google Cloud project with BigQuery and Vertex AI APIs enabled
• A BigQuery connection of type Cloud Resource so BigQuery can call Vertex AI models (create it in BigQuery Studio under Add data → Connections to external data sources, then grant the connection's service account the Vertex AI User role)
• A dataset in the same region as the connection — the examples use US

Step 1: Create a remote embedding model

CREATE OR REPLACE MODEL `demo.text_embedder`
REMOTE WITH CONNECTION `us.vertex-ai`
OPTIONS (ENDPOINT = 'gemini-embedding-001');

gemini-embedding-001 is the current Vertex AI text-embedding endpoint as of this writing; check the embedding documentation for the model names supported in your region. The model object is a pointer — no data is copied.

Step 2: Pick a corpus and generate embeddings

We will use the public Stack Overflow dataset, limited to a manageable slice so the demo costs cents rather than dollars.

CREATE OR REPLACE TABLE `demo.so_questions` AS
SELECT id, title, body, tags, creation_date
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags LIKE '%google-bigquery%'
  AND creation_date >= '2022-01-01'
  AND LENGTH(body) < 4000;

Now embed the title and body together:

CREATE OR REPLACE TABLE `demo.so_embeddings` AS
SELECT
  id,
  title,
  content,
  ml_generate_embedding_result AS embedding
FROM ML.GENERATE_EMBEDDING(
  MODEL `demo.text_embedder`,
  (
    SELECT id, title, CONCAT(title, '\n\n', body) AS content
    FROM `demo.so_questions`
  ),
  STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_DOCUMENT' AS task_type)
);

The task_type hint matters: embed documents with RETRIEVAL_DOCUMENT and queries with RETRIEVAL_QUERY so the model optimises both sides of the match. ml_generate_embedding_status is also returned; filter for empty status in production to drop rows the model rejected.

Embedding calls are billed by Vertex AI per input token, separately from BigQuery. Embed once and store the result — never recompute embeddings inside a query.

Step 3: Query without an index (brute force)

VECTOR_SEARCH works with no index at all, computing exact distances across the whole table. That is fine up to a few hundred thousand rows and is the right way to validate results before indexing.

SELECT
  base.id,
  base.title,
  distance
FROM VECTOR_SEARCH(
  TABLE `demo.so_embeddings`,
  'embedding',
  (
    SELECT ml_generate_embedding_result AS embedding
    FROM ML.GENERATE_EMBEDDING(
      MODEL `demo.text_embedder`,
      (SELECT 'why is my partitioned table still scanning every partition' AS content),
      STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_QUERY' AS task_type)
    )
  ),
  top_k => 5,
  distance_type => 'COSINE'
);

You should see questions about partition pruning and require_partition_filter rank at the top even though none of them share the exact wording of the query.

Step 4: Create a vector index

Once the corpus grows past a few hundred thousand rows — or the query needs sub-second latency — add an index. Two index types are available:

• IVF (inverted file) clusters vectors with k-means and searches only the nearest clusters. Good general default; tune num_lists and, at query time, fraction_lists_to_search to trade recall for speed.
• TreeAH (tree + asymmetric hashing, Google's ScaNN algorithm) is optimised for large batch queries — many query vectors at once — and typically costs less per batched search on big tables.

CREATE OR REPLACE VECTOR INDEX so_ivf
ON `demo.so_embeddings` (embedding)
OPTIONS (
  index_type = 'IVF',
  distance_type = 'COSINE',
  ivf_options = '{"num_lists": 100}'
);

Index builds are asynchronous and free. Check coverage before relying on it:

SELECT table_name, index_name, index_status, coverage_percentage, last_refresh_time
FROM `demo`.INFORMATION_SCHEMA.VECTOR_INDEXES;

A query with VECTOR_SEARCH uses the index automatically once coverage is high; pass options => '{"use_brute_force": true}' when you want to compare results against exact search. Note the table must have at least 5,000 rows to create an index.

Step 5: A minimal RAG query

Retrieval-augmented generation is just vector search feeding a text-generation call. Create a Gemini remote model, then chain the two:

CREATE OR REPLACE MODEL `demo.gemini`
REMOTE WITH CONNECTION `us.vertex-ai`
OPTIONS (ENDPOINT = 'gemini-2.5-flash');

WITH question AS (
  SELECT 'How do I stop a query from scanning every partition?' AS q
),
hits AS (
  SELECT base.title, base.content
  FROM VECTOR_SEARCH(
    TABLE `demo.so_embeddings`, 'embedding',
    (SELECT ml_generate_embedding_result AS embedding
     FROM ML.GENERATE_EMBEDDING(MODEL `demo.text_embedder`,
       (SELECT q AS content FROM question),
       STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_QUERY' AS task_type))),
    top_k => 3, distance_type => 'COSINE')
)
SELECT ml_generate_text_llm_result AS answer
FROM ML.GENERATE_TEXT(
  MODEL `demo.gemini`,
  (
    SELECT CONCAT(
      'Answer the question using only the context below.\n\nContext:\n',
      STRING_AGG(CONCAT('- ', title, ': ', SUBSTR(content, 1, 1500)), '\n'),
      '\n\nQuestion: ', (SELECT q FROM question)
    ) AS prompt
    FROM hits
  ),
  STRUCT(TRUE AS flatten_json_output, 0.2 AS temperature)
);

Every governance control on demo.so_embeddings — row-level security, column masking, audit logs — applies to the retrieval step, which is the main argument for keeping RAG inside the warehouse rather than exporting data to a standalone vector store.

Cost and latency notes

• Embedding generation is the dominant cost; it scales with tokens, not rows. Chunk long documents to roughly 500–1,000 tokens rather than embedding whole files.
• Brute-force VECTOR_SEARCH scans the full embedding column; a 768-dimension FLOAT64 vector is about 6 KB per row, so a 10-million-row table costs ~60 GB per query on-demand. Index it.
• Indexed queries on IVF return in roughly one to a few seconds for tables in the tens of millions; TreeAH is the better choice when you are searching with thousands of query vectors per call.
• Store embeddings in their own table keyed by source ID so you can re-embed on a model upgrade without rewriting the source.

Next steps

Hybrid search (combine SEARCH() keyword matches with vector similarity), metadata filters inside VECTOR_SEARCH via a pre-filtered subquery, and scheduled incremental embedding of new rows are the usual follow-ons. If you are building a production RAG system on governed data, our AI and Gemini services cover the full pipeline; contact us to discuss.