Semantic search inside BigQuery: embeddings, VECTOR_SEARCH and when to add an index

BigQuery vector search and embeddings, in plain words

An embedding is a long list of numbers that captures what a piece of text (or an image) means, so two passages about the same idea land close together even when they share no words. BigQuery can create embeddings for a whole column in one SQL statement: you register an embedding model from Gemini Enterprise Agent Platform (formerly Vertex AI), such as text-embedding-005, as a remote model, then call AI.GENERATE_EMBEDDING. Newer helpers include AI.EMBED for one value at a time and autonomous embedding generation, which keeps an embedding column in sync as the source text changes.

To search, VECTOR_SEARCH takes a table of stored embeddings plus one or more query embeddings and returns the top_k nearest rows with their distance. Without an index it does brute force: it compares the query with every row, which is exact but scans the whole column. A vector index (IVF, or TreeAH built on Google's ScaNN algorithm) switches to approximate nearest-neighbor search, trading a little recall for speed on big tables. BigQuery only populates an index once the table is at least 10 MB; below that, searches quietly stay brute force.

The professional decision is where vectors should live. BigQuery is the natural home when the text already sits in your warehouse and searches are analytical or batch, like de-duplicating tickets or finding related products for a nightly job. A dedicated engine such as Agent Platform's Vector Search runs on always-on serving machines for low-latency lookups behind an app, and an operational database with vector support suits search that must change with every transaction. Recent news: on October 21, 2026, BigQuery made AI.CHUNK_DOCUMENT generally available for splitting documents into chunks before embedding, and on October 5 gemini-embedding-2 became generally available for its embedding functions.

The AI angle

This is where BigQuery meets Gemini Enterprise Agent Platform: the embedding model runs on Agent Platform and BigQuery calls it through a remote model, either with your own credentials or through a Cloud resource connection whose service account holds the Agent Platform User role (roles/aiplatform.user; the role ID kept its old aiplatform name). The same pattern feeds RAG: VECTOR_SEARCH finds the passages and AI.GENERATE_TEXT with a Gemini remote model writes an answer from them, all in one query.

Hands-on lab: Build a tiny semantic FAQ search in BigQuery with an explicit connection

Create a dataset and a ten-row FAQ table, connect BigQuery to an Agent Platform embedding model through a Cloud resource connection with one narrowly scoped IAM grant, embed the rows, run brute-force VECTOR_SEARCH for a question that shares no keywords with its answer, then remove every resource and the IAM binding.

1. Open Cloud Shell, set variables, enable APIs and create a dataset

   Choose Activate Cloud Shell in the console. The dataset and connection both live in the US multi-region, because a remote model must sit in the same location as its connection. If Cloud Shell reconnects later, run the variable lines again.

   gcloud config set project YOUR_PROJECT_ID
   PROJECT_ID=YOUR_PROJECT_ID
   DS=vs_lab
   CONN=vs_lab_conn
   gcloud services enable bigquery.googleapis.com bigqueryconnection.googleapis.com aiplatform.googleapis.com
   bq mk --dataset --location=US $DS

   Done

2. Create a Cloud resource connection and grant its service account one role

   The connection gets its own Google-managed service account. Run bq show, copy the serviceAccountId value from the output into SA, then grant that account only Agent Platform User, so BigQuery can call the embedding model and nothing else. IAM changes can take a minute or two to apply.

   bq mk --connection --location=US --project_id=$PROJECT_ID \
     --connection_type=CLOUD_RESOURCE $CONN
   bq show --connection $PROJECT_ID.us.$CONN
   SA=PASTE_THE_serviceAccountId_HERE
   gcloud projects add-iam-policy-binding $PROJECT_ID \
     --member="serviceAccount:$SA" --role="roles/aiplatform.user" --condition=None

   Done

3. Load a ten-row FAQ table and register the embedding model

   The SQL goes into small files and is passed to bq query, which avoids quoting trouble in the shell. The CREATE MODEL statement stores no model inside BigQuery; it just records which Agent Platform endpoint (text-embedding-005) to call through your connection. The backticks are escaped because this heredoc expands $PROJECT_ID.

   cat > faq.sql <<'SQL'
   CREATE OR REPLACE TABLE vs_lab.faq AS
   SELECT * FROM UNNEST([
     STRUCT(1 AS id, "How do I reset my password if I forgot it?" AS content),
     (2, "Where can I download last month's invoice?"),
     (3, "I was billed two times for the same order."),
     (4, "How long does standard shipping take?"),
     (5, "Can I change the delivery address after ordering?"),
     (6, "My parcel arrived damaged."),
     (7, "How do I turn on two-step verification?"),
     (8, "Do you ship to Canada and Mexico?"),
     (9, "How do I cancel my subscription?"),
     (10, "The app crashes when I open the camera.")
   ]);
   SQL
   bq query --use_legacy_sql=false "$(cat faq.sql)"
   cat > model.sql <<SQL
   CREATE OR REPLACE MODEL vs_lab.embed_model
     REMOTE WITH CONNECTION \`$PROJECT_ID.us.$CONN\`
     OPTIONS (ENDPOINT = 'text-embedding-005');
   SQL
   bq query --use_legacy_sql=false "$(cat model.sql)"

   Done

4. Embed every row with AI.GENERATE_EMBEDDING

   The function reads a column that must be called content and returns it with an embedding array and a status string (empty when the row succeeded). The second query checks that all ten rows came back with a vector and shows how many numbers each one has. If you see a permission error, the IAM grant from step 2 is still propagating; wait a minute and run it again.

   cat > embed.sql <<'SQL'
   CREATE OR REPLACE TABLE vs_lab.faq_emb AS
   SELECT * FROM AI.GENERATE_EMBEDDING(
     MODEL vs_lab.embed_model,
     (SELECT id, content FROM vs_lab.faq));
   SQL
   bq query --use_legacy_sql=false "$(cat embed.sql)"
   bq query --use_legacy_sql=false \
     'SELECT COUNT(*) AS rows_ok, MAX(ARRAY_LENGTH(embedding)) AS dims FROM vs_lab.faq_emb WHERE LENGTH(status) = 0'

   Done

5. Search by meaning with brute-force VECTOR_SEARCH

   The question shares almost no words with FAQ 3, yet it should rank first. use_brute_force forces an exact scan; on a ten-row table BigQuery would do that anyway, because an index is only populated once the table reaches 10 MB. Smaller distance means closer. Try your own wording by editing search.sql.

   cat > search.sql <<'SQL'
   SELECT query.query, base.id, base.content, distance
   FROM VECTOR_SEARCH(
     TABLE vs_lab.faq_emb, 'embedding',
     (SELECT embedding, content AS query
      FROM AI.GENERATE_EMBEDDING(
        MODEL vs_lab.embed_model,
        (SELECT 'my card got charged twice' AS content))),
     top_k => 3,
     distance_type => 'COSINE',
     options => '{"use_brute_force":true}');
   SQL
   bq query --use_legacy_sql=false "$(cat search.sql)"

   Done

bq rm -r -f -d $PROJECT_ID:$DS
gcloud projects remove-iam-policy-binding $PROJECT_ID \
  --member="serviceAccount:$SA" --role="roles/aiplatform.user" --condition=None
bq rm --connection --location=us $CONN
rm -f faq.sql model.sql embed.sql search.sql
# confirm nothing is left
bq ls
bq ls --connection --project_id=$PROJECT_ID --location=US
gcloud projects get-iam-policy $PROJECT_ID \
  --flatten="bindings[].members" --filter="bindings.members:$SA" --format="value(bindings.role)"
# optional, sandbox only:
# gcloud services disable bigqueryconnection.googleapis.com
# optional, practice-only project:
# gcloud projects delete YOUR_PROJECT_ID

Gotchas

• The remote model, its connection and the dataset must share one location. A dataset in US cannot use a connection created in us-central1, and the embedding endpoint must be offered in that location too.
• Embeddings from different models (or different versions of one model) can't be compared. Embed the stored rows and the query with the same model, and re-embed everything when you switch to something like gemini-embedding-2.
• Vector indexes only kick in on tables of 10 MB or more and aren't supported in the Standard edition, so a demo-sized table is always brute force. Check INFORMATION_SCHEMA.VECTOR_INDEXES for status and coverage before trusting latency numbers.
• With on-demand pricing, every search is billed on bytes scanned from the base table, the index and the query. Store only the columns you need in the index, and filter on partitioned or stored columns so pre-filters prune instead of scanning the whole table.
• Using the DEFAULT connection is convenient, but BigQuery grants roles to its service account automatically, and anyone allowed to use that connection inherits them. An explicit connection with one documented grant is easier to audit.