Build Warehouse-Native Retrieval Without New Infrastructure

Tier: Core · Workflow area: RAG data pipelines

The warehouse vendors noticed: Snowflake Cortex Search, Databricks Mosaic AI Vector Search, and BigQuery VECTOR_SEARCH over AI.EMBED output let you serve retrieval where the data already sits — inheriting the governance, tenancy, and ops you have. This playbook decides when warehouse-native wins, builds the retrieval path in SQL-adjacent tooling, and evaluates it with the same golden-set harness as every other playbook so the decision is measured, not ideological.

When to reach for this

• •
  The corpus already lands in the warehouse and data-governance gravity (masking policies, row access, audit) is strong.
• •
  The team is warehouse-fluent and thin on service-ops capacity — no one wants to run a Qdrant cluster.
• •
  pb-31's decision framework pointed warehouse-native and you now need the implementation and its eval.
• •
  A dedicated vector DB exists but syncing governed warehouse data into it keeps failing security review.

The workflow

1. 1.

   Confirm the fit honestly. Warehouse-native wins on governance inheritance, zero new infra, and pipeline adjacency (dbt models feed the index directly). It concedes on query latency floors (warehouse serving layers are typically tens-to-hundreds of ms and per-query cost is metered), exotic index tuning (pb-32 knobs are mostly managed away), and portability. If p95 retrieval under ~50 ms or fine HNSW control is a hard requirement, go back to pb-31's dedicated-engine branch. Record the call.

2. 2.

   Prototype the shape locally first. DuckDB's vss extension gives a laptop-scale rehearsal of "vectors as a table column + SQL search" so the pipeline design is settled before burning warehouse credits:

   Copy

   INSTALL vss; LOAD vss;
   CREATE TABLE chunks (chunk_id TEXT, doc_id TEXT, content TEXT, embedding FLOAT[1024]);
   -- embeddings from sentence-transformers, per pb-30
   CREATE INDEX idx ON chunks USING HNSW (embedding);
   SELECT chunk_id, doc_id FROM chunks
   ORDER BY array_distance(embedding, $query_vec::FLOAT[1024]) LIMIT 10;
   

3. 3.

   Build the managed path on your warehouse. The three equivalents, each fed by a governed table of parsed chunks (pb-27 output as a dbt model):

   • •
     Snowflake Cortex Search: a managed hybrid (semantic + keyword) service over a table — chunk, create service, query; embedding and fusion are handled for you, which folds pb-33's job into one service definition.
   • •
     BigQuery: explicit and SQL-native — AI.EMBED (or AI.GENERATE_EMBEDDING) populates a vector column, CREATE VECTOR INDEX builds ANN, VECTOR_SEARCH queries it:

   Copy

   CREATE VECTOR INDEX chunk_idx ON rag.chunks(embedding)
     OPTIONS (index_type = 'IVF', distance_type = 'COSINE');
   
   SELECT base.chunk_id, base.doc_id, distance
   FROM VECTOR_SEARCH(TABLE rag.chunks, 'embedding',
        (SELECT AI.EMBED('what is the late-delivery penalty?') AS embedding),
        top_k => 10);
   

   • •
     Databricks Mosaic AI Vector Search: a Delta Sync index over a Delta table — the index tracks table changes automatically, which is pb-36's CDC sync as a managed feature; built-in retrieval-quality evaluation hooks cover part of pb-37.
4. 4.

   Wire freshness the warehouse way. Point the index at the table your ingestion dbt job maintains: Delta Sync (Databricks) and Cortex Search's target-lag setting handle incremental refresh declaratively; on BigQuery, schedule index refresh alongside the ingestion job. Then verify it: apply a source edit and measure minutes until the new content retrieves — this is pb-36's freshness SLA, now a configuration assertion instead of a pipeline you wrote.

5. 5.

   Evaluate with the same golden set — the metric layer. The pb-28 harness ports directly: run the 30–50 golden questions through the warehouse retrieval path and compute recall@10, plus p95 query latency and cost per 1K queries from the warehouse's own metering. The decision-grade output is a three-column comparison:

   Copy

   Expected shape:
   local pgvector   : recall@10 0.84 · p95 45ms  · infra you run
   warehouse-native : recall@10 0.83 · p95 210ms · $X per 1K queries, zero new infra
   

   Near-parity recall with governance inherited for free is the common result — and exactly the evidence that closes the "but shouldn't we run a real vector DB?" debate one way or the other.

6. 6.

   Verify governance inheritance — the reason you chose this path. Confirm row-access policies and masking on the chunk table actually constrain retrieval results per caller (run pb-38's leakage assertions through the warehouse path), and that retrieval queries appear in the warehouse audit log like any other query. If the serving layer bypasses table policies (read the vendor's fine print for service-account patterns), you have lost the main benefit — fix the service's query context before go-live.

Verify before you trust it

• Recall@10 paritywithin ~2 points of your best dedicated-engine baseline on the identical golden set; a larger gap usually traces to the managed embedding model differing from your pb-29 winner — check which model the service used.
• Freshness assertion measuredsource edit to retrievable in under the stated target lag.
• Governance test greenmasked columns stay masked in retrieved payloads, row-access policies filter results per caller, and the leakage suite passes through the warehouse endpoint.
• Cost model validated at realistic volumerun a day of replayed production-like queries and reconcile metered spend against the projection — per-query serving costs are where warehouse-native surprises teams.

Pitfalls

• Assuming warehouse governance applies automatically. Serving services sometimes query as a privileged principal; policies you rely on may not bind. Test enforcement empirically (step 6), never from the architecture diagram.
• Ignoring per-query economics. A dedicated vector DB is mostly fixed-cost; warehouse serving is metered. At high QPS the cost curves cross — model both before committing a user-facing workload.
• Using the bundled embedding model unexamined. Managed services default to their own embeddings; if pb-29 crowned a different model, check the service supports bringing it, or re-run the benchmark on the bundled one before accepting the default.
• Rebuilding pb-33/pb-36 by hand on top of managed features. Cortex Search already does hybrid; Delta Sync already does incremental refresh. Duplicating them in custom code doubles the failure surface.
• Warehouse latency in an interactive path. Hundreds of ms is fine for an internal assistant, painful inside a chat product's tight loop. Match the serving layer to the latency budget, not to team convenience alone.

Tools

• Snowflake Cortex Searchmanaged hybrid retrieval service over governed tables; 30-day trial credits cover the lab.
• Databricks Mosaic AI Vector SearchDelta Sync indexes with built-in retrieval-quality eval; Databricks Free Edition for practice.
• BigQuery VECTOR_SEARCH + AI.EMBEDfully SQL-native path; the no-card sandbox tier makes it the most accessible for self-study.

Client-safe variant

Warehouse-native is often the most client-friendly retrieval architecture: data never leaves the client's existing cloud contract, their masking and row-access policies keep working, and there is no new vendor to push through procurement — lead with that in the proposal. The obligations that remain are cost and verification: agree the per-query cost model in writing (metered serving on client credits can surprise a client who expected flat infrastructure cost), run the step-6 governance tests with the client's security team witnessing, and deliver the recall/latency/cost comparison table so the architecture choice is documented as measured rather than assumed. Prototype on your own trial account or the BigQuery sandbox with synthetic documents; touch client tables only after access is formally granted through their standard warehouse roles.