← Blog
9 min read · Jul 03, 2026

Building a Confluence RAG Pipeline Without Leaving BigQuery

A walkthrough of turning a Confluence space into a queryable, cited knowledge base using Cloud Composer, BigQuery's native AI functions, and Gemini — including the silent failures that almost made it into production.

Building a Confluence RAG Pipeline Without Leaving BigQuery

A walkthrough of turning a Confluence space into a queryable, cited knowledge base using Cloud Composer, BigQuery's native AI functions, and Gemini — including the silent failures that almost made it into production.

The problem

Every team's real documentation lives in two places: the wiki nobody fully trusts, and the heads of the three people who've been there the longest. Confluence is usually the former. It's got the runbooks, the architecture decisions, the "why did we do it this way" pages — and it's a nightmare to search once a space passes a few hundred pages. Keyword search finds the page with the right words in it, not the page with the right answer.

RAG fixes that mismatch, in theory. In practice, the moment you say "let's build a RAG pipeline," most tutorials point you at four new pieces of infrastructure: a document parser, a chunker, a vector database, and a serving layer. If you're already sitting on BigQuery and Cloud Composer, that's three services you don't need.

This is the version I ended up building — no dedicated vector database, no separate embedding service, just Confluence in, SQL in the middle, Gemini out.

Two ways to build this

There's a fast path and a control path.

The fast path is Vertex AI Search (currently mid-rename to "Agent Search"). It ships a native Confluence connector — point it at a space, authenticate, and it handles crawling, chunking, and embedding for you. I tried this first. It works, right up until you try to query the data store you just built: a data store created through the Confluence connector can't be wired into a plain custom search app. Google currently routes connector-based data stores through the full Agentspace/Agent Platform product tier, which is a heavier product — and a heavier bill — than "give me search over one wiki space." If your org already has that tier provisioned, this is genuinely the fastest option. If you don't, it's a surprising wall to hit after the ingestion already succeeded.

The control path is everything below: build the pipeline yourself, on infrastructure you already run.

Why I went all-in on BigQuery

BigQuery picked up native vector search a while back — CREATE VECTOR INDEX, VECTOR_SEARCH, and a pair of functions, AI.GENERATE_EMBEDDING and AI.GENERATE_TEXT, that call Vertex AI models through a remote connection without you ever touching a Python SDK. Put together, that's embedding, indexing, retrieval, and generation, all expressible as SQL against a table you already own. For anyone who's spent years in BigQuery, that's a much smaller mental model than "stand up a vector DB and a model-serving container."

The architecture ends up in two halves:

  • Ingestion (batch, scheduled): Confluence → Cloud Composer DAG → GCS landing → chunked table in BigQuery → embedded → indexed.
  • Retrieval (real-time, per query): user question → embedded the same way → VECTOR_SEARCH for top-k chunks → AI.GENERATE_TEXT (or a direct Gemini call) produces a cited answer.

Step 1: getting pages out of Confluence

Confluence Cloud's REST API supports CQL (Confluence Query Language), which is the right tool for incremental sync — don't re-pull the whole space every run.

import requests

def fetch_updated_pages(space_key: str, since_iso: str, session: requests.Session):
    cql = f'space="{space_key}" and lastmodified>="{since_iso}"'
    params = {"cql": cql, "expand": "body.storage,version", "limit": 50}
    url = "https://your-domain.atlassian.net/wiki/rest/api/content/search"

    while url:
        resp = session.get(url, params=params)
        resp.raise_for_status()
        data = resp.json()
        yield from data["results"]
        url = data.get("_links", {}).get("next")
        params = {}  # next link already has the query string baked in

The watermark (since_iso) needs to live somewhere durable — I keep it in a one-row-per-space BigQuery control table and update it only after a run completes successfully, the same pattern you'd use for any freshness-tracked ingestion job. Trusting Confluence's own "recently updated" view instead of your own watermark is how you end up silently missing pages during a retry.

First real error, on run two: a 429 with a Retry-After header I wasn't reading. Confluence Cloud rate-limits aggressively on paginated CQL calls, and the naive retry loop I'd written just hammered the endpoint again immediately. Fix was boring — respect Retry-After, cap concurrent space pulls to one at a time during backfill.

Step 2: the XHTML you didn't ask for

Confluence doesn't give you clean text. It gives you "storage format" — XHTML wrapped in Confluence-specific macros:

<ac:structured-macro ac:name="expand">
  <ac:parameter ac:name="title">Rollback procedure</ac:parameter>
  <ac:rich-text-body>
    <p>Run the following before paging anyone...</p>
  </ac:rich-text-body>
</ac:structured-macro>

Embed that directly and your vectors are encoding ac:structured-macro and ac:rich-text-body as if they were meaningful content, because as far as the embedding model is concerned, they are. The first batch of chunks I generated had noticeably worse retrieval quality than expected, and it took pulling actual chunk text to see why — a good third of the token budget on macro-heavy pages was going to markup, not prose.

The fix is a normalization pass before chunking:

from bs4 import BeautifulSoup

def clean_storage_format(html: str) -> str:
    soup = BeautifulSoup(html, "lxml")
    for macro in soup.find_all("ac:structured-macro"):
        # keep the rich-text body content, drop the macro wrapper
        body = macro.find("ac:rich-text-body")
        if body:
            macro.replace_with(body)
        else:
            macro.decompose()  # code macros, TOC macros, etc.
    return soup.get_text(separator="\n", strip=True)

Panels, expand sections, and code macros need different handling — code blocks especially are worth keeping as fenced blocks rather than flattening to plain text, since "run kubectl rollout restart" reads very differently from a wall of text with the command buried in it.

Step 3: chunking by structure, not tokens

Confluence pages already have a heading hierarchy. Chunking along h1/h2/h3 boundaries instead of a fixed token window keeps each chunk semantically coherent, and lets you carry space → page → heading through as metadata — which is what makes the final answer's citations useful instead of just "source: some_page.html".

def chunk_by_headings(page_title, breadcrumb, sections, max_tokens=600, overlap_pct=0.12):
    chunks = []
    for heading, body_text in sections:
        for piece in split_on_token_budget(body_text, max_tokens, overlap_pct):
            chunks.append({
                "page_title": page_title,
                "breadcrumb": f"{breadcrumb} > {heading}",
                "chunk_text": piece,
            })
    return chunks

600 tokens with roughly 12% overlap was a reasonable starting point; pages with very short sections (FAQ-style content) sometimes needed a minimum chunk size to avoid embedding six-word fragments that add noise without adding signal.

Step 4: embedding without leaving SQL

With chunks loaded into a staging table, AI.GENERATE_EMBEDDING handles the embedding step via a remote connection to a Vertex AI text embedding model:

CREATE OR REPLACE TABLE `my_project.rag.confluence_chunks_embedded` AS
SELECT
  chunk_id,
  page_title,
  breadcrumb,
  chunk_text,
  ml_generate_embedding_result AS embedding
FROM ML.GENERATE_EMBEDDING(
  MODEL `my_project.rag.embedding_model`,
  TABLE `my_project.rag.confluence_chunks_staging`
);

First attempt at this failed with:

Access Denied: BigQuery BigQuery: Permission denied to use connection
'my_project.us.confluence-embed-conn'. Grant the caller the
BigQuery Connection User role (roles/bigquery.connectionUser)
on the connection.

Creating the remote model and having permission to query through it are two different IAM surfaces — the connection's service account needs Vertex AI access, and the calling identity (in this case, the Composer service account running the DAG) separately needs roles/bigquery.connectionUser on the connection itself. Easy to miss because the CREATE MODEL step succeeds fine; it's only the first query against it that surfaces the gap.

Step 5: building the vector index (and waiting for it)

CREATE OR REPLACE VECTOR INDEX confluence_chunk_idx
ON `my_project.rag.confluence_chunks_embedded`(embedding)
OPTIONS(
  index_type = 'IVF',
  distance_type = 'COSINE',
  ivf_options = '{"num_lists":500}'
);

This is the gotcha I'd flag loudest: index creation returns almost immediately, but the index itself populates asynchronously in the background — it can take a couple of minutes before it's actually usable. Query against it too early and BigQuery doesn't error. It just quietly falls back to brute-force search, which returns correct results at higher cost and, at real scale, higher latency. Nothing tells you this happened unless you go looking.

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

I added a Composer sensor task that polls this view and blocks downstream retrieval traffic until coverage_percentage is meaningfully above zero — better than finding out via a latency spike days later that the index silently stopped being used after a large batch load.

Step 6: retrieval and generation, also in SQL

Two options here, and I ended up trying the more surprising one first.

Option A — orchestrate from application code: VECTOR_SEARCH for top-k chunks, then hand those chunks to Gemini via a normal API call from a Cloud Run service.

Option B — do the whole thing in one query:

SELECT AI.GENERATE_TEXT(
  MODEL `my_project.rag.gemini_model`,
  (
    SELECT STRING_AGG(
      FORMAT('[%s > %s]: %s', page_title, breadcrumb, chunk_text), '\n\n'
    )
    FROM VECTOR_SEARCH(
      TABLE `my_project.rag.confluence_chunks_embedded`,
      'embedding',
      (SELECT ml_generate_embedding_result
       FROM ML.GENERATE_EMBEDDING(MODEL `my_project.rag.embedding_model`,
         (SELECT @user_question AS content))),
      top_k => 5
    )
  ),
  STRUCT('Answer using only the provided context. Cite the page title for each claim.' AS prompt)
) AS answer;

Option B is one round trip, no separate serving container, and the entire pipeline stays inspectable as SQL — which matters a lot when you need to explain to someone else why the bot said what it said. The tradeoff is less control over prompt construction and streaming than you'd get calling Gemini directly, so for a chat-style interface I still front it with a small Cloud Run layer; for anything batch or Slack-bot shaped, Option B alone is enough.

Step 7: wiring it all together with Composer

The DAG shape is unremarkable, which is the point — it's the same extract → land → transform → load pattern as any other ingestion pipeline, just with an embedding and an index-refresh task added at the end:

with DAG("confluence_rag_sync", schedule="@daily", ...) as dag:
    extract = PythonOperator(task_id="extract_confluence", python_callable=fetch_and_land)
    clean_chunk = PythonOperator(task_id="clean_and_chunk", python_callable=clean_and_chunk_pages)
    load_staging = GCSToBigQueryOperator(task_id="load_staging", ...)
    embed = BigQueryInsertJobOperator(task_id="generate_embeddings", configuration={...})
    refresh_index = BigQueryInsertJobOperator(task_id="refresh_vector_index", configuration={...})
    wait_for_index = BigQuerySensor(task_id="wait_for_index_coverage", ...)

    extract >> clean_chunk >> load_staging >> embed >> refresh_index >> wait_for_index

Nothing exotic — the value of doing this in Composer instead of a separate scheduler is that it lives next to every other ingestion DAG you already monitor, with the same alerting and retry semantics.

A note on permissions

Confluence spaces usually aren't uniformly readable — some are team-restricted, some have page-level permissions layered on top. If the RAG answer needs to respect that, don't skip this: either carry a permission/space identifier into every chunk and apply it as a VECTOR_SEARCH pre-filter (BigQuery row-level access policies work well here, since they apply automatically regardless of which query hits the table), or take the simpler route of only indexing spaces that are org-wide readable in the first place. Building the retrieval layer first and bolting access control on afterward is the wrong order — chunks from a restricted space will happily show up in someone else's answer if you don't filter at query time.

What I'd do differently

Two things, in hindsight. First, I'd add the INFORMATION_SCHEMA.VECTOR_INDEXES health check from day one instead of after noticing a latency regression — the silent brute-force fallback is the kind of bug that doesn't announce itself. Second, I'd store the macro-stripped and the raw storage-format text side by side; regenerating chunks after tweaking the macro-stripping logic meant re-fetching pages I already had, when I could have just re-run the transform.

Closing thoughts

None of this is exotic technology — it's the same extract/transform/load shape as any data pipeline, with an embedding call and a similarity search bolted on. The part worth internalizing is that if BigQuery is already your warehouse, RAG doesn't have to mean adding a vector database to your architecture diagram. It can mean adding two columns and a function call to a table you already have.