RAG Without a Vector Database: A Pipeline in BigQuery SQL and Where It Breaks

A vector database, an orchestrator, an embedding service, API keys scattered across all of them, and three containers just to answer questions about your own documents. That's the shape retrieval-augmented generation took in most tutorials, and for a large class of use cases it may no longer be needed.

BigQuery's AI functions let the LLM behave like any other SQL operator, in the way JOIN or WHERE does. Embedding, vector search and structured generation all happen where the data already sits, under the IAM, audit logging and cost controls you already have. It isn't a walled garden around a single vendor's model either. Claude, Llama, Mistral and open-source models from Hugging Face can all be used.

The sections below build the same pipeline in two ways on a public dataset anyone can query, the BBC news corpus at bigquery-public-data.bbc_news.fulltext. The first is a one-shot batch version using generally available features. The second is a living version, in preview, where the corpus keeps itself embedded as new rows arrive. After that comes an honest account of where the approach breaks and Python is still the better answer.

The stack most tutorials assume

A standard RAG setup usually means:

1. Something to produce embeddings: a hosted API, or a model endpoint that you stand up and maintain
2. A vector database, which adds one more vendor, one more bill and one more sync pipeline to keep consistent with the source of truth
3. An orchestrator such as LangChain, LlamaIndex or hand-written glue
4. An LLM API with its own rate limits and quotas
5. Hosting for all of it, commonly a few containers that someone has to package and keep patched

Count the parts: five systems and at least three API keys. Then there's the cost nobody mentions, which is that your data leaves the warehouse. Every governance rule, row-level security policy and audit requirement built into BigQuery stops applying the moment chunks are exported to an outside vector store.

That architecture existed because there was no alternative. The premise has changed.

The LLM as a SQL operator

The AI.* family of functions in BigQuery has reached general availability. Three pieces matter for RAG:

• AI.GENERATE_EMBEDDING creates embeddings in bulk from any table.
• VECTOR_SEARCH does semantic retrieval as a built-in GoogleSQL function. You can add an IVF or TreeAH index, and TreeAH rests on Google's ScaNN algorithm.
• AI.GENERATE is a row-wise scalar function that can return structured output through an output_schema, and it fits anywhere a value does, including SELECT, WHERE and even JOIN conditions.

No key appears in your code. Authentication runs through a BigQuery connection, a managed service account that you grant a role once. A default connection now exists, which removes most of the setup.

You aren't locked into Gemini

A common worry is that this ties you to Gemini. It doesn't, though the details are often misunderstood. The functions come in two kinds.

General-purpose functions such as AI.GENERATE_TEXT, AI.GENERATE_TABLE and AI.GENERATE_EMBEDDING with a MODEL argument give you full control over which model runs. Supported remote models include the Gemini family, plus Anthropic Claude, Meta Llama and Mistral AI as managed endpoints, and open-source models from Hugging Face or the Model Garden on Google's agent platform (formerly Vertex AI). For open models, CREATE MODEL can even deploy the model automatically.

Managed functions (AI.IF, AI.CLASSIFY, AI.SCORE, and scalar AI.GENERATE when given an endpoint) are Gemini-backed shortcuts. BigQuery chooses the model and takes care of the prompting.

Say compliance requires embeddings from a self-hosted multilingual E5 or Qwen model, with Claude producing the answers. Each starts from a single CREATE MODEL statement. For embeddings that is the full change, since nothing downstream moves. Generation carries one caveat.

Path one: a one-shot pipeline (generally available)

This pattern fits batch and analytical work, where a corpus is embedded once or on a schedule and then queried.

Step 0: one-time setup

The connection is the step most warehouse-native tutorials leave out. It's the managed service account through which BigQuery reaches the models, and it's created a single time.

The quick route, if you hold the BigQuery Admin role, is the default connection. Write WITH CONNECTION DEFAULT in the statement and BigQuery provisions it and grants the right roles on first use.

If you'd rather own the connection, which Path two needs as us.my_connection, it takes two commands: create a Cloud resource connection, then give its service account the role named Agent Platform User (roles/aiplatform.user, formerly Vertex AI User).

PROJECT_ID=my-poject-id

# 1. Create a Cloud resource connection (its location must match your dataset)
bq mk --connection --location=US --project_id=$PROJECT_ID \
  --connection_type=CLOUD_RESOURCE my_connection

# 2. Grant the connection's service account the Agent Platform User role
gcloud projects add-iam-policy-binding $PROJECT_ID \
  --member="serviceAccount:$(bq show --format=prettyjson \
    --connection $PROJECT_ID.US.my_connection | jq -r .cloudResource.serviceAccountId)" \
  --role=roles/aiplatform.user

Where you grant the role depends on how the model is referenced: in the project that owns the model when you pass a name, or in the project from the endpoint URL when you pass a full URL. Authentication ends there, with one role and no keys.

The embedding model comes next, reusing the default connection:

CREATE OR REPLACE MODEL `rag_demo_us.emb_model`
  REMOTE WITH CONNECTION DEFAULT
  OPTIONS (ENDPOINT = 'gemini-embedding-001');

That statement is the entire embedding service deployment. There's no container, no endpoint to scale and no key to rotate. If you deployed an open-source model, swap in its endpoint URL.

Step 1: embed the corpus

One statement embeds the BBC articles:

CREATE OR REPLACE TABLE `rag_demo_us.news_emb` AS
SELECT *
FROM AI.GENERATE_EMBEDDING(
  MODEL `rag_demo_us.emb_model`,
  (
    SELECT title, body AS content
    FROM `bigquery-public-data.bbc_news.fulltext`
    WHERE category = 'tech'
  ),
  STRUCT('RETRIEVAL_DOCUMENT' AS task_type)
);

Two details are easy to miss. The input query has to expose a column named content, aliased if needed, and adding a title column improves embedding quality for RETRIEVAL_DOCUMENT. And task_type matters: use RETRIEVAL_DOCUMENT for the corpus and RETRIEVAL_QUERY for the user's question, since asymmetric embeddings noticeably help retrieval quality.

The resulting table holds your pass-through columns and adds three generated ones. embedding holds the vector, statistics is a JSON value with token_count and truncated, and status reports errors. Note that the newer AI.GENERATE_EMBEDDING returns embedding, and not the ml_generate_embedding_result seen in older ML.GENERATE_EMBEDDING guides. Mixing the two layouts is a classic copy-paste failure.

Pitfall one: silent partial failure. A batch job can succeed overall while individual rows fail because of quota pressure or service hiccups. BigQuery reports this per row in status, where an empty string means success, so always filter with WHERE status = ''. If parallel batches hit resource-exhausted errors, Google provides remote-inference retry scripts and a Dataform package that iterate until every row is done.

Pitfall two: silent truncation. Each row's statistics shows its token count and a truncated flag. A news article is a couple of hundred tokens, but documents longer than the model's input limit get embedded in truncated form without any error. For long documents, monitor the truncated flag and chunk beforehand.

Step 2: add an index, optionally

Small corpora can skip it, because VECTOR_SEARCH falls back to exact brute-force search. At scale, build one:

CREATE OR REPLACE VECTOR INDEX news_idx
ON `rag_demo_us.news_emb`(embedding)
OPTIONS (index_type = 'IVF', distance_type = 'COSINE');

Some behaviours to expect: maintenance of the index is handled for you in the background, rows added later can be searched at once (BigQuery scans the unindexed remainder by brute force), tables below 10 MB get no populated index, and the Standard edition doesn't support indexes.

Step 3: retrieval and generation in one query

Embed the question, fetch the top five articles, ground the prompt and produce a typed answer:

SELECT
  AI.GENERATE(
    (
      'Answer using only this context:\n',
      STRING_AGG(base.content, '\n---\n'),
      '\nQuestion: what were the major browser security stories?'
    ),
    endpoint      => 'gemini-2.5-flash',
    output_schema => 'answer STRING, confidence FLOAT64, sources ARRAY<STRING>'
  ).*
FROM VECTOR_SEARCH(
  TABLE `rag_demo_us.news_emb`, 'embedding',
  (
    SELECT embedding
    FROM AI.GENERATE_EMBEDDING(
      MODEL `rag_demo_us.emb_model`,
      (SELECT 'major browser security stories' AS content),
      STRUCT('RETRIEVAL_QUERY' AS task_type)
    )
  ),
  top_k => 5
);

The core pipeline is about a dozen lines, with no LangChain, no external vector store and nothing to deploy. And output_schema yields typed columns for the answer, a confidence score and sources, not a JSON blob you have to parse and hope about.

The limitation of the one-shot version is a serious one, though. Data doesn't hold still. New articles arrive, descriptions change and tickets get updated. Every insert or edit means re-running the embedding statement, detecting deltas, handling retries and watching for staleness.

Path two: a corpus that maintains itself (preview)

Autonomous embedding generation is the answer. Declare the embedding as a generated column and BigQuery takes over keeping it up to date:

CREATE TABLE `rag_demo_us.news_live` (
  title STRING,
  body  STRING,
  body_embedding STRUCT<result ARRAY<FLOAT64>, status STRING>
    GENERATED ALWAYS AS (
      AI.EMBED(
        body,
        connection_id => 'us.my_connection',
        endpoint      => 'gemini-embedding-001')
    ) STORED OPTIONS (asynchronous = TRUE)
);

Then insert data from anywhere:

INSERT INTO `rag_demo_us.news_live` (title, body)
SELECT title, body
FROM `bigquery-public-data.bbc_news.fulltext`
WHERE category = 'tech';

The design is worth reading twice. With GENERATED ALWAYS AS AI.EMBED(...), the embedding becomes table metadata and not pipeline code. Insert or update the source column and BigQuery creates or refreshes the vector in the background. With asynchronous = TRUE, inserts don't wait on model calls, and the embedding work runs on background slots whose jobs show a gc_ prefix in job history. The column is a STRUCT<result, status>, so failures show up per row and aren't swallowed.

On model choice, the example uses gemini-embedding-001 because it was generally available and strong on the multilingual MTEB leaderboard at the time. The documentation's canonical example uses text-embedding-005, which works equally well. Plan for one consequence: the default output is 3072 dimensions against 768 for the text-embedding models, and that pushes up storage and index expense. To avoid it, ask for a smaller size through model_params on AI.EMBED. The model is trained Matryoshka-style, so it truncates cleanly to 768 or 256.

Querying gets easier as well. AI.SEARCH looks up which model the table was built with and embeds the search string for you:

SELECT base.title, base.body, distance
FROM AI.SEARCH(
  TABLE `rag_demo_us.news_live`, 'body',
  'major browser security stories',
  top_k => 5
);

Text is searched against a text column, and the warehouse deals with vectors behind the scenes. Vector indexes are supported here too: build one on the source column, and training starts once 80% of rows hold embeddings.

For anything headed to production, this is the version that matters. The one-shot approach is a demo, whereas a self-maintaining corpus is a design you can run for years.

One label applies, though: this part is in preview, under pre-GA terms that may change. Also keep in mind that each table gets one auto-generated embedding column, that inserts should be batched and not trickled in as frequent DML because concurrent DML can delay generation, and that copies, clones and snapshots carry the data without the generation setup. Release notes say you can enable it on new or existing tables through CREATE TABLE or ALTER TABLE. The BigQuery release notes are the authoritative source for feature status, so check them before you build.

What you get without extra work

This is the part data teams should care about most, and it gets the least attention.

• Row-level and column-level security carry over to VECTOR_SEARCH. A row a user isn't allowed to see never reaches the prompt as RAG context. Reproducing that guarantee through a synced outside vector database is difficult.
• IAM is the only authentication model. There are no keys in environment variables and no rotation runbooks for the pipeline.
• Audit logs, cost attribution and quotas all come from infrastructure your platform team already runs. You can even trace per-job charges in Cloud Billing through the bigquery_job_id_prefix label.
• No sync job to run. Vectors sit next to the rows they describe, so a vector store can't drift from the warehouse when there's only one store. With autonomous embeddings, no refresh job needs an owner either.

The best infrastructure is the kind you never deploy.

Where it breaks

1. Billing is by token, and Gemini 2.5 bills thinking as well. Those models charge for reasoning tokens in addition to output. For classification-type jobs, setting the thinking budget to zero in model_params reduces both latency and spend. Skip that step and batch costs climb without notice. Use AI.COUNT_TOKENS to estimate first.
2. Complex queries may feed the model a different number of rows than you expect. JOINs and ORDER BY with LIMIT are the usual causes. Google suggests first writing the input to a table, then running inference over that table.
3. Quotas are real. Embedding throughput is limited by tokens-per-minute quotas, a few million by default. That suits most workloads, but a plan to embed an entire data lake overnight needs preparation, or Provisioned Throughput through request_type => 'dedicated'.
4. VECTOR_SEARCH is charged by bytes scanned. On-demand pricing counts the base table, the index and the query. Put STORING columns in the index so you needn't join back to the base table, and filter early with WHERE on those stored columns.
5. Nothing versions your prompts. A prompt is simply a string inside SQL, so there's no registry, A/B test or evaluation harness. Teams that rework a production assistant's prompts every week feel this, and that's where a small Python layer, or dbt holding prompts as versioned config, pays off.

Check the status label

Feature status changes, so verify it. As originally reported, AI.GENERATE and AI.GENERATE_TABLE reached general availability in late January 2026, and the partner models (Claude, Llama, Mistral) went generally available in the first half of 2025. Preview features such as autonomous embeddings may have moved since then, and the release notes are the place to confirm.

Is SQL really all you need?

Three conclusions come out of this.

For batch and analytical RAG, it's worth serious consideration. Document classification, semantic deduplication, enrichment pipelines and internal Q&A over governed data all favour the warehouse-native approach on governance, simplicity and total cost, and you keep your choice of model.

A corpus that changes constantly is a different case, and autonomous embeddings shift the balance. Search was never the difficult part of production RAG. Keeping vectors consistent with current data was, and a generated column eliminates that entire category of work.

The skill is knowing where the boundary lies. Sub-second user-facing chat still wants a dedicated serving layer, prompt lifecycle still wants versioning and cost per token still wants monitoring. Those constraints didn't vanish. They moved.

For further reading, Google's BigQuery documentation covers the generative AI overview and model support matrix, CREATE MODEL for partner and open models, AI.GENERATE, AI.GENERATE_EMBEDDING, AI.SEARCH, autonomous embedding generation, search functions, vector index management and the release notes.